Execution of dbms_logstdby.instantiate_table gets error: ORA-31634: job already exists

I got such an error when recreating a table on logical standby database.


SQL> alter database stop logical standby apply;

SQL> alter database guard standby;

SQL> exec dbms_logstdby.unskip('DML', 'FATTURATO', 'ANALISI_ORDINI_PWH');

SQL> exec dbms_logstdby.instantiate_table('FATTURATO', 'ANALISI_ORDINI_PWH', 'PRIM_DBLINK');
BEGIN dbms_logstdby.instantiate_table('FATTURATO', 'ANALISI_ORDINI_PWH', 'PRIM_DBLINK'); END;

*
ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 636
ORA-06512: at line 1

It seems Oracle only allows 100 datapump Master Tables to exist in a database, if it exceeds this numbers, you will get the error “ORA-31634”.

Get a list of Master Tables


select job_name, state from dba_datapump_jobs;

Solution

drop table sys.SYS_IMPORT_TABLE_nn where nn is 0 .. 99 and restart instantiate_table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: