andyho99_at_yahoo.com (Andrew) wrote in message news:<8882aa3c.0308270645.30cc3612_at_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
> You are kidding. They are DBAs and they never run any APPLICATION
> program. I think SOME of DBAs are lack of development experience.
> Sometimes it surprised me that they don't know fetch across commit...
> > 2 You escalate this issue and have the DBA replaced.
> This is beyond my control.
>
> Thanks for your advice.
Maybe you could ask the DBA to give your application login it's own
scratchpad or user tablespace. The DBA could grant resource and
create table privs to that tablespace only and the DBA could size the
tablespace to fit your needs. Sounds like logging could be turned off
also if you have the ability to start your load over should it fail.
We have lots of these types of tablespaces that are used to capture
the bulk data coming into the system, clean it up, and then insert it
into the production tables via synonyms. To make things safe, they
are intentionally seperated from the other production tables and
tablespaces so if we have an unexpected large batch, only the process
doing the load is effected by it filling up. Normally, we keep the
tables there permanently since every day we need to load many batches
into them.
Received on Wed Aug 27 2003 - 13:32:17 CDT
