您好,登錄后才能下訂單哦!
編者按
云和恩墨大講堂社群(本文底部有入群方式)里有人提出一個(gè)需求:一張表數(shù)據(jù)量很大,如何只導(dǎo)出其中一部分列?云和恩墨CTO、Oracle ACE總監(jiān)、ACOUG核心專家楊廷琨老師使用了數(shù)據(jù)泵的方式,細(xì)致入微地解釋了過(guò)程并給出具體的代碼實(shí)現(xiàn)。數(shù)據(jù)和云(ID:OraNews)獨(dú)家發(fā)布,以饗讀者。
以下為正文
無(wú)論是老版本exp還是數(shù)據(jù)泵expdp,Oracle都提供了QUERY的功能,這使得查詢表中部分記錄的功能可以實(shí)現(xiàn),但是QUERY只能過(guò)濾行,而不能過(guò)濾列,Oracle數(shù)據(jù)泵會(huì)讀取表中全部列的。
在12c中,Oracle為數(shù)據(jù)泵提供了VIEW功能,使得導(dǎo)出的時(shí)候可以根據(jù)視圖的定義來(lái)導(dǎo)出表中的數(shù)據(jù):
SQL>select banner from v$version; BANNER-------------------------------------------------------------------------------- OracleDatabase 18c Enterprise Edition Release 18.0.0.0.0 - Production SQL>CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES;
(左右滑動(dòng)查看代碼部分,下同)
表已創(chuàng)建。
SQL>create view v_tables as select owner, table_name, tablespace_name from t_tableswhere owner not in ('SYS');
視圖已創(chuàng)建。
利用數(shù)據(jù)泵的VIEWS_AS_TABLES參數(shù)可以直接導(dǎo)出視圖對(duì)應(yīng)的表數(shù)據(jù):
C:\Users\yangt>expdpc##u1 directory=d_output dumpfile=t_tab_view views_as_tables=v_tables
Export:Release 18.0.0.0.0 - Production on 星期二 2月 26 13:37:312019
Version18.3.0.0.0
Copyright(c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
口令:
連接到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -Production
警告: 連接到容器數(shù)據(jù)庫(kù)的根或種子時(shí)通常不需要 Oracle Data Pump 操作。
啟動(dòng) "C##U1"."SYS_EXPORT_TABLE_01": c##u1/******** directory=d_outputdumpfile=t_tab_view views_as_tables=v_tables 處理對(duì)象類型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA 處理對(duì)象類型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE . . 導(dǎo)出了"C##U1"."V_TABLES" 8.289 KB 71 行 已成功加載/卸載了主表"C##U1"."SYS_EXPORT_TABLE_01" ****************************************************************************** C##U1.SYS_EXPORT_TABLE_01的轉(zhuǎn)儲(chǔ)文件集為: D:\TEMP\T_TAB_VIEW.DMP 作業(yè) "C##U1"."SYS_EXPORT_TABLE_01" 已于 星期二 2月 26 13:37:40 2019 elapsed 0 00:00:06 成功完成
雖然是通過(guò)視圖導(dǎo)出的,但是數(shù)據(jù)泵把它當(dāng)作一個(gè)表來(lái)處理,因此這個(gè)dump文件是可以直接導(dǎo)入到數(shù)據(jù)庫(kù)中變成表的:
SQL>drop view v_tables;
視圖已刪除。
完成導(dǎo)入操作:
C:\Users\yangt>impdpc##u1 directory=d_output dumpfile=t_tab_view full=y Import:Release 18.0.0.0.0 - Production on 星期二 2月 26 14:33:022019 Version18.3.0.0.0 Copyright(c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. 口令: 連接到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -Production
警告: 連接到容器數(shù)據(jù)庫(kù)的根或種子時(shí)通常不需要 Oracle Data Pump 操作。
已成功加載/卸載了主表"C##U1"."SYS_IMPORT_FULL_01" 啟動(dòng) "C##U1"."SYS_IMPORT_FULL_01": c##u1/******** directory=d_outputdumpfile=t_tab_view full=y 處理對(duì)象類型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE處理對(duì)象類型 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA . . 導(dǎo)入了"C##U1"."V_TABLES" 8.289 KB 71 行 作業(yè) "C##U1"."SYS_IMPORT_FULL_01" 已于 星期二 2月 26 14:33:07 2019 elapsed 0 00:00:02 成功完成
查詢表數(shù)據(jù):
SQL>select count(*) from v_tables; COUNT(*) ---------- 71 SQL>select table_name, tablespace_name from user_tables where table_name ='V_TABLES'; TABLE_NAME TABLESPACE_NAME------------------------------------------------------------ V_TABLES USERS
這個(gè)功能是12c的特新特,如果是12c之前的版本,可以考慮使用ORACLE_DATAPUMP類型的外部表來(lái)實(shí)現(xiàn):
SQL>create table t_external_tables (owner, table_name, tablespace_name)organization external 2 (type oracle_datapump 3 default directory d_output 4 location ('external_table.dp')) 5 asselect owner, table_name, tablespace_name from t_tables where owner not in('SYS');
表已創(chuàng)建。
通過(guò)將外部表導(dǎo)出的 external_table.dp 文件拷貝到目標(biāo)環(huán)境對(duì)應(yīng)的目錄下,根據(jù)上面的定義重建外部表,注意目標(biāo)環(huán)境創(chuàng)建過(guò)程由于已經(jīng)拷貝了導(dǎo)出的數(shù)據(jù),因此語(yǔ)法截至到 location 語(yǔ)句就結(jié)束了,不要再包含 as select 部分。
SQL>create table t_target_ext_tab (owner varchar2(30), table_name varchar2(128),tablespace_name varchar2(30)) 2 organization external 3 (type oracle_datapump 4 default directory d_output 5 location ('external_table.dp'));
表已創(chuàng)建。
SQL>select count(*) from t_target_ext_tab; COUNT(*)---------- 71 SQL>col table_name for a30SQL>select * from t_target_ext_tab where owner = 'SYSTEM'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------------------------------------ ------------------------------ SYSTEM OL$SYSTEM OL$HINTS SYSTEM OL$NODES SYSTEM HELP SYSTEM
所以對(duì)于11g和10g的版本,也可以不通過(guò)新建表,然后拷貝數(shù)據(jù)的方式來(lái)實(shí)現(xiàn)數(shù)據(jù)的導(dǎo)出和轉(zhuǎn)移。但是如果是9i及以前的版本,那么要不然通過(guò)建表然后exp導(dǎo)出的方式,要不然自己寫程序?qū)崿F(xiàn)數(shù)據(jù)的導(dǎo)出吧。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。