CODEWITHSUNDEEP
oracle-databaseoracle11gsql-loader
Lawrence Tierney
Lawrence Tierney

Reputation: 898

Oracle catldr.sql multiple errors

I'm preparing my new Oracle 11g install for "Direct" SQL*Loader operation. As per the documentation here:

http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1007669

To prepare the database for direct path loads, you must run the setup script, catldr.sql, to create the necessary views. You need only run this script once for each database you plan to do direct loads to.

So I execute the following sql script:

$ORACLE_HOME/rdbms/admin/catldr.sql

Problem is, when I run this script I get multiple errors. E.g. (and there are a lot more than this, stuff about circular synonyms too):

grant select on gv_$loadistat to public
                *
ERROR at line 1:
ORA-04063: view "SUKLTI.GV_$LOADISTAT" has errors


create or replace view v_$loadpstat as select * from v$loadpstat
                       *
ERROR at line 1:
ORA-01731: circular view definition encountered



Synonym created.

grant select on v_$loadpstat to public
                *
ERROR at line 1:
ORA-04063: view "SUKLTI.V_$LOADPSTAT" has errors


create or replace view v_$loadistat as select * from v$loadistat
                       *
ERROR at line 1:
ORA-01731: circular view definition encountered



Synonym created.

grant select on v_$loadistat to public
                *
ERROR at line 1:
ORA-04063: view "SUKLTI.V_$LOADISTAT" has errors


                                   from x$kzsro
                                        *
ERROR at line 15:
ORA-00942: table or view does not exist

And then when I try to run SQL*Loader with "direct=true" I receive the following errors:

ORA-26014: unexpected error on column SYS_NTEOzTt73hE9LgU+XYHax0tQ==.DUMMYCOL NAME
 while retrieving virtual column status
ORA-01775: looping chain of synonyms

Note this was a clean Oracle install with some XML schema registered(8) and tables generated off the back of the schema.

Any ideas?

Upvotes: 0

Views: 1107

Answers (2)

Lawrence Tierney
Lawrence Tierney

Reputation: 898

@AlexPoole

You are completely correct. The script had to be run as SYS.

As this was a "test db" we tore it down and ran the script as SYS at DB re-creation time.

Everything now working!

thanks for reply

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

The catldr.sql script says:

Rem NAME
Rem    catldr.sql
Rem  FUNCTION
Rem    Views for the direct path of the loader
Rem  NOTES
Rem    This script must be run while connected as SYS or INTERNAL.

From the error messages you seem to have run it as your normal user, SUKLTI, rather than as SYS. The documentation you linked to also stated it should be run once per database - not once per end-user.

It should have been run during database creation anyway, via the catalog.sql script, so I'm surprised you need to run it manually at all; and some of the errors suggest the objects it creates did already exist. Through re-running it as SYS doesn't really look like it should hurt.

You can see which objects have been created by querying:

select object_type, object_name
from all_objects
where created > time_just_before_you_ran_the_script

You may need to cross-reference public synonyms with the all_synonyms view to check the table owner, and drop any objects it created from the SUKLTI schema as well as those new public synonyms. (But don't drop anything from the SYS schema...)

You may then need to re-run catldr.sql as SYS to recreate those synonyms pointing to the correct SYS objects.

Upvotes: 1

Related Questions