1  BLOG文档结构图

wpsF1DD.tmp 

 

2  本文简介

建荣的新书《Oracle DBA工作笔记》第二章的目录如下图,主要讲解了SQL*Plus、exp/imp、expdp/impdp以及常见的问题分析,第二章的目录如下:

wpsF1EE.tmp 

wpsF1EF.tmp 

下边小麦苗将自己阅读完第二章后整理的一些内容分享给大家。

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;