Export Script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Export Script

  1. #1
    Join Date
    Nov 2000

    Oracle 8.1.6 on NT. Our production schema has over 400 tables. I am writing a export script for table export. The only way that I know to write this script is to list all tables. My question is how to write export script dynamically getting table names.

    Thank you!


  2. #2
    Join Date
    Nov 2000

    Depends on what you're trying to do.

    If you are exporting the entire database, you can use the full=y option. For example:
    exp system/manager file=mydump.out full=y log=mydump.out

    If you are trying to export just one user, use the owner=ownername option. For example:
    exp system/manager file=mydump.dmp owner=bigdaddy log=mydump.out

    To get all the options available, you can type exp help=y

  3. #3
    Join Date
    Apr 2000
    Edison, NJ
    You can use the parfile option during export. Specify the tables to be exported in this file and then export them. Keep this parfile for future use. For example: exp system/manager file=expdat.dmp parfile=tables.par ....
    Contents of parfile--> Tables=(Table1, Table2,.....)

  4. #4
    Join Date
    Jun 2000
    As mentioned you would just use the tables=(table1, table2,...,tablen) option in export. generating this list shouldn't be too hard.

    I'm not sure if you could do it in one oracle query, but with one query (depending on layout) you can get a list of all the tables you want to export into a file, like this


    then use a shell/perl script to read each line of of the table and create a statement like


    on one line, and print out any other export lines into a file, and use that as your parameters table.

    if you have the list of files in say tables.lst, a quick perl script would be something like below. It may not be the "easiest" way to accomplish the job but it would get it done without too much work.

    <font face="courier">

    open (TABLES, tables.lst');
    $table_param = "tables=(";

    while( $next_table = &lt;TABLES&gt; ) {
    chomp; # removes the newline character, other whitespace shouldn't cause a problem
    $table_param .= "$next_table,";
    chop; # remove the comma from the last table
    $table_param .= ")";

    # at this point, $table_param = "tables=(table1,table2,...,tablen)"
    # now just print it out, and other parameters to a file
    open (PARAMS, "export.params");
    print PARAMS "file=mydump.out\n";
    print PARAMS "full=n\n";
    # whatever other parameters you want
    print PARAMS "$table_param\n";


    Now there's a file called export.params with the table list and whatever else you added.

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