Split database to test and QA

[dasniper]

I have an Oracle 8.1.6.3 database running on Solaris that I've been using for about a month. Now, we'd like to split the database into a test environment and a QA environment.

I tried following Oracle's guide to making a new database instance, and successfully crashed the database. Is there an easy way to duplicate all of the tables and data and either put them into a new tablespace or make a new database instance? I've tried exporting and importing to a new username, but only a few tables get moved.

 

[Kenrae]

That's strange. When I export-import data from a tablespace to another, I move all the data. When you export data you have the option to export only tables or every object in the tablespace, have you seen it?

 

[dasniper]

Yeah, I saw that option. I exported by User, as the user that owns the original data is the same.

Maybe I should export by tables and list the tables I want to export and then import that dump as the new tablespace user?

 

[Kenrae]

I don't know. I always export by user and it have never failed me. You could try :-I

 

[Guest_imported]

You can export full database :

exp user/password@service_name file=c:\path_name\export_file.dmp log=c:\path\log.log full=y

you can add param: consistency=y

and then, import only user:
imp user/password file=c:\thesameexportfile.dmp log=c:\anotherlog.log fromuser=UserID

Doing it this way, before importing, you need to create this user by hand in your new database.

hth
wysza

 

[dasniper]

Thanks to everyone who responded.

It turns out, it helps if your tablespace is not out of room.

After I turned auto-extend on for the tablespace, the tables imported without a problem.

 

[DBAwhosaysNIE]

just out of curiosity why didn't you just physically clone the original database from a backup and rename it & its instance?

I have to do this all the time and that method guarantees identical physical structure (down to the extent & even block level) and instance configuration. you can also set up separate homes for testing patches.

just my 2c/never 2bHO...

 

[dasniper]

I tried to create a new instance and wound up trashing the original control files (because Orcalse said to use the REUSE keyword)