[20150729]資料泵造成的資料損失2.txt
[20150729]資料泵造成的資料損失2.txt
--前一陣子，重複測試：
http://blog.itpub.net/267265/viewspace-1725204/
--參看連結，重複測試
1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLE T_PART PARTITION BY RANGE (CREATED)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2012-2-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2012-3-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (TO_DATE('2012-4-1', 'YYYY-MM-DD')),
PARTITION PMAX VALUES LESS THAN (MAXVALUE))
AS SELECT * FROM DBA_OBJECTS;
SCOTT@test> select count(*) from t_part partition (p1);
COUNT(*)
----------
67590
SCOTT@test> select count(*) from t_part partition (p2);
COUNT(*)
----------
115
SCOTT@test> select count(*) from t_part partition (p3);
COUNT(*)
----------
9
SCOTT@test> select count(*) from t_part partition (p4);
COUNT(*)
----------
31
SCOTT@test> select count(*) from t_part partition (pmax);
COUNT(*)
----------
10082
$ expdp scott/btbtms directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part.log tables=t_part:p3,t_part:p4
Export: Release 11.2.0.3.0 - Production on Wed Jul 29 08:50:35 2015
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, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/a******* directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part.log tables=t_part:p3,t_part:p4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 16 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_PART":"P3" 11.49 KB 9 rows
. . exported "SCOTT"."T_PART":"P4" 13.89 KB 31 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle11g/admin/test/dpdump/t_part.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 08:51:03
--上次我使用如下命令會導致其它分割槽的資訊刪除。這次不做了，參考：
http://blog.itpub.net/267265/viewspace-1725204/
2.匯入1個分割槽：
impdp scott/btbtms directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p3 table_exists_action=replace
--實際上imdpd匯入分割槽也存在如下引數:
PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].
--預設是NONE,這樣會匯出其它分割槽的資訊破壞。加入引數PARTITION_OPTIONS=DEPARTITION看看:
impdp scott/btbtms directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p3 table_exists_action=replace PARTITION_OPTIONS=DEPARTITION
$ impdp scott/btbtms directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p3 table_exists_action=replace PARTITION_OPTIONS=DEPARTITION
Import: Release 11.2.0.3.0 - Production on Wed Jul 29 08:55:03 2015
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, 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/x******* directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p3 table_exists_action=replace PARTITION_OPTIONS=DEPARTITION
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T_PART_P3" 11.49 KB 9 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 08:55:11
--如果你仔細看imported 那行，可以發現實際上是建立了一個新表SCOTT.T_PART_P3.
SCOTT@test> select count(*) from t_part_p3;
COUNT(*)
----------
9
SCOTT@test> select count(*) from t_part;
COUNT(*)
----------
77827
--看看要好好理解PARTITION_OPTIONS的含義。原來t_part並沒有變化。
3.做一次merge看看。
impdp scott/btbtms directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p4 table_exists_action=replace PARTITION_OPTIONS=MERGE
$ impdp scott/btbtms directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p4 table_exists_action=replace PARTITION_OPTIONS=MERGE
Import: Release 11.2.0.3.0 - Production on Wed Jul 29 09:10:04 2015
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, 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/a******* directory=DATA_PUMP_DIR dumpfile=t_part.dmp logfile=t_part_imp.log tables=t_part:p4 table_exists_action=replace PARTITION_OPTIONS=MERGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T_PART":"P4" 13.89 KB 31 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 09:10:06
SCOTT@test> select count(*) from t_part partition (p4);
select count(*) from t_part partition (p4)
*
ERROR at line 1:
ORA-14501: object is not partitioned
--why?什麼回事?
SCOTT@test> select count(*) from t_part;
COUNT(*)
----------
31
SCOTT@test> @ddl scott.t_part
C100
-----------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T_PART"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--可以發現t_part定義被覆蓋了，變成了普通表。
--這些在以後工作中要注意，再次說明理解引數以及測試很重要。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1754950/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵造成的資料損失
- [20150707]資料泵造成的資料損失.txt
- 【北亞資料恢復】企業如何避免伺服器資料丟失造成重大損失？資料恢復伺服器
- 刪除資料泵備份失敗的表
- 資料泵
- 資料檔案損壞、丟失
- Oracle資料恢復:強制Resetlogs的可能資料損失Oracle資料恢復
- 資料泵的使用
- Redis 的 KEYS 命令引起 RDS 資料庫雪崩，當機 2 次，造成幾百萬損失Redis資料庫
- ORACLE 資料泵Oracle
- oracle資料泵Oracle
- 資料檔案丟失損壞的恢復--
- 陣列櫃故障造成控制檔案損壞，資料檔案損壞陣列
- Oracle 資料泵的使用Oracle
- 資料庫升級之-資料泵資料庫
- 使用資料泵impdp匯入資料
- 殺停資料泵
- oracle 資料泵解析Oracle
- 資料泵 impdp 操作
- 資料泵檔案
- 資料泵小bug
- oracle之資料泵Oracle
- 海量資料處理_資料泵分批資料遷移
- 資料泵引數檔案用於執行資料泵命令
- 資料泵避免個別表資料的匯出
- 【北亞資料恢復】誤操作分割槽損壞導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- Impdp資料泵匯入
- oracle 資料泵引數Oracle
- 資料泵用法筆記筆記
- 資料泵過濾匯出資料的where條件
- 對資料泵資料傳輸的時間統計
- 資料泵避免個別表資料的匯出（二）
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- 使用資料泵工具expdp工具匯出資料
- 【移動資料】data pump（上） 資料泵概述
- 資料泵匯出資料包錯處理
- 資料庫遷移之資料泵實驗資料庫