BLOG Document structure chart

 


《Oracle DBA Work notes 》 Chapter two Common tools and problem analysis (1)-- Delete library 、orabase、sqlplus http://mp.weixin.qq.com/s?src=3&timestamp=1470396751&ver=1&signature=3zOxvwdWqIAgGXx0pyNNHRtVIP-JqJS0WZD-Tq*7H2azxglzF0p1Ni-bsPrW*35Jq-zpEA*3ihQlunpvfe-0XCsHRELNgghB5HNTarBrkluNO7Ne9QiKRIeTwPZOv0U5KYwgVc5T1t9ymz8UkilPkVBa66Izb5TXBxzpeAexcWY=


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

 

[email protected]> create table test_query_lhr as select * from dba_tables;

 

Table created.

 

[email protected]> select count(1) from test_query_lhr where owner='SCOTT';

 

  COUNT(1)

----------

         4

 

[email protected]> 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))-1)

        t=index( ln, ")," )

        if ( t==0 ) { t=index( ln, ", " ) }

        if ( t==0 ) { t=index( ln, ") " ) }

        if ( t > 0 ) {

          printf "%s\n%s",substr( ln,1,t+1), substr(ln, t+2)

          lcnt=0

        }

        else {

          printf "%s", ln

          if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }

        }

      }

      else {

        printf "%s",substr(       0,s+1,length(substr(      0,s+1))-1 )

        if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }

      }

    }

  }

  END { printf "\n/\n"}

* |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  
[[email protected] 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

 

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 ; else print ","$1 }'

tables=DEF$_AQCALL

,DEF$_AQERROR

,DEF$_CALLDEST

,DEF$_DEFAULTDEST

,DEF$_DESTINATION

,DEF$_ERROR

,DEF$_LOB

,DEF$_ORIGIN

,DEF$_PROPAGATOR

,DEF$_PUSHED_TRANSACTIONS

,MVIEW$_ADV_INDEX

[ZFZHLHRDB1:oracle]:/tmp>

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