oracle aud$ 数据的备份和导入, Oracle aud$ table exp imp

在接手的oracle 11g database中，发现aud$占用system tablespace太大了27G，现在想一步步的转移之，为此在测试环境中做了一下相关的测试工作，记录如下：


1 创建一个表
SQL> select count(*) from sys.aud$;

COUNT(*)
----------
3332

SQL> create table asmuser_aud as select * from sys.aud$;

Table created.

SQL> select count(*) from sys.aud$;

COUNT(*)
----------
3332


2 exp 出来(因为生产环境是需要条件的所以这里也是用条件测试）
exp asmuser/password file=asmuser090421.dmp tables=asmuser_aud query=\" where NTIMESTAMP\# \<\= to_date\(\'20090421\', \'yyyymmdd\'\)\"


[[oracle@crdb11g ~]$ exp asmuser/password file=asmuser090421.dmp tables=asmuser_aud query=\" where NTIMESTAMP\# \<\= to_date\(\'20090422\', \'yyyymmdd\'\)\"

Export: Release 11.1.0.6.0 - Production on Tue Apr 21 17:34:26 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table ASMUSER_AUD 3332 rows exported
Export terminated successfully without warnings.
[oracle@crdb11g ~]$


3,将该表删除，然后imp进入（相当于恢复）：

[oracle@crdb11g ~]$ sqlplus asmuser/password

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 21 17:35:32 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table asmuser_aud;

Table dropped.

4，导入数据

imp system/circri file=asmuser090421.dmp fromuser=asmuser touser=asmuser tables=asmuser_aud ignore=y

imp system/cricri file=asmuser090421.dmp fromuser=asmuser touser=asmuser tables=asmuser_aud ignore=y

Import: Release 11.1.0.6.0 - Production on Tue Apr 21 17:37:49 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path

Warning: the objects were exported by ASMUSER, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing ASMUSER's objects into ASMUSER
. . importing table "ASMUSER_AUD" 3332 rows imported
Import terminated successfully without warnings.

[oracle@crdb11g ~]$ sqlplus asmuser/password

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 21 17:38:19 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from asmuser_aud;

COUNT(*)
----------
3332

SQL>

5. 测试aud$的添加，看清楚了是测试
SQL> conn / as sysdba;
Connected.
SQL> truncate table aud$;

Table truncated.

SQL>

--insert into sys.aud$ select * from asmuser.asmuser_aud;

SQL> insert into sys.aud$ select * from asmuser.asmuser_aud;

3332 rows created.

success!!!


方法是不是笨了点，但是安全第一，日后对oracle的理解进一步深入，可能会有更巧妙的办法 ，呵呵!