How can we specify per-file directory objects for each dump file, log file and sql file when using expdp utility?
I haven't found the syntax after googling?
Printable View
How can we specify per-file directory objects for each dump file, log file and sql file when using expdp utility?
I haven't found the syntax after googling?
Stop googling, see manuals:
http://download.oracle.com/docs/cd/B...b14215/toc.htm
DUMPFILE=[directory_object:]file_name [, ...]
LOGFILE=[directory_object:]file_name
SQLFILE=[directory_object:]file_name
thanks.
I have been practising use of expdp and impdp. I m facing this problem.
C:\>expdp 'hr/hr@orac as sysdba' dumpfile=t2.dmp logfile=lg2.log query=employees : "where department_id in (20)"
Export: Release 10.2.0.1.0 - Production on Thursday, 16 July, 2009 15:31:24
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.
The action suggested "Specify a different data filter" is not understood to me. That's why , I need assistance.
Escape the double quotation marks:
query=employees : \"where department_id in (20)\"
STILL THE SAME ERROR:
C:\>expdp 'hr/hr as sysdba' dumpfile=t2.dmp logfile=lg2.log query=employees : \"where department_id in (20)\"
Export: Release 10.2.0.1.0 - Production on Thursday, 16 July, 2009 16:37:27
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.
There was probably some issue with hr doing export, though I have given sysdba role and exp_full_database to hr. Now, I have done it as follows:
C:\>expdp 'SYS/SYS@ORAC as sysdba' dumpfile=t2.dmp logfile=lg2.log query=employees: \"where department_id in (20)\"
Export: Release 10.2.0.1.0 - Production on Thursday, 16 July, 2009 16:49:54
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_SCHEMA_02": 'SYS/********@ORAC AS SYSDBA' dumpfile=t2.dmp logfile=lg2.log query=employees: "where department_id in (20)"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
E:\ORACLE\ADMIN\ORAC\DPDUMP\T2.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:50:42
Thanks ales.
No, you have not.Quote:
Originally Posted by mahajanakhil198
You didn't export the HR schema but SYS. Use the SCHEMAS clause: expdp ... schemas=hr
The reason for the ORA-39035 is the space between "employees" and the semicolon.
Ooops i didnt at all read it.
I tried specifying schemas but it started doing schema export.
Then I tried doing table export using query parameter as follows:
C:\>expdp 'hr/hr@orac' dumpfile=t2.dmp logfile=lg2.log tables=hr.employees query=employees:\"where department_id in (50)\"
Export: Release 10.2.0.1.0 - Production on Thursday, 16 July, 2009 17:12:33
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": 'hr/********@orac' dumpfile=t2.dmp logfile=lg2.log tables=hr.employees query=employees:"where department_id in (
50)"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES" 11.30 KB 45 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
E:\ORACLE\ADMIN\ORAC\DPDUMP\T2.DMP
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 17:12:59
I hope this time it is right.