Operating System - HP-UX
1748031 Members
5212 Online
108757 Solutions
New Discussion юеВ

Re: deleting a tablespace

 

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 8
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 ?

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 ?

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

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)

Re: deleting a tablespace

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