Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: guideline for creating temporary (working) table

Re: guideline for creating temporary (working) table

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 27 Aug 2003 06:40:47 -0700
Message-ID: <2687bb95.0308270540.3e5480d8@posting.google.com>


"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<vkoqdmcib1qjab_at_corp.supernews.com>...
> "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.

I disagree with option 2 since we have only a one-sided partial account of the problem. The rules as described here are in accordance with the policies we enforce, and meet outside auditor requirements. Developers are not allowed access to the production owner accounts, nor should developers normally need such access.

We do allow sqlldr to run in production batch jobs. The userid and password are pulled from the environemt using environmental variables set up by the job manager. Developers are unaware of the id/password being used. We also provide a truncate routine that production jobs can call to truncate tables owned by a different user than what the job runs under.

For that matter we have setup ID/passwords for use with desktop applications that have access to only a limited number of tables. Perhaps your DBA is being unflexible. You could request the truncate routine. Writing and testing such a routine with edit checks against a list of authorized to truncate tables only takes about an hour. That would help you some.

HTH -- Mark D Powell -- Received on Wed Aug 27 2003 - 08:40:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US