Export Script

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.

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

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,.....)

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.


#!/usr/bin/perl

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

while( $next_table = <TABLES> ) {
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);


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