Analyze on all SYS tables (Except AWR Tables)

****************************************************************************************
Generate Script to Analyze on all SYS non-partitioned tables
****************************************************************************************

cd /data/oracle/SHPMLS/admin/changes
$ sqlplus / as sysdba;

SQL> spool analyze_all_nonawr_sys_tables.sql
SQL> set linesize 150
set pagesize 10000
select 'analyze table '||owner||'.'||table_name||' validate structure cascade online;'
from dba_indexes
where TABLE_OWNER='SYS'
and table_name NOT LIKE 'WR%'
AND partitioned='NO'
group by owner, table_name
order by owner, table_name;

SQL> spool off
SQL> !cat analyze_all_nonawr_sys_tables.sql

SQL> spool analyze_all_nonawr_sys_tables.log
SQL> @analyze_all_nonawr_sys_tables.sql
SQL> spool off

analyze table SYS.SMON_SCN_TO_TIME_AUX validate structure cascade online;

SQL> analyze table SYS.SMON_SCN_TIME validate structure cascade online;

Table analyzed.

analyze table SYS.C_COBJ# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_FILE#_BLOCK# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_MLOG# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_OBJ# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_OBJ#_INTCOL# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_RG# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_TOID_VERSION# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_TS# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.C_USER# validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.SMON_SCN_TO_TIME_AUX validate structure cascade online
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

analyze table SYS.SYSNTOLbcoBdUAOrgUwrylQiUdw== validate structure cascade online
                                             *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTOLbcoBdWAOrgUwrylQiUdw== validate structure cascade online
                                             *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTOLbcoBdYAOrgUwrylQiUdw== validate structure cascade online
                                             *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTOLbcoBddAOrgUwrylQiUdw== validate structure cascade online
                                             *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTOLbcoBdfAOrgUwrylQiUdw== validate structure cascade online
                                             *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTOLbcoBdhAOrgUwrylQiUdw== validate structure cascade online
                                             *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTw+kZ/KVLJXTgRAAUT7fhzA== validate structure cascade online
                        *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTw+kZ/KVNJXTgRAAUT7fhzA== validate structure cascade online
                        *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

analyze table SYS.SYSNTw+kZ/KVPJXTgRAAUT7fhzA== validate structure cascade online
                        *
ERROR at line 1:
ORA-01490: invalid ANALYZE command

Generate Script to Rebuild all SYS non-partitioned Indexes

SQL> set linesize 150
set pagesize 10000
select 'alter index '||owner||'.'||index_name||' rebuild online;'
from dba_indexes
where TABLE_OWNER='SYS'
    and table_name NOT like 'WR%'
    AND partitioned='NO'
group by owner, index_name
order by owner, index_name;

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
-----------------------------------------------------------------------------------------
alter index SYS.APPLY$_CONF_HDLR_COLUMNS_UNQ1 rebuild online;
alter index SYS.APPLY$_CONF_HDLR_COLUMNS_UNQ2 rebuild online;
alter index SYS.APPLY$_CONSTRAINT_COLUMNS_IDX1 rebuild online;
alter index SYS.APPLY$_CONSTRAINT_COLUMNS_UIX1 rebuild online;
alter index SYS.APPLY$_ERROR_HANDLER_UNQ rebuild online;
..........
alter index SYS.TXN_BACKOUT_STATE_IDX1$ rebuild online;
alter index SYS.TXN_BACKOUT_STATE_IDX2$ rebuild online;
alter index SYS.TXN_BACKOUT_STATE_IDX3$ rebuild online;
alter index SYS.UI_REDEF_ID$ rebuild online;
alter index SYS.UI_REDEF_NAME$ rebuild online;
alter index SYS.USTATS1 rebuild online;
alter index SYS.UTL_RECOMP_SORT_IDX1 rebuild online;
alter index SYS.XS$SESSIONS_I1 rebuild online;
alter index SYS.XS$SESSION_APPNS_I1 rebuild online;
alter index SYS.XS$SESSION_ROLES_I1 rebuild online;

899 rows selected.

**********************************************************************************************************************************************************
$ sqlplus / as sysdba;

Generate Script to Analyze on all SYS Partitioned Tables

SQL> spool analyze_par_nonawr_sys_tables.sql

SQL> set linesize 150
set pagesize 1000
select 'analyze table '||table_name||' partition ('||partition_name||
          ') validate structure cascade into invalid_rows;'
from user_tab_partitions
where table_name IN (SELECT table_name
                     FROM dba_tables
                     WHERE owner='SYS'
                     AND table_name NOT LIKE 'WR%'
                     AND partitioned='YES')
order by table_name;

'ANALYZETABLE'||TABLE_NAME||'PARTITION('||PARTITION_NAME||')VALIDATESTRUCTURECASCADEINTOINVALID_ROWS;'
-------------------------------------------------------------------------------------------------------------------------------------
analyze table STREAMS$_APPLY_SPILL_MSGS_PART partition (P0) validate structure cascade into invalid_rows;

SQL> spool off
SQL> !cat analyze_par_nonawr_sys_tables.sql

SQL> spool analyze_par_nonawr_sys_tables.log
SQL> @analyze_par_nonawr_sys_tables.sql
SQL> spool off

SQL> analyze table STREAMS$_APPLY_SPILL_MSGS_PART partition (P0) validate structure cascade into invalid_rows;

Table analyzed.

Generate Script to Rebuild all SYS Partitioned Indexes

SQL> spool rebuild_nonawr_par_sys_indexes.sql

SQL> set linesize 150
select 'alter index '||index_name||' rebuild partition '||partition_name||' online;'
from dba_ind_partitions
where index_name IN (select index_name
                     from dba_indexes
                     where TABLE_OWNER='SYS'
                     and table_name not like 'WR%'
                     AND partitioned='YES')
                     order by index_name;

'ALTERINDEX'||INDEX_NAME||'REBUILDPARTITION'||PARTITION_NAME||'ONLINE;'
---------------------------------------------------------------------------------------------------
alter index I_STREAMS_APPLY_SPILL_MSGS_PT1 rebuild partition P0 online;
alter index SYS_IL0000011464C00008$$ rebuild partition SYS_IL_P34 online;

SQL> spool off
SQL> !cat rebuild_nonawr_par_sys_indexes.sql

SQL> spool rebuild_nonawr_par_sys_indexes.log
SQL> @rebuild_nonawr_par_sys_indexes.sql
SQL> spool off

SQL> alter index I_STREAMS_APPLY_SPILL_MSGS_PT1 rebuild partition P0 online;

Index altered.

SQL> alter index SYS_IL0000011464C00008$$ rebuild partition SYS_IL_P34 online;
alter index SYS_IL0000011464C00008$$ rebuild partition SYS_IL_P34 online
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype ADT

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26664718/viewspace-2156115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26664718/viewspace-2156115/