oracle清空dbms历史结果,使用DBMS_SWRF_INTERNAL清理导入数据库的AWR信息

使用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 |