1  BLOG Document structure chart

wpsF1DD.tmp 

 

2   Brief introduction

Jianrong's new book 《Oracle DBA Work notes 》 The table of contents of chapter two is shown below , Mainly explained SQL*Plus、exp/imp、expdp/impdp And the analysis of common problems , The contents of chapter two are as follows :

wpsF1EE.tmp 

wpsF1EF.tmp 

The following wheat seedlings will read the second chapter after finishing some of the content to share with you .

3   Chapter one content revision

3.1   There are several ways to delete a database

This is the first chapter (http://blog.itpub.net/26736162/viewspace-2121930/) Wheat seedlings are listed , But there's a problem in the middle , When the library to be deleted is rac When I was in the library , use dbca -silent It is possible to delete the database in a silent way , But use drop database You can't do it in the same way , Report errors :ORA-01586: database must be mounted EXCLUSIVE and not open for this operation, At this time, we need to turn off the cluster parameters cluster_database Can be deleted , The order is :alter system set cluster_database=false sid='*' scope=spfile;, So the wheat seedling still recommends the way of silence , No matter how to build or delete a database, many contents are automatically completed in a silent way , We don't have to do too much .

1、dbca Silent delete Library :dbca -silent -deleteDatabase -sourceDB mydb

2、SQL window :

alter database close;

alter system enable restricted session;

drop database;

3、SQL window :

sql > startup force mount restrict;

sql > drop database;

Be careful : The first method is highly recommended , above 2 and 3 The best way is rac The library needs to be set up cluster_database by false Only after that can we execute drop database, The order is :alter system set cluster_database=false sid='*' scope=spfile;

 

4   Chapter II Contents

4.1  orabase command

To put it simply , This command can print $ORACLE_BASE Value .

[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 Use

4.2.1   Login configuration

The configuration of wheat seedling is like this :

[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   obtain show sga The definition statement of

Jianrong is a very detailed chapter , Let's list the final results directly . We run the command vi $ORACLE_HOME/bin/sqlplus open sqlplus file , matching SGA You can find this line of code :

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;

The result and execution of this line of code show sga You can get the same results .

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 Official explanation :

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 in , The meaning of each part is as follows :

1. Total System Global Area:Fixed Size、Variable Size、 Database buffers and Redo Buffers The size of the sum

2. Fixed Size: This part is Oracle An internal use area , Including the control information of database and instance 、 State information 、 Dictionary information, etc , When it starts, it's fixed in SGA in , Will not change .Oracle Find... Through this area SGA Other areas , Like a SGA Index of each component , It's storing SGA  Information about the components of each part , It can be seen as guiding the establishment of SGA Region , The size of this part may be different for different platforms and different versions .

3. Variable Size: Include Shared Pool ,Java Pool ,Large Pool,stream pool、 Cursor area and other structures . 

4. Database Buffers: Where data blocks are buffered in a database , yes SGA The biggest place in the world , Determine database performance . by db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size The total size of , Of course this is sga_target by 0 The situation of , It's manual SGA In management mode , If it's automatic SGA management (sga_target>0), Then this value is based on sga Automatically adjust the distribution of .

5. Redo Buffers: This part is actually distributed Redo log buffer Size , By initialization parameter log_buffer according to SGA The smallest allocation unit of granule  Round up to get . Provide REDO Buffer space , stay OLAP It doesn't need to be too big in the middle . One additional point to make here is , about v$parameter、v$sgastat、v$sga The query value may be different .v$parameter  The value of the inside , It refers to the value set by the user in the initialization parameter file ,v$sgastat yes oracle  The actual allocated log buffer size ( Because the allocation value of the buffer is actually discrete , Not with block  Allocated for the smallest unit ),v$sga  The value of the query inside , Is in oracle  After allocating the log buffer , To protect the log buffer , Set up some protection pages , Usually we find that the size of the protection page is 8k( Different environments may be different )

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

4.2.3  SQL*Plus Command settings

About more SQL*Plus Please refer to :【OH】SET System Variable Summary SQLPLUS  System variable Settings :http://blog.itpub.net/26736162/viewspace-2121072/

 

4.2.4  SQL*Plus Unable to start properly

1、linux The environment needs to be disabled SELinux, Disable mode :setenforce 0

2、 Improper setting of environment variables , You can see .bash_profile or .profile file , Make sure there are export; Switching users ;ORACLE_HOME At the end of the day, there is no backslash “/” Of ; And from windows To Linux When copying, is there ^M Garbled characters and so on .

 

4.2.5   Use strace To diagnose SQL*Plus The login problem of

if sqlplus There are some special problems , We can use strace To follow orders , The command to track is simple :

--------------linux track sqlplus process

strace -o /tmp/output.txt -T -tt -e trace=all  sqlplus / as sysdba

------------- Unix track sqlplus process

truss -dfaie -o /tmp/sched_trace.out.02271 sqlplus '/as sysdba'

 

However, the generated file needs to call many functions of the operating system , It looks more cloudy .

 

4.3  exp/imp A series of questions

4.3.1   Use query Options

Oracle Of exp The tool has a query The parameter can specify a where Conditional to conditionally export records ,  For people who don't use this option very often ,  We often encounter such mistakes :

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

This is because in the where There are usually spaces in the condition ,  The command line will be interpreted as several command line parameters ,  You need to use single or double quotation marks to put the whole where All in all ,  That's all right. , Or use a forward slash \ To escape characters ,windows Lower and linux It's different , This is more complicated , But there's a common way to do that is to use parfile Parsing , It doesn't matter windows still linux You can use it all the time , for instance , We need to export the table test_query_lhr Medium owner by SCOTT The record of , We can execute :

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.

Use below parfile To export :

[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   Get the object's DDL sentence

imp Tool use show=y log=get_ddl.sql The way , You can see clearly ddl Script , At the same time, it doesn't really perform data import :

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>

Because the format is confusing , Direct operation will report an error , Jianrong's book gives you a piece of code to format :

[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>

It's much easier to run in this way .

4.3.3   common problem

One 、 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.

 

The problem is due to the client's NLS_LANG There is something wrong with the setting :

1) Query the character set of the database

SQL> select userenv('language') from dual; 

USERENV('LANGUAGE') 
---------------------------------------------------- 
AMERICAN_AMERICA.ZHS16GBK 

2) Set up Linux Operating system NLS_LANG environment variable  
[oracle@RH207 exp]$export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

 

Two 、 IMP-00013: only a DBA can import a file exported by another DBA

The import user does not have enough permissions , We can empower :grant imp_full_database to user_xxx; that will do .

If it's still wrong , You can try :alter user user_xxx default role all; Make sure the query dba_role_privs Medium DEFAULT_ROLE The value of the column is YES.

 

4.3.4   Use strace To keep track of exp

strace exp n1/n1 tables=scott.emp file=a.dmp

strace -c -p 25805

 

One 4.3.5   From generated dmp Information that can be obtained from files

One 、  Get basic information : Exported version 、 Time 、 Exported users

[ZFZHLHRDB1:oracle]:/tmp>strings exp_ddl_lhr_02.dmp | head -10

TEXPORT:V11.02.00  ====》 Version number  

DSYS    ====》 Use SYS User export

RTABLES ====》 Export based on table schema ,RUSERS Represents based on user mode ,RENTIRE It is based on the whole database pattern

4096

Tue Aug 2 16:8:8 2016/tmp/exp_ddl_lhr_02.dmp====》 Generated time and file address

#C#G

#C#G

+00:00

BYTE

UNUSED

 

Two 、  obtain dmp Table information in the file

[ZFZHLHRDB1:oracle]:/tmp>strings  exp_ddl_lhr_02.dmp | grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'

EMP ====》 explain exp_ddl_lhr_02.dmp Only one of them emp surface

 

3、 ... and 、  analysis dmp File generation parfile file

[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>

Actually, this can be used UE or editplus The column pattern of the text editor is also very fast .

4.4  expdp/impdp A series of questions

 

4.4.1   Use query Options

For example, we want to export SCOTT.EMP In the table DEPTNO=20 and SCOTT.DEPT In the table DNAME='SALES' The record of , We can do it in parfile Write in :query=SCOTT.EMP:"WHERE DEPTNO=20",SCOTT.DEPT:"WHERE DNAME='SALES'", Examples are as follows :

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   Use include

Export only procedure,function And contain TEST Sequence .

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%'"

Or use parfile file :

include=procedure,function,sequence:"like '%TEST%'"

or :

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   Get the object's DDL sentence

IMP Tool use show=y log=get_ddl.sql Way to obtain ddl sentence , Again ,impdp You can also get dmp Of documents ddl sentence .IMPDP Tools give us SQLFILE Command line options for , Get only DDL sentence , Data import is not really performed :

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;

/

《《《《........ The space for , With Ellipsis , The rest is Statistics , Generate sqlfile You don't need to generate it ........》》》》

 

 

4.4.4  Datapump How it works

General data will be generated during the import process 3 Class , Respectively IMPORT surface 、ERR Table and ET surface , Only one IMPORT Tables can be queried ,ERR Table and ET Table cannot access , newspaper ORA-29913 error , But it can do drop operation .

TABLE_EXISTS_ACTION=REPLACE The underlying operation of this option is drop purge+create The operation of .

 

4.4.5   Use trace To keep track of

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

 

Please refer to :

1、【MOS】Export/Import DataPump Parameter TRACE ( file  ID 286496.1):http://blog.itpub.net/26736162/viewspace-2085076/

2、 Use implicit Trace Parameter diagnosis Oracle Data Pump fault :http://blog.itpub.net/26736162/viewspace-2072331/

 

4.5   How to stop it completely expdp

A lot of people are using expdp On command , Accidentally pressed CTLR+C, And then type in exit command ( Or network interruption and other abnormal phenomena ), Lead to expdp Process does not exist , but oracle Database session There is still ,dmp And the files have been growing .

Treatment method

1、 Check expdp Is the process still going on

ps –ef | grep expdp

( If it exists , You can use kill -9 process Order to kill )

2、 Check session Whether it still exists

3、 Relate session kill , If not DBA jurisdiction

drop table JOBID purge;

(JOBID That is to say DIRECTORY, This example is LZT_CASS1DATAJOB)

 

4、 Check the relevant tables and dump Of session

select * from GV$DATAPUMP_SESSION;

select * From USER_DATAPUMP_JOBS;

DBA_DATAPUMP_JOBS;

dba_datapump_sessions;

The results should be unrecorded

 

5、 Delete exported dmp file . If you don't delete , Hark back to the expdp On command , Will be submitted to the dmp file already exist

 

summary : Check the process 、 see session、 See the table GV$DATAPUMP_SESSION and USER_DATAPUMP_JOBS

4.5.1   My view

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;