Slow Rename A Table In Oracle
Last updated on NOVEMBER 28, 2017
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 and later
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2017***
Renaming a table takes more time than expected (10 seconds when it normally finishes in less than a second):
This happens with any table even if the table has only 1 column.
From the 10046 trace, the following statement was found to be taking all the delay time:
select count(*) from sys.dba_refs r where r.scope_table_owner=&1 and r.scope_table_name=&2;
The DBA_REFS is made up of the following syntax:
select distinct u.name, o.name,
decode(bitand(c.property, 1), 1, ac.name, c.name),
decode(bitand(rc.reftyp, 2), 2, 'YES', 'NO'),
decode(bitand(rc.reftyp, 1), 1, 'YES', 'NO'),
when bitand(reftyp,4) = 4 then 'USER-DEFINED'
when bitand(reftyp, 8) = 8 then 'SYSTEM GENERATED AND USER-DEFINED'
else 'SYSTEM GENERATED'
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.refcon$ rc, sys.obj$ so,
sys.user$ su, sys.attrcol$ ac
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = rc.obj#
and c.col# = rc.col#
and c.intcol# = rc.intcol#
and rc.stabid = so.oid$(+)
and so.owner# = su.user#(+)
and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+)
and bitand(c.property,32768) != 32768 /* not unused column */
STABID is part of the query and does not have an index on it.
Gathering dictionary stats does not affect the execution time.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms