My Oracle Support Banner

Slow Rename A Table In Oracle (Doc ID 1537295.1)

Last updated on AUGUST 23, 2021

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 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,,
      decode(bitand(, 1), 1,,,
      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(,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.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.