Importing partitioned tables and indexes into another database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Importing partitioned tables and indexes into another database

Hybrid View

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    dbms_metadata, or learn how to do global replaces

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Is there any other smart solution?
    Very simple.
    Use sample clause.

    Tamil

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use the same tablespace names on the target database as on the source.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    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 01:29 PM.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width