topic Re: deleting a tablespace in Operating System - HP-UX

deleting a tablespace

Ignacio Rodríguez Arrós — Wed, 16 Oct 2002 05:47:24 GMT

yesterday i began to delete a 5 Gb tablespace in oracle. Previously i deleted nearly all the data in it so if you looked through the oracle storage manager,it was empty.
Now one instance of the oracle storage manager is still deleting the tablespace, but if i open another storage manager and look this tablespace it show me like if it has 4.8 Gb of data. Why?

Re: deleting a tablespace

Tom Geudens — Wed, 16 Oct 2002 07:03:50 GMT

Hi,
I'm not quite clear on this. How exactly are you "deleting" the tablespace ?

Regards,
Tom

Re: deleting a tablespace

Ignacio Rodríguez Arrós — Wed, 16 Oct 2002 07:23:29 GMT

It has finished yet. I deleted it pointing it in the oracle storage manager and selecting the option delete, not from the svrmgrl in unix but from a pc with the oracle client.
Thanks in advance

Re: deleting a tablespace

Tom Geudens — Wed, 16 Oct 2002 09:50:36 GMT

Hi,
This sounds like the behavior you would have when someone is doing an action that has not been committed yet. Now, in the versions of Oracle I have (seen), this would not be the case for the dropping of tablespaces, since those actions do not need commits. However, I have heard - I'm no longer a DBA but used to be - that very recent releases of Oracle support commit/rollback for this kind of action too. What version of Oracle are you running ?

Regards,
Tom

Re: deleting a tablespace

Ignacio Rodríguez Arrós — Wed, 16 Oct 2002 09:53:27 GMT

i??m still using oracle 7.3.4 :-(

Re: deleting a tablespace

T G Manikandan — Wed, 16 Oct 2002 09:53:52 GMT

I probably think that you are trying to delete the objects of a tablespace and try to reduce the amount of space on the tablespace.

When you delete the objects from the tablespace the space is not released.
Only when you truncate or drop the objects the free space is re-claimed by the tablespace.
If you want to reclaim the space in the tablespace export the objects from the tablespace
drop the objects
recreate the objects by importing the objects from the export file.

Thanks

Thanks

Re: deleting a tablespace

Ignacio Rodríguez Arrós — Thu, 17 Oct 2002 05:37:31 GMT

I can??t assign points, it happens me very often and i don??t know why. I??m doing all the process rigth, i??m sure.
??can anybody help me?

Re: deleting a tablespace

Yogeeraj_1 — Thu, 17 Oct 2002 05:55:26 GMT

hi,
connect as system using SQLPLUS and run the following SQL commands (you should get the list of objects residing on the tablespace you are trying to delete)

select substr(owner,1,20) owner,
substr(table_name,1,20) "Table name"
from dba_tables
where tablespace_name = 'TBS1'

select substr(owner,1,20) owner,
substr(table_name,1,20) "Table name"
from dba_indexes
where tablespace_name = ''

and also (since it can also be temporary objects too):
select username
from dba_users
where default_tablespace=''
or temporary_tablespace='';

[NB. substitute TBS1 for the tablespace in question here]

and finally, the output generated by the attached script (free.sql).

Post your results! SQLPLUS queries will give us the right picture.

Regards
Yogeeraj

Re: deleting a tablespace

Ignacio Rodríguez Arrós — Thu, 17 Oct 2002 06:08:08 GMT

Hi Jogeeraj, i can??t do that yet because i??ve already delete the tbs.