RE: [Ora_DBA_Exp] oracle 9i >How to exclude tables from export/import?

[Justin Cave (DDBC)]

> -----Original Message-----

> From: ORACLE_DB...@googlegroups.com [mailto:ORACLE_DB...@googlegroups.com] On Behalf Of ExpertDba

> Sent: Friday, June 17, 2005 3:37 AM

> To: ORACLE_DB...@googlegroups.com

> Subject: [Ora_DBA_Exp] oracle 9i >How to exclude tables from export/import?

>

>

> Hi group,

>

> Problem: Say i want to export a dump file excluding 27 tables of the

> 500 tables available in the database. Please consider the fact that a

> few tables in the "27" tables that i want to exclude are pretty huge

> having millions of records.

>

>  I came across this interesting problem in some group:also i did face a

> similar problem before but since the number of tables and size of the

> backup file didn't matter;I just imported the entire dump file at

> destination and dropped the irrelevant tables....

> ......but think of a scenario<particularly in "teradata" systems> of

> having very huge tables containing millions of records,then the method

> i used will not suffice well!!

>

> Possible Conditions to take into account:

> <1>.The tables being excluded are really very large

> <2>.tables=... clause not practical for enterring 400+ tablenames in

> export/import scripts...unless you have a workaround for this<which i

> can suugest one>

> <3>..

 

 

In 9.2 and earlier releases, I would take issue with the statement that entering the table names individually is impractical.  You can very easily write a script that generates the list of tables you want to import in whatever your scripting language of choice happens to be.  In SQL, assuming you have Tom Kyte's "stragg" function (if not, search asktom.oracle.com for "stragg"), you can do

 

SELECT stragg( table_name )

  FROM dba_tables

 WHERE table_name NOT IN (<<list of tables to exclude>>)

 

And cut-and-paste that into your script.  Any scripting language should have similar capabilities.

 

In 10g, there is a command-line option to exclude a list of tables.

 

The discussions in other forums I've seen recently involved hacking data dictionary definitions, which is not something I would ever consider, particularly on a production database.  Just because it happens to work in one particular situation does not mean that it is a safe solution-- you never know what sort of side effects you might introduce.  Beyond that, it is totally unsupported and terribly