Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Making database clone (without data)

Posted on 2007-10-17
7
Medium Priority
?
846 Views
Last Modified: 2013-12-19
Hi experts,

I am newbie in Oracle administration. I am using Oracle 10g database. I would like to clone database i.e. make an exact copy of tablespaces, tables, indexes, triggers, functions etc but without data.
What is the most efficient way to do it.?

Currently I am doing it in the way that taking sql scripts from 1st database and I am creating users, roles, tablespaces on the second database an than I am importing using dump files schema by schema (taken from 1st database). It takes a lot of time, and there are many errors that appears, so sometimes I need to perform import few times to be sure that I have everything in the new database.

Is there any other faster and more automatic way to have the same result?

Thanks for any advice

Adam
0
Comment
Question by:adamgasior
7 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 20092287
Use Export/Import or DataPump with
ROWS=N
parameter
0
 

Author Comment

by:adamgasior
ID: 20092335
I know this, but to use export import, tables and users need to be in the destination database.
0
 
LVL 9

Expert Comment

by:konektor
ID: 20092642
you can create database using Database Configuration Assistant
http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/create.htm#i1005759
it allows you to Manage Templates ... you can create template for creating DB with all you need, you've described
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 48

Expert Comment

by:schwertner
ID: 20093360
----> but to use export import, tables and users need to be in the destination database.

NO!

If you use DATA PUMP (Oracle 10g R2) you have to create only the NONSYSTEM tablespaces.
Users will be created from DATA PUMP.

Neither in Export/Import nor in Data Pump you have to create tables!
0
 

Author Comment

by:adamgasior
ID: 20093833
what about trigger, stored fucntions, roles, synonyms,  packages,indexes will they also be created by importing using import dump? (i have oracle 10g2)
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 20098268
Hi adamgasior,

Yes, all those database objects will be imported. I've provided a slightly more detailed instruction in one of my previous answer http://www.experts-exchange.com/Database/Oracle/9.x/Q_22822286.html.

The instruction is import and export. The steps for doing the data pump is similar.

Cheers,
NicksonKoh
0
 
LVL 48

Accepted Solution

by:
schwertner earned 240 total points
ID: 20099058
Of course all DB objects will be created by Export/Import.
Problems will arise if you change the schema names FROM_USER/TO_USER
but this is not thge case.
All will strongly recommend to use DATA PUMP if this is Oracle 10g R2.
Precreate only the nonsuystem tables.
Users will be created by Data Pump.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month21 days, 3 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question