Re: guideline for creating temporary (working) table

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0308281510.635a9897_at_posting.google.com>...
> andyho99_at_yahoo.com (Andrew) wrote in message news:<8882aa3c.0308280641.1e571283_at_posting.google.com>...
> > joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0308271236.51a741f3_at_posting.google.com>...
> > > 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
> > >
> > > They may need to be involved if you are using the direct option of the
> > > loader, since if anything goes wrong, everything goes wrong.
> > >
> >
> > One of the previous responses had provided a good solution. DBA may
> > provide a truncate routine. Both security and flexibility are being
> > addressed. I still think it's silly we can not use truncate command
> > against a temp table while I know the best the purpose of this (big)
> > temp table. I don't think you read my posting carefully enough. I load
> > data to TEMP tables using SQL*Loader.
>
> Probably didn't read it closely enough :-)
>
> If you have to keep them around for a while, are they really temp?
> One might think of a temp file as one that doesn't outlast a session.

Sorry, STAGING TABLES should be more proper words. Oracle does provide a way to create TEMP tables. The contents would be deleted at end of session or after commit.

>
> I agree with you. You (well, your program) are doing some admin
> tasks, so need some admin privileges. The DBA ought to know the
> advantages of truncate over delete for this case, and I'm wondering
> why partitioning isn't mentioned.
>
> >
> > > > program. I think SOME of DBAs are lack of development experience.
> > >
> > > So? Now, having done both dev and dba, I may be more open to
> > > develop-think than some, but I don't think it should be a requirement
> > > for a production DBA. At least, as long as there is a development DBA
> > > or super-developer equivalent available. A production DBA _should_ be
> > > conservative and protective of the db, and a development DBA _should_
> > > be willing to try new things.
> > >
> > > Personally, I think developers should be allowed a "wild-west"
> > > environment, then there should be a defined process of bringing
> > > development to production. That way, if some new way of doing things
> > > has value, it can be explicated in the process, and then implemented
> > > if useful. This is especially useful for bringing in new features due
> > > to upgrades.
> > >
> > > > Sometimes it surprised me that they don't know fetch across commit...
> > >
> > > Is that something you should be doing?
> >
> > Do you think you should tell developer to commit inside the cursor
> > loop after each insert while you specify FOR UPDATE on cursor
> > declaration?
>
> I think we should slice and dice Kyte's book and inject it directly
> into our brains. :-)
>
> jg
Received on Fri Aug 29 2003 - 08:40:47 CDT