52 lines
1.4 KiB
Markdown
52 lines
1.4 KiB
Markdown
## Purge all AWR snapshots
|
|
|
|
> To run from `CDB$ROOT` and not inside a **PDB**.
|
|
|
|
```sql
|
|
DECLARE
|
|
v_lo_snap_id NUMBER;
|
|
v_hi_snap_id NUMBER;
|
|
v_awr_dbid NUMBER;
|
|
BEGIN
|
|
-- 1. Get the DBID that actually exists in the AWR repository
|
|
SELECT DISTINCT dbid INTO v_awr_dbid FROM dba_hist_snapshot WHERE ROWNUM = 1;
|
|
|
|
-- 2. Find the min and max snapshot IDs for that specific DBID
|
|
SELECT min(snap_id), max(snap_id)
|
|
INTO v_lo_snap_id, v_hi_snap_id
|
|
FROM dba_hist_snapshot
|
|
WHERE dbid = v_awr_dbid;
|
|
|
|
IF v_lo_snap_id IS NOT NULL THEN
|
|
DBMS_OUTPUT.PUT_LINE('Deleting snapshots for DBID ' || v_awr_dbid ||
|
|
' from ' || v_lo_snap_id || ' to ' || v_hi_snap_id);
|
|
|
|
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
|
|
low_snap_id => v_lo_snap_id,
|
|
high_snap_id => v_hi_snap_id,
|
|
dbid => v_awr_dbid
|
|
);
|
|
|
|
DBMS_OUTPUT.PUT_LINE('AWR Snapshots cleared.');
|
|
ELSE
|
|
DBMS_OUTPUT.PUT_LINE('No snapshots found in dba_hist_snapshot.');
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN TOO_MANY_ROWS THEN
|
|
DBMS_OUTPUT.PUT_LINE('Error: Multiple DBIDs found in AWR. Please specify one manually.');
|
|
WHEN NO_DATA_FOUND THEN
|
|
DBMS_OUTPUT.PUT_LINE('Error: No snapshot data exists to delete.');
|
|
END;
|
|
/
|
|
```
|
|
|
|
Verify:
|
|
```sql
|
|
select min(snap_id),max(snap_id), dbid from dba_hist_snapshot group by dbid;
|
|
```
|
|
|
|
To take a snapshot:
|
|
```sql
|
|
execute dbms_workload_repository.CREATE_SNAPSHOT;
|
|
```
|