WRI$_ADV_OBJECTS 对象进行处理时报错ORA-54013

刘泽宇

### Check the no.of rows in WRI$_ADV_OBJECTS for Auto Stats Advisor Task ###
SQL> select count(*) from wri$_adv_objects where task_id=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');

  COUNT(*)
----------
  46324479

### Do CTAS from WRI$_ADV_OBJECTS to keep the rows apart from AUTO_STATS_ADVISOR_TASK ###
SQL> create table wri$_adv_objects_new as select * from wri$_adv_objects where task_id !=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');
SQL> select count(*) from wri$_adv_objects_new;

  COUNT(*)
----------
       359

### Truncate the table ###
SQL> truncate table wri$_adv_objects;

### Insert the rows from backed up table WRI$_ADV_OBJECTS_NEW to restore the records of ther advisor objects ###
SQL> insert /*+ APPEND */ into wri$_adv_objects select * from wri$_adv_objects_new;
SQL> commit;
SQL> drop table wri$_adv_objects_new;

### Reorganize the indexes ###
SQL> alter index wri$_adv_objects_idx_01 rebuild;
SQL> alter index wri$_adv_objects_pk rebuild;


在通过这种方式处理WRI$_ADV_OBJECTS对象时，在插入过程出现报错ORA-54013
原因是在19c之后的版本，WRI$_ADV_OBJECTS表新增了一个字段，插入语句需要进行调整

For 19c & above, use the below insert statement to avoid ORA-54013 error as there is a new column SQL_ID_VC added to WRI$_ADV_OBJECTS.
SQL> INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" ) SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,
"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;

SQL> COMMIT;