Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

deleting a tablespace

Ignacio Rodríguez Arrós
Frequent Advisor

deleting a tablespace

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?
8 REPLIES
Tom Geudens
Honored Contributor

Re: deleting a tablespace

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

Regards,
Tom
A life ? Cool ! Where can I download one of those from ?
Ignacio Rodríguez Arrós
Frequent Advisor

Re: deleting a tablespace

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
Tom Geudens
Honored Contributor

Re: deleting a tablespace

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
A life ? Cool ! Where can I download one of those from ?
Ignacio Rodríguez Arrós
Frequent Advisor

Re: deleting a tablespace

i??m still using oracle 7.3.4 :-(
T G Manikandan
Honored Contributor

Re: deleting a tablespace

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
Ignacio Rodríguez Arrós
Frequent Advisor

Re: deleting a tablespace

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?
Yogeeraj_1
Honored Contributor

Re: deleting a tablespace

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ignacio Rodríguez Arrós
Frequent Advisor

Re: deleting a tablespace

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