Re: guideline for creating temporary (working) table

"Andrew" <andyho99_at_yahoo.com> wrote in message news:8882aa3c.0308262100.1deb3fee_at_posting.google.com...
> 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.

You have 2 alternatives, I think
1 You make the DBA responsible for running your programs 2 You escalate this issue and have the DBA replaced.

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address