【IMPDP】使用 TRANSFORM選項去掉表空間和儲存子句
使用IMPDP工具完成資料匯入時,會按照dump檔案中有關的儲存的引數資訊完成資料的匯入。很多情況下我們希望按照被匯入使用者的預設引數完成資料的匯入,此時我們可以使用IMPDP的TRANSFORM引數輔助完成。
1.IMPDP的TRANSFORM引數描述
secooler@secDB /expdp$ impdp help=y
……省略……
TRANSFORM
Metadata transform. to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.
……省略……
2.建立一個測試表T
sec@11gR2> create table t (x varchar2(8));
Table created.
sec@11gR2> insert into t values ('secooler');
1 row created.
sec@11gR2> commit;
Commit complete.
3.使用EXPDP生成表T的邏輯備份檔案
secooler@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t
Export: Release 11.2.0.1.0 - Production on Thu May 13 09:32:44 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 5.007 KB 1 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_expdp.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 09:32:57
4.生成dump檔案中的SQL語句
1)使用SQLFILE引數生成SQL建立語句
secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql
Import: Release 11.2.0.1.0 - Production on Thu May 13 09:33:23 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SEC"."SYS_SQL_FILE_FULL_01": sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 09:33:26
2)檢視sec_expdp.sql檔案獲得SQL建立語句
secooler@secDB /expdp$ cat sec_expdp.sql
-- CONNECT SEC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SEC"."T"
( "X" VARCHAR2(8 BYTE)
) 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 "TBS_SEC_D" ;
可見,此時包含了大量的儲存引數及表空間引數。
5.使用TRANSFORM去掉表空間和儲存子句
secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n
Import: Release 11.2.0.1.0 - Production on Thu May 13 09:34:12 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SEC"."SYS_SQL_FILE_FULL_01": sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 09:34:14
再次檢視生成的穿件SQL語句:
secooler@secDB /expdp$ cat sec_expdp.sql
-- CONNECT SEC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SEC"."T"
( "X" VARCHAR2(8 BYTE)
) ;
此時生成的表T建立語句非常的簡介,沒錯,就是這麼簡單。
6.小結
使用TRANSFORM選項可以完成去掉表空間和儲存子句的目的,這樣我們便可以控制匯入時按照目標預設的引數。
我們的目標:所有要完成的任務都要在自己的掌控之中,UNDER CONTROL!
Good luck.
secooler
19.05.12
-- The End --
1.IMPDP的TRANSFORM引數描述
secooler@secDB /expdp$ impdp help=y
……省略……
TRANSFORM
Metadata transform. to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.
……省略……
2.建立一個測試表T
sec@11gR2> create table t (x varchar2(8));
Table created.
sec@11gR2> insert into t values ('secooler');
1 row created.
sec@11gR2> commit;
Commit complete.
3.使用EXPDP生成表T的邏輯備份檔案
secooler@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t
Export: Release 11.2.0.1.0 - Production on Thu May 13 09:32:44 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SEC"."T" 5.007 KB 1 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec_expdp.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 09:32:57
4.生成dump檔案中的SQL語句
1)使用SQLFILE引數生成SQL建立語句
secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql
Import: Release 11.2.0.1.0 - Production on Thu May 13 09:33:23 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SEC"."SYS_SQL_FILE_FULL_01": sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 09:33:26
2)檢視sec_expdp.sql檔案獲得SQL建立語句
secooler@secDB /expdp$ cat sec_expdp.sql
-- CONNECT SEC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SEC"."T"
( "X" VARCHAR2(8 BYTE)
) 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 "TBS_SEC_D" ;
可見,此時包含了大量的儲存引數及表空間引數。
5.使用TRANSFORM去掉表空間和儲存子句
secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n
Import: Release 11.2.0.1.0 - Production on Thu May 13 09:34:12 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SEC"."SYS_SQL_FILE_FULL_01": sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 09:34:14
再次檢視生成的穿件SQL語句:
secooler@secDB /expdp$ cat sec_expdp.sql
-- CONNECT SEC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SEC"."T"
( "X" VARCHAR2(8 BYTE)
) ;
此時生成的表T建立語句非常的簡介,沒錯,就是這麼簡單。
6.小結
使用TRANSFORM選項可以完成去掉表空間和儲存子句的目的,這樣我們便可以控制匯入時按照目標預設的引數。
我們的目標:所有要完成的任務都要在自己的掌控之中,UNDER CONTROL!
Good luck.
secooler
19.05.12
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-662584/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何刪除 Mac 儲存空間的其他選項?Mac
- innodb表空間儲存結構
- Mac蘋果儲存空間的其他選項該如何刪除?Mac蘋果
- 表空間OFFLINE的3種選項。
- 2、儲存容量和儲存地址空間的轉換
- IMPDP匯入沒有某個表空間的處理方法__加上引數TRANSFORM就可以了ORM
- Synology群暉NAS儲存正確建立儲存池和儲存空間的方法
- 臨時表空間和回滾表空間使用率查詢
- IMPDP分割槽表注意事項
- oracle 建立表空間和使用者Oracle
- Oracle建立表空間和使用者Oracle
- dbms_lob儲存過程導致臨時表空間100%儲存過程
- Mac新手使用技巧——如何在Mac上優化儲存空間Mac優化
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- MySQL 中的共享表空間與獨立表空間如何選擇MySql
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- 3-04. 實現箱子儲物空間的儲存和資料交換
- 16、表空間 建立表空間
- win10保留的儲存空間怎麼檢視_win10保留的儲存空間的檢視方法Win10
- Oracle 建立表空間和使用者指令碼Oracle指令碼
- 多名稱空間結合cgroup保障儲存QoS
- 鐵威馬NAS建立儲存空間的方法
- UniCloud空間雲端儲存圖床原始碼Cloud圖床原始碼
- 本地儲存-系統和保留-系統檔案佔用儲存空間過大的解決方式
- [20200330]sar報表儲存時間.txt
- Oracle新建使用者、表空間、表Oracle
- InnoDB的邏輯儲存結構是什麼,表空間組成包括哪些?
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 讀取和儲存Excel表Excel
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- Python 儲存字串時是如何節省空間的?Python字串
- PG的物理儲存結構、版本控制、空間回收
- 伺服器儲存空間不足怎麼處理?伺服器
- 3. 使用者和表空間—3.1. 使用者
- 雲空間服務,助力使用者資料儲存與協同
- 教程示例:控制儲存空間和資料夾的訪問許可權訪問許可權
- 達夢表空間管理注意事項總結
- 查詢表空間使用情況
- 表空間使用量查詢