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

Thread: Export Script

  1. #1
    Join Date
    Nov 2000
    Posts
    57
    Hi,

    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!

    Eugene.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    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
    Location
    Edison, NJ
    Posts
    759
    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
    Posts
    417
    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

    TABLE1
    TABLE2
    TABLE3

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

    tables=(table1,table2,...,tablen)

    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">
    #!/usr/bin/perl

    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";

    close(PARAMS);
    close(TABLES);
    </font>

    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