CODEWITHSUNDEEP
oracle-databaseoracle10gschema
Louie Warren
Louie Warren

Reputation: 41

Oracle: New database vs new user.schema?

Backstory: I had this database dropped in my lap when the company, I began working for last October, won the contract. The developers gave me very little turn over, so I am digging back in my memory to when I maintained an Oracle DB in 2002. My skills are extremely dated, so please be gentle.

Oracle 10.2.0.0.0 Windows Server 2003

My customer now wants me to create a blank copy of an existing application/database for a future effort. My 1st idea was to clone the existing database and remove all the data. However, I seem to remember working on a project years ago that led me to believe I could clone the existing user/schema and not have the whole other database to maintain. There is a high possibility this new application will never be used, and I'd like to do the least amount of work as possible. The current database is small. Any suggestions would be appreciated.

Upvotes: 0

Views: 183

Answers (2)

Suresh Gautam
Suresh Gautam

Reputation: 893

I'd suggest to use datapump(expdp/impdp) from 10g onwards. You can go through following commands:

--Export dump in source server:

expdp user/password directory=oracle_directory dumpfile=mydump_abc.dmp logfile=mydump_abc_export.log schemas=abc content=metadata_only

--Import dump in destination server

impdp user/password directory=oracle_directory1 dumpfile=mydump_abc.dmp logfile=mydump_abc_restore.log remap_schema=abc:xyz

Upvotes: 2

Brian McGinity
Brian McGinity

Reputation: 5935

You can export the database with the rows=n parameter:

exp username/password@db owner=abc rows=n

This will export everything except the data.

Then if you want to import it, you can change users if needed:

imp username/password@db fromuser=abc touser=xyz

Upvotes: 2

Related Questions