Re: guideline for creating temporary (working) table

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.

> 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?

> > 2 You escalate this issue and have the DBA replaced.
> This is beyond my control.

See http://bofh.ntk.net/Bastard.html :-)

jg

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