Database size reduced after tablespace deletion

[Former Member]

Hi,

DB:Oracle 11g

Before EHP, we had PSAPSR3701 tablespace. After EHP, PSAPSR3702 created and PSAPSR3701 became empty. System showed in DB02 size and free size of PSAPSR3701 (100 GB). Using brtools(brspace), I dropped tablespace PSAPSR3701. What I see in DB02-> Space overview is Size of DB is reduced and free space also reduced. on file system level, I dont see any change in size. I was expecting free DB size to increase.

Can somebody explain what happens here.

Thank you in Adv.

Regards

[paul_power]

Hi,

If you deleted the entire tablespace, then the space will cath up once the regular jobs have run (usually these kind of jobs run in the background once a week to update overall db size) Its not something you would see reflected instantly.

If you have deleetd tables from a tablespace, you would need to run a reorg of that tablespace for the free space to be updated.

Regards,

Paul

[former_member189725]

Also suggest you to go to DBACOCKPIT -->Space --> Overview and refresh .

This would schedule a job REFRESH SPACE STATISTIC:<SID>. Let this job finish .

Then check the output in DB02 .

I believe you dropped the tablespace including datafiles as Juan mentioned.

Regards

Ratnajit

[JPReyes]

on file system level, I dont see any change in size. I was expecting free DB size to increase.     

Datafiles are been deleted (dropped) so you should check the filesystems where those datafiles existed to make sure they are gone. Beside that you should not be expecting anything else.

Regards, Juan

[Former Member]

Thanks Juan. I see data files deleted.

BR0280I BRSPACE time stamp: 2012-08-08 02.40.50

BR0794I Database file /oracle/CP1/sapdata4/sr3701_1/sr3701.data1 deleted

BR0480I Directory /oracle/CP1/sapdata4/sr3701_1 deleted

BR0794I Database file /oracle/CP1/sapdata3/sr3701_2/sr3701.data2 deleted

BR0480I Directory /oracle/CP1/sapdata3/sr3701_2 deleted

BR0794I Database file /oracle/CP1/sapdata2/sr3701_3/sr3701.data3 deleted

BR0480I Directory /oracle/CP1/sapdata2/sr3701_3 deleted

BR0794I Database file /oracle/CP1/sapdata1/sr3701_4/sr3701.data4 deleted

BR0480I Directory /oracle/CP1/sapdata1/sr3701_4 deleted

BR0794I Database file /oracle/CP1/sapdata1/sr3701_5/sr3701.data5 deleted

BR0480I Directory /oracle/CP1/sapdata1/sr3701_5 deleted

BR0794I Database file /oracle/CP1/sapdata1/sr3701_6/sr3701.data6 deleted

BR0480I Directory /oracle/CP1/sapdata1/sr3701_6 deleted

Verified that they are not there. Shouldnt I expect file system free space to increase.Why?

[JPReyes]

Certainly. You should expect filesystem free space to increase not "free DB size" as stated above.

Regards, Juan