let me explain this….
I had running 1 session of sqlldr while I run indexes into table for 6 fields… now the indexes are completed but the sqlldr running very slow it load one file after each 30 minute…
now when I do any alter to set the indexes off it won't let me do it and though an error of ( ORA-000054 and ORA-000604) even if I run another sqlldr….
any suggestion will be highly appreciate…. bellow is sample of my control files header…
OPTIONS(DIRECT=TRUE,ROWS=10000,BINDSIZE=209700000,readsize=209700000)
load data
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols
When I run following query
select * from dba_dml_locks
session_id,owner,name,mode_held,mode_requested,last_convert,blocking_others
123,username,tablename,exclusive,none,1228,not blocking
any suggestion … this is my daily scheduler to load around 8000+ files around 20GB so any suggestion
Best Answer
Since no other sessions are accessing your table now, the lock shows as NOT BLOCKING. As soon as an other session tries to manage that table, it suddenly feels it is BLOCKED. The index is used so you can not manage it as long as other sessions are using it.
Having indexes on havily loaded tables is for sure like putting a brake on the loader process. You could help yourself by checking out the docs. See Conventional and Direct Path Loads
Maybe partitioning can help you. Than you can prepare a single table, load that, finish it's indexing and perform partition exchange. Just don't create global indexes on such a table.