question regarding expdp utility
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: question regarding expdp utility

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    question regarding expdp utility

    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?
    lucky

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    thanks.
    lucky

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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.
    Last edited by mahajanakhil198; 07-16-2009 at 07:09 AM.
    lucky

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Escape the double quotation marks:
    query=employees : \"where department_id in (20)\"
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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.
    lucky

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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.
    lucky

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by mahajanakhil198 View Post
    Now, I have done it ...
    No, you have not.
    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.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  9. #9
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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.
    lucky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width