-
Title
Errors “Poster: Oracle error: ORA-14452: attempt to create, alter or drop an index on temporary table already in use” -
Description
When a CREATE GLOBAL TEMPORARY statement is issued on source, on target the Post process of Shareplex is locking that table due to a DDL lock. The event log shows the following errors though posting is not impacted in any way and the transactions are posting fine. Here are the event log entries:
sp_ctrl>show log reverse
Error 2017-08-07 16:58:16.032087 27216 2329409280 s:1 Poster: Skipping failed DDL Replication (posting from SID1, queue queue_name, to SID2) [module opo]
Error 2017-08-07 16:58:16.017140 27216 2329409280 s:1 Poster: 15033 - Failed to execute SQL on table "OWNER"."TABLENAME”: attempt to create, alter or drop an index on temporary table already in use (posting from SID1, queue queue_name, to SID2) [module opo]
Notice 2017-08-07 16:58:16.014760 27216 2329409280 s:1 Poster: Oracle error: ORA-14452: attempt to create, alter or drop an index on temporary table already in use rowid:N/A (posting from SID1, queue queue_name, to SID2) [module opo]
Notice 2017-08-07 16:58:16.012162 27216 2329409280 s:1 Poster: ORA-14452: attempt to create, alter or drop an index on temporary table already in use (posting from SID1, queue queue_name, to SID2) [module osp]
Notice 2017-08-07 16:58:15.985239 27216 2329409280 s:1 Poster: Replicated DDL "truncate table Table_name" (posting from SID1, queue queue_name, to SID2) [module opo]
Notice 2017-08-07 16:57:32.632051 27216 2330433280 s:1 Poster: Replicated DDL "CREATE GLOBAL TEMPORARY TABLE Table_name ON COMMIT P..." (posting from SID1, queue queue_name, to SID2) [module opo]
-
Cause
For some reasons a DDL lock is held by Post on a Global Temporary Table. -
Resolution
Carry out the following steps to identify the problem and resolve it:
1. Identify the SharePlex session that is holding lock on the global temporary table by issuing the following to query the database to see if indeed the table is locked by SharePlex user (in the output of our query it is indeed locked by SharePlex user):
1 SELECT 'USER: '||s.username||' SID: '||s.sid||' SERIAL #: '||S.SERIAL# "USER HOLDING LOCK"
2 FROM v$lock l
3 ,dba_objects o
4 ,v$session s
5 WHERE l.id1 = o.object_id
6 AND s.sid = l.sid
7* and o.owner='OWNER_NAME'
SQL> /
USER HOLDING LOCK
--------------------------------------------------------------------------------
USER: SPLEX SID: 1887 SERIAL #: 159
2. Kill that session holding the lock. Nothing needs to be done at the SharePlex instance level. Once that session is killed, the messages will stop appearing in the event log.