1 2 Previous Next 16 Replies Latest reply on Dec 17, 2009 8:26 PM by user11138625

    exclude tables in datapump export

    user097815
      I am trying to a full schema level export using datapump but want to exclude some tables...and i found the below from oracle-base.com site
      http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

      expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log


      but when i try to test it out....it throws me an error
      LRM-00116: syntax error at 'TABLE:' following '='


      what is the right cmd for doing a full schema level export and exclude couple of tables that i do not needed...using expdp

      sorry forgot to mention i am on 10.2.0.3 on HP

      Edited by: S2K on Dec 17, 2009 8:11 AM
        • 1. Re: exclude tables in datapump export
          591186
          Try this:
          EXCLUDE=TABLE:”IN (’EMP’,'DEPT’)”
          Or
          exclude=TABLE:"='BONUS'"
          HTH
          -Anantha
          • 2. Re: exclude tables in datapump export
            Pavan Kumar
            Hi,

            Space costs many things while implementing.

            - Pavan Kumar N
            Oracle 9i/10g - OCP
            http://oracleinternals.blogspot.com/
            • 3. Re: exclude tables in datapump export
              591186
              If its on windows, Use
              exclude=TABLE:\"='TABLE_NAME'\"
              HTH
              -Anantha
              1 person found this helpful
              • 4. Re: exclude tables in datapump export
                user097815
                looks like thats working...but now i get some other error....

                $ expdp system schemas=SCOTT exclude=TABLE:"IN ('EMP')" directory=data_pump_dir dumpfile=test.dmp logfile=test.log

                Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 17 December, 2009 8:25:21

                Copyright (c) 2003, 2005, Oracle. All rights reserved.
                Password:

                Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                With the Partitioning, OLAP and Data Mining options
                ORA-39002: invalid operation
                ORA-39070: Unable to open the log file.
                ORA-29283: invalid file operation
                ORA-06512: at "SYS.UTL_FILE", line 475
                ORA-29283: invalid file operation
                • 5. Re: exclude tables in datapump export
                  591186
                  With the Partitioning, OLAP and Data Mining options
                  ORA-39002: invalid operation
                  ORA-39070: Unable to open the log file.
                  ORA-29283: invalid file operation
                  ORA-06512: at "SYS.UTL_FILE", line 475
                  ORA-29283: invalid file operation
                  Check the directory permission where you're trying to write the log file.

                  HTH
                  -Anantha
                  • 6. Re: exclude tables in datapump export
                    Deepak_DBA
                    hi,

                    try this
                    expdp username/password schemas=scott exclude=table:"IN('emp')" directory=data_pump_dir dumpfile=test.dmp logfile=test.log



                    regards,
                    Deepak
                    • 7. Re: exclude tables in datapump export
                      user097815
                      i did give system user the right grant.....
                      GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO SYSTEM;

                      and get same error
                      • 8. Re: exclude tables in datapump export
                        591186
                        Try this:
                        expdp username/password schemas=scott exclude=table:"IN('emp')" directory=data_pump_dir dumpfile=test.dmp logfile=data_dump_dir:test.log
                        HTH
                        -Anantha
                        • 9. Re: exclude tables in datapump export
                          user097815
                          nope looks like the same error...

                          $ expdp system schemas=scott exclude=table:"IN('emp')" directory=data_pump_dir dumpfile=test.dmp logfile=data_pump_dir:test.log

                          Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 17 December, 2009 8:45:19

                          Copyright (c) 2003, 2005, Oracle. All rights reserved.
                          Password:

                          Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                          With the Partitioning, OLAP and Data Mining options
                          ORA-39002: invalid operation
                          ORA-39070: Unable to open the log file.
                          ORA-29283: invalid file operation
                          ORA-06512: at "SYS.UTL_FILE", line 475
                          ORA-29283: invalid file operation
                          • 10. Re: exclude tables in datapump export
                            591186
                            Post the results of:
                            SELECT directory_name, grantee, privilege FROM user_tab_privs t, all_directories d  WHERE t.table_name(+)=d.directory_name  ORDER BY 1,2,3;
                            • 11. Re: exclude tables in datapump export
                              277993
                              Where is data_pump_dir physically located. e.g /app/oracle/export. Ensure that it exists.

                              select * from dba_directories
                              • 12. Re: exclude tables in datapump export
                                289595
                                GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO SYSTEM;

                                Check if you really have a directory called sys.data_pump_dir .
                                • 13. Re: exclude tables in datapump export
                                  user097815
                                  it does exist...not sure why its causing the problem...but i found a work around to use the parameter file and just did include all tables that i needed...thanks all
                                  • 14. Re: exclude tables in datapump export
                                    user097815
                                    I tried you cmd and looks like that is working...but its still exporting the emp table...when we said not too

                                    $ expdp system schemas=scott exclude=table:"IN('emp')" directory=data_pump_dir dumpfile=test.dmp logfile=test.log

                                    Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 17 December, 2009 11:17:45

                                    Copyright (c) 2003, 2005, Oracle. All rights reserved.
                                    Password:

                                    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                                    With the Partitioning, OLAP and Data Mining options
                                    Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=scott exclude=table:IN('emp') directory=data_pump_dir dumpfile=test.dmp logfile=test.log
                                    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/STATISTICS/TABLE_STATISTICS
                                    . . exported "SCOTT"."DEPT" 5.656 KB 4 rows
                                    . . exported "SCOTT"."EMP" 7.781 KB 13 rows
                                    . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
                                    . . exported "SCOTT"."BONUS" 0 KB 0 rows
                                    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
                                    ******************************************************************************
                                    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
                                    /oracle/app/oracle/product/10.2.0/admin/testdb/test.dmp
                                    Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:18:23

                                    $
                                    1 2 Previous Next