创建测试表,同时drop对应测试表，模拟drop table的案例
SQL> create table mdata_test ( a number,b varchar2(10),c nvarchar2(30),d varchar2(20),e date);
Table created.
SQL> insert into mdata_test select rownum,lpad('x',10),'NC测试' || rownum, 'ZHS测试'|| rownum,sysdate+dbms_random.value(0,100) from dba_objects where rownum< =20000;
20000 rows created.
SQL> commit;
Commit complete.
SQL> select object_id,object_name from dba_objects where object_name='MDATA_TEST';
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
51997
MDATA_TEST
SQL> drop table mdata_test_bak purge;
Table dropped.
SQL> create table mdata_test_bak as select * from mdata_test;
Table created.
SQL> drop table mdata_test purge;
Table dropped.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
加载mdata
olm@hc10 /home/oracle/MDATA$ ./MDATA.sh MDATA for oracle 9I,10G,11G, release 3.0.3 (@)copyright LUDATOU,HC all rights reserved. Web:www.ludatou.com,www.hcdba.com Email:feigigi@qq.com,564439763@qq.com QQ group:66809572 loading default config....... load config file 'config.txt' successful loading default asm disk file ...... start loading default control file ...... load control file 'control.txt' successful load BOOTSTRAP$ success. load TAB$ success. load COL$ success. load OBJ$ success. load USER$ success. load PROPS$ success. load TABPART$ success. load TABSUBPART$ success. load IND$ success.
先对被drop的表所在tablespace进行scan做操
MDATA>scan extent tablespace 4 scan extent start: Tue Jan 20 22:23:10 CST 2015 scanning extent... scanning extent finished. scan extent completed: Tue Jan 20 22:23:12 CST 2015
接着通过drop恢复的语法对drop的表进行恢复
MDATA>unload object 51994 column number varchar2 nvarchar2 varchar2 date Unloading Object,object ID: 51994, Cluster: 0 -1 rows unloaded MDATA>unload object 51997 column number varchar2 nvarchar2 varchar2 date Unloading Object,object ID: 51997, Cluster: 0 20000 rows unloaded MDATA>exit
恢复结束后，切换到mdata的ddl目录，通过生成的ddl语句在instance中重建该table
olm@hc10 /home/oracle/MDATA$ cd ddl olm@hc10 /home/oracle/MDATA/ddl$ sqlplus hc/hc SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 20 22:23:56 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> host ls appclean.sql MDATA_0000051979.sql MDATA_0000051994.sql SYS.IND.sql SYS.TABPART.sql SYS.USER.sql app.sql MDATA_0000051981.sql MDATA_0000051997.sql SYS.PROPS.sql SYS.TABSUBPART.sql SQL> host ls -lrt total 44 -rw-r--r-- 1 oracle oinstall 281 Oct 6 16:57 appclean.sql -rw-r--r-- 1 oracle oinstall 1909 Oct 6 17:44 app.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 16:22 MDATA_0000051979.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 16:53 MDATA_0000051981.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 22:20 MDATA_0000051994.sql -rw-r--r-- 1 oracle oinstall 1328 Jan 20 22:23 SYS.USER.sql -rw-r--r-- 1 oracle oinstall 193 Jan 20 22:23 SYS.PROPS.sql -rw-r--r-- 1 oracle oinstall 1367 Jan 20 22:23 SYS.TABSUBPART.sql -rw-r--r-- 1 oracle oinstall 1277 Jan 20 22:23 SYS.TABPART.sql -rw-r--r-- 1 oracle oinstall 1761 Jan 20 22:23 SYS.IND.sql -rw-r--r-- 1 oracle oinstall 249 Jan 20 22:23 MDATA_0000051997.sql SQL> @MDATA_0000051997.sql Table created. SQL> quit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
接着切换到mdata目录下的data目录，通过sqlldr工具，对导出来的数据恢复到被刚创建的表中去
olm@hc10 /home/oracle/MDATA/ddl$ cd ../data olm@hc10 /home/oracle/MDATA/data$ sqlldr "'hc/hc' control=MDATA_0000051997.ctl" SQL*Loader: Release 10.2.0.5.0 - Production on Tue Jan 20 22:24:51 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 6502 Commit point reached - logical record count 13004 Commit point reached - logical record count 19506 Commit point reached - logical record count 20000
导入完成后，登入数据库验证恢复
olm@hc10 /home/oracle/MDATA/data$ sqlplus / as sysdba; SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 20 22:25:08 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn hc/hc Connected. SQL> select * from mdata_test_bak minus select * from MDATA_0000051997; no rows selected
至此drop的恢复完成。
使用mdata恢复drop的表需要知道该对象的object id，同时需要知道该表的表结构（字段，字段属性）
