-
Importing partitioned tables and indexes into another database
I am copying metadata of our 3.5T oracle 9i production database into a development database which is 1% of production.
the three schemas I am copying have about 2000 partitioned tables and indexes and import has failed on tablespace names.
I created an indexfile which has 29000 rows. Editing this file and changing the tablespace names, extent sizes etc., undoubtly is a very painful task and may take along time. Importing one or two tables by editing is ok. But editing 2000 tables each has a definition of 100 lines or more is cumbersome to say the least.
Is there any other smart solution?
-
dbms_metadata, or learn how to do global replaces
-
Is there any other smart solution?
Very simple.
Use sample clause.
Tamil
-
Use the same tablespace names on the target database as on the source.
-
Thanks Tamil, Davey, Slim.
Tamil,
Can you elaborate by an example? I never heard of sample clause.
Davey,
Can dbms_metadata generate DDL for partitioned tables and indexes without names of tablespaces?
Last edited by newbie5; 11-15-2005 at 02:29 PM.
-
Originally Posted by newbie5
Thanks Tamil, Davey, Slim.
Tamil,
Can you elaborate by an example? I never heard of sample clause.
Davey,
Can dbms_metadata generate DDL for partitioned tables and indexes without names of tablespaces?
First, run exp with rows=N .
exp / file=test.dmp owner=tamil rows=N
Then, do import with indexfile option and rows =N :
imp / file=test.dmp indexfile=test2.sql rows=N FULL=Y
The test2.sql file contain all tables and indexes' creation script for the schema TAMIL.
Just change the TABLESPACE name by globally.
Tamil
-
So where is a SAMPLE clause?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally Posted by jmodic
So where is a SAMPLE clause?
I thought the original poster wanted 1 % of data, that's why I said use "SAMPLE" clause. Then later I realized that he had issue with TABLESPACE name.
Tamil
-
Perhaps you can use toad's generating schema scripts function.
It is conveninent to ask toad to create tables and indexes onto
the new tablespace. Also, you can specify whether you need
the table/index-level storage parametes or not.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|