Re: guideline for creating temporary (working) table

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.

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

--
@home.com is bogus
http://www.signonsandiego.com/news/uniontrib/thu/business/news_1b28music.html