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.