5 Replies Latest reply: Nov 16, 2012 3:53 AM by Osama_Mustafa RSS

    how to export specific set of tables in a schema which name starts with "A"

    847798
      Dear all,

      I need to take oracle export of specific tables in a schema which name starts with "AA_" .
      Is it possible to take export like this....

      Plz help me regarding this ..

      Oracle version : 11gr2

      Regards,
      Vamsi....
        • 1. Re: how to export specific set of tables in a schema which name starts with "A"
          Saubhik
          http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#BABJEHJI
          Look at the parameters (specifically TABLES). Recommended expdp.
          • 2. Re: how to export specific set of tables in a schema which name starts with "A"
            847798
            Hi Saubhik,

            Thanks for your reply,
            I have searched all the options in EXP and EXPDP , in the above 2 utilities options are there to take the export of

            1.specified tables where we need to specify the full name of the table .
            2.Include and exclude tables.

            My requirement was , we have one sehema in our database which contains thousands of tables ...we do not want to take backup of all the tables....we need to take the backup of those tables whose name starts with "AA_"....there are many tables whose name starts with "AA_" .Listing all those thables with full names in export was difficult .....


            Is there any way to take export of all the tables whose name starts with "AA_".So that it will take backup of all the tables whose name starts with "AA_" without specifying the entire table name...


            Regards,
            Vamsi....
            • 4. Re: how to export specific set of tables in a schema which name starts with "A"
              Osama_Mustafa
              you have 2 option
              1- using  exp user/password file=location\name.dmp log=location\name.log tables=AA% 
              
              Or 
              
              Sqlplus username/password
              
              Spool export.log
              
              select ''''|| table_name ||''''||',' from user_tables
              where table_name like 'AA%'
              
              Spool off ;
              
              And Put table name that generated in Spool file in Tables parameter (export)
              Mark this thread as answered please .
              • 5. Re: how to export specific set of tables in a schema which name starts with "A"
                Girish Sharma
                See this demo :
                C:\Windows\system32>expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=testtbls.dmp logfile=log.txt tables=TEST%
                
                Export: Release 11.2.0.1.0 - Production on Fri Nov 16 15:24:49 2012
                
                Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                
                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
                Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=DATA_PUMP_DIR dumpfile=testtbls.dmp logfile=log.txt tables=TEST%
                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/STATISTICS/TABLE_STATISTICS
                . . exported "SCOTT"."TESTEMP"                           8.578 KB      14 rows
                . . exported "SCOTT"."TEST"                                  0 KB       0 rows
                Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
                ******************************************************************************
                Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
                  E:\APP\SERVERROOM\ADMIN\ORCL\DPDUMP\TESTTBLS.DMP
                Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:25:05
                
                
                C:\Windows\system32>dir E:\app\serverroom\admin\orcl\dpdump\testtbls.dmp
                 Volume in drive E is HCL_DISK3
                 Volume Serial Number is A62A-F491
                
                 Directory of E:\app\serverroom\admin\orcl\dpdump
                
                16-11-2012  15:25           135,168 TESTTBLS.DMP
                               1 File(s)        135,168 bytes
                               0 Dir(s)  80,173,527,040 bytes free
                
                C:\Windows\system32>
                In your case just replace tables=TEST% with tables=AA_%. I hope it should work.

                Regards
                Girish Sharma