Re: guideline for creating temporary (working) table

andyho99_at_yahoo.com (Andrew) 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.

Ive been doing alot of work with bulk loads. The best way Ive found to handle this is the following:

1. Seperate tablespace and user account for your 'staging tables'. You can do whatever you want here. Reason for not having truncate in production is because you might truncate the wrong table. Its for your protection. Less for you to have to worry about. Well you dont care what you do in your staging tablespace?
2. Put it on a seperate mount point. Speeds up data loads. This isnt always an option.
3. Then copy over.
4. If you have rollback segment problems your DBA should take care of this immediately and make your rollbacks larger. If he doesnt, he is not doing his job.

This is standard and generic data publication. Your DBA 'should' know this. Doesnt sound like your DBA is development friendly. Its stupid to not have priviledges on your own account on your own tablespace.

BTW, check out dba_tablespaces and see if he using locally managed tablespaces.
Im willing to bet he isnt. Its probably why he is under the impression that you cant create tables due to the fact that he wants very specific settings. Received on Thu Aug 28 2003 - 09:22:14 CDT