Oracle 從Dump 檔案裡提取 DDL 語句 方法說明
有關Dump 檔案的命令有exp/imp 和 expdp/impdp。 這四個命令之前都有整理過相關的文章。
exp/imp 與 expdp/impdp 對比 及使用中的一些優化事項
http://space.itpub.net/15880878/viewspace-720015
http://space.itpub.net/15880878/viewspace-720017
Oracle 10g Data Pump Expdp/Impdp 詳解
http://space.itpub.net/15880878/viewspace-720012
Oracle expdp/impdp 從高版本 到 低版本 示例
http://space.itpub.net/15880878/viewspace-720018
對於Dump 檔案,我們不能直接提取出Data資料,但是我們可以通過相關的引數,從Dump檔案中提取出對應的DDL 語句。
(1)如果是匯出匯入(exp/imp),那麼是indexfile引數。
(2)如果是資料泵(expdp/impdp),那麼是sqlfile 引數。
準備工作:
SYS@anqing1(rac1)> create user dvdidentified by dvd;
User created.
SYS@anqing1(rac1)> grant dba to dvd;
Grant succeeded.
SYS@anqing1(rac1)> conn dvd/dvd;
Connected.
DVD@anqing1(rac1)> create table t1(idnumber);
Table created.
DVD@anqing1(rac1)> insert into t1values(1);
1 row created.
DVD@anqing1(rac1)> commit;
Commit complete.
DVD@anqing1(rac1)> create index idx_t1on t1(id);
Index created.
DVD@anqing1(rac1)>
一.使用匯出匯入命令
1.1 匯出dvd 使用者的資料,生成dump檔案
[oracle@rac1 ~]$ exp dvd/dvd file=dvd.dmpowner=dvd
Export: Release 10.2.0.4.0 - Production onWed Sep 21 19:50:14 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set andAL16UTF16 NCHAR character set
server uses ZHS16GBK character set(possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objectsand actions
. exporting foreign function library namesfor user DVD
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions foruser DVD
About to export DVD's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DVD's tables via ConventionalPath ...
. . exporting table T1 1 rows exported
EXP-00091: Exporting questionablestatistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrityconstraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional andextensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objectsand actions
. exporting statistics
Export terminated successfully withwarnings.
1.2 從dump 檔案裡提取DDL語句
[oracle@rac1 ~]$ imp dvd/dvd file=dvd.dmpfromuser=dvd touser=dvd indexfile=dvd.sql
Import: Release 10.2.0.4.0 - Production onWed Sep 21 19:50:50 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 viaconventional path
import done in US7ASCII character set andAL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possiblecharset conversion)
. . skipping table "T1"
Import terminated successfully withoutwarnings.
這裡要注意2點:
(1) 該import 命令並沒有真正的import data,而只是生成了我們對應使用者下所有DDL的sql 語句。
(2) 對於表的DDL語句,用REM 進行了註釋。
[oracle@rac1 ~]$ cat dvd.sql
REM CREATE TABLE "DVD"."T1" ("ID" NUMBER)PCTFREE 10 PCTUSED 40 INITRANS
REM 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
REM BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM ... 1 rows
CONNECT DVD;
CREATE INDEX "DVD"."IDX_T1"ON "T1" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS"LOGGING ;
如果只想看索引的DDL,那麼可以用grep命令,講REM 的不顯示。
http://blog.csdn.net/tianlesoftware/article/details/6277193
[oracle@rac1 ~]$ cat dvd.sql|grep -v REM
CONNECT DVD;
CREATE INDEX"DVD"."IDX_T1" ON "T1" ("ID" ) PCTFREE10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS"LOGGING ;
二.資料泵(expdp/impdp)
2.1 匯出dvd使用者的資料
[oracle@rac1 ~]$ expdp dvd/dvddirectory=backup dumpfile=dvd.dmp schemas=dvd
Export: Release 10.2.0.4.0 - Production onWednesday, 21 September, 2011 20:16:59
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preservedatabase integrity.
Starting "DVD"."SYS_EXPORT_SCHEMA_01": dvd/******** directory=backupdumpfile=dvd.dmp schemas=dvd
Estimate in progress using BLOCKS method...
Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object typeSCHEMA_EXPORT/SYSTEM_GRANT
Processing object typeSCHEMA_EXPORT/ROLE_GRANT
Processing object typeSCHEMA_EXPORT/DEFAULT_ROLE
Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported"DVD"."T1" 4.906 KB 1 rows
Master table"DVD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DVD.SYS_EXPORT_SCHEMA_01is:
/u01/backup/dvd.dmp
Job"DVD"."SYS_EXPORT_SCHEMA_01" successfully completed at20:17:34
2.2 產生DDL
[oracle@rac1 ~]$ impdp dvd/dvddirectory=backup dumpfile=dvd.dmp sqlfile=dvd.sql
Import: Release 10.2.0.4.0 - Production onWednesday, 21 September, 2011 20:18:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
Master table"DVD"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting"DVD"."SYS_SQL_FILE_FULL_01": dvd/******** directory=backupdumpfile=dvd.dmp sqlfile=dvd.sql
Processing object type SCHEMA_EXPORT/USER
Processing object typeSCHEMA_EXPORT/SYSTEM_GRANT
Processing object typeSCHEMA_EXPORT/ROLE_GRANT
Processing object typeSCHEMA_EXPORT/DEFAULT_ROLE
Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object typeSCHEMA_EXPORT/TABLE/TABLE
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job"DVD"."SYS_SQL_FILE_FULL_01" successfully completed at20:18:54
2.3 檢視DDL 文字
[oracle@rac1 backup]$ cat dvd.sql
-- CONNECT DVD
-- new object type path is:SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "DVD" IDENTIFIED BYVALUES '1111602792579CCE'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path is:SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO"DVD";
-- new object type path is:SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "DVD";
-- new object type path is:SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "DVD" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT DVD
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),export_db_name=>'ANQING.REGRESS.RDBMS.DEV.US.ORACLE.COM',inst_scn=>'9530068');
COMMIT;
END;
/
-- new object type path is:SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "DVD"."T1"
( "ID" NUMBER
)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)
TABLESPACE "USERS" ;
-- new object type path is:SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX"DVD"."IDX_T1" ON "DVD"."T1"("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "DVD"."IDX_T1" NOPARALLEL;
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE IND_NAME VARCHAR2(60);
IND_OWNER VARCHAR2(60);
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
IND_NAME := 'IDX_T1'; IND_OWNER:= 'DVD';
INSERT INTO "SYS"."IMPDP_STATS" (type, version,flags, c1, c2, c3, c5,
n1, n2, n3, n4, n5, n6, n7, n8,n9, n10, n11, n12, d1)
VALUES ('I', 4, 0, IND_NAME, NULL, NULL, 'DVD', 1, 1, 1, 1, 1, 1, 0, 1,NULL, NULL, NULL, NULL, TO_DATE('2011-09-21 19:45:20','YYYY-MM-DD:HH24:MI:SS'));
DBMS_STATS.IMPORT_INDEX_STATS( '"' || ind_owner || '"','"' || ind_name || '"', NULL, '"IMPDP_STATS"', NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
從exp/imp 與 expdp/impdp 的DDL 結果進行對比,expdp/impdp 提取DDL 語句的更詳細,可讀性要好很多。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15880878/viewspace-720019/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle中獲取TABLE的DDL語句的方法Oracle
- oracle檢視物件DDL語句Oracle物件
- 如何從 dump 檔案中提取出 C# 原始碼?C#原始碼
- oracle快速拿到重建控制檔案語句的方法二Oracle
- Oracle安裝光碟內容的檔案說明Oracle
- C++檔案說明及使用方法C++
- fepk檔案格式說明
- Oracle vs PostgreSQL,研發注意事項(2)-DDL語句與事務OracleSQL
- Nginx的配置檔案說明Nginx
- Oracle Latch 說明Oracle
- 6.3. 基本SQL語句——6.3.1. DDLSQL
- MySQL入門---(一)SQL的DDL語句MySql
- nginx日誌配置檔案說明Nginx
- Oracle exp dmp包檔案轉化為insert語句,extract dmp to sqlfileOracleSQL
- 流量中提取檔案的若干種方法
- ORACLE常用語句:Oracle
- 說說 Python 的 if 語句Python
- TPCH模型規範、測試說明及22條語句模型
- oracle orapwd使用說明Oracle
- 【ROWID】Oracle rowid說明Oracle
- django的初始化檔案說明Django
- Python提取文字檔案(.txt)資料的方法Python
- SQL語句收縮日誌檔案SQL
- MySQL 執行DDL語句 hang住了怎麼辦?MySql
- Oracle基本SQL語句OracleSQL
- Oracle 建立序列語句Oracle
- Hadoop之HDFS檔案讀寫流程說明Hadoop
- CentOS8中systemd配置檔案說明CentOS
- Mybatis 裡對映檔案的動態 SQL 語句,實現if,where,foreache的SQL語句動態拼接查詢MyBatisSQL
- 關於證書,如何使用 go 語言從 pfx 檔案中提取出私鑰Go
- jacoco + ant 如何從 k8s 容器 dump 出 exec 檔案K8S
- 註冊 sql語句+後端PHP檔案SQL後端PHP
- 【nacos】透過curl語句更新配置檔案
- !!!提取檔案記錄!!!
- Linux中log檔案是什麼意思?Linux日誌檔案說明Linux
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- 易優CMS模板目錄各檔案說明
- 如何用Python從PDF檔案中提取文字詞彙Python
- SQL查詢語句 (Oracle)SQLOracle