Tom , I'm a Junior DBA and I was looking at this kind of program exactly.Thanks.

It was a great help

I am naive DBA, it was very helpful to me

This is very useful tip for me, and specially your free.sql script again was a very useful script to know the largest contigous extent available. I use your site for getting my problems solved and most of the times it has worked.

Thank you Tom

Tom:

When i run your free.sql I get thr follwoing:

SQL> @free.sql
order by uTESTp
         *
ERROR at line 19:
ORA-00904: invalid column name

2.  When i take the order statement out  Iget :

6 rows selected.


Statistics
----------------------------------------------------------
         14  recursive calls
          8  db block gets
        182  consistent gets
          0  physical reads
          0  redo size
        661  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
          6  rows processed

Neither is giving me the output you are showing?  Any ideas.   

SQL> @free 1


free uses &1, you must have run another script whose first argument was uTESTp (looks like my update cascade stuff ;)

Just pass in the column you want free to sort by. 

Tom:

Does this tell you that this system in under configured.
Apparently the rollback space is full and there is no index tablespace and temp is small.

Am I correct? what do you recommend doing? Does it tell you
that DBA is pretty junior?

Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
PICDATA 1,024,000 272,744 751,256 26.6 196,160
PICRBS 307,200 307,200 0 100.0 0
SYSTEM 204,800 73,328 131,472 35.8 131,368
TEMP 204,800 1,488 203,312 .7 203,312
------------ ------------ ------------
sum 1,740,800 654,760 1,086,040

Why do you say RBS should be 100% full all the time?

Tom:

1. Where do you place all indexex created? with data?

2. Also, I thought if rollback is full, you will get an error when you do a rollback since there is no space to store the old data?




Thansks

Tom:

Am I reading this right:

It says you can store 1 giga bytes in PICDATA and there are only 272,744 of bytes stored.

Used tell you how much data size you have right?
Free is what you can store?

Tom:

You define:

Used = space allocated within the tablespace to specific
objects
Free = space NOT yet allocated to any objects

Does this mean that 272,744 kbytes of space is allocated which means that there is that much data physically? or we can basically have 272 k bytes allocated but we only have 100 K bytes of data maybe only?

For rollback space, you deined it differently.
You said 100% used means it is all allocated. It does not mean that there is 100% data in that space?

I am a junior DBA and it is exactlly that I wanted to implement.
Thanks

Tom:

OK thanks. This tells me how much space is allocated for the initial size of the tabespace.

Is there a way to modify this to tell me how much data I have in each allocated space?

Thank you,

TOm:

Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
PICDATA 1,024,000 272,744 751,256 26.6 196,160


OK the tablespace size is 1 giga bytes of which 272,744 is allocated space (space that is ready to store data).
(e.g. analogy to a 10 x 6 ft room of which 30 square foot is allocated for storage. The remaining 30 square foot is not ready yet but can be used later.)

Can I know how much of this 272,744 k bytes is consumed by data (*.dbf files). Bascially do i have all 272 k free or do i have 100k of data etc.?

Tom,

It doesn't look like your script accounts for auto-allocation. Yes, this is a bad thing to have turned on, but when I got here it was the defacto standard. However, we soon started getting into situations where we would run a whole filesystem out of space, as we had multiple files trying to claim space that just wasn't there. In effect, we had "logically" allocated more than was physically available. Of course, had we not used auto-allocate, then this wouldn't have been an issue. The advent of our datamarts kind of reversed this problem as it was such a huge project that we had to allocate all the space up front, but we still needed to deal with the tens of little projects that had autoextend on every file and shared the filesystems with all other projects in that particular database.

This is the script we came up with. It also shows a fragmentation index because we were having problems with internal file fragmentation (which LMT fixes 100%) even though we tried using uniform extents (in DMT...which was easily overridden of course). I forget because it's been so long, but I think that as long as the fragmentation index was over 15% all was good. Of course, now that we're LMT, this number is pretty much worthless.

