creating blank test database from refrence DB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: creating blank test database from refrence DB

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    creating blank test database from refrence DB

    Hi

    I need to create a blank testdb from a refrence DB.This contains all the proceedures,triggers,roles,synonyms,grants,views etc.

    My idea is this

    1)use dbms_metadata to get tablespace ddl and create the tablespaces
    2)use export on the refrence db
    3)run import on the test db

    does the import and export get all proceedures,triggers,views,synonyms,grants,functions etc

    regards
    Hrishy

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes exp gets everything

    exp systen/xx file=export.dmp full=y rows=n

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Davey

    Thank you .

    I was thinking after step 1 i will precreate those tablespaces .and then run the import.

    regards
    Hrishy

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    You can also take a backup of your control file,
    alter database backup control file to trace;.
    This will create a trace file in user_dump_dest, which will contain the create database script. U can modify this as per your requirement and create the database and do an export/import of the schema

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Anandkl

    That was really cool :-).Thank you very much

    regards
    Hrishy

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by anandkl
    Hi,
    You can also take a backup of your control file,
    alter database backup control file to trace;.
    This will create a trace file in user_dump_dest, which will contain the create database script. U can modify this as per your requirement and create the database and do an export/import of the schema

    regards
    anandkl
    not it wont. It's a statement to recreate the control file not the database! A very different thing

    a full import will try and create the tablespaces if the paths and everything are the same

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Davey,
    I mentioned ..We need to modify this script to create the database.
    I.e u need to remove CONTROLFILE REUSE keyword and specify the size for the datafiles as per required.

    regards
    anandkl
    anandkl

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    All it gives you is the path of the datafile, not the size not whether its an LMT or a DMT, not whether it is uniform or system allocated and if uniform what the extent sizes are. not whether it is an ASSM tablespace or not

    What if you have a tablespace with many datafiles which arent named like each other, what you gonna do then?

    What if you have 100 tablespaces, you wanna edit 100 lines?

    That method is totally wrong when there are already 'proper' wasys to do thing

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Davey,
    What you say is correct, but doing a full import and not having the correct path would error out.

    So what would you do in this case.


    regards
    anandkl
    anandkl

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    as i said before it will create them IF the paths are correct.

    Then you use dbms_metadata (which was his original plan) and change the paths in there. Without having to hunt around a bunch od DD tables to get the same info

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