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: Andrew <andyho99_at_yahoo.com>
Date: 28 Aug 2003 07:41:26 -0700
Message-ID: <8882aa3c.0308280641.1e571283@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.

> > 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?
>
> > > 2 You escalate this issue and have the DBA replaced.
> > This is beyond my control.
>
> See http://bofh.ntk.net/Bastard.html :-)
>
> jg
Received on Thu Aug 28 2003 - 09:41:26 CDT

Original text of this message

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