\nThe Oracle AWR, Automatic Workload Respository, is tored in the SYSAUX tablespace. This post conatians information useful for purging\/cleaning the AWR when it becomes large due to failure of cleanup jobs to remove partitions in the large AWR table, e.g. WRH$_ACTIVE_SESSION_HISTORY. Mainly Oracle 12c release 1.\n<\/p>\n
\r\n-- check ASH history compared to retention period\r\nselect sysdate - a.sample_time ash,\r\nsysdate - s.begin_interval_time snap,\r\nc.RETENTION\r\nfrom sys.wrm$_wr_control c,\r\n(\r\nselect db.dbid,\r\nmin(w.sample_time) sample_time\r\nfrom sys.v_$database db,\r\nsys.Wrh$_active_session_history w\r\nwhere w.dbid = db.dbid group by db.dbid\r\n) a,\r\n(\r\nselect db.dbid,\r\nmin(r.begin_interval_time) begin_interval_time\r\nfrom sys.v_$database db,\r\nsys.wrm$_snapshot r\r\nwhere r.dbid = db.dbid\r\ngroup by db.dbid\r\n) s\r\nwhere a.dbid = s.dbid\r\nand c.dbid = a.dbid;\r\n<\/pre>\n\r\n-- How much space is being used?\r\nSELECT occupant_name "Item",\r\nspace_usage_kbytes\/1048576 "Space Used (GB)",\r\nschema_name "Schema",\r\nmove_procedure "Move Procedure"\r\nFROM v$sysaux_occupants\r\nWHERE occupant_name in ('SM\/AWR','SM\/OPTSTAT')\r\nORDER BY 1\r\n<\/pre>\n\r\n-- Get orphaned ASH rows\r\nSELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a\r\n WHERE NOT EXISTS\r\n (SELECT 1\r\n FROM wrm$_snapshot\r\n WHERE snap_id = a.snap_id\r\n AND dbid = a.dbid\r\n AND instance_number = a.instance_number\r\n );\r\n<\/pre>\n\r\n-- check ASH history compared to retention period\r\nselect sysdate - a.sample_time ash,\r\nsysdate - s.begin_interval_time snap,\r\nc.RETENTION\r\nfrom sys.wrm$_wr_control c,\r\n(\r\nselect db.dbid,\r\nmin(w.sample_time) sample_time\r\nfrom sys.v_$database db,\r\nsys.Wrh$_active_session_history w\r\nwhere w.dbid = db.dbid group by db.dbid\r\n) a,\r\n(\r\nselect db.dbid,\r\nmin(r.begin_interval_time) begin_interval_time\r\nfrom sys.v_$database db,\r\nsys.wrm$_snapshot r\r\nwhere r.dbid = db.dbid\r\ngroup by db.dbid\r\n) s\r\nwhere a.dbid = s.dbid\r\nand c.dbid = a.dbid;\r\n<\/pre>\n\r\n-- Get current settings\r\nselect DBID, SNAP_INTERVAL, RETENTION, TOPNSQL from DBA_HIST_WR_CONTROL;\r\n\r\nselect\r\n extract( day from snap_interval) *24*60+\r\n extract( hour from snap_interval) *60+\r\n extract( minute from snap_interval ) "Snapshot Interval (minutes)",\r\n extract( day from retention) *24*60+\r\n extract( hour from retention) *60+\r\n extract( minute from retention ) "Retention Interval (minutes)"\r\nfrom dba_hist_wr_control\r\n\r\n<\/pre>\n\r\n-- Check snapshoits are being created properly\r\nSELECT (CASE WHEN NUMTODSINTERVAL((SYSDATE - MAX(CAST(END_INTERVAL_TIME AS DATE))), 'DAY') \r\n< SNAP_INTERVAL THEN 'OK AWR Snapshots are being collected' ELSE 'NOK Problems with Snapshot collection' END) "RESULT"\r\nFROM DBA_HIST_SNAPSHOT, DBA_HIST_WR_CONTROL\r\nGROUP BY SNAP_INTERVAL\r\n\r\n<\/pre>\n\r\n-- Disable snapshots\r\nBEGIN\r\n DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(\r\n retention => 11520, -- Mins (43200 = 30 Days). Current value retained if NULL.(11520 is 8 days)\r\n interval => 0); -- Mins. Current value retained if NULL. If zero --> snapshots are stopped\r\nEND;\r\n\/\r\n\r\n-- Enable snapshots\r\nBEGIN\r\n DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(\r\n retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL. (11520 is 8 days)\r\n interval => 30); -- Minutes. Current value retained if NULL. If zero --> snapshots are stopped\r\nEND;\r\n\/\r\n\r\n<\/pre>\n\r\n-- Get Objects at high end of tablespace\r\nselect * from (\r\nselect\r\nowner,segment_name, partition_name, segment_type, max((block_id + blocks-1)*8192)\/1024\/1024 "HWM_MB"\r\nfrom dba_extents where file_id=<file id for SYSAUX (2)>\r\ngroup by owner,segment_name, partition_name, segment_type)\r\nwhere HWM_MB > <Put size here e.g. 10000 for 10G>\r\norder by 5 DESC\r\n<\/pre>\nMove Segments to a temp SYSAUX to reorganize SYSAUX objects<\/h2>\n
\u2022\tMove CTXSYS
\n\u2022\tMove LogMiner
\n\u2022\tMove DBAudit Tables
\n\u2022\tMove LOB segments (BLOB and CLOB columns)
\n\u2022\tMove Normal tables and rebuild indexes
\n\u2022\tMove partitioned tables and rebuild partitioned indexes
\n\u2022\tMove tables with LONG columns
\n\u2022\tMove nested tables
\n\u2022\tMove IOTs
\n\u2022\t12c \u2013 HEATMAP
\n\u2022\tAQ datapump tables<\/p>\n
\r\nexec ctxsys.DRI_MOVE_CTXSYS(TBS_NAME=>'SYSAUX_TEMP', PRINT_ONLY=>false)\r\n\r\nselect count(*), segment_type from dba_segments where tablespace_name = 'SYSAUX'\r\nand owner = 'CTXSYS'\r\ngroup by segment_type\r\n\r\nexec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX_TEMP')\r\n\r\nexec DBMS_AUDIT_MGMT.move_dbaudit_tables('SYSAUX_TEMP')\r\n\r\n\r\nBEGIN\r\ndbms_audit_mgmt.set_audit_trail_location(\r\naudit_trail_type => dbms_audit_mgmt.audit_trail_unified,\r\naudit_trail_location_value => 'SYSAUX_TEMP');\r\nEND;\r\n\/\r\n\r\nBEGIN\r\nDBMS_AUDIT_MGMT.set_audit_trail_location(\r\naudit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$\r\naudit_trail_location_value => 'SYSAUX_TEMP');\r\nEND;\r\n\/\r\n\r\nBEGIN\r\nDBMS_AUDIT_MGMT.set_audit_trail_location(\r\naudit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$\r\naudit_trail_location_value => 'SYSAUX_TEMP');\r\nEND;\r\n\/\r\n\r\nNone of these move the CLI_ objects\r\n\r\nPROCEDURE MOVE_DBAUDIT_TABLES\r\n Argument Name Type In\/Out Default?\r\n ------------------------------ ----------------------- ------ --------\r\n AUDIT_TRAIL_TBS VARCHAR2 IN DEFAULT\r\n\r\n\r\n12c\r\n\r\n--THIS ONE removed CLI_SWP* (very quickly!) \u2013 when the AUDIT objects are moved\r\n-- they are recreated in the new tablespace\r\nSQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp=>FALSE);\r\n\r\nBLOB and CLOB Segments\r\nGeneral\r\nalter table <owner>.<table_name> move lob(<column name>) store as ( tablespace sysaux_temp );\r\n\r\n\r\n\r\nFind Generate Move LOB Commands\r\n\r\nSELECT 'alter table '||owner||'.'||TABLE_NAME||\r\n' move lob('||COLUMN_NAME||') store as (tablespace SYSAUX_TEMP);' "move lob script",\r\nDATA_TYPE\r\nFROM dba_tab_columns\r\nWHERE DATA_TYPE IN ('CLOB','BLOB')\r\nAND table_name IN (SELECT table_name\r\nFROM dba_tables\r\nWHERE tablespace_name='SYSAUX'\r\nand partitioned = 'NO'\r\nand NESTED = 'NO') \r\nand owner = 'SYS'\r\n\r\nThis will generate commands that will work for normal (non-partitioned and non-nested) tables. \r\n\r\nPartitioned table commands are different\r\n\r\nMove to SYSAUX_TEMP\r\nalter table WRH$_SQL_PLAN move lob(other_xml) store as ( tablespace sysaux_temp )\r\n\r\n12c\r\nalter table WRH$_SQLTEXT move lob(SQL_TEXT) store as ( tablespace sysaux_temp )\r\nMove back to SYSAUX\r\nalter table WRH$_SQL_PLAN move lob(other_xml) store as ( tablespace sysaux )\r\n\r\n12c\r\nalter table WRH$_SQLTEXT move lob(SQL_TEXT) store as ( tablespace sysaux )\r\n\r\nalter table WRI$_ADV_RATIONALE move lob(ATTR5) store as ( tablespace sysaux_temp )\r\n\r\nalter table WRI$_DBU_FEATURE_USAGE move lob(FEATURE_INFO) store as ( tablespace sysaux_temp )\r\n\r\n\r\nAnd for partitioned tables, example:\r\n\r\nFind the LOB PARTITION name\r\nFind the table name from dba_lobs\r\nFind the column name by DESC\u2019ing the table\r\nselect 'alter table <table name> move partition '\r\n||partition_name||' lob(<column name>) '||' store as (tablespace SYSAUX_TEMP);'\r\nfrom dba_tab_partitions where table_name = '<table name>'\r\n\r\n\r\nselect 'alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move partition '\r\n||partition_name||' lob(EXPRESSION) '||' store as (tablespace SYSAUX_TEMP);'\r\nfrom dba_tab_partitions where table_name = 'WRI$_OPTSTAT_HISTHEAD_HISTORY'\r\n\r\n\r\nNormal Tables and Indexes\r\n\r\nGenerate Table Move (to SYSAUX_TEMP) Scripts\r\n\r\nselect 'alter table '||owner||'.'||segment_name||' move tablespace sysaux_temp;'\r\nfrom dba_segments where segment_type = 'TABLE' and tablespace_name = 'SYSAUX'and owner = 'SYS'\r\n\r\n\r\nGenerate Index Rebuild (to SYSAUX_TEMP) Scripts\r\n\r\nselect 'alter index '||owner||'.'||index_name||' rebuild tablespace SYSAUX_TEMP;'\r\nfrom dba_indexes where status = 'UNUSABLE'\r\nand tablespace_name = 'SYSAUX'\r\n\r\nPartitioned tables and Indexes\r\n\r\nMove table partitions to SYSAUX_TEMP\r\n\r\nselect 'alter table '||owner||'.'||segment_name||' move partition '||partition_name||' tablespace sysaux_temp;'\r\nfrom dba_segments where segment_type = 'TABLE PARTITION' and tablespace_name = 'SYSAUX'\r\norder by segment_name\r\n\r\n\r\nRebuild Index Partitions in SYSAUX_TEMP\r\nThey become USABLE again and are rebuilt in SYSAUX_TEMP\r\n\r\nselect 'alter index '||index_name|| ' rebuild partition '||\r\npartition_name||' tablespace SYSAUX_TEMP;' from dba_ind_partitions\r\nwhere status = 'UNUSABLE'\r\nand tablespace_name = 'SYSAUX'\r\n\r\nMove to SYSAUX_TEMP\r\n\r\nselect 'alter table '||owner||'.'||segment_name ||' move tablespace SYSAUX_TEMP;'\r\nfrom dba_segments where segment_name like '%==' and tablespace_name = 'SYSAUX'\r\nand segment_type = 'NESTED TABLE'\r\n\r\n\r\nIndex-Organized Tables\r\nIOT\r\n\r\nFind the table concerned and shrink it\r\nEnable row movement on the table first (if not already enabled)\r\n\r\nDisplay row_movement\r\n\r\n\r\nselect owner,table_name, row_movement from dba_tables where table_name in \r\n(select table_name from dba_indexes where index_name like '%IOT%' and tablespace_name = 'SYSAUX')\r\n\r\n\r\nGSMADMIN_INTERNAL \u2013 may not be required to move this \u2013 see \r\n\r\nWMSYS \r\n\r\nCTXSYS \u2013 these should have been moved with the move proc but may not have been\r\n\r\nIOT Indexes\r\n\r\nalter table AQ$_ALERT_QT_H shrink space cascade;\r\n\r\n12c - HEATMAP\r\n\r\nSee MOS\r\nDoc ID 2024036.1\r\nHEATMAP Segment Size Is Large In SYSAUX Even When Heatmap=Off (Doc ID 2024036.1)\r\nThis is not the same as my problem \u2013 but the solution to my HEATMAP problem is in this document\r\n\r\n\r\nSQL> show parameter HEAT_MAP;\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ -------------------------------------------- ------------------------------\r\nheat_map string OFF\r\n\r\nALTER SYSTEM SET "_drop_stat_segment"=1 scope=memory;\r\n\r\nHeatmap is dropped\r\n\r\nAQ DATAPUMP Tables\r\n\r\nExamples\r\nalter table "AQ$_KUPC$DATAPUMP_QUETAB_2_P" move tablespace sysaux_temp;\r\n\r\nalter table "AQ$_KUPC$DATAPUMP_QUETAB_2_S" move tablespace sysaux_temp;\r\n\r\nTables with LONG columns\r\n\r\nDisplay Tables with LONG Columns\r\n\r\n\r\nSELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE\r\n FROM dba_tab_columns\r\n WHERE DATA_TYPE IN ('LONG','LONG RAW')\r\n AND table_name IN (SELECT table_name\r\n FROM dba_tables\r\n WHERE tablespace_name='SYSAUX')\r\n\r\n\r\nSome tables with long columns do not contain data- these can be truncated directly. \r\n\r\nFor tables with LONG columns that contain data AND there is no data in the LONG column:\r\n\u2022\tcreate a temporary table with the data without the LONG column(s)\r\n\u2022\ttruncate the table\r\n\u2022\tinsert the rows back into the table\r\n\r\nWRI$_ADV_SQLT_PLAN\r\nDoc ID 2205210.1\r\n\r\n\r\ncreate table SQLT_PLANS_TEMP as select\r\nTASK_ID, OBJECT_ID, ATTRIBUTE, PLAN_HASH_VALUE, PLAN_ID,STATEMENT_ID, TIMESTAMP,\r\nREMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS,\r\nOBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,\r\nCOST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,\r\nDISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,\r\nPROJECTION,TIME,QBLOCK_NAME,OTHER_XML from WRI$_ADV_SQLT_PLANS\r\n\r\nselect count(*) from WRI$_ADV_SQLT_PLANS where other is not null\r\n\r\nselect count(*) from SQLT_PLANS_TEMP\r\n\r\nselect * from SQLT_PLANS_TEMP\r\n\r\ntruncate table WRI$_ADV_SQLT_PLANS\r\n\r\ninsert into WRI$_ADV_SQLT_PLANS\r\nselect\r\nTASK_ID,OBJECT_ID,ATTRIBUTE, PLAN_HASH_VALUE, PLAN_ID,STATEMENT_ID, TIMESTAMP,\r\nREMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS,\r\nOBJECT_INSTANCE, OBJECT_TYPE,OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH,POSITION,\r\nCOST, CARDINALITY, BYTES, OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,\r\nNULL,DISTRIBUTION,CPU_COST, IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,\r\nPROJECTION,TIME,QBLOCK_NAME, OTHER_XML from SQLT_PLANS_TEMP\r\n\r\n\r\n\r\n\r\n\r\ncreate table PLAN_temp as select\r\nSIGNATURE, CATEGORY, OBJ_TYPE, PLAN_ID, STATEMENT_ID, XPL_PLAN_ID, TIMESTAMP,\r\nREMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME,\r\nOBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID,\r\nPARENT_ID, DEPTH, POSITION, COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,\r\nPARTITION_STOP,PARTITION_ID,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,\r\nACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML\r\nfrom SQLOBJ$PLAN\r\n\r\ntruncate table SQLOBJ$PLAN\r\n\r\ninsert into SQLOBJ$PLAN select\r\nSIGNATURE, CATEGORY, OBJ_TYPE, PLAN_ID, STATEMENT_ID, XPL_PLAN_ID, TIMESTAMP, REMARKS, OPERATION,\r\nOPTIONS, OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS,OBJECT_INSTANCE,OBJECT_TYPE,\r\nOPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,\r\nPARTITION_START,PARTITION_STOP,PARTITION_ID,NULL,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,\r\nACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML from PLAN_TEMP;\r\n\r\ncommit\r\n\r\nselect count(*) from SQLOBJ$PLAN\r\n\r\nCheck ADDM Jobs \u2013 Shows AWR is Working\r\n\r\nIf there are unusable indexes or other problems, ADDM tasks may not have a status of \u2018COMPLETED\u2019\r\n\r\nThis query shows ADDM tasks\r\n\r\nselect owner || '\/' || advisor_name as owner_adv\r\n , task_id || '\/' || task_name as task_id_name\r\n , created\r\n , (execution_end - execution_start)*86400 as exe_duratn\r\n , (execution_end - created) * 86400 as exe_creatn\r\n , how_created\r\n , status\r\n , 'Description: ' || description as task_desc\r\n , 'Error Msg : ' || error_message as error_msg\r\nfrom (select t.*, rank() over (order by execution_end desc) rnk\r\n from dba_advisor_tasks t) dat\r\nwhere dat.rnk <= 50\r\n and 1 = 1\r\norder by created desc;\r\n\r\n\r\n\r\nCheck Object Validity and Recompile\r\n\r\nSelect count(*) from dba_objects where status <> 'VALID'\r\n\r\n@?\/rdbms\/admin\/utlrp.sql\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"AWR, Automatic Workload Respository The Oracle AWR, Automatic Workload Respository, is tored in the SYSAUX tablespace. This post conatians information useful for purging\/cleaning the AWR when it becomes large due to failure of cleanup jobs to remove partitions in the … Continue reading