| Dropping database objects... [message #176696] |
Fri, 09 June 2006 04:01  |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
I am restoring a database to a state that it was before i made some changes. Can someboody please advice on teh order in which i should drop these items.
1.Synonyms
2.Constraints
3.Indexes
4.Tables
5.Sequences
6.Grants
7.Primary Keys
Can i just drop the tables and any synonyms, constraints, indexes etc that are related to the table will be dropped automatically?
An explanation on the order of deletion will be helpfull, e.g why i need to delete the constraints first before dropping the tables etc..
And also i am looking at the list of constraints, i see several constraints that have a name like SYS_C00***. These are also related to the tables i would like to drop but i didnt create these. Most of them look like NOT NULL constrainsts on the PKs and FKs on teh above tables. Were these created automatically by oracle? Do i need to drop these as well or will they be dropped when i delete the tables.
Many Thanks
[Updated on: Fri, 09 June 2006 04:02] Report message to a moderator |
|
|
|
| Re: Dropping database objects... [message #176698 is a reply to message #176696] |
Fri, 09 June 2006 04:11  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This order will work:
1) Referential integrity constraints
2) Everything else.
If you drop a table, then all the constraints, indexes and triggers on that table will go too.
Sequences and Synonyms will need to be dropped seperately.
The SYS* constraints are ones that Oracle has named, because whoever created them ommited to name them explicitly.
If you are trying to get rid of all the objects for a user, the easist way is probably:
DROP USER CASCADE;
Followed by recreating the user with an empty schema.
|
|
|
|
| Re: Dropping database objects... [message #176705 is a reply to message #176698] |
Fri, 09 June 2006 04:24 |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
| JRowbottom wrote on Fri, 09 June 2006 04:11 |
This order will work:
1) Referential integrity constraints
2) Everything else.
If you drop a table, then all the constraints, indexes and triggers on that table will go too.
Sequences and Synonyms will need to be dropped seperately.
The SYS* constraints are ones that Oracle has named, because whoever created them ommited to name them explicitly.
If you are trying to get rid of all the objects for a user, the easist way is probably:
DROP USER CASCADE;
Followed by recreating the user with an empty schema.
|
Im trying to avoid dropping the user coz then i would have to recreate all the tables, indexes for the user which is a very long process..
So if i drop all the foreign keys first then i can drop anything else after that?
thanks
|
|
|
|
| Re: Dropping database objects... [message #176711 is a reply to message #176705] |
Fri, 09 June 2006 04:36 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You'll have to drop/disable all the foreign key constraints that point to primary keys on the tables that you want to drop.
Once they're gone, you should be able to drop pretty much anything you like.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
