迁移awr快照数据到自定义表空间
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在19c中有些情况,考虑把awr的快照数据存储在非sysaux表空间,可以通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS来进行设置
sys@ORA19C 21:57:02> select BANNER_FULL from v$version; BANNER_FULL ---------------------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 Elapsed: 00:00:00.01 PROCEDURE MODIFY_SNAPSHOT_SETTINGS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RETENTION NUMBER IN DEFAULT INTERVAL NUMBER IN DEFAULT TOPNSQL NUMBER IN DEFAULT DBID NUMBER IN DEFAULT TABLESPACE_NAME VARCHAR2 IN DEFAULT PROCEDURE MODIFY_SNAPSHOT_SETTINGS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RETENTION NUMBER IN DEFAULT INTERVAL NUMBER IN DEFAULT TOPNSQL VARCHAR2 IN DBID NUMBER IN DEFAULT TABLESPACE_NAME VARCHAR2 IN DEFAULT
这两个proc,主要是TOPNSQL一个是number类型,一个是varchar2类型
If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.
If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.
进行了简单的测试,确认是部分awr的分区表设置到新表空间中
sys@ORA19C 21:41:51> CREATE TABLESPACE AWRTBS DATAFILE '/data/oradata/ORA19C/awrtbs01.dbf' size 128M autoextend on; Tablespace created. Elapsed: 00:00:00.53 sys@ORA19C 21:42:21> exec dbms_workload_repository.modify_snapshot_settings(tablespace_name=> 'AWRTBS'); PL/SQL procedure successfully completed. Elapsed: 00:00:01.53 sys@ORA19C 21:53:56> execute dbms_workload_repository.create_snapshot(); PL/SQL procedure successfully completed. Elapsed: 00:00:01.44 sys@ORA19C 21:53:58> select segment_name,PARTITION_NAME,segment_type from dba_segments where tablespace_name='AWRTBS'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE ------------------------------ ------------------------------------------------------------ --------------- WRH$_FILESTATXS WRH$_FILESTATXS_1232450071_2690 TABLE PARTITION WRH$_SQLSTAT WRH$_SQLSTAT_1232450071_2690 TABLE PARTITION WRH$_SYSTEM_EVENT WRH$_SYSTEM_EVENT_1232450071_2690 TABLE PARTITION WRH$_WAITSTAT WRH$_WAITSTAT_1232450071_2690 TABLE PARTITION WRH$_LATCH WRH$_LATCH_1232450071_2690 TABLE PARTITION WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH_MISSES_SUMMARY_1232450071_2690 TABLE PARTITION WRH$_DB_CACHE_ADVICE WRH$_DB_CACHE_ADVICE_1232450071_2690 TABLE PARTITION WRH$_ROWCACHE_SUMMARY WRH$_ROWCACHE_SUMMARY_1232450071_2690 TABLE PARTITION WRH$_SGASTAT WRH$_SGASTAT_1232450071_2690 TABLE PARTITION WRH$_SYSSTAT WRH$_SYSSTAT_1232450071_2690 TABLE PARTITION WRH$_PARAMETER WRH$_PARAMETER_1232450071_2690 TABLE PARTITION WRH$_SEG_STAT WRH$_SEG_STAT_1232450071_2690 TABLE PARTITION WRH$_SERVICE_STAT WRH$_SERVICE_STAT_1232450071_2690 TABLE PARTITION WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SESSION_HISTORY_1232450071_2690 TABLE PARTITION WRH$_SYSMETRIC_HISTORY WRH$_SYSMETRIC_HISTORY_1232450071_2690 TABLE PARTITION WRH$_LATCH_CHILDREN WRH$_LATCH_CHILDREN_1232450071_0 TABLE PARTITION WRH$_LATCH_PARENT WRH$_LATCH_PARENT_1232450071_0 TABLE PARTITION WRH$_DLM_MISC WRH$_DLM_MISC_1232450071_0 TABLE PARTITION WRH$_INST_CACHE_TRANSFER WRH$_INST_CACHE_TRANSFER_1232450071_0 TABLE PARTITION WRH$_INTERCONNECT_PINGS WRH$_INTERCONNECT_PINGS_1232450071_0 TABLE PARTITION WRH$_TABLESPACE_STAT WRH$_TABLESPACE_STAT_1232450071_2690 TABLE PARTITION WRH$_OSSTAT WRH$_OSSTAT_1232450071_2690 TABLE PARTITION WRH$_SYS_TIME_MODEL WRH$_SYS_TIME_MODEL_1232450071_2690 TABLE PARTITION WRH$_SERVICE_WAIT_CLASS WRH$_SERVICE_WAIT_CLASS_1232450071_2690 TABLE PARTITION WRH$_EVENT_HISTOGRAM WRH$_EVENT_HISTOGRAM_1232450071_2690 TABLE PARTITION WRH$_MVPARAMETER WRH$_MVPARAMETER_1232450071_2690 TABLE PARTITION WRH$_CELL_GLOBAL_SUMMARY WRH$_CELL_GLOBAL_SUMMARY_1232450071_2690 TABLE PARTITION WRH$_CELL_DISK_SUMMARY WRH$_CELL_DISK_SUMMARY_1232450071_2690 TABLE PARTITION WRH$_CELL_GLOBAL WRH$_CELL_GLOBAL_1232450071_2690 TABLE PARTITION WRH$_CELL_IOREASON WRH$_CELL_IOREASON_1232450071_2690 TABLE PARTITION WRH$_CELL_DB WRH$_CELL_DB_1232450071_2690 TABLE PARTITION WRH$_CELL_OPEN_ALERTS WRH$_CELL_OPEN_ALERTS_1232450071_2690 TABLE PARTITION WRH$_IM_SEG_STAT WRH$_IM_SEG_STAT_1232450071_2690 TABLE PARTITION WRM$_PDB_IN_SNAP WRM$_PDB_IN_SNAP_1232450071_2690 TABLE PARTITION WRH$_CON_SYSMETRIC_HISTORY WRH$_CON_SYSMETRIC_HISTORY_1232450071_2690 TABLE PARTITION WRM$_ACTIVE_PDBS WRM$_ACTIVE_PDBS_1232450071_2690 TABLE PARTITION WRH$_CON_SYSSTAT WRH$_CON_SYSSTAT_1232450071_2690 TABLE PARTITION WRH$_CON_SYSTEM_EVENT WRH$_CON_SYSTEM_EVENT_1232450071_2690 TABLE PARTITION WRH$_PROCESS_WAITTIME WRH$_PROCESS_WAITTIME_1232450071_2690 TABLE PARTITION WRH$_ASM_DISK_STAT_SUMMARY WRH$_ASM_DISK_STAT_SUMMARY_1232450071_2690 TABLE PARTITION WRH$_AWR_TEST_1 WRH$_AWR_TEST_1_1232450071_2690 TABLE PARTITION WRH$_SESS_NETWORK WRH$_SESS_NETWORK_1232450071_2690 TABLE PARTITION WRH$_CON_SYS_TIME_MODEL WRH$_CON_SYS_TIME_MODEL_1232450071_2690 TABLE PARTITION 43 rows selected. Elapsed: 00:00:00.01 sys@ORA19C 21:54:08>