《Oracle DBA工作笔记》第二章 常用工具和问题分析
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了《Oracle DBA工作笔记》第二章 常用工具和问题分析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含37456字，纯文字阅读大概需要54分钟。
内容图文
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,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,length(substr(0,s+1))-1 )
if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }
}
}
}
END { printf "\n/\n"}
' * |sed '1,2d; /^* |sed '1,2d; /^/ 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 $3}'|sed 's/"//g'|awk '{ if (FNR==1) print "tables="1;elseprint","1;elseprint","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;
内容总结
以上是互联网集市为您收集整理的《Oracle DBA工作笔记》第二章 常用工具和问题分析全部内容，希望文章能够帮你解决《Oracle DBA工作笔记》第二章 常用工具和问题分析所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。