=> Run the below query to identify default retention period and snapshot interval. By default snapshots are created every 1 hour with a retention period of 8 days
Note : Run below statements by connecting to root container as SYS user incase of Multi-Tenant Database
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> col user for a10
SQL> select name,user from v$database;
--------- ----------
DB1010 SYS
col retention for a20
select snap_interval, retention from dba_hist_wr_control;
-------------------- --------------------
+00000 01:00:00.0 +00008 00:00:00.0
=> Use the MODIFY_SNAPSHOT_SETTINGS procedure of the DBMS_WORKLOAD_REPOSITORY package to modify the retention period and interval settings.
It is a good idea to modify the default settings for your database, as 8 days of retention is not enough when performing database tuning activities on your database. It is a good idea to store a minimum of 60 days of snapshots.
Here I am changing the interval to 30 minutes and retention to 60 days, both the parameters are passed in minutes.
-------------------- --------------------
+00000 00:30:00.0 +00060 00:00:00.0
=> To store snapshots for an unlimited amount of time, you can specify a zero value for retention parameter, Oracle keeps the snapshot information for 40150 days or 110 years
-------------------- --------------------
+00000 00:30:00.0 +40150 00:00:00.0
=> To change back to default values.
-------------------- --------------------
+00000 01:00:00.0 +00008 00:00:00.0
No comments:
Post a Comment