RE: Reclaim Tablespace after drop with purge options
Date: Wed, 3 Sep 2014 15:17:10 -0400
Message-ID: <0cdc01cfc7ab$a9b9e0e0$fd2da2a0$_at_rsiz.com>
that *might* be okay, but I don’t have time to parse it. Please try something simple like:
select sum(blocks),tablespace_name from dba_free_space group by tablespace_name order by tablespace_name;
sure, go ahead and do different units than blocks, but let’s avoid the joins and all that and just look at dba_free_space.
mwf
From: Bala Krishna [mailto:krishna000_at_gmail.com]
Sent: Wednesday, September 03, 2014 2:53 PM
To: Mark W. Farnham
Cc: Seth Miller; oracle-l_at_freelists.org
Subject: Re: Reclaim Tablespace after drop with purge options
Below is the query that i'm using to check free space.
col tbs for a15
set head on
select
a.tablespace_name "TBS",nvl(a.total,0) "TOTSP",nvl((a.total-f.free),a.total) "USEDSP",
nvl(f.free,0) "FREESP"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by 2 desc
/
On Wed, Sep 3, 2014 at 2:46 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
Create and drop a wee little table in one of those tablespaces and check again.
By the way, HOW are you checking for free space?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bala Krishna Sent: Wednesday, September 03, 2014 1:49 PM
To: Seth Miller
Cc: oracle-l_at_freelists.org
Subject: Re: Reclaim Tablespace after drop with purge options
It doesn't have any un-structured data types at all and recycle bin is already empty.
Regards
Bala
On Wed, Sep 3, 2014 at 1:42 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:
Bala,
We're there LOB columns in any of those tables?
Seth Miller
On Wednesday, September 3, 2014, Bala Krishna <krishna000_at_gmail.com> wrote:
Miller,
Its not compressed.
Regards
Bala
On Wed, Sep 3, 2014 at 1:17 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:
Bala,
Were these compressed tables? Is it possible that they were only occupying 3GB of physical space?
Seth Miller
On Wed, Sep 3, 2014 at 12:05 PM, Bala Krishna <krishna000_at_gmail.com> wrote:
Hi All,
Our DB oracle 11gr2 with segment space mangement manual for all the tablespaces that we have for some other reasons so that i dont want to go deep in that.
But we have dropped some 1TB tables with purge option directly but the space is not reclaimed at all not sure why if i check the free space of tablespace it just freed only 3GB. Any ideas appreciated.
Regards
Bala
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 03 2014 - 21:17:10 CEST