Wednesday, May 23, 2007

ORACLE - No Table Owner in Indexfile

I was trying to use an indexfile generated from Oracle's "imp" command to create the indexes on a database after the table rows were imported but I ran into a problem with the way the table names were specified in the "on" clause of the index create statements. They were in the form tablename instead of owner.tablename. This was giving me:

ORA-00942: table or view does not exist

The default indexfile has "connect username;" statements in it, but these statements prompt a request for a password, so that wouldn't work for me.

In order to solve this problem I set the "current_schema" in the indexfile script to the correct table owner whenever the owner changed for a group of index creation statements.

Here's the statement that I used:

alter session set current_schema = owner;

I had to manually put the statements in the script, but it was a lot faster for me to do it that way than trying to come up with a script to do the same thing.

I'll meet you further on up the road.

Jim

No comments: