一.3.2 gdul恢复truncate的表
set time on;
set timing on;
set serveroutput on;
drop table scott.TB_0322_05;
create table scott.TB_0322_05 as SELECT * FROM dba_objects;
SELECT COUNT(1) FROM scott.TB_0322_05;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
SELECT COUNT(1) FROM scott.TB_0322_05;
SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0322_05';
truncate table scott.TB_0322_05;
alter system checkpoint;
col ownere format a10
col DIRECTORY_NAME format a30
col DIRECTORY_PATH format a50
select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
bootstrap
desc scott.TB_0322_05
unload table scott.TB_0322_05
scan tablespace 4
untrunc table scott.TB_0322_05
cp SCOTT_TB_0322_05.dmp /oracle/app/oracle/admin/oralhr/dpdump/
impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05
15:41:04 SQL> set time on;
15:59:49 SQL> set timing on;
15:59:49 SQL> set serveroutput on;
15:59:49 SQL> drop table scott.TB_0322_05;
create table scott.TB_0322_05 as SELECT * FROM dba_objects;
SELECT COUNT(1) FROM scott.TB_0322_05;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
Table dropped.
Elapsed: 00:00:00.07
15:59:49 SQL> COMMIT;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
INSERT INTO scott.TB_0322_05 SELECT * FROM scott.TB_0322_05;
COMMIT;
SELECT COUNT(1) FROM scott.TB_0322_05;
SELECT d.BYTES/1024/1024 FROM dba_segments d WHERE d.segment_name ='TB_0322_05';
truncate table scott.TB_0322_05;
alter system checkpoint;
Table created.
Elapsed: 00:00:00.97
15:59:50 SQL> 15:59:50 SQL>
COUNT(1)
----------
75707
Elapsed: 00:00:00.86
15:59:51 SQL>
75707 rows created.
Elapsed: 00:00:00.23
15:59:52 SQL>
Commit complete.
Elapsed: 00:00:00.17
15:59:52 SQL>
151414 rows created.
Elapsed: 00:00:00.50
15:59:52 SQL>
Commit complete.
Elapsed: 00:00:00.23
15:59:52 SQL>
302828 rows created.
Elapsed: 00:00:01.63
15:59:54 SQL>
Commit complete.
Elapsed: 00:00:00.22
15:59:54 SQL>
605656 rows created.
Elapsed: 00:00:06.19
16:00:00 SQL>
Commit complete.
Elapsed: 00:00:00.02
16:00:01 SQL>
COUNT(1)
----------
1211312
Elapsed: 00:00:00.07
16:00:01 SQL> 16:00:01 SQL>
D.BYTES/1024/1024
-----------------
136
Elapsed: 00:00:00.17
16:00:01 SQL> 16:00:01 SQL> 16:00:01 SQL>
Table truncated.
Elapsed: 00:00:01.26
16:00:02 SQL> 16:00:02 SQL>
System altered.
Elapsed: 00:00:00.15
16:00:02 SQL>
16:00:02 SQL> SELECT COUNT(1) FROM scott.TB_0322_05;
COUNT(1)
----------
0
Elapsed: 00:00:00.00
16:02:35 SQL>
[oracle@ZFFR4CB1101:/home/oracle/gdul]$ ./gdul
*********************************************************************
GDUL for ORACLE DB.
Version 3.5.0.1, build date: 2016.03.07.
Copyright (c) 2007, 2016. Andy Geng. ALL RIGHTS RESERVED.
Email: gengyonghui@aliyun.com
QQ group: 235019291, WeChat Official Account: dbtool
*********************************************************************
GDUL> bootstrap
Bootstrap finish.
GDUL> desc scott.TB_0322_05
object_id: 78302, dataobj#: 78303, cluster tab#: 0
segment header: (ts#: 4, rfile#: 4, block#: 682))
Seg Column# Column# Name Null? Type
------------ ---------- -------------------- --------------- --------------
1 1 OWNER VARCHAR2(30)
2 2 OBJECT_NAME VARCHAR2(128)
3 3 SUBOBJECT_NAME VARCHAR2(30)
4 4 OBJECT_ID NUMBER
5 5 DATA_OBJECT_ID NUMBER
6 6 OBJECT_TYPE VARCHAR2(19)
7 7 CREATED DATE
8 8 LAST_DDL_TIME DATE
9 9 TIMESTAMP VARCHAR2(19)
10 10 STATUS VARCHAR2(7)
11 11 TEMPORARY VARCHAR2(1)
12 12 GENERATED VARCHAR2(1)
13 13 SECONDARY VARCHAR2(1)
14 14 NAMESPACE NUMBER
15 15 EDITION_NAME VARCHAR2(30)
GDUL> unload table scott.TB_0322_05
2016-03-22 16:01:54...unloaded "SCOTT"."TB_0322_05" 0 rows
GDUL> scan tablespace 4
start scan tablespace 4...
scan tablespace completed.
GDUL> untrunc table scott.TB_0322_05
2016-03-22 16:04:29...untruncating table TB_0322_05 1211312 rows unloaded.
GDUL>
16:02:35 SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ -----------------------------------------------------------------------
SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
8 rows selected.
Elapsed: 00:00:00.00
16:05:29 SQL>
[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$ impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05
Import: Release 11.2.0.3.0 - Production on Tue Mar 22 16:16:48 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=DATA_PUMP_DIR dumpfile=SCOTT_TB_0322_05.dmp LOGFILE=SCOTT_TB_0322_05.log TABLES=TB_0322_05
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TB_0322_05" 117.1 MB 1211312 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:16:59
[oracle@ZFFR4CB1101:/home/oracle/gdul/dump]$
[oracle@ZFFR4CB2101:/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 22 16:17:39 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> SELECT COUNT(1) FROM scott.TB_0322_05;
COUNT(1)
----------
1211312
SQL>
数据成功恢复。
4 实验总结
总体而言用fy_recover_data包或GDUL工具都是非常好的，fy_recover_data可以恢复truncate的数据，但不能恢复drop的数据，而GDUL工具就比较全面了，具体可以参考前边的简介或下载文档来看，小麦苗的共享云盘里也有比较全的文档。
