| Database Back and restore [message #175063] |
Wed, 31 May 2006 10:18  |
yadala
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
Hi,
I am having an application using Oracle database. I am customizing the application and to apply the customization it need a clean database. every week I will apply the customization don in that week. I request you to suggest proper method to do this. I had taken back up of the Oracle immediately after loading..
Regards
Sudhakar
|
|
|
|
| Re: Database Back and restore [message #175121 is a reply to message #175063] |
Wed, 31 May 2006 17:36  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Am I hearing you say you want to remove all the data from an Oracle schema?
Truncate is better because it is much faster and it leaves the shell of the database intact. All the indexes, functions, packages, procedure, and other database objects remain intact. When you start adding data again it already has the data structures out there and you do not have to pay the performance costs associated with data growth.
If you think this is so then:
Log onthe the database under the schema owner.
>sqlplus
username> (use the user that owns the schema)
password> (use properly formatted passwords, i.e. honda42sam&&)
spool truncate.txt
set linesize 100
set pagesize 0
select 'truncate table ' || table_name || ';'
from all_tables where owner = <schema user>
order by table_name;
.
.
a bunch of stuff displayed
.
.
commit;
exit
Now vi or edit truncate.txt
take out the sql statements used to build the file, and the commit at the end.
save the truncate.txt file
>sqlplus
username> (your schema name)
password> (your properly formatted password)
@truncate.txt
commit;
***
Now, BE SURE YOU KNOW that you are removing all of the data in your database.
Do not truncate any tables owned by the Oracle dictionary.
Only truncate the tables owned by the schema owner.
Good luck!
Neil
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
