1

Topic: To count free space for a DB> =100 TB

Greetings to all!
I will be glad, if who shares council/inquiry as in Oracle 11.2.0.4
Quickly = for pair minutes to count the size free space for tabular spaces.
All question that the size of a DB 100 , and request of type
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round (sum (bytes)/1024/1024, 2) as free_space
from dba_free_space
group by tablespace_name) a;
(select tablespace_name, sum (bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b;

2

Re: To count free space for a DB> =100 TB

You brag or is really dissatisfied?:-D
Try to begin with  / * + rule */

3

Re: To count free space for a DB> =100 TB

At such DB it is not necessary to consider.
It is necessary to buy at once simply still disks.

4

Re: To count free space for a DB> =100 TB

BigBudda wrote:

quickly = for pair minutes to count the size free space for tabular spaces.

At first so, and then consider

create table dba_free_space_tmp as select * from dba_free_space;
create table dba_data_files_tmp as select * from dba_data_files;

5

Re: To count free space for a DB> =100 TB

Can throw off more bright request, it is possible on others I twist?
Launched with  rule, hangs minutes 20....
And than temporary tables with _temp help?

6

Re: To count free space for a DB> =100 TB

BigBudda;
, you count, how many records on segments for 100 bases
12 500 000 000 8k units
Productivity will depend also on HARDWARE types
To begin with look at the request plan to estimate that oracle thinks
p.s. It is possible to try parallel to include, but it is not assured that flies up on fix view

7

Re: To count free space for a DB> =100 TB

BigBudda wrote:

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round (sum (bytes)/1024/1024, 2) as free_space
from dba_free_space
group by tablespace_name) a;
(select tablespace_name, sum (bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b;

at first where a condition  lost?
Secondly, try so: [spoiler]

with
ts as (select/* + materialize */name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts $)
,fi as (select/* + materialize */* from sys.file $)
,ktfbfe as (select/* + materialize */* from sys.x$ktfbfe)
,free_space (tablespace_name, bytes) as (
select ts.name,
sum (f.length) * ts.blocksize
from ts, sys.fet $ f
where ts.ts# = f.ts#
and ts.bitmapped = 0
group by ts.name, ts.blocksize
union all
select
ts.name;
sum (f.ktfbfeblks) * ts.blocksize
from ts, ktfbfe f
where ts.ts# = f.ktfbfetsn
and ts.bitmapped <> 0 and ts.online $ in (1,4) and ts.contents $ = 0
and bitand (ts.flags, 4503599627370496) <> 4503599627370496
group by ts.name, ts.blocksize
union all
select
ts.name;
sum (u.ktfbueblks) * ts.blocksize
from sys.recyclebin $ rb, ts, sys.x$ktfbue u, fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online $ in (1,4) and ts.contents $ = 0
and bitand (ts.flags, 4503599627370496) <> 4503599627370496
group by ts.name, ts.blocksize
union all
select ts.name;
sum (u.length) * ts.blocksize
from ts, sys.uet $ u, fi, sys.recyclebin $ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
group by ts.name, ts.blocksize
union all
select
ts.name;
sum (f.extent_length_blocks_2K) *2048
from ts, sys.new_lost_write_extents $ f, fi
where ts.ts# = f.extent_datafile_tsid
and f.extent_datafile_tsid = fi.ts#
and ts.bitmapped <> 0 and ts.online $ in (1,4) and ts.contents $ = 0
and bitand (ts.flags, 4503599627370496) = 4503599627370496
group by ts.name
)
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round (sum (bytes)/1024/1024, 2) as free_space
from free_space
group by tablespace_name) a;
(select tablespace_name, sum (bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
/

[/spoiler]
Should be much faster

8

Re: To count free space for a DB> =100 TB

Thanks for a script, but under sys I can not fulfill:
sys.new_lost_write_extents $ does not exist

9

Re: To count free space for a DB> =100 TB

BigBudda;
[spoiler for 11.2]

with
ts as (select/* + materialize */name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts $)
,fi as (select/* + materialize */* from sys.file $)
,ktfbfe as (select/* + materialize */* from sys.x$ktfbfe)
,free_space (tablespace_name, bytes) as (
select ts.name,
sum (f.length) * ts.blocksize
from ts, sys.fet $ f
where ts.ts# = f.ts#
and ts.bitmapped = 0
group by ts.name, ts.blocksize
union all
select
ts.name;
sum (f.ktfbfeblks) * ts.blocksize
from ts, ktfbfe f
where ts.ts# = f.ktfbfetsn
and ts.bitmapped <> 0 and ts.online $ in (1,4) and ts.contents $ = 0
group by ts.name, ts.blocksize
union all
select
ts.name,
sum (u.ktfbueblks) * ts.blocksize
from sys.recyclebin $ rb, ts, sys.x$ktfbue u, fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online $ in (1,4) and ts.contents $ = 0
group by ts.name, ts.blocksize
union all
select ts.name,
sum (u.length) * ts.blocksize
from ts, sys.uet $ u, fi, sys.recyclebin $ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
group by ts.name, ts.blocksize
)
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round (sum (bytes)/1024/1024, 2) as free_space
from free_space
group by tablespace_name
) a;
(select tablespace_name, sum (bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name
) b
where a.tablespace_name=b.tablespace_name

[/spoiler]

10

Re: To count free space for a DB> =100 TB

By the way, in 11.2 dba_free_space   . I generally would avoid to it to use in 11.2

11

Re: To count free space for a DB> =100 TB

xtender,
Sayan mountains, missed the mark, for 11.2 with it is useless...

12

Re: To count free space for a DB> =100 TB

Missed the mark;
At what here 11.2?
To replace [quote =]

from dba_free_space

on

from free_space

did not master?