May the Oracle Gurus help me !

[uparna]

Hi !
I need to put the production database's data into the test database that i have, in the fastest possible way. The simplest solution would be to take an extract and import it into the test database. Aha ! Here lies the catch: the test database can hold only 60% of the data of the production database. I have identified 3 tables which take up 50% of the size of the production database (yup, these are denormalised tables having huge amounts of derived data in them. As to why i have these tables is another long story). Now since i can do without the data in these 3 tables, the plan is to move all the data "except" the data in these 3 tables from production to test. Well, i have more than 300+ tables and i find the solution of typing in each 20+character tablename in the export tedious.....does anyone have any suggestions for me?? SantaMufasa, Sem , Carp , and other Gods of this forum , do you have any gems of advice for me (other than "Jay , go take a walk" ) ?
Help !

Regards,
S. Jayaram Uparna .

 

[dbtoo2001]

If you are looking to just export a bunch of tables, here is an example - it is not meant to be exact, just an example:

in sqlplus:
set pages 0
spool tables.lst
select owner||'.'||table_name||',' from dba_tables
-- you may want to add where owner = 'SOMEBODY'
/
spool off

Edit tables.lst and remove the last ',' on the last line.
(Ex. contents of table.lst:
TIMEDBA.CONTRACT_GROUPS,
TIMEDBA.CONTRACT_SHIPPED,
TIMEDBA.CONTRACT_PO,
TIMEDBA.CONTRACT_PRODUCT, <<--- remove this ',' )

and delete the tables that you don't want from this list.

Then using a parameter file, paste this list into the tables option. (ex. contents of 'param_file.txt':
FULL=N
TABLES=(
TIMEDBA.CONTRACT_GROUPS,
TIMEDBA.CONTRACT_SHIPPED,
TIMEDBA.CONTRACT_PO,
TIMEDBA.CONTRACT_PRODUCT)
GRANTS=Y
INDEXES=Y
CONSISTENT=Y

Then do the export using the paramfile and a user that is permitted access to the tables you are exporting:

exp user/password parfile=/share/home/oracle/bin/param_file.txt

-david

 

[carp]

Perhaps you export/import the three big tables, then blow away the amount of data you don't need once it's in the new database. Then export all of the data and import it with IGNORE = N. This way, the large tables would not be imported.

 

[uparna]

woo hoo !
great going guys thank you !... keep the ideas flowing! Both David's and Carp's solutions seem great to me, but i need to present the team handling the database (they are the mothership) with a set of ideas. Now i have 2 under my belt .. any more? woo-hoo again !

Regards,
S. Jayaram Uparna .

 

[sem]

Do you plan to migrate to the same OS/Oracle version? If so, you may look at
thread186-591861 that explanes how to do it without export/import, by just moving database files. This is not exactly what you want, but if the time (not disk space) is an issue, this way is much faster.

Regards, Dima

 

[chukws]

oracle has a step-by-step on how to do this. It is called Oracle by example obe. the common dba task.

Common DBA Tasks

http://otn.oracle.com/obe/start/index.html

chukws