exp/imp

[lgaorcl]

Hi, Is there a easy way to export or import all tables under one schema except one table?

Thanks,

Lisa

[Mr.Hanky]

Sure, do the whole thing and then drop the table.

MH

[lgaorcl]

What I wanted was to export all tables from one schema except one table from one database, then import into another database. The table I don't need is too big. So if there is a way to avoid to export it, it would save me plenty of time for both export and import.

Thanks!

[stecal]

Sure, list all the tables you do want.

[jmodic]

Short of listing all tables except the big one in your TABLES=(...) parameter when exporting (which has its own problems and gotchas), I could think of couple of ways to exclude a few tables from full/schema export:

- If your release support it, you could use row level security (aka RLS, FGAC, VPD) feature - check this article that describes exactly this scenario: http://www.dbasupport.com/oracle/ora9i/FGAC.shtml

- A more crude method, which can be used if you can afford the big table to be unavailable to anyone during the time of export:
Move that table to a separate tablespace, put the tablespace offline, perform full/schema export and bring that tablespace back online. As the big table is not available during the export it will not be included in the dump file (you will probably get some EXP error message as a warning about that, though).

[marist89]

quick and dirty...

1. export foo/bar@db1 file=foo.dmp owner=yourowner
2. sqlplus foo2/bar@db1 << !
create table table_I_want_to_exclude (x char(1));
!
3. imp system/manager@db1 file=foo.dmp fromuser=foo touser=bar ignore=n

[jmodic]

Yep, however he explicitly stated he wants to avoid exporting of that big table in order to save time for export too, not only for import...