« Oracle Database12c新特性:EM Express - 精简EM版本 | Blog首页 | 恩墨学院 OCM 认证首战告捷 100%全员通过 »

使用DBMS_SWRF_INTERNAL清理导入数据库的AWR信息

经常向数据库导入AWR信息，可能导致SYSAUX或存储表空间不断膨胀，在不再需要时，可以通过DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE来清理注册和导入数据库的AWR信息。 如下是一个简单的执行过程示范：

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX                                     487.9375
UNDOTBS1                                          9
USERS                                       567.375
SYSTEM                                           13

Elapsed: 00:00:00.02
SQL> exec DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(280689037);

PL/SQL procedure successfully completed.

Elapsed: 00:01:04.61
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX                                     785.8125
UNDOTBS1                                          3
USERS                                       567.375
SYSTEM                                           13

Elapsed: 00:00:00.01
SQL> exec p_awr_info
Database Awr INFO:
------------------------------------------- -----------
632125311 . 1 . czjydb1 . CZJYDB
632125311 . 2 . czjydb2 . CZJYDB
1994895652 . 1 . ebiz1 . EBIZ
1994895652 . 2 . ebiz2 . EBIZ
4177678342 . 1 . ora11g . ORA11G

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> exec DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(632125311);

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.05
SQL> exec p_awr_info
Database Awr INFO:
------------------------------------------- -----------
1994895652 . 1 . ebiz1 . EBIZ
1994895652 . 2 . ebiz2 . EBIZ
4177678342 . 1 . ora11g . ORA11G

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX                                      893.625
UNDOTBS1                                          7
USERS                                       567.375
SYSTEM                                           13

供参考。

By eygle on 2013-07-05 11:06 | Comments (0) | FAQ | 3108 |

AWR