ex.php,exphp导出数据报错 着急在线等

Export/Import DataPump Parameter QUERY - How to Specify a Query [ID 277010.1]

--------------------------------------------------------------------------------

修改时间 02-SEP-2011     类型 HOWTO     状态 PUBLISHED

In this Document

Goal

Solution

1. QUERY in Parameter file.

2. QUERY on Command line.

3. QUERY in Oracle Enterprise Manager Database Console.

4. Import Data Pump parameter QUERY.

5. Known Issues.

References

--------------------------------------------------------------------------------

Applies to:

Enterprise Manager for RDBMS - Version: 10.1.0.2 to 11.2.0.1 - Release: 10.1 to 11.2

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.1   [Release: 10.1 to 11.2]

Oracle Server - Personal Edition - Version: 10.1.0.2 to 11.2.0.1   [Release: 10.1 to 11.2]

Oracle Server - Standard Edition - Version: 10.1.0.2 to 11.2.0.1   [Release: 10.1 to 11.2]

Information in this document applies to any platform.

***Checked for relevance on 24-Jun-2010***

Goal

This document demonstrates how the QUERY parameter can be used with Export Data Pump (expdp) and Import Data Pump (impdp). It also shows where quotes must be used in the WHERE clause.

Incorrect usage of single or double quotes (or a space between the colon and the double quote) for the QUERY parameter can result in parse errors or errors such as:

LRM-00101: unknown parameter name 'empno'

ORA-39001: invalid argument value

ORA-39035: Data filter SUBQUERY has already been specified

ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:

ORA-00933: SQL command not properly ended

LRM-00111: no closing quote for value ''

EXP-19: failed to process parameters, type 'EXP HELP=Y' for help

EXP-0: Export terminated unsuccessfully

ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:

ORA-06502: PL/SQL: numeric or value error

ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:

LPX-314: an internal failure occurred

Solution

The examples below are based on the following demo schema's:

user SCOTT created with script: $ORACLE_HOME/rdbms/admin/scott.sql

user HR created with script: $ORACLE_HOME/demo/schema/human_resources/hr_main.sql

The Export Data Pump and Import Data Pump examples that are mentioned below are based on the directory my_dir. This directory object needs to refer to an existing directory on the server where the Oracle RDBMS is installed. Example:

-- for Windows platforms:

CONNECT system/manager

CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';

GRANT read,write ON DIRECTORY my_dir TO public;

-- for Unix platforms:

CONNECT system/manager

CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';

GRANT read,write ON DIRECTORY my_dir TO public;

1. QUERY in Parameter file.

Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and

from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.

File: expdp_q.par

-----------------

DIRECTORY = my_dir

DUMPFILE  = exp_query.dmp

LOGFILE   = exp_query.log

SCHEMAS   = hr, scott

INCLUDE   = TABLE:"IN ('EMP', 'DEPARTMENTS')"

QUERY     = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000"

# place following 3 lines on one single line:

QUERY     = hr.departments:"WHERE department_id IN (SELECT DISTINCT

department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id

AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)"

-- Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par

Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.

2. QUERY on Command line.

The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

table scott.dept; and

from table scott.emp all employees whose name starts with an 'A'

-- Example Windows platforms:

-- Note that the double quote character needs to be 'escaped'

-- Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp

LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\"

-- Example Unix platforms:

-- Note that all special characters need to be 'escaped'

% expdp scott/tiger DIRECTORY=my_dir \

DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \

QUERY=emp:\"WHERE ename LIKE \'A\%\'\"

-- Example VMS platform:

-- Using three double-quote characters

$ expdp scott/tiger DIRECTORY=my_dir -

DUMPFILE=exp_cmd.dmp LOGFILE=exp_cmd.log TABLES=emp,dept -

QUERY=emp:"""WHERE ename LIKE 'A%'"""

Note that with the original export client two jobs were required:

-- Example Windows platforms:

-- Place following statement on one single line:

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp

QUERY=\"WHERE ename LIKE 'A%'\"

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

-- Example Unix platforms:

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \

QUERY=\"WHERE ename LIKE \'A\%\'\"

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

-- Example VMS platform:

$ exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp -

QUERY="""WHERE ename LIKE 'A%'"""

$ exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

Note that with original export client in Oracle8i on VMS, the syntax was different (also note the extra space that is needed between two single quotes):

... QUERY="'WHERE ename LIKE \'A%\' '"

That is: [double_quote][single_quote]WHERE ename LIKE [backslash][single_quote]A%[backslash][single_quote][space][single_quote][double_quote]

3. QUERY in Oracle Enterprise Manager Database Console.

The QUERY can also be specified in the Oracle Enterprise Manager Database Console. E.g.:

Login to the Oracle Enterprise Manager 10g Database Console, e.g.: http://my_node_name:5500/em

Click on link 'Maintenance'

Under 'Utilities', click on link 'Export to Files'

Answer questions on the following pages.

At 'step 2 of 5' (the page with the Options), click on link 'Show Advanced Options'

At the end of the page, under the QUERY option, click on button 'Add'

At the next page, choose the table name (SCOTT.EMP)

And specify the SELECT statement predicate clause to be applied to tables being exported, e.g.: WHERE ename LIKE 'A%'

Continue with the remaining options, and submit the job.

4. Import Data Pump parameter QUERY.

Similar to previous examples with Export Data Pump, the QUERY parameter can also be used during the import. An example of how to use the QUERY parameter with Import Data Pump:

-- In source database:

-- Export the schema SCOTT:

%expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \

LOGFILE=expdp_s.log SCHEMAS=scott

-- In target database:

-- Import all employees of department 10:

%impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \

LOGFILE=impdp_s.log TABLES=emp TABLE_EXISTS_ACTION=append \

QUERY=\"WHERE deptno = 10\" CONTENT=data_only

Note that this feature was not available with the original import client (imp). Also note that the parameter TABLE_EXISTS_ACTION=append is used to allow the import into an existing table and that CONTENT=data_only is used to skip importing statistics, indexes, etc.