Home » RDBMS Server » Server Administration » Deleting dba_tablespaces
Deleting dba_tablespaces [message #55436] Fri, 24 January 2003 05:12 Go to next message
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 #55443 is a reply to message #55436] Fri, 24 January 2003 06:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
NO USER is supposed to ISSUE delte/update/insert statments against any DBA_*,V_$* or any system table.
if you want to delete or drop the tablespaces,

mag@itloaner1_local > select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
RBS
USERS
TEMP
TOOLS
INDX
OEM

7 rows selected.

--
-- you cannot just drop the tablespace if you have some data in it
--

mag@itloaner1_local > drop tablespace tools;
drop tablespace tools
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

--
-- so use including contents
--

mag@itloaner1_local > drop tablespace tools including contents;

Tablespace dropped.

mag@itloaner1_local > select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
RBS
USERS
TEMP
INDX
OEM

6 rows selected.
--
-- But this wont delete the datafile physically (8i). 
-- in 9i, you can add this clause so that, the concerned datafiles are also dropped.

mag@itloaner1_local > DROP TABLESPACE tool INCLUDING CONTENTS AND DATAFILES;

Re: Deleting dba_tablespaces [message #55446 is a reply to message #55436] Fri, 24 January 2003 06:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Archieve log problem helpme!
Next Topic: Moving table to new tablespace
Goto Forum:
  


Current Time: Thu Sep 19 20:22:25 CDT 2024