6 Replies Latest reply: Oct 16, 2010 7:13 AM by user632098 RSS

    Exclude syntax in expdp

    user632098
      I am using Oracle 11.1.0.7.x on HP UNIX 11i. I am doing full expdp and want to exclude certain tables.
      Expdp full=Y parfile=expdp.par
      I am using following syntax is an expdp.par
      EXCLUDE=TABLE:"IN ('PQM.COMPLAINT_LTR_ORIG')"
      Where PQM is my schema. My database has many schemas I want to exclude
      Table in a specific schema.
      I am finding that expdp is not excluding this table. However, If I remove PQM,
      EXCLUDE=TABLE:"IN ('COMPLAINT_LTR_ORIG')"
      Table is excluded. Problem I have is table same name exists in other schemas as well
      So many tables get excluded which is not what I want. How can I exclude table in a specific schema while
      doing full export data pump.
        • 1. Re: Exclude syntax in expdp
          sb92075
          Handle:      user632098
          Status Level:      Newbie
          Registered:      Apr 20, 2008
          Total Posts:      153
          Total Questions:      46 (43 unresolved)
          so many questions & so few answers; but I wonder how many are DUPLICATES ??

          please post a few more times in case somebody missed this problem
          Exclude syntax in expdp
          Exclude syntax in expdp
          Exclude syntax in expdp
          Exclude syntax in expdp

          Edited by: sb92075 on Oct 15, 2010 6:48 PM
          • 2. Re: Exclude syntax in expdp
            user632098
            Sorry, there was some problem with Forum SUBMIT button; it won't response so I pressed it again and again and after 5-6 tries and 5-6 minutes it finally submitted my post.
            • 3. Re: Exclude syntax in expdp
              sb92075
              Sorry, there was some problem with Forum SUBMIT button;
              Problem Existed Between Keyboard And Chair (PEBKAC).
              bcm@bcm-laptop:~$ expdp / schemas=user1 exclude=table:"IN('FIRMAS')" file=user1.dmp log=user1.log directory=DATA_PUMP_DIR
              
              Export: Release 11.2.0.1.0 - Production on Fri Oct 15 18:55:07 2010
              
              Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
              
              UDE-01017: operation generated ORACLE error 1017
              ORA-01017: invalid username/password; logon denied
              
              Username: / as sysdba
              
              Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              Legacy Mode Active due to the following parameters:
              Legacy Mode Parameter: "file=user1.dmp" Location: Command Line, Replaced with: "dumpfile=user1.dmp"
              Legacy Mode Parameter: "log=user1.log" Location: Command Line, Replaced with: "logfile=user1.log"
              Legacy Mode has set reuse_dumpfiles=true parameter.
              Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=user1 exclude=table:IN('FIRMAS') dumpfile=user1.dmp logfile=user1.log directory=DATA_PUMP_DIR reuse_dumpfiles=true 
              Estimate in progress using BLOCKS method...
              Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
              Total estimation using BLOCKS method: 640 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/VIEW/VIEW
              Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
              Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
              . . exported "USER1"."BASELINE_TEST"                     6.289 KB       1 rows
              . . exported "USER1"."EMPLOYEE_MASTER"                   5.484 KB       3 rows
              . . exported "USER1"."EMPLOYEE_TIME"                     6.773 KB       5 rows
              . . exported "USER1"."EXAMPLE"                           5.039 KB       3 rows
              . . exported "USER1"."MY_TABLE"                          5.820 KB       1 rows
              . . exported "USER1"."STUDENT"                           5.492 KB       6 rows
              . . exported "USER1"."TAB_1"                             5.476 KB       3 rows
              . . exported "USER1"."TAB_2"                             5.460 KB       2 rows
              . . exported "USER1"."TEST"                              5.070 KB      10 rows
              . . exported "USER1"."DUMMY"                                 0 KB       0 rows
              . . exported "USER1"."PRODUCTS"                              0 KB       0 rows
              Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
              ******************************************************************************
              Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
                /u01/app/oracle/admin/v112/dpdump/user1.dmp
              Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:56:46
              • 4. Re: Exclude syntax in expdp
                user632098
                You are doing export dp of a schema and then excluding a table. I alerady know how to do that. I am sking if I want to do full export expdp full+Y and want to excludd tables from differen schema how can I do that
                • 5. Re: Exclude syntax in expdp
                  Pavan Kumar
                  Hi Sb,
                  Problem Existed Between Keyboard And Chair (PEBKAC).
                  Innovative root causes... :-)

                  - Pavan kumar N
                  • 6. Re: Exclude syntax in expdp
                    user632098
                    Still waiting for the corect answer. Oracle documntaion is not goos on this suject. While google provides answers whild doing export on a signle schema; I hav enot found answer how to exclude table of a soecifc schema while doing expdp full=Y.