SELECT
sysdate inst_date ,
ddf.tablespace_name tspace ,
ROUND(fst.FRAG_INDEX,2) fragindex ,
allspc.KB totalaloc ,
(ddf.fretot - fst.total_free ) /1024 totalused ,
fst.total_free /1024 totalfree ,
ROUND((fst.total_free /ddf.fretot),2) * 100 frepct ,
fst.max_hole /1024 maxdiv ,
ROUND((fst.avg_hole /1024),2) avgdiv ,
fst.holes num_of_div
FROM dual,
(SELECT
tablespace_name tablespace_name ,
SUM(bytes) fretot
FROM dba_data_files
WHERE tablespace_name != 'TEMP'
group by tablespace_name) ddf,
(
SELECT tablespace_name tablespace_name,
SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)) )) FRAG_INDEX,
SUM(bytes) total_free,
MAX(bytes) max_hole,
AVG(bytes) avg_hole,
COUNT(*) holes
FROM dba_free_space
WHERE tablespace_name != 'TEMP'
GROUP BY tablespace_name
) fst,
(select table_space tablespace_name , sum(x) KB from
(select tablespace_name table_space,sum(BYTES/1024) x from
dba_data_files
where maxbytes=0 and
tablespace_name != 'TEMP'
group by tablespace_name
union all
select tablespace_name table_space,sum(MAXBYTES/1024) x from
dba_data_files
where tablespace_name != 'TEMP'
group by tablespace_name
)group by table_space) allspc
WHERE ddf.tablespace_name = fst.tablespace_name(+)
and ddf.tablespace_name = allspc.tablespace_name(+);


