Deleting dba_tablespaces [message #55436] |
Fri, 24 January 2003 05:12 |
Bruno
Messages: 12 Registered: May 2002
|
Junior Member |
|
|
Hi all,
I'd like to know why I could run
"delete * from dba_tablespaces" ?
(Please don't try this instruction because I've destroyed my db!)
This DML seems not rollbackable, infact I immedaitely obtain "ORA-600".
Thank you
|
|
|
|
Re: Deleting dba_tablespaces [message #55446 is a reply to message #55436] |
Fri, 24 January 2003 06:52 |
psmyth
Messages: 81 Registered: October 2002
|
Member |
|
|
can't say 'why' you can do this... other than I can't say why you shouldn't be able to do it... rather unhelpfully, it's like 'life' in that there's lots of things you are capable of doing, but some are probably best avoided ;-)
Have a look in $ORACLE_HOME/rdbms/admin - maybe you can run catspace.sql to recreate it... if you can get into the db at all.
If not, you get to test your recovery procedures ;-)
|
|
|
Re: Deleting dba_tablespaces [message #55504 is a reply to message #55443] |
Tue, 28 January 2003 02:22 |
Bruno
Messages: 12 Registered: May 2002
|
Junior Member |
|
|
Yes, I know how deleting a tablespacecorrectly but I can tell you that if you connect as Sys the sql
"delete from dba_tablespaces" works!!!And this is not a transaction like the other because you cannot rollback it
|
|
|
Re: Deleting dba_tablespaces [message #55506 is a reply to message #55504] |
Tue, 28 January 2003 03:02 |
psmyth
Messages: 81 Registered: October 2002
|
Member |
|
|
sys creates the view dba_tablespaces upon database creation, and if, for example, you upgraded your database and needed to run catalog.sql etc, it would recreate dba_tablespaces - to recreate it, sys would need to drop the view initially.
Basically, for full functionality, SYS has privileges to do everything (delete/drop/update etc) to the internal RDBMS objects views etc.
Its like having 'root' privileges on a unix server - you can delete everything on the server (try it if you like 'rm -r *'), but that doesn't mean you should do it.
One of the reasons DBA's and systems administrators get paid well (current job market conditions excepting) is that they work on systems which require care and attention to detail. SYS should be able to delete everything from dba_tablespaces - if you don't like want to risk it, don't connect as sys!
Everyone makes mistakes - The Gartner Group have produced statistics to suggest that 40% of server downtime is directly related to engineers logged onto the servers (so if you don't let anyone login, you could theoretically keep your systems running with 40% better uptime).
The golden rule is to make sure that you always have backups and recovery paths that are tested. Its often worth having two separate recovery paths. Some people think that having online backups of Oracle databases is sufficient, but in my experience, you will often find that having regular exports in addition to online backups offer a more flexible recovery path.
So don't be surprised that you can delete things when connected as SYS... it owns them, it should be able to delete them. Maybe you could create a user which has 'read' access only to the SYS objects and use that most of the time - if you need to delete or update something, you'll have to specifically login as another user to do that.
|
|
|