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

yes exp gets everything

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

Hi Davey

Thank you .

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

regards
Hrishy

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

Hi Anandkl

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

regards
Hrishy

Quote:

---

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

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

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

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

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