Why do I need to Export?
you want to save AWR data for comparing with another database or after upgrading database and so on reasons.
you want to save AWR data for comparing with another database or after upgrading database and so on reasons.
How do I save AWR report?
You can simply run awrrpt.sql located in <Oracle_home>\rdbms\admin directory and save the reports.
Another way is to export the AWR report itself similar to database export.
Below script let you export AWR data to dump file.
BEGIN DBMS_SWRF_INTERNAL.AWR_EXTRACT (DMPFILE => 'awr_data.dmp', -- Dump file name DMP_DIR => 'DB_DIR', -- Database directory BID => 4283, -- Beginning snapshot EID => 4315 –- Ending snapshot ); END;
Prerequisites: (1) you need to have Execute privs on the DBMS_SWRF_INTERNAL package, (2) Set-up a Database directory. You can use existing directory [ query from DBA_DIRECTORIES]. In above case, I have directory called DB_DIR
BID and EID represents the beginning and ending snapshot ids. You can obtain available snapshots from DBA_HIST_SNAPSHOT view
Once you complete the exporting, you can import back into different/same database using below statements. This is two step process
BEGIN DBMS_SWRF_INTERNAL.AWR_LOAD (SCHEMA => "DEV",
DMPFILE => 'awr_data.dmp', -- Dump file name exported earlier.
DMP_DIR => 'DB_DIR', -- Database directory ); END;
Above statement loads AWR data into DEV schema, later that needs to be moved to SYS.
Same pre-requisites applies. Note that if database is located on different server, you need to move dump file where the Database directory is configured on destination database.
Second part of the import is move imported data to SYS using below statement
BEGIN DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHEMA => “DEV ); END;
Oracle Enablement 