This is how the output of your script compares to the output of our script (yours is formatted much nicer). You'll notice that your script accounts for the allocated, but not the "yet to be allocated". I know it's hard to read due to the limitations of the display (maybe it will show up better in the "big window" after posting. I'm comparing your KBytes with our TotalAloc <hm. that's a typo - should be alloc...oh well>

%
Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
BRASSDATA01 10,240 3,672 6,568 35.9 6,120
BRASSDATA02 30,720 24,168 6,552 78.7 3,192
BRASSDATA03 10,240 2,664 7,576 26.0 7,576
BRASSDATA04 10,240 1,984 8,256 19.4 8,256


++++++++


INST_DATE TSPACE FRAGINDEX TOTALALOC TOTALUSED TOTALFREE FREPCT MAXDIV AVGDIV NUM_OF_DIV
--------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
19-AUG-02 BRASSDATA01 41.6 102400 3672 6568 64 6120 226.48 29
19-AUG-02 BRASSDATA02 32.23 102400 24168 6552 21 3192 297.82 22
19-AUG-02 BRASSDATA03 100 102400 2664 7576 74 7576 7576 1
19-AUG-02 BRASSDATA04 100 102400 1984 8256 81 8256 8256 1

Also, once we have a sense of the "total space that logically could be allocated" we run another script that makes sure we actually have the room in unix directory. This script is:

select dir, sum(x) GB from
(select substr(file_name,1,5) dir,sum(((BYTES/1024)/1024))/1024 x from
dba_data_files
where maxbytes=0
group by substr(file_name,1,5)
union all
select substr(file_name,1,5) dir,sum(((MAXBYTES/1024)/1024))/1024 x from
dba_data_files
group by substr(file_name,1,5)
)group by dir
/

Which gives us output in the form of:
DIR GB
----- ----------
/u30/ 15.0634918
/u31/ 4.02734375


Then we can go run some df -k and df -u and see how much more we can actually fit into the filesystems.

Is there a better way to do all this, or do we have a fairly good method? I realize that LMT caused all the fragmentation concerns to go away, but we haven't had time to update the script (ok, so it wouldn't be that time consuming, but it's a mental thing :) )

Thanks,
Dennis

I misled you. The storage_by_directory script (the last one) is used to help us find the logically allocated. That's what we use to get our sense of what we've tried to allocate. I suppose you could get that from the first script, but who wants to add up all those numbers manually.

Sorry for the poorly worded statement.

Dennis

Dear Tom
This was really usefull tip that solved my problem of knowing the free space of tablespace. Looking for some more usefull tips

Tom:

Why iam getting too different answers with the following two queries. First one show 4 spaces while secong shows 7. Problem is that rollback and temp space is getting full but it sounds more of allocation problem. What do you think?

ICT> @free 1
old 19: order by &1
new 19: order by 1


%
Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
ICDATA 409,600 203,264 206,336 49.6 172,328
ICRBS 266,240 61,448 204,792 23.1 204,792
SYSTEM 204,800 72,304 132,496 35.3 130,896
TEMP 153,600 16 153,584 .0 102,392
------------ ------------ ------------
sum 1,034,240 337,032 697,208

4 rows selected.

ICT> select substr(a.file_name,1,30),
2 a.file_id,
3 substr(a.tablespace_name,1,20),
4 a.bytes allocated,
5 nvl(b.free,0) free,
6 a.bytes-nvl(b.free,0) used
7 from dba_data_files a,
8 ( select file_id, sum(bytes) free
9 from dba_free_space
10 group by file_id ) b
11 where a.file_id = b.file_id (+);


SUBSTR(A.FILE_NAME,1,30) FILE_ID SUBSTR(A.TABLESPACE_ ALLOCATED Free Used
------------------------------ ---------- -------------------- ---------- ------------ ------------
/ora817/dbs/ict/ict1.dbf 1 SYSTEM 209715200 135,675,904 74,039,296
/ora817/dbs/ict/ict2.dbf 2 PICDATA 209715200 34,824,192 174,891,008
/ora817/dbs/ict/ict3.dbf 3 TEMP 52428800 52,420,608 8,192
/ora817/dbs/ict/ict5.dbf 5 ICDATA 209715200 176,463,872 33,251,328
/ora817/dbs/ict/pict6.dbf 6 ICRBS 209715200 209,707,008 8,192
/ora817/admin/ict/pict7.dbf 7 TEMP 104857600 104,849,408 8,192
/ora817/dbs/ict/pict4.dbf 4 ICRBS 62914560 0 62,914,560
------------ ------------
sum 713,940,992 345,120,768

7 rows selected.

Tom:

what do you mean by seeing only 1 query. 1 is free.sql and the other is the select statement.

the first one has 4 rows selected and second one has 7 rows selected.

Handy script to have

Using LMT with either Uniform Extent Size or Autoallocate, with autoextend on, is there any need to make sure that you have at least 10% free in a tablespace? Can it be a problem if a tablespace is "full"?

Cheers

Hello
I guess that this is for admins only. I am a user
and get the error
select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name 2 3 4 ;
from sys.dba_data_files
*
ERROR at line 3:
ORA-00942: table or view does not exist


I would like to monitor my personal work quota. Can you suggest how best to do this. I was told by my DBA that max # extents was exceded on an index and I would prefere to be able monitor this myself. Thanks for any help

Hi,

I'm using your script which is really very helpful.

How to change this script so that output will show only
tablespaces which is above 90% used..

Could you help me out in this


Regards,
atul

Tom,

1. When determining the total space allocated per tablespace on an instance, should we use BYTES or USER_BYTES in dba_data_files to arrive at total space allocated. Usually I find some differences between the two except for some tablespaces like SYSTEM. Why is the difference.
2. I used the following query to find the actual space used for each user in a database.
select owner,ROUND(sum(bytes)/(1024*1024),0)
from dba_segments
group by owner
Is the above query correct or is there any other way to find it. Are there any tools available that will show a clean report of the space consumed by each user in a database.
3. I am using the following query to find out the allocated, used, free space and pct (%) for each tablespace in a database.
select ts.tablespace_name "Tablespace Name",
ROUND(tot.Alloc/(1024*1024),1) "Alloc (MB)",
ROUND(tot.ubytes/(1024*1024),1) "Size (MB)",
ROUND(NVL(fet.bytes,0)/(1024*1024),1) "Free (MB)",
ROUND(tot.ubytes/(1024*1024),1) - ROUND(NVL(fet.bytes,0)/(1024*1024),1) "Used (MB)"
from (select tablespace_name from dba_tablespaces) ts,
(select tablespace_name,
sum(bytes) Alloc,
sum(user_bytes) ubytes
from dba_data_files
group by tablespace_name) tot,
(select tablespace_name,
sum(bytes) bytes
from dba_extents
group by tablespace_name) uet,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) fet
where ts.tablespace_name = tot.tablespace_name
and ts.tablespace_name = uet.tablespace_name(+)
and ts.tablespace_name = fet.tablespace_name(+)
order by ts.tablespace_name

Is there any better way to do it

Tom,

I saw the free.sql in your site and have the following question :
You use the DBA_FREE_SPACE to determine the free space and subtract the free space (DBA_FREE_SPACE) from the total space allocated (DBA_DATA_FILES) to get the used space.
The script you used above and other scripts that I have
seen before use this logic to determine used space.
Can't we use DBA_EXTENTS directly to determine the used
space in a tablespace? Will using DBA_EXTENTS directly
won't provide correct results?
Any tools available in Oracle to provide such info?
Which view can you use to see info about LMT TEMPORARY
tablespace. If a LMT TEMPORARY tablespace becomes full,
can we add space to it just like as for any other
tablespace

Tom,

What is the easiest method to calculate free space (Fragmentations) inside Tables/Indexes. Which is the result of the purging (Deleting) data inside Tables.

I have recently analyzed the objects.

Thanks

Dilip Patel

Tom,

I am using your query, but i have a doubt. 9ir1/9ir2

select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc/1024 "Total MB" ,
(kbytes_alloc-nvl(kbytes_free,0))/1024 "Total_Size_Used",
(nvl(kbytes_free,0)/1024) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
and a.tablespace_name like '%DATA_WIPRO%'

in the above query, there was a tablespace with 100% filled, but i didnt get that in the output. we are making an outer join, but how did we miss when we can see that tablespace in "b" ..(output from sys.dba_data_files table). i moved the (+) sign to other side, even then i didnt get.

why did it ignore? .. what change i need to do, to see even if there are no free space?

Thanks a lot

was it a temporary tablespace?   with tempfiles?

my latest greatest is:

ops$tkyte@ORA9IR2> -------------------------------------------------------
ops$tkyte@ORA9IR2> -- free.sql
ops$tkyte@ORA9IR2> --
ops$tkyte@ORA9IR2> -- This SQL Plus script lists freespace by tablespace
ops$tkyte@ORA9IR2> --------------------------------------------------------
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column dummy noprint
ops$tkyte@ORA9IR2> column       pct_used format 999.9       heading "%|Used"
ops$tkyte@ORA9IR2> column       name    format a19         heading "Tablespace Name"
ops$tkyte@ORA9IR2> column       Kbytes   format 999,999,999       heading "KBytes"
ops$tkyte@ORA9IR2> column       used    format 999,999,999      heading "Used"
ops$tkyte@ORA9IR2> column       free    format 999,999,999  heading "Free"
ops$tkyte@ORA9IR2> column       largest    format 999,999,999  heading "Largest"
ops$tkyte@ORA9IR2> column       max_size format 999,999,999 heading "MaxPoss|Kbytes"
ops$tkyte@ORA9IR2> column       pct_max_used format 999.9          heading "%|Max|Used"
ops$tkyte@ORA9IR2> break        on report
ops$tkyte@ORA9IR2> compute sum of kbytes on report
ops$tkyte@ORA9IR2> compute sum of free on report
ops$tkyte@ORA9IR2> compute sum of used on report
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select (select decode(extent_management,'LOCAL','*',' ') ||
  2                 decode(segment_space_management,'AUTO','a ','m ')
  3                from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
  4                           nvl(a.tablespace_name,'UNKOWN')) name,
  5             kbytes_alloc kbytes,
  6             kbytes_alloc-nvl(kbytes_free,0) used,
  7             nvl(kbytes_free,0) free,
  8             ((kbytes_alloc-nvl(kbytes_free,0))/
  9                                                    kbytes_alloc)*100 pct_used,
 10             nvl(largest,0) largest,
 11             nvl(kbytes_max,kbytes_alloc) Max_Size,
 12             decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 13  from ( select sum(bytes)/1024 Kbytes_free,
 14                            max(bytes)/1024 largest,
 15                            tablespace_name
 16             from  sys.dba_free_space
 17             group by tablespace_name ) a,
 18       ( select sum(bytes)/1024 Kbytes_alloc,
 19                            sum(maxbytes)/1024 Kbytes_max,
 20                            tablespace_name
 21             from sys.dba_data_files
 22             group by tablespace_name
 23             union all
 24        select sum(bytes)/1024 Kbytes_alloc,
 25                            sum(maxbytes)/1024 Kbytes_max,
 26                            tablespace_name
 27             from sys.dba_temp_files
 28             group by tablespace_name )b
 29  where a.tablespace_name (+) = b.tablespace_name
 30  order by &1
 31  /
 

No it wasnt the temp tablespace. I will check out with this new script.


Thanks as always!!

You do a sum on maxbytes, but in the case where bytes is larger than maxbytes, the percentage of Max Possible used could be over 100%. How about sum(greatest(maxbytes,bytes))?

Unless I am just missing something obvious to everyone else (happens).

Hi Tom, 

The script will not show temp tablespace value since it is not in dba_data_file (9204).

Thanks so much for your help.

Sean 

-------------------------------------------------------


SQL> @free
Enter value for 1: 1
old  19: order by &1
new  19: order by 1

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
INDXES             32,768,000   24,439,936    8,328,064   74.6    7,512,960
MED_INDXES         32,768,000   17,193,088   15,574,912   52.5    5,312,384
MED_USERS          32,768,000   24,085,632    8,682,368   73.5    2,520,064
SM_INDXES           3,072,000      929,920    2,142,080   30.3    2,141,120
SM_USERS           32,768,000   13,109,120   19,658,880   40.0    4,063,232
SYSTEM              1,638,400      725,632      912,768   44.3      258,048
TOOLS               2,150,400    1,232,000      918,400   57.3      917,440
UNDOTBS1           34,816,000    1,362,368   33,453,632    3.9    8,257,536
USERS             111,496,000   52,392,448   59,103,552   47.0    7,667,520
                 ------------ ------------ ------------

                                                             %
Tablespace Name        KBytes         Used         Free   Used      Largest
---------------- ------------ ------------ ------------ ------ ------------
sum               284,244,800  135,470,144  148,774,656

9 rows selected.

SQL> select distinct tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
INDXES
MED_INDXES
MED_USERS
SM_INDXES
SM_USERS
SYSTEM
TOOLS
UNDOTBS1
USERS

9 rows selected.

SQL> select name from v$tablespace;

Tablespace Name
----------------
SYSTEM
UNDOTBS1
TEMP
INDXES
TOOLS
USERS
MED_USERS
SM_USERS
MED_INDXES
SM_INDXES
TEMP_NEW

11 rows selected.
------------------------------------------------------------
 

Hi Tom,
I just ran your new script and got some things I did not expect or understand,

*WORK 32,768 128 32,640 .4 32,640 10,240 320.0

TEMP 7,440,384 199,712 7,240,672 2.7 1,048 2,097,152 354.8

*RBS 5,242,880 5,069,440 173,440 96.7 173,440 4,194,304 125.0

ps8sys > select extent_management,allocation_type from dba_tablespaces
2 where TABLESPACE_NAME in ('WORK','TEMP','RBS');

EXTENT_MAN ALLOCATIO
---------- ---------
LOCAL UNIFORM
DICTIONARY USER
LOCAL UNIFORM

How is it that the %maxused is showing more than 100% in the three cases here?
What sould I look for in here?

Regards,
RD.

Hi Tom,

The checking freespace query works fine, but not working in the cursor.  Thanks so much for your help.

Sean 

---------------------------
SQL> create or replace procedure sp_tablespace_usage
  2  is 
  3  
  4  cursor c_usage is select round((1-free/total)*100, 2) usage, a.tablespace_name
  5    from (select sum(bytes) total, tablespace_name
  6                from dba_data_files
  7               group by tablespace_name) a,
  8         (select sum(bytes) free, tablespace_name
  9                from dba_free_space
 10               group by tablespace_name) b
 11   where a.tablespace_name = b.tablespace_name;
 12  begin
 13     
 14   null;
 15    
 16  end;
 17  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE SP_TABLESPACE_USAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/19     PL/SQL: SQL Statement ignored
9/20     PL/SQL: ORA-00942: table or view does not exist
SQL> 
------------------------------------
 

free.sql is a great script to find out the status of the current space allocation.
I would like to get the same information on each tablespace, back in time, (let's say over 2 years) on monthly basis to get a trend analysis of space allocation . So that we could forecast how much space would be needed in future, based on the past data.
Thanks in advance

How can I achieve it myself?
I would appreciate if you show us with some example.
Thanks

Hi,
This script is really useful. It made my job easier.

Thanks !!!

Tom, I have developed a script to find free space etc., following is the script. Could you please give you comments

SELECT TABLESPACE_NAME,
SUM(BYTES) free_space,
sum(free_extents) free_extents,
max(next_extent) max_next_extent,
max(tbsp_type) tablespace_type,
max(allocated_bytes) allocated_bytes,
round(sum(bytes)/max(allocated_bytes)*100,1) percent_free
FROM (
SELECT A.TABLESPACE_NAME,
round(F.BYTES/A.next_extent)*A.next_extent bytes,
round(F.BYTES/A.next_extent) free_extents,
a.next_extent,
a.tbsp_type,
d.bytes allocated_bytes
FROM DBA_FREE_SPACE F,
(select /*+ rule no_unnext */ tablespace_name, sum(user_bytes) bytes from dba_data_files group by tablespace_name) d,
( select tablespace_name,
(case when next_extent > seg_next_extent then next_extent else seg_next_extent end) next_extent,
tbsp_type
from (
select /*+ no_unnest */ tablespace_name, next_extent, next_extent seg_next_extent, 'LU' tbsp_type
from dba_tablespaces t
where extent_management = 'LOCAL' and
allocation_type = 'UNIFORM' and
status!='READ ONLY' and
contents='PERMANENT'
union all
select /*+ no_unnest */ tablespace_name, next_extent,
(select max( nvl(next_extent*power(1+pct_increase/100,3),power(1024,2)))
from dba_segments
where tablespace_name = t.tablespace_name) seg_next_extent, 'OT' tbsp_type
from dba_tablespaces t
where not (extent_management = 'LOCAL' and allocation_type = 'UNIFORM' ) and
status!='READ ONLY' and
contents='PERMANENT'
)
) A
WHERE
A.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and
A.TABLESPACE_NAME = D.TABLESPACE_NAME
)
GROUP BY TABLESPACE_NAME
having sum(free_extents) < 6 or sum(bytes) < 128*power(10,2) or sum(bytes)/max(allocated_bytes)*100<.1

here I am looking for less than 6 extents free (of max ext size within tablespace) or freesize < 128mb or %free<.1
If there is pct_increase defined on an object, I try to use some size expansion (limit to 3rd extent down the road) as a size to look for free space. And, then if the free space chunk is accomodating at least max_next_extent then I consider that freespace worthwhile.

also, for uniform lmt there is one section and for
non(uniform+lmt) there is another section.

thanks for your help and comments

If you get chance could you give a word or two on hint problems. If you get chance .. I will look more into it myself first.
thanks again

Running free.sql tells me that in some of my large data/index tablespaces, I have (in one case) 42G free, but the largest is only 3.8G. This is on an LMT. When, if ever, might the "lost" 38.2G be used by the DB? My uniform size for LMT is 64M.

Just to provide more info this is on Oracle 9.2.0.4 on linux.

My belief when we first migrated to 9.2 from 7.3 was that LMT made it basically impossible to have fragmentation and unusable space since all extents were uniform size and so they would all fit together like nice building blocks.

Should I be concerned about that "largest" report then?

Points well taken.

regarding latest comments from Don Seiler,
if you converted from dictionary to lmt, then the extents are not necessarily uniform for the past data ... you can check if its USER type in dba_tablespaces and not uniform

Thanks for the feedback - as it's useful information to me!

Hi Tom,

I have the following requirement:

We have installed a network monitoring tool which helps us in automating some maintenance tasks that we do manually right now. As part of that, I wanted the tool to automatically run a script which will spool the results of freespace.sql to a file and then email me with that result file as an attachment. I want to divide this into two - routine check and error. The routine check will just email the full result file (This works fine). For the error check, I want the results to be spooled only if atleast one tablespace has %used > 85%. This works fine except under one situation. We have a few tables partitioned. So now consider that I have a table t1 which is partitioned as

Partition 1- Values less than 1000 to tablespace tbs1. (%used = 89%)
Partition 2- Values less than 2000 to tablespace tbs2. (%used = 90%)

Now I am currently in Partition 2 and hence I am only interested in tbs2. But if I run my freespace.sql to spool all tablespaces > 85% I will get both tbs1 and tbs2. Is there any way to say that spool results from currently used tablespaces alone? Please help.

Thanks.

Tom,

I am in the understanding that the used space obtained by subtracting dba_free_space.bytes from dba_data_files.bytes should be equal to the sum of dba_extents.bytes for a tablespace.

Is it not valid for undo tablespace (using dba_undo_extents) also? The figures are not matching for me.

Please see below:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as cn2_rptuser

SQL>
SQL> select mb_tot, nvl(mb_free,0), mb_tot - nvl(mb_free,0) as mb_used
2 from (select (select sum(bytes) / 1024 / 1024
3 from dba_data_files df
4 where df.tablespace_name = 'UNDOTBS2') mb_tot,
5 (select sum(bytes) / 1024 / 1024
6 from dba_free_space df
7 where df.tablespace_name = 'UNDOTBS2') mb_free
8 from dual);

MB_TOT NVL(MB_FREE,0) MB_USED
---------- -------------- ----------
3691.3125 0 3691.3125

SQL> select sum(bytes) / 1024/1024 as mb
2 from dba_undo_extents e
3 where tablespace_name = 'UNDOTBS2';

MB
----------
2278.6875

SQL> select sum(bytes) / 1024/1024 as mb
2 from dba_extents e
3 where tablespace_name = 'UNDOTBS2';

MB
----------
2278.6875

SQL> select mb_tot, nvl(mb_free,0), mb_tot - nvl(mb_free,0) as mb_used
2 from (select (select sum(bytes) / 1024 / 1024
3 from dba_data_files df
4 where df.tablespace_name = 'CN2DATA2') mb_tot,
5 (select sum(bytes) / 1024 / 1024
6 from dba_free_space df
7 where df.tablespace_name = 'CN2DATA2') mb_free
8 from dual);

MB_TOT NVL(MB_FREE,0) MB_USED
---------- -------------- ----------
22575 2234.6875 20340.3125

SQL> select sum(bytes) / 1024/1024 as mb
2 from dba_extents e
3 where tablespace_name = 'CN2DATA2';

MB
----------
20339.9375

SQL>

Is my understanding wrong?

Thanks and regards,

Albert Nelson A.

ops$tkyte%ORA10GR2> drop tablespace xxx;

Tablespace dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create tablespace xxx
  2  datafile size 512000
  3  extent management local
  4  uniform size 50k
  5  /

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sum(bytes), sum(bytes)+65536 from dba_free_space where tablespace_name = 'XXX';

SUM(BYTES) SUM(BYTES)+65536
---------- ----------------
    401408           466944

ops$tkyte%ORA10GR2> select sum(bytes) from dba_data_files where tablespace_name = 'XXX';

SUM(BYTES)
----------
    516096

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop tablespace xxx;

Tablespace dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create tablespace xxx
  2  datafile size 577536
  3  extent management local
  4  uniform size 50k
  5  /

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sum(bytes), sum(bytes)+65536 from dba_free_space where tablespace_name = 'XXX';

SUM(BYTES) SUM(BYTES)+65536
---------- ----------------
    516096           581632

ops$tkyte%ORA10GR2> select sum(bytes) from dba_data_files where tablespace_name = 'XXX';

SUM(BYTES)
----------
    581632

I have a question-related to add size for the database, for example, the current database size is 10GB, and I want to increase the size to 15 GB by adding 5 GB. from where we get the space? do we have free space in instance from SGA or where exactly?