Thursday, June 18, 2015

Few ORA- error while "alter table ....move"

[oracle@vmxdb01 ~]$ grep ORA- move_table.log |sort|uniq
ORA-00997: illegal use of LONG datatype
ORA-01735: invalid ALTER TABLE option
ORA-14512: cannot perform operation on a clustered object
ORA-25191: cannot reference overflow table of an index-organized table


ORA-00997: illegal use of LONG datatype
SQL> alter table SYS.WRI$_SQLSET_WORKSPACE_PLANS move;
SQL> alter table SYS.WRI$_SQLSET_PLAN_LINES move;
SQL> alter table SYS.WRI$_ADV_SQLT_PLANS move;
SQL> alter table SYS.SQLOBJ$PLAN move;

To fix:
In this example, all tables are actually empty.
For application tables, suggest to use copy or expdp/impdp.

ORA-01735: invalid ALTER TABLE option
SQL> alter table XDB.SYS_NT/ZrP7FfoSKngQ7ap6Ar7bw== move;

To fix:
SQL> alter table XDB."SYS_NT/ZrP7FfoSKngQ7ap6Ar7bw==" move;
Table altered.

ORA-14512: cannot perform operation on a clustered object
SQL> alter table SYS.SMON_SCN_TIME move;

To fix:
SQL> select cluster_owner,cluster_name from dba_tables
  2  where owner='SYS' and table_name='SMON_SCN_TIME';

CLUSTER_OWNER                  CLUSTER_NAME
------------------------------ ------------------------------
SYS                            SMON_SCN_TO_TIME_AUX

SQL> select owner,table_name from dba_tables
  2  where cluster_name='SMON_SCN_TO_TIME_AUX' and cluster_owner='SYS';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            SMON_SCN_TIME

Expdp/impdp to create the object if not sys internal objects.

ORA-25191: cannot reference overflow table of an index-organized table
SQL> alter table SYS.SYS_IOT_OVER_18291 move;
SQL> alter table SYS.SYS_IOT_OVER_17517 move;
SQL> alter table SYS.SYS_IOT_OVER_7687 move;

To fix:
SQL> select owner,table_name,iot_name from dba_tables where owner='SYS' and table_name='SYS_IOT_OVER_7687';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
IOT_NAME
--------------------------------------------------------------------------------
SYS                            SYS_IOT_OVER_7687
RECENT_RESOURCE_INCARNATIONS$


SQL> alter table RECENT_RESOURCE_INCARNATIONS$ move tablespace sysaux overflow tablespace sysaux;

Table altered.

SQL> select owner,table_name,iot_name from dba_tables where owner='SYS' and table_name='SYS_IOT_OVER_18291';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
IOT_NAME
--------------------------------------------------------------------------------
SYS                            SYS_IOT_OVER_18291
AQ$_SYS$SERVICE_METRICS_TAB_G

SQL> alter table AQ$_SYS$SERVICE_METRICS_TAB_G move tablespace sysaux overflow tablespace sysaux;
alter table AQ$_SYS$SERVICE_METRICS_TAB_G move tablespace sysaux overflow tablespace sysaux
            *
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table
SYS.AQ$_SYS$SERVICE_METRICS_TAB_G
SQL> DECLARE
  2    po dbms_aqadm.aq$_purge_options_t;
  3  BEGIN
  4    po.block := FALSE;
  5    DBMS_AQADM.PURGE_QUEUE_TABLE(
  6      queue_table => 'AQ$_SYS$SERVICE_METRICS_TAB_G',
  7      purge_condition => NULL,
    purge_options => po);
  8    9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24
characters
ORA-06512: at "SYS.DBMS_AQADM", line 1668
ORA-06512: at line 5
SQL> DECLARE
  2    po dbms_aqadm.aq$_purge_options_t;
  3  BEGIN
  4    po.block := FALSE;
  5    DBMS_AQADM.PURGE_QUEUE_TABLE(
  6      queue_table => 'SYS$SERVICE_METRICS_TAB',
  7      purge_condition => NULL,
  8      purge_options => po);
  9  END;
 10  /

PL/SQL procedure successfully completed.