guideline for creating temporary (working) table

Hi,

I need to do bulk inserts to Oracle tables from a flat file. The flat file contains one parent record type (for parent table) and several child record types (for child tables). The file layout is arranged as one parent record type followed by one or more child record types. I use SQL*Loader direct load to temporary (working) tables and run a pl/sql to populate them to the target tables. The reason that I load it to working tables first since the sequence number on child tables is derived from parent table. The sequence in parent table is generated by Sequence object during load.

Anyway, DBA would not allow me to use SQL*Loader to load data to the mission critical tables directly. I can buy that. However, there is another rule bothered me. That is the Oracle account I use is not allowed to create/truncate tables. DBA will create these temporary tables for me. I only can use DELETE command to delete all rows on these temporary tables (this is a routine job and I need to keep the temp tables for possible analysis before next run). The problem is some of these temp tables have couple million rows. I encountered rollback segment problem during testing. I can get around this by deleting a chunk at a time.

Now (sorry for the long background), is it worth to make thing so complicated? We are in a typical setup. The DBA privilege account OWNS the objects and all application programs run by another account. This account accesses synonyms that point to the target objects. I don't see any harm to truncate table in application program account. Am I right or the DBA is over protected the system? Thanks for your opinion. Received on Wed Aug 27 2003 - 00:00:34 CDT