《Oracle DBA工作筆記》第二章 常用工具和問題分析
《Oracle DBA工作筆記》第二章 常用工具和問題分析
一.1 BLOG文件結構圖
一.2 本文簡介
建榮的新書《Oracle DBA工作筆記》第二章的目錄如下圖，主要講解了SQL*Plus、exp/imp、expdp/impdp以及常見的問題分析，第二章的目錄如下：
下邊小麥苗將自己閱讀完第二章後整理的一些內容分享給大家。
一.3 第一章內容修改
一.3.1 刪除資料庫的幾種方式
這個內容是第一章（http://blog.itpub.net/26736162/viewspace-2121930/）小麥苗列出來的，但是中間發現一個問題，就是當要刪除的庫是rac庫的時候，採用dbca -silent靜默方式刪除資料庫是可以的，但是使用drop database的方式就不行了，報錯：ORA-01586: database must be mounted EXCLUSIVE and not open for this operation，這個時候需要我們關閉叢集引數cluster_database才可以刪除，命令為：alter system set cluster_database=false sid='*' scope=spfile;，所以小麥苗還是推薦靜默的方式，無論建庫還是刪庫靜默方式把很多內容自動完成，不用我們做太多。
1、dbca靜默刪庫：dbca -silent -deleteDatabase -sourceDB mydb
2、SQL視窗：
alter database close;
alter system enable restricted session;
drop database;
3、SQL視窗：
sql > startup force mount restrict;
sql > drop database;
注意：強烈推薦第一種辦法，以上2和3的辦法若是rac庫需要設定cluster_database為false後才可以執行drop database，命令為：alter system set cluster_database=false sid='*' scope=spfile;
一.4 第二章內容
一.4.1 orabase命令
簡單點說，這個命令可以列印$ORACLE_BASE的值。
[ZFZHLHRDB1:oracle]:/oracle>which orabase
/oracle/app/11.2.0/grid/bin/orabase
[ZFZHLHRDB1:oracle]:/oracle>orabase
/oracle/app/oracle
[ZFZHLHRDB1:oracle]:/oracle>
一.4.2 SQL*Plus的使用
一.4.2.1 登入配置
小麥苗的配置一般是這樣的：
[ZFZHLHRDB1:oracle]:/oracle>more $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- All rights reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
set sqlprompt "_user'@'_connect_identifier> "
[ZFZHLHRDB1:oracle]:/oracle>
[ZFZHLHRDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 2 14:45:02 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@test1>
一.4.2.2 得到show sga的定義語句
建榮這個章節講的很詳細，小麥苗直接列出最終的結果吧。我們執行命令vi $ORACLE_HOME/bin/sqlplus開啟sqlplus檔案，匹配SGA可以發現這麼一行程式碼：
SELECT DECODE(NULL, '', 'Total System Global Area', '') NAME_COL_PLUS_SHOW_SGA,
SUM(VALUE),
DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA
FROM V$SGA
UNION ALL
SELECT NAME NAME_COL_PLUS_SHOW_SGA,
VALUE,
DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA
FROM V$SGA;
該行程式碼的結果和執行show sga可以得到一樣的結果。
SYS@omflhr> show sga
Total System Global Area 1068937216 bytes
Fixed Size 2253216 bytes
Variable Size 771755616 bytes
Database Buffers 289406976 bytes
Redo Buffers 5521408 bytes
SYS@omflhr> SELECT DECODE(NULL, '', 'Total System Global Area', '') NAME_COL_PLUS_SHOW_SGA,
2 SUM(VALUE),
3 DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA
4 FROM V$SGA
5 UNION ALL
6 SELECT NAME NAME_COL_PLUS_SHOW_SGA,
7 VALUE,
8 DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA
9 FROM V$SGA;
NAME_COL_PLUS_SHOW_SGA SUM(VALUE) UNITS_COL_PLUS_
------------------------ ---------- ---------------
Total System Global Area 1068937216 bytes
Fixed Size 2253216 bytes
Variable Size 771755616 bytes
Database Buffers 289406976 bytes
Redo Buffers 5521408 bytes
show sga的官方解釋：
Displays information about the current instance's System Global Area. You need SELECT ON V_$SGA object privileges otherwise you will receive a message
ORA-00942: table or view does not exist
show sga中，各部分的含義如下：
1. Total System Global Area：Fixed Size、Variable Size、 Database buffers和Redo Buffers的大小總和
2. Fixed Size: 這部分是Oracle內部使用的一個區，包括了資料庫與例項的控制資訊、狀態資訊、字典資訊等，啟動時就被固定在SGA中，不會改變。Oracle透過這個區找到SGA其他區，類似一個SGA各個元件的索引，裡面儲存了SGA 各部分元件的資訊，可以看作引導建立SGA的區域，不同平臺和不同版本下這部分的大小可能不一樣。
3. Variable Size: 包括Shared Pool ,Java Pool ,Large Pool,stream pool、遊標區和其他結構。
4. Database Buffers: 資料庫中資料塊緩衝的地方，是SGA中最大的地方，決定資料庫效能。為db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size的總大小，當然這是sga_target為0的情況，也就是手動SGA管理模式下，如果是自動SGA管理(sga_target>0)，則這個值根據sga的分配情況自動進行調整。
5. Redo Buffers:這部分是實際分配的Redo log buffer的大小，由初始化引數log_buffer根據SGA的最小分配單位granule 向上取整得到。提供REDO緩衝的地方，在OLAP中不需要太大。在這裡要額外說明一點的是，對於v$parameter、v$sgastat、v$sga查詢值可能不一樣。v$parameter 裡面的值，是指使用者在初始化引數檔案裡面設定的值，v$sgastat是oracle 實際分配的日誌緩衝區大小（因為緩衝區的分配值實際上是離散的，也不是以block 為最小單位進行分配的），v$sga 裡面查詢的值，是在oracle 分配了日誌緩衝區後，為了保護日誌緩衝區，設定了一些保護頁，通常我們會發現保護頁大小是8k(不同環境可能不一樣)
一.4.2.3 SQL*Plus命令設定
關於更多SQL*Plus的命令設定請參考：【OH】SET System Variable Summary SQLPLUS 系統變數設定： http://blog.itpub.net/26736162/viewspace-2121072/
一.4.2.4 SQL*Plus無法正常啟動
1、linux環境需要禁用SELinux，禁用方式：setenforce 0
2、環境變數設定不當，可以檢視.bash_profile或.profile檔案，確保有export;切換使用者;ORACLE_HOME的值最後是沒有反斜槓“/”的;還有從windows到Linux複製的時候是否有^M亂碼字元等問題。
一.4.2.5 使用strace來診斷SQL*Plus的登入問題
若sqlplus有一些特殊的問題，我們可以使用strace來跟蹤命令，跟蹤的命令很簡單：
--------------linux 跟蹤sqlplus程式
strace -o /tmp/output.txt -T -tt -e trace=all sqlplus / as sysdba
------------- Unix 跟蹤sqlplus程式
truss -dfaie -o /tmp/sched_trace.out.02271 sqlplus '/as sysdba'
不過生成的檔案需要呼叫作業系統的很多函式，看起來比較雲裡霧裡的。
一.4.3 exp/imp系列問題
一.4.3.1 使用query選項
Oracle的exp工具有一個query引數可以指定一個where條件來有條件地匯出記錄, 對於不經常用這個選項的人來說, 經常會遇到這樣的錯誤:
LRM-00112: multiple values not allowed for parameter 'query'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
這是因為在where條件中一般都會有空格, 而命令列下就會被釋成幾個命令列引數, 需要用單引號或雙引號將整個where條件括起來, 就可以了，或者使用正斜槓\來對字元進行轉義，windows下和linux下不太一樣，這個就比較繁瑣了，但是有個通用的辦法就是使用parfile來解析，這個無論是windows還是linux下都可以使用，舉個例子，我們需要匯出表test_query_lhr中的owner為SCOTT的記錄，我們可以執行：
exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott.dmp query=\" where owner=\'SCOTT\' \" log=/tmp/test_query_lhr_scott.log
[ZFZHLHRDB1:oracle]:/oracle>ORACLE_SID=raclhr1
[ZFZHLHRDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 2 15:18:56 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@raclhr1> create table test_query_lhr as select * from dba_tables;
Table created.
SYS@raclhr1> select count(1) from test_query_lhr where owner='SCOTT';
COUNT(1)
----------
4
SYS@raclhr1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[ZFZHLHRDB1:oracle]:/oracle>exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott.dmp query=\" where owner=\'SCOTT\' \" log=/tmp/test_query_lhr_scott.log
Export: Release 11.2.0.4.0 - Production on Tue Aug 2 15:23:08 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_QUERY_LHR 4 rows exported
Export terminated successfully without warnings.
下邊使用parfile的方式來匯出：
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
query="where owner='SCOTT'"
[ZFZHLHRDB1:oracle]:/oracle> exp \'/ AS SYSDBA\' tables=test_query_lhr file=/tmp/test_query_lhr_scott_01.dmp parfile=/tmp/scottfile.par log=/tmp/test_query_lhr_scott_01.log
Export: Release 11.2.0.4.0 - Production on Tue Aug 2 15:30:09 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_QUERY_LHR 4 rows exported
Export terminated successfully without warnings.
[ZFZHLHRDB1:oracle]:/oracle>
一.4.3.2 得到物件的DDL語句
imp工具使用show=y log=get_ddl.sql的方式,可以看到清晰的ddl指令碼，同時也不會真正的執行資料匯入：
exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n
imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
[ZFZHLHRDB1:oracle]:/oracle>exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n
Export: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:11 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP
Export terminated successfully without warnings.
[ZFZHLHRDB1:oracle]:/oracle>imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y
Import: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:44 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"
"AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"
"BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST "
"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
"CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX"
"TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"
"IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"
"X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN"
"EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US"
"ERS" LOGGING ENABLE "
"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
"CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully without warnings.
[ZFZHLHRDB1:oracle]:/oracle>
由於格式比較混亂，直接執行會報錯，建榮的書中給了一段程式碼來格式化：
[ZFZHLHRDB1:oracle]:/tmp>more /tmp/get_ddl.sql
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"
"AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"
"BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST "
"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
"CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX"
"TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"
"IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"
"X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN"
"EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US"
"ERS" LOGGING ENABLE "
"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
"CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully without warnings.
[ZFZHLHRDB1:oracle]:/tmp>more /tmp/gettabddl.sh
awk '
/ \"BEGIN / { N=1; }
/ \"CREATE / { N=1; }
/ \"CREATE INDEX/ { N=1; }
/ \"CREATE UNIQUE INDEX/ { N=1; }
/ \"ALTER / { N=1; }
/ \" ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n"; N++ }
/\"$/ {
if (N==0) next;
s=index( $0, "\"" );
ln0=length( $0 )
if ( s!=0 ) {
lcnt++
if ( lcnt >= 30 ) {
ln=substr( 0,s+1,length(substr(0,s+1))-1)
t=index( ln, ")," )
if ( t==0 ) { t=index( ln, ", " ) }
if ( t==0 ) { t=index( ln, ") " ) }
if ( t > 0 ) {
printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2)
lcnt=0
}
else {
printf "%s", ln
if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }
}
}
else {
printf "%s",substr( 0,s+1,length(substr(0,s+1))-1 )
if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }
}
}
}
END { printf "\n/\n"}
' / d;
s/STORAGE *(INI/~ STORAGE (INI/g;
s/, "/,~ "/g;
s/ (\"/~ &/g;
s/PCT[FI]/~ &/g;
s/[( ]PARTITION /~&/g;
s/) TABLESPACE/)~ TABLESPACE/g;
s/ , / ,~/g;
s/ DATAFILE /&~/' | tr "~" "\n"
[ZFZHLHRDB1:oracle]:/tmp>
[ZFZHLHRDB1:oracle]:/tmp>ksh /tmp/gettabddl.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql
[ZFZHLHRDB1:oracle]:/tmp>more /tmp/gen_tabddl.sql
ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"
/
CREATE TABLE "EMP"
("EMPNO" NUMBER(4, 0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4, 0),
"HIREDATE" DATE,
"SAL" NUMBER(7, 2),
"COMM" NUMBER(7, 2),
"DEPTNO" NUMBER(2, 0))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" LOGGING NOCOMPRESS
/
CREATE UNIQUE INDEX "PK_EMP" ON "EMP"
("EMPNO" )
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" LOGGING
/
ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"
/
ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY
("EMPNO") USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" LOGGING ENABLE
/
ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY
("DEPTNO") REFERENCES "DEPT"
("DEPTNO") ENABLE NOVALIDATE
/
ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"
/
[ZFZHLHRDB1:oracle]:/tmp>
這樣執行起來就方便多了。
一.4.3.3 常見問題
一、 EXP-00091: Exporting questionable statistics[ZFZHLHRDB1:oracle]:/tmp>oerr exp 91
00091, 00000, "Exporting questionable statistics."
// *Cause: Export was able export statistics, but the statistics may not be
// usuable. The statistics are questionable because one or more of
// the following happened during export: a row error occurred, client
// character set or NCHARSET does not match with the server, a query
// clause was specified on export, only certain partitions or
// subpartitions were exported, or a fatal error occurred while
// processing a table.
// *Action: To export non-questionable statistics, change the client character
// set or NCHARSET to match the server, export with no query clause,
// export complete tables. If desired, import parameters can be
// supplied so that only non-questionable statistics will be imported,
// and all questionable statistics will be recalculated.
該問題由於客戶端的NLS_LANG設定有問題導致的：
1）查詢資料庫的字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
2）設定Linux作業系統的NLS_LANG環境變數
[oracle@RH207 exp]$export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
二、 IMP-00013: only a DBA can import a file exported by another DBA
匯入使用者的許可權不夠，我們可以賦權：grant imp_full_database to user_xxx;即可。
若還是報錯，可以嘗試：alter user user_xxx default role all;確保查詢dba_role_privs中的DEFAULT_ROLE列的值為YES。
一.4.3.4 使用strace來跟蹤exp
strace exp n1/n1 tables=scott.emp file=a.dmp
strace -c -p 25805
一.4.3.5 從生成的dmp檔案可以獲取到的資訊
一、 獲取基本資訊：匯出的版本、時間、匯出的使用者[ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_02.dmp | head -10
TEXPORT:V11.02.00 ====》版本號
DSYS ====》使用SYS使用者匯出
RTABLES ====》基於表模式匯出，RUSERS表示基於使用者模式,RENTIRE表示基於全庫模式
4096
Tue Aug 2 16:8:8 2016/tmp/exp_ddl_lhr_02.dmp====》生成的時間和檔案地址
#C#G
#C#G
+00:00
BYTE
UNUSED
二、 獲取dmp檔案中的表資訊
[ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_02.dmp | grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'
EMP ====》說明exp_ddl_lhr_02.dmp中只有一個emp表
三、 解析dmp檔案生成parfile檔案
[ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_03.dmp | grep "CREATE TABLE"|awk '{print 1 ; else print ","$1 }'
tables=DEF$_AQCALL
,DEF$_AQERROR
,DEF$_CALLDEST
,DEF$_DEFAULTDEST
,DEF$_DESTINATION
,DEF$_ERROR
,DEF$_LOB
,DEF$_ORIGIN
,DEF$_PROPAGATOR
,DEF$_PUSHED_TRANSACTIONS
,MVIEW$_ADV_INDEX
[ZFZHLHRDB1:oracle]:/tmp>
其實這個可以使用UE或editplus文字編輯器的列模式實現也很快。
一.4.4 expdp/impdp系列問題
一.4.4.1 使用query選項
比如我們想匯出SCOTT.EMP表中DEPTNO=20和SCOTT.DEPT表中DNAME='SALES'的記錄，我們可以在parfile中寫：query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'"，示例如下：
SYS@raclhr1> select * from scott.emp where DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
SYS@raclhr1> SELECT * FROM SCOTT.DEPT where DNAME='SALES';
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
[ZFZHLHRDB1:oracle]:/oracle>more /tmp/scottfile.par
query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'"
[ZFZHLHRDB1:oracle]:/tmp>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log
Export: Release 11.2.0.4.0 - Production on Wed Aug 3 09:32:21 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=test_query_lhr_scott_02.log" Location: Command Line, Replaced with: "logfile=test_query_lhr_scott_02.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par logfile=test_query_lhr_scott_02.log reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.859 KB 1 rows
. . exported "SCOTT"."EMP" 8.195 KB 5 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/admin/raclhr1/dpdump/test_query_lhr_scott_02.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 3 09:32:34 2016 elapsed 0 00:00:12
一.4.4.2 使用include
只匯出procedure,function和含有TEST的序列。
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_03.dmp logfile=test_include_lhr_scott_03.log job_name=my_job_lhr include=procedure,function,sequence:"like '%TEST%'"
或使用parfile檔案：
include=procedure,function,sequence:"like '%TEST%'"
或：
include=procedure
include=function
include=sequence:"like '%TEST%'"
[ZFZHLHRDB1:oracle]:/tmp>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_05.dmp logfile=test_include_lhr_scott_05.log job_name=my_job_lhr parfile=/tmp/parfile.par
Export: Release 11.2.0.4.0 - Production on Wed Aug 3 10:06:04 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."MY_JOB_LHR": "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_include_lhr_scott_05.dmp logfile=test_include_lhr_scott_05.log job_name=my_job_lhr parfile=/tmp/parfile.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Master table "SYS"."MY_JOB_LHR" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.MY_JOB_LHR is:
/oracle/app/oracle/admin/raclhr1/dpdump/test_include_lhr_scott_05.dmp
Job "SYS"."MY_JOB_LHR" successfully completed at Wed Aug 3 10:06:10 2016 elapsed 0 00:00:05
一.4.4.3 得到物件的DDL語句
IMP工具使用show=y log=get_ddl.sql的方式獲取ddl語句，同樣，impdp也可以獲取到dmp檔案的ddl語句。IMPDP工具給我們提供了SQLFILE的命令列選項，只獲取DDL語句，並未真正的執行資料匯入：
impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=get_ddl.sql
--expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp
impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
[ZFXDESKDB1:oracle]:/oracle>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp
Export: Release 11.2.0.4.0 - Production on Wed Aug 3 15:14:55 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."TEST" 5.007 KB 1 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/admin/lhrdb/dpdump/exptest_sql.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 3 15:15:16 2016 elapsed 0 00:00:20
[ZFXDESKDB1:oracle]:/oracle>impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
Import: Release 11.2.0.4.0 - Production on Wed Aug 3 15:16:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Aug 3 15:16:09 2016 elapsed 0 00:00:02
[ZFXDESKDB1:oracle]:/oracle>cd /oracle/app/oracle/admin/lhrdb/dpdump/
[ZFXDESKDB1:oracle]:/oracle/app/oracle/admin/lhrdb/dpdump>more exptest.sql
-- CONNECT SYS
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: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
ACCOUNT LOCK;
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'LHRDB', inst_scn=>'4225469');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 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 "USERS" ;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) 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" ;
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"SAL" NUMBER,
"COMM" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) 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" ;
CREATE TABLE "SCOTT"."TEST"
( "DUMMY" VARCHAR2(1 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 "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYS
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX "SCOTT"."PK_DEPT" ENABLE;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX "SCOTT"."PK_EMP" ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
I_O VARCHAR2(60);
NV VARCHAR2(1);
c DBMS_METADATA.T_VAR_COLL;
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2(300) := ' 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,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,
:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'PK_DEPT';
i_o := 'SCOTT';
EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,4,1,4,1,1,1,0,4,NV,NV,TO_DATE('2016-07-07 22:00:11',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
《《《《。。。。。。。。篇幅原因，有省略,剩下的都是統計資訊，生成sqlfile的時候也可以不用生成。。。。。。。。》》》》
一.4.4.4 Datapump的工作原理
一般資料在匯入的過程中會生成3類的臨時表，分別為IMPORT表、ERR表和ET表，其中只有IMPORT表可以查詢，ERR表和ET表不能訪問，報ORA-29913錯誤，但可以執行drop操作。
TABLE_EXISTS_ACTION=REPLACE 這個選項的底層操作是drop purge+create的操作。
一.4.4.5 使用trace來跟蹤
expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=test_query_lhr_scott_02.dmp parfile=/tmp/scottfile.par log=test_query_lhr_scott_02.log trace=4a0300
更多內容請參考：
1、【MOS】Export/Import DataPump Parameter TRACE (文件 ID 286496.1)：http://blog.itpub.net/26736162/viewspace-2085076/
2、使用隱含Trace引數診斷Oracle Data Pump故障：http://blog.itpub.net/26736162/viewspace-2072331/
一.4.5 如何徹底停止expdp
許多人在使用expdp命令時，不小心按了CTLR+C,然後又輸入exit命令（或者網路中斷等異常現象），導致expdp程式不存在，但oracle資料庫的session仍存在，dmp檔案也一直在增長。
處理辦法
1、檢查expdp程式是否還在
ps –ef | grep expdp
(如存在，可用kill -9 process命令殺掉)
2、檢查session是否仍存在
3、把相關session殺掉，如無DBA許可權
drop table JOBID purge;
(JOBID即為DIRECTORY，此例為LZT_CASS1DATAJOB)
4、檢查相關表及dump的session
select * from GV$DATAPUMP_SESSION;
select * From USER_DATAPUMP_JOBS;
DBA_DATAPUMP_JOBS;
dba_datapump_sessions;
結果應該無記錄
5、刪除匯出的dmp檔案。如不刪除，重提expdp命令時，會報dmp檔案已存在
總結：檢視程式、檢視session、檢視錶GV$DATAPUMP_SESSION和USER_DATAPUMP_JOBS
一.4.5.1 我的檢視
set line 9999
col owner_name for a10
col job_name for a25
col operation for a10
col job_mode for a10
col state for a15
col job_mode for a10
col state for a15
col osuser for a10
col "degree|attached|datapump" for a25
col session_info for a20
SELECT ds.inst_id,
dj.owner_name,
dj.job_name,
dj.operation,
dj.job_mode,
dj.state,
dj.degree || ',' || dj.attached_sessions || ',' ||
dj.datapump_sessions "degree|attached|datapump",
ds.session_type,
s.osuser ,
(SELECT s.SID || ',' || s.SERIAL# || ',' || p.SPID
FROM gv$process p
where s.paddr = p.addr
AND s.inst_id = p.inst_id) session_info
FROM DBA_DATAPUMP_JOBS dj --gv$datapump_job
full outer join dba_datapump_sessions ds --gv$datapump_session
on (dj.job_name = ds.job_name and dj.owner_name = ds.owner_name)
left outer join gv$session s
on (s.saddr = ds.saddr and ds.inst_id = s.inst_id)
ORDER BY dj.owner_name, dj.job_name;
---------------------------------------------------------------------------------------------------------------------
About Me
..........................................................................................................................................................................................................
v 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
v 本文在ITpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新
v QQ群：230161599 微信群：私聊
v 本文在itpub地址：http://blog.itpub.net/26736162/viewspace-2122942/ 在部落格園地址：http://www.cnblogs.com/lhrbest/articles/5734580.html
v 本文pdf版： （提取碼：ed9b）
v 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/
v 聯絡我請加QQ好友(642808185)，註明新增緣由
v 於 2016-08-02 09:00~ 2016-08-03 19:00 在中行完成
v 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】
..........................................................................................................................................................................................................
長按識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2123162/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle DBA 問題記錄Oracle
- 《Oracle DBA工作筆記》第一章Oracle筆記
- Oracle DBA學習日記筆記Oracle筆記
- Oracle DBA職責及日常工作分析Oracle
- Oracle DBA職責及日常工作分析（轉）Oracle
- Oracle EBS DBA指南筆記1-3Oracle筆記
- oracle一些工作筆記Oracle筆記
- javaSE第二章筆記Java筆記
- EBS DBA指南筆記筆記
- PerformanceTuning筆記1 常用工具ORM筆記
- DBA筆試題（轉）筆試
- 筆記本省電問題分析及其實用小技巧筆記
- oracle dba 日常工作內容Oracle
- Oracle DBA的日常工作(zt)Oracle
- oracle DBA 日常工作職責Oracle
- 滑鼠問題導致筆記本開機點選桌面和工作列無反應筆記
- iOS開發筆記— 資料庫、Crash、記憶體問題分析iOS筆記資料庫記憶體
- Oracle 9i DBA Fundamentals I學習筆記（二）Oracle筆記
- 第二章（backup and recovery 筆記）筆記
- Oracle DBA 和Oracle EBS DBA 的最大區別Oracle
- Oracle DBA手記2Oracle
- Oracle DBA手記前言Oracle
- 《Oracle DBA手記》出版Oracle
- Oracle DBA面試題（1）Oracle面試題
- 《DBA手記一》讀書筆記筆記
- 工作筆記筆記
- 工作遇到的問題小記（一）
- 記錄我DBA的工作和生活
- ORACLE 訪問MYSQL 配置筆記OracleMySql筆記
- itpub oracle 10g dba培訓課程筆記(二)Oracle 10g筆記
- itpub oracle 10g dba培訓課程筆記(一)Oracle 10g筆記
- 學習Oracle Objects的文件和問題記錄OracleObject
- Oracle分割槽資料問題的分析和修復Oracle
- oracle db、dba和rdbaOracle
- IFrame跨域問題筆記跨域筆記
- Oracle效能優化視訊學習筆記－資料庫配置和I/O問題Oracle優化筆記資料庫
- 讀書筆記-《基於Oracle的SQL優化》-第二章-1筆記OracleSQL優化
- (轉載)DBA筆試題目筆試