All entries for Thursday 28 May 2009

May 28, 2009

DBMS_LOGSTDBY.INSTANTIATE and Job already exists error

When you run DBMS_LOGSTDBY.INSTANTIATE_TABLE, it actually create a table SYS_IMPORT_TABLE_nn, (I run as sys. If you run as another user, the table name might be different.).

After the job run successfully, Oracle does not delete the table.

Seems Oracle only allowing 100 such tables (i.e., nn between 0 and 99). If you run the procedure more than 100 times, you will receive an error ORA-31634: job already exists.

Quite a silly omission from Oracle.

You can use : select * from dba_datapump_jobs order by job_name to verify.

The solution is quite simple, just drop the table SYS_IMPORT_TABLE_nn. Before dropping it, make sure the job associated with the table is not running. The status is “NOt Running” in dba_datapump_jobs.

Search this blog

Search Warwick Blogs



Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder