Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ** export excluding some tables

RE: ** export excluding some tables

From: Reardon, Bruce \(CALBBAY\) <>
Date: Fri, 16 Jun 2006 09:42:20 +1000
Message-ID: <>

Are you using consistent =y and do you need to - if you can avoid this may assist with avoiding the ora-1555 errors.

Hacking the export views was mentioned.
Have a look at for an earlier posting by me on how this can be done and also a reply by Chris Marquez suggesting that in certain circumstances you may be able to achieve the desired affect by taking tablespaces offline.

Bruce Reardon

-----Original Message-----
From: []On Behalf Of Sent: Friday, 16 June 2006 3:13 AM

Use this simple SQL to generate the Tables= list of your *.par file. Just remove the comma from the first table in the list.

Select ',' || Table_Name
From User_Tables
Where Table_Name Not In (<TablesToExcludeList>) Order By Table_Name

We have over 100,000 tables in our Student Info. System so we're constantly using the above SQL to generate a *.par file with a Tables= list of the ones we want to export. Usually we use patterns such as Table_Name Like '____5___' to get the ones we want. We've found that you can list up to about 32,000 tables in the list before Export barfs - at least in 9i.

It's easy. Hope it helps.

Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk) / 512.935.5929 (pager)

"Jared Still" <>

06/15/2006 11:50 AM Please respond to

On 6/15/06, A Joshi <> wrote: Hi,

   I am trying to take an export of whole database but it fails due to some big tables with rollback segment eror Ora-1555. I know we can specify tables but is there a way to exclude certain tables. Thanks for your help.

I find that dropping the tables prior to exporting seems to work.

Other than that, the only other workable method I have found is to include only the tables I do want to export (tables=(t1,t2,t2...)

This may require more than one export if the command line becomes too long. Cumbersome, but it works.

If the number of tables is really large, you could use sql to generate your export commands. This saves some typing, and is less error prone than manually inputting all the table names.

The first method ticks off the users, so I generally avoid it.

Jared Still

This e-mail and any attachments are private and confidential and may contain privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments.
This notice should not be removed.
Received on Thu Jun 15 2006 - 18:42:20 CDT

Original text of this message