SQL PLUS

[joeythelips]

Hi,

I want to make a copy of an oracle database.

Is it possible to do this through SQLPLUS, or what is the best recommended way to do this.

Regards

 

[lfoata]

I do not recommend using sqlplus. You will have a lot of work trying to copy all the tables.
I can see two ways:
If you are copying to the same OS version
1 - Shutdown the database, copy all the files (Oracle software and database files)
- start the database.
if you need to rename, create a backup control file, edit it and restart the instance
2 - most commonly used : Do a full export of the database
- install oracle software on the other server
- create an empty instance
- import the data


Hope this helps Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA

 

[joeythelips]

Hi Thanks for your help.

I am very new to database admin. Can i export the database to the same server as the current one resides? The only reason i need to create a copy is that i want to test a sql update statement on a test version befor ei do it on the live db.

Joe

 

[carp]

Yes, you can have more than one database on the same server. However, if you are going to just be testing a few statements, it might be easier to clone the tables and use the same database (this may or may not be frowned upon by your employer - check first!). Create a test schema and copy the tables into the test schema. In order to avoid any possibility of honking up your production data, give the tables similar but different names. Also, you might want to limit the data to a subset of the real data - not only will this speed up your test queries and take up less storage, but it will also make it easier to tell if you have a cartesian join. To clone the table, try the following:

CREATE TABLE test_schema.test_my_table AS
SELECT * FROM production_schema.my_table
WHERE rownum < 11; <= This will give you ten rows of data.

Note that this will copy the structure and data, but not indexes, constraints, or triggers. This may be a problem, depending on what you are working on - just getting the SQL right or tuning for performance.

If this approach works for you, it's a LOT easier than cloning an entire database.