Re: export/import

In article <3919487C.7FEF0DB_at_gei-aachen.de>,   Dominik Schroeder <dschroeder_at_gei-aachen.de> wrote:
> Hi all!
>
> the database:
> one user (next to standard users) w/ around 1000 tables.
> all tables are rather small. many triggers and constraints tangle
 them
> together.
> one table VERY big. it is located in an extra table space within an
> extra database file. no triggers or constraint apply to this big
> beauty.
> several stored procedures exist (wrapped, no source code available).
>
> my problem:
> i need to copy this database, except the big beauty!
>
> first tries:
> copy of database files, except the big beauty file. => new database
 was
> corrupted.

my guess here is that you tried copying the files while the database was open, resulting in inconsistent files.

>
> make list of tables and ordering it in regard to the constraints.
> removing big beauty from list.
> exporting (via exp) all those tables.
> dropping all those tables in reverse order from the other database.
> importing the previously exported tables.
> => didn't lead to a perfect database. i suppose, that there are more
> things to pay attention to, than the constraints, when ordering the
 list
> of tables.
>

you could try disabling all the constraints before you export the tables then re-enable them after import.

> questions:
> is it possible to export like "exp TABLES=(all, except big_beauty)"?
not that I am aware of. If big_beauty is owned by a separate user, who owns no other objects, you could do multiple exports by user and skip over the big_beauty owner.

> is it possible to export on basis of database files?
sounds like you are asking if you can export by OS file, and I think the answer to that is no.

> is it possible to export on basis of table spaces?
beginning with 8.1 it is.

> how can i delete all tables of a user, w/o also deleting its stored
> procedures?

using SQL to build SQL.

select 'drop table '||owner||'.'||table_name||';'   from dba_tables
  where owner = '<whomever>'
spool drop_tables.sql
/
spool off

edit the drop_tables.sql file and remove header & footer junk, then run it. You may need to include the CASCADE CONSTRAINTS clause to the end of the drop table command to force the drop of tables with unique or primary key constraints.

HTH,
Roy

>
> i know, this is a lot...but i have to find a way how to get all data
> except the big beauty from one database into another......
>
> thanx for ready, even more thanx for answers, whatsoever... :-)
>
> dominik
>
> e-mail: schroeder.dominik_at_gmx.de
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 10 2000 - 00:00:00 CDT