This discussion is archived
5 Replies Latest reply: Nov 16, 2012 1:53 AM by Osama_Mustafa RSS

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

847798 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points