Re: Reclaim Tablespace after drop with purge options

  • From: Bala Krishna <krishna000@xxxxxxxxx>
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>
  • Date: Wed, 3 Sep 2014 14:52:36 -0400

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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Bala Krishna
> *Sent:* Wednesday, September 03, 2014 1:49 PM
>
> *To:* Seth Miller
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *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@xxxxxxxxx>
> wrote:
>
> Bala,
>
>
>
> We're there LOB columns in any of those tables?
>
>
>
> Seth Miller
>
>
>
>
> On Wednesday, September 3, 2014, Bala Krishna <krishna000@xxxxxxxxx>
> wrote:
>
> Miller,
>
> Its not compressed.
>
>
> Regards
>
> Bala
>
>
>
> On Wed, Sep 3, 2014 at 1:17 PM, Seth Miller <sethmiller.sm@xxxxxxxxx>
> 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@xxxxxxxxx>
> 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
>
>
>
>
>
>
>

Other related posts: