SQL*Loader - DIRECT=TRUE Problem

[OzWolf]

Hi,

I'm relatively new to the Oracle world, having only recently started using Oracle as a reporting warehouse for extracted data from our Ingres billing database.

I have run into a slight problem with SQL*Loader. It may be a restriction of the tool, it may not. Anyway, here goes:

We have a control script. A central script that looks for the existence of data files and calls specific load scripts if that file exists. The scripts that are called utilise two schemas (A and B for arguments sake). So, a user A and a user B exist. However, there is a restriction that user A can only see schema A and B can only see schema B. In steps user C, who has overreaching access to both schemas (as well as their own schema C) for admin/dataloads.

Some of the data is pretty hefty that we are loading and there is a requirement for three tables to be IOTs. Because of this, we can't up the ROWS value in SQL*Loader. Due to time constraints, the committing after each 64 rows is pushing us dangerously close to cut-off. Tests with using the DIRECT=TRUE flag showed favoured results in time, but we discovered that user C cannot load the files because SQL*Loader can no longer cross schemas. Synonyms don't work. The whole point of using C was so as we didn't need to code multiple username password combinations into the control script (and people that shouldn't access a certain schema couldn't come in a get the username/password combo from the script). The username and password of user C were passed to the control script as parameters.

Tests with external tables failed due to running out of undo tablespace and until we can convince management here that the box needs more hardware, there isn't an option to extend that area.

Any suggestions on how we can use the DIRECT=TRUE flag and cross schemas would be greatly appreciated.

 

[LKBrwnDBA]

Maybe this can help:

http://www.catb.org/~esr/faqs/smart-questions.html

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[OzWolf]

I'm not one on subtlety, so I would have preferred if you simply stated that my question made no sense...please reword it. Early morning questions should be avoided at all costs...hehe.

So, what I am trying to find out is if it is possible to do a direct load into an Oracle database using SQL*Loader by one user into another schema:

eg. User A runs SQL*Loader with the DIRECT flag set to TRUE and I want the data to go into schema B.

If I explicity state the table as B.table_name, SQL*Loader returns an error that it cannot locate the table. If I use synonyms in schema A, it returns the same error.

Is there a way around this?

 

[HimanB]

Hi,
This is a Bug.Refer to BUG:2341550 at Metalink.

The workaround for this are:
Create a public synonym for the table to be inserted.(For 9i this workaround may not work)

HTH
Regards
Himanshu

 

[OzWolf]

Cheers HimanB. Tried the synonym approach with no luck. Have scrapped the notion of an over-reaching user to call sqlldr (still used to run PL/SQL functions though). Instead, we have have hard-coded the usernames and passwords for the individual schemas into our central control script. Then we locked it up tight so only the development staff can read it and for good measure, we encrypted it (well, I didn't...our computer tech staff did).

Wasn't the approach we wanted but gets us the result we do want.