Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jane.

Asked: March 30, 2001 - 11:35 pm UTC

Last updated: February 06, 2019 - 12:44 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

I do not have DBA background but I recently inherited an Oracle database on NT that's extensively used for testing. While checking the tablespaces, I noticed that system ts is only 0.01% free. What does this mean and what should I do to have more free % on this ts. Any help is appreciated.

Thanks in advance

and Tom said...

Well, it might be 0.01% full but it might also be autoextensible. Use the following script to see whats really there:

--------------------------------------------------------
-- free.sql
-- usage @free <orderbycolumn>
-- @free 1
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------

column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a16 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
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,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/


for example on my system it shows: Max
Max pct
Tablespa KBytes Used Free Used Largest Kbytes Used
SYSTEM 201,728 200,960 768 99.6 704 33,554,416 .6

shows that my system tablespace is currently 201m and has 0.4% fre HOWEVER it can grow to 33gig and hence has only used 0.6% of its potential.


If you find that you really have 0.01% free, you might consider adding more space by resizing or adding another datafile to SYSTEM.

See the

ALTER DATABASE DATAFILE 'filename' resize command or
ALTER TABLESPACE ADD DATAFILE ... command...





Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pauline, April 01, 2001 - 5:16 pm UTC

The name of the view 'dba_temp_files' in the script should be 'dba_data_files', right?

Datafile Size

Venkateswra Reddy, February 28, 2002 - 8:01 am UTC

Dear Tom

Today I am facing new problem ( I dint face it so far ). Two weeks back through EM I am resizing my datafile size from 80 MB TO 8000MB( This has been done by mistake). While creating datafile system shown outof space. So untile that time my datafile has been created with 750 MB,(Maximum size on the disk). After that again resized to 200 mb. Today it has while creating one table from other user table. Disk out of space came.

My doubt is I am sure about that what this datafile contains not more than 85mb. Because I have exportated data from my existing user ( HRMS, THIS USER CONTAINS AROUND 80MB DATA). So there is no chance beyond 85mb of data in my new datafile.

While resing to again 250 mb of datafile .System is showing data contains morethan what you have specified. It is showing data contgain around 800MB( But it is not correct).

I would like to resinze datafile If not I want to drop the datafile. But I want that data what is their in this datafile.Can I export data to another user or what I can do. Even I have taken export DMP file size is only 800KB only.

It is very very urgent.

Thanks in advance.

Reddy

Tom Kyte
February 28, 2002 - 10:23 am UTC

space is files is allocated by extents. It is quite possible that the file you have (the 8000meg on) cannot be shrunk by even a single byte -- even if it only contains 1meg of data.

Suppose the following is a "map" of the file from byte 0 to 8000mb. X's are allocated extents of 1meg, .'s are free space:

.....X........................X.........................X........................
0 4000mb 6000mb 8000mb

In that file, which holds 3meg of data, the smallest we can make that file is 6000mb because of that extent that is way out there (search this site for maxshrink, a script that will tell you the min size each file in your system can be shrunk to).


You need to move those objects OUT of that file (create a new tablespace, alter table t move new_tablespace), shrink the file, then move them back and drop that scratch tablespace. Remember to rebuild the indexes after the last move back.

Or, just move them, drop this tablespace and files if you like.

How about move back to it's own tablespace

Lisa, February 28, 2002 - 11:57 am UTC

In Oracle 8.1.6 and up, we can also move the objects back to it's own tablespace dynamically while user is accessing the application and updating the tables.
Right?

Tom Kyte
February 28, 2002 - 1:13 pm UTC

If they are index organized tables -- yes. You can rebuild these online.

If they are "heap" (normal) tables -- no, you cannot modify them during the move.

In 9i there is the ability to move the object whilst people read and WRITE it (online everything)

Refer to 'moving tables across tablespaces'

Lisa, February 28, 2002 - 3:59 pm UTC

I used the script 'moveall' to build the script to move both tables and indexes from current tablespace A to current tablespace A.
The script run with no errors (use oracle8.1.6). I checked the tablespace map, before the extents spread all over the 1 gig bytes tablespace, after the move/rebuild objects, I am able to resize the tablespace to half of the size (500M, before it was only able to shrink to 900M).
In this case both the table/ indexes are in tablespace A, when I run the script, I specified new tablespace is A also.

But your comment said only IOT tables are able to do this online, the regular tables are not, could you explain why?
and what will happen if the regular table did with this method ?

Tom Kyte
February 28, 2002 - 4:31 pm UTC

You can move the tables but during the move, no updates allowed. The move is not "online" with regards to the table.

You can rebuild an IOT ONLINE (and hence move it). that allows non-stop DML during the move.

SPACE IN SYSTEM TABLESPACE

A reader, August 20, 2002 - 10:29 pm UTC

The script that you provided to calculate used% etc for tablespaces..gave me this on mine:
SYSTEM 270,336 256,784 13,552 95.0 13,552 33,554,416 .8


0.8% used of the total...so it's O.K.

There is another script that I used to see other owners besides sys and system that have objects in SYSTEM TABLE SPACE:

select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');
it gave me some other users:
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------
OUTLN OL$
OUTLN OL$HINTS
AURORA$JIS$UTILITY$ AURORA$IIOP$SYSTEM$PROPERTIES
AURORA$JIS$UTILITY$ SNS$BINDINGS$
AURORA$JIS$UTILITY$ SNS$INODE$
AURORA$JIS$UTILITY$ SNS$ATTRIBUTES$
AURORA$JIS$UTILITY$ SNS$REFADDR$
AURORA$JIS$UTILITY$ SNS$PERMISSIONS$
AURORA$JIS$UTILITY$ SNS$SHARED$OBJ$
AURORA$JIS$UTILITY$ JAVA$HTTP$REALM$PRINCIPAL$
AURORA$JIS$UTILITY$ JAVA$HTTP$REALM$ID$
AURORA$JIS$UTILITY$ JAVA$HTTP$REALM$GROUP$
AURORA$JIS$UTILITY$ JAVA$HTTP$REALM$POLICY$
AURORA$JIS$UTILITY$ JAVA$HTTP$REALM$MAPPING$
AURORA$JIS$UTILITY$ JAVA$HTTP$LOG$
OSE$HTTP$ADMIN HTTP$LOG$
OSE$HTTP$ADMIN EVENT$LOG
OSE$HTTP$ADMIN ERROR$LOG
AURORA$JIS$UTILITY$ JAVA$HTTP$DEPLOYMENT$DIGEST$
OUTLN OL$NAME
OUTLN OL$SIGNATURE
OUTLN OL$HNT_NUM
AURORA$JIS$UTILITY$ KEY_C
AURORA$JIS$UTILITY$ SYS_C001012
AURORA$JIS$UTILITY$ SNS$NODE_INDEX
AURORA$JIS$UTILITY$ SYS_IL0000022418C00003$$
AURORA$JIS$UTILITY$ SYS_C001013
AURORA$JIS$UTILITY$ SNS$REFADDR_INDEX
AURORA$JIS$UTILITY$ SNS$PERM_INDEX
AURORA$JIS$UTILITY$ SNS$SHARED$OBJ_INDEX
AURORA$JIS$UTILITY$ HTTP_PRINCIPAL_CON
AURORA$JIS$UTILITY$ HTTP_ID_CON
AURORA$JIS$UTILITY$ HTTP_GROUP_CON
AURORA$JIS$UTILITY$ HTTP_POLICY_CON
AURORA$JIS$UTILITY$ P_MAP_CON
AURORA$JIS$UTILITY$ SYS_LOB0000022418C00003$$

36 rows selected.

Question:
is it O.K. for these to be there.....

Thanks a bunch for being there.....


Tom Kyte
August 21, 2002 - 7:37 am UTC

Yes, it is perfectly OK for those to be there.

write a procedure to warn free space usage

an, August 29, 2002 - 11:07 am UTC

Hi Tom:

I run your script free.sql daily to check our database usage, it's very helpful. thank you very much.
now I am thinking how I can adopt the free.sql script and write a proceduce that should
(1) calculate tablespace usage in megabytes, what the remainign percent free is in that tablespace, and the total available mb (used & free).
(2) the collected data should be stored in a table SPACE_USAGE. and should contain at minimum the following the tablespace name, date/time, mbused, percent free, and total mb available.
(3) if a tablespace drops below <= 20% than it should write a message out to the WARN.log locatied in the adump directory of that database on Unix.

would you please help me how I can write the procedure and pkg about it?
thank you very much as always,
an

Tom Kyte
August 29, 2002 - 8:09 pm UTC

Just add trunc(sysdate) to my free.sql (as a column).

create table that holds that output.

then write a procedure that:


inserts into table select * from free.sql;
for x in ( select * from table where used > 80 )
loop
utl_file -- to write to the file
end loop



use dbms_job to schedule that every day.

Free tablespace

Abdul Wahab, May 24, 2003 - 2:14 am UTC

Dear Tom,
Thanks a lot for the script you wrote for finding out the freespace. It has been very useful to me to analyze the tablespaces in my database.
Thanks again for being there.


Some datafiles have autoextents, some don't

Steve, August 30, 2006 - 5:38 pm UTC

In one of our databases (which I just inherited) the free.sql creates a usage percent of 135%:
Thank you for your outstanding site, I've learned a lot here.

When I run the "free.sql", I get a max percent that is incorrect:

% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
---------------- ------------ ------------ ------------ ------ ------------ ------------ ------
SYSTEM 308,224 226,816 81,408 73.6 80,832 1,332,224 23.1
UNDO 23,666,688 1,573,888 22,092,800 6.7 2,044,864 30,732,288 77.0
USERS 3,382,272 1,219,456 2,162,816 36.1 735,104 0 .0
LRG_DATA 612,965,376 612,965,376 0 100.0 0 424,072,192 144.5
LRG_INDEX 78,940,160 73,172,992 5,767,168 92.7 655,360 106,989,568 73.8
DATA01 85,206,016 84,913,664 292,352 99.7 24,576 92,169,216 92.4
INDEX01 54,178,816 52,534,656 1,644,160 97.0 95,168 71,689,216 75.6
TEMP 4,610,048 4,610,048 0 100.0 0 0 .0

The problem is the max used % is 144.5 for LRG_DATA. I think this is caused by the
autoextensibles having both YES and NO in the datafiles.

====================
select tablespace_name, autoextensible,count(*)
from dba_data_files
group by tablespace_name, autoextensible;

TABLESPACE_NAME AUT COUNT(*)
----------------- --- ----------
SYSTEM YES 1
UNDO YES 12
USERS NO 3
LRG_DATA NO 75
LRG_DATA YES 161
LRG_INDEX YES 34
DATA01 YES 9
INDEX01 YES 9

This is the adjustmented in the query that I made:
-------
Was
(kbytes_alloc-nvl(kbytes_free,0))/kbytes_max*100 pct_max_used
...
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
-------
Now
decode(mbytes_max(0,0,(mbytes_alloc-nvl(mbytes_free,0)))/mbytes_max*100 pct_max_used
...
( select sum(bytes)/1024 Kbytes_alloc,
sum(decode(autoextensible,'YES',maxbytes,bytes))/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
--------
Query is now:
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode(kbytes_max,0,0,(kbytes_alloc-nvl(kbytes_free,0)))/kbytes_max*100 pct_max_used
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,
sum(decode(autoextensible,'YES',maxbytes,bytes))/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
----------

results are:

% MaxPoss Max
Tablespace Name KBYTES Used Free Used Largest Mbytes Used
----------------- ---------- ------------ ------------ ------ ------------ ------------ ------
DATA01 85718016 85,660,544 57,472 99.9 41,920 92,169,216 92.9
INDEX01 54895616 54,814,720 80,896 99.9 29,632 71,689,216 76.5
LRG_DATA 616111104 616,111,104 0 100.0 0 669,244,416 92.1
LRG_INDEX 78940160 74,614,784 4,325,376 94.5 655,360 106,989,568 69.7
SYSTEM 308224 226,816 81,408 73.6 80,832 1,332,224 17.0
TOOLS 2256896 209,664 2,047,232 9.3 334,720 2,256,896 9.3
UNDO 23666688 4,396,160 19,270,528 18.6 2,232,256 30,732,288 14.3
USERS 3382272 1,228,032 2,154,240 36.3 735,104 3,382,272 36.3


Is the above change in the query correct?


Tom Kyte
August 30, 2006 - 6:01 pm UTC 

you have the max autoextend size of a file set smaller than the actual file size is currently, that is all.


Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a BIG_TABLE             204,800      140,352       64,448   68.5       64,448   33,554,416     .6
*a EXAMPLE               102,400       69,888       32,512   68.3       32,064   33,554,416     .3
*a SYSAUX                337,920      321,792       16,128   95.2        8,128   33,554,416    1.0
*a USERS                 152,320       11,392      140,928    7.5      138,752   33,554,416     .5
*m MANUAL                102,400           64      102,336     .1      102,336   33,554,416     .3
*m SYSTEM                512,000      505,216        6,784   98.7        6,080   33,554,416    1.5
*m TEMP                   26,624       26,624            0  100.0            0   33,554,416     .1
*m UNDOTBS1              517,120        8,896      508,224    1.7      501,696   33,554,416    1.5
                    ------------ ------------ ------------
sum                    1,955,584    1,084,224      871,360

8 rows selected.

ops$tkyte%ORA10GR2> column file_name new_val f

ops$tkyte%ORA10GR2> select file_name from dba_data_files where tablespace_name = 'USERS';

FILE_NAME
------------------------------
/home/ora10gr2/oradata/ora10gr
2/users01.dbf


ops$tkyte%ORA10GR2> alter database datafile '&f' autoextend on maxsize 100m;
old   1: alter database datafile '&f' autoextend on maxsize 100m
new   1: alter database datafile '/home/ora10gr2/oradata/ora10gr2/users01.dbf' autoextend on maxsize 100m

Database altered.

ops$tkyte%ORA10GR2> @free 1
Wrote file /tmp/xtmpx.sql
old  30: order by &1
new  30: order by 1

                                                                                                 %
                                                                %                   MaxPoss    Max
Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a BIG_TABLE             204,800      140,352       64,448   68.5       64,448   33,554,416     .6
*a EXAMPLE               102,400       69,888       32,512   68.3       32,064   33,554,416     .3
*a SYSAUX                337,920      321,792       16,128   95.2        8,128   33,554,416    1.0
*a USERS                 152,320       11,392      140,928    7.5      138,752      102,400  148.8
*m MANUAL                102,400           64      102,336     .1      102,336   33,554,416     .3
*m SYSTEM                512,000      505,216        6,784   98.7        6,080   33,554,416    1.5
*m TEMP                   26,624       26,624            0  100.0            0   33,554,416     .1
*m UNDOTBS1              517,120        8,896      508,224    1.7      501,696   33,554,416    1.5
                    ------------ ------------ ------------
sum                    1,955,584    1,084,224      871,360

8 rows selected.


ops$tkyte%ORA10GR2> alter database datafile '&f' autoextend on maxsize 31g;
old   1: alter database datafile '&f' autoextend on maxsize 31g
new   1: alter database datafile '/home/ora10gr2/oradata/ora10gr2/users01.dbf' autoextend on maxsize 31g

Database altered.

ops$tkyte%ORA10GR2> @free 1
Wrote file /tmp/xtmpx.sql
old  30: order by &1
new  30: order by 1

                                                                                                 %
                                                                %                   MaxPoss    Max
Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a BIG_TABLE             204,800      140,352       64,448   68.5       64,448   33,554,416     .6
*a EXAMPLE               102,400       69,888       32,512   68.3       32,064   33,554,416     .3
*a SYSAUX                337,920      321,792       16,128   95.2        8,128   33,554,416    1.0
*a USERS                 152,320       11,392      140,928    7.5      138,752   32,505,856     .5
*m MANUAL                102,400           64      102,336     .1      102,336   33,554,416     .3
*m SYSTEM                512,000      505,216        6,784   98.7        6,080   33,554,416    1.5
*m TEMP                   26,624       26,624            0  100.0            0   33,554,416     .1
*m UNDOTBS1              517,120        8,896      508,224    1.7      501,696   33,554,416    1.5
                    ------------ ------------ ------------
sum                    1,955,584    1,084,224      871,360

8 rows selected.

A reader, August 30, 2006 - 11:51 pm UTC

Tom,

If the file size is already greater than 100m then shouldn't
this command fail or complain about filesize already bigger etc etc..

>>alter database datafile >>'/home/ora10gr2/oradata/ora10gr2/users01.dbf'
>>autoextend on maxsize 100m.

TIA.


Tom Kyte
August 31, 2006 - 9:09 am UTC

nope, it shouldn't and it didn't.

it is only used by autoextend.
autoextend will look at it and say "hey, file is beyond maxsize, no autoextend for you"



Mix of autoextensible in a tablespace

Steve, August 31, 2006 - 5:23 pm UTC 

Thank you for your quick response.

The following query should find those datafiles which have 
a maxbyte count less then the bytes with autoextensible=YES

SQL> select file_name 
     from dba_data_files 
     where maxbytes < bytes 
     and autoextensible = 'YES'
SQL> /

no rows selected

The problem is with the files that have autoextensible=NO,
these datafiles have a value of zero for maxbytes.

  1  select autoextensible,
  2         sum(bytes)/1024 Kbytes_alloc,
  3         sum(maxbytes)/1024 Kbytes_max
  4    from sys.dba_data_files
  5   where tablespace_name = 'LRG_DATA'
  6*  group by autoextensible

AUT KBYTES_ALLOC KBYTES_MAX
--- ------------ ----------
NO     245172224          0
YES    371201024  424072192
       ---------  ---------
       616373248  424072192

The number I need is 245172224 + 424072192 for the max bytes. Currently, only the 424072192 value is used for the max bytes.

Do you recommend changing all datafiles in this tablespace that have autoextensible=NO to YES and set the max size to the current size. Can I change the max size to the current size and not change autoextensible?

Do you think a standard of not allowing a mix of autoextensible in any tablespace would be a good idea?

Tom Kyte
August 31, 2006 - 7:08 pm UTC

why? what is your *goal*. Thats the only thing that can get us to an answer for you. Why are you doing this, what is the purpose.

max kbytes

Roderick, August 31, 2006 - 8:58 pm UTC

For example, if your purpose is to make sure you have enough physical disk space to hold the maximum potential size of all your datafiles, then you could just rewrite your SQL statement (rather than alter your datafiles).

e.g.
select sum(decode(autoextensible, 'YES', maxblocks, blocks))
from dba_data_files;

I do not want "Max pct Used" over 100%.

Steve, September 01, 2006 - 2:09 pm UTC 

My goal is very simple. When I run the free.sql 
I do not want "Max pct Used" over 100%. 
It is currently 144% for LRG_DATA using free.sql. 

As you said the problem is
"you have the max autoextend size of a file set smaller than the actual file size". I agree with you.

In my case this is caused by datafiles that have autoextensible=NO. 
The free.sql computes the max autoextend size (based on the maxbytes value) for all datafiles in a tablespace. The value of maxbytes is zero when autoextensible=NO.

SQL> CREATE TABLESPACE LRG_DATA DATAFILE 
       '/db/u06/oradata/lrg_data_001.dbf' SIZE 8M AUTOEXTEND OFF,
       '/db/u06/oradata/lrg_data_002.dbf' SIZE 1M AUTOEXTEND ON maxsize 4m
     LOGGING
     ONLINE
     PERMANENT
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
     BLOCKSIZE 8K
     SEGMENT SPACE MANAGEMENT MANUAL;

Tablespace created.

SQL> create table t (data_01 varchar2(100)) tablespace lrg_data;

Table created.

SQL> insert into t (select username from dba_users);

38 rows created.

@free 1

                                                             %                   MaxPoss    Max
Tablespace Name        KBytes         Used         Free   Used      Largest       Kbytes   Used
---------------- ------------ ------------ ------------ ------ ------------ ------------ ------
LRG_DATA                9,216          384        8,832    4.2        8,064        4,096  225.0

A value of 225% for "Max Used" is incorrect.

Tom Kyte
September 01, 2006 - 3:29 pm UTC

ok, if you would like to fix this cosmetic situation - just adjust the maxsize to the current size of the file OR modify the script to always return 100% if autoextend it off.

maxsize with "autoextend off"

A reader, October 28, 2006 - 4:38 am UTC

Example if size of my datafile is 1Gb and "autoextend on" is enabled on datafile with maxsize 4GB. And later I do alter database datafile 'file' autoextend off.

Will datafile 'file' will grow from 1GB if maxsize is already set to 4GB even after with autoextend is off.

Tom Kyte
October 28, 2006 - 10:39 am UTC

if autoextend is off - the file is done growing, it will not grow, you disabled the growth.

Here's my version that may satisfy some of these other requests

Stephan, November 18, 2006 - 10:32 pm UTC

SELECT a.tablespace_name,
d.maxbytes max_size,
d.COUNT number_of_files,
d.currentbytes current_size,
nvl(b.free, 0) free_space,
d.currentbytes -nvl(b.free, 0) used_size,
CASE
WHEN d.currentbytes > d.maxbytes THEN
100 -ROUND(((a.avail -nvl(b.free, 0)) / d.currentbytes) * 100, 2)
ELSE
100 -ROUND(((a.avail -nvl(b.free, 0)) / d.maxbytes) * 100, 2)
END percent_free
FROM
(SELECT tablespace_name,
SUM(COUNT) COUNT,
SUM(ROUND(ma / 1048576, 2)) maxbytes,
SUM(ROUND(mc / 1048576, 2)) currentbytes
FROM
(SELECT tablespace_name,
SUM(bytes) ma,
SUM(bytes) mc,
COUNT(file_name) COUNT
FROM dba_data_files
WHERE maxbytes = 0
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
SUM(maxbytes) ma,
SUM(bytes) mc,
COUNT(file_name) COUNT
FROM dba_data_files
WHERE maxbytes <> 0
GROUP BY tablespace_name)
GROUP BY tablespace_name,
COUNT)
d,
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576, 2) avail
FROM dba_data_files
GROUP BY tablespace_name)
a,
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576, 2) free
FROM dba_free_space
GROUP BY tablespace_name)
b
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = d.tablespace_name
--AND ROUND(((a.avail -b.free) / d.maxbytes) * 100, 2) > 90
/

It gives me results like:
TS MAXSIZE #OF FILE FREE USED %FREE
FILES SIZE SPACE SIZE
TOOLS 3800 2 167 0 167 95.61
SYSTEM 1900 1 1000 73.75 926.25 51.25
CWMLITE 1900 1 15 .19 14.81 99.22
EXAMPLE 1900 1 425 0 425 77.63
FINDATA 3800 2 400 180 220 94.21
FININDX 3800 2 600 136 464 87.79

It's been able to handle every test I've thrown at it for files autoextensible and not; files larger than maxsize; combinations of those

SYSTEM Tablespace's datafile shows 0.2% free

Dipika Poraniya, January 28, 2019 - 6:44 am UTC

Hello experts,

In my oracle 11g database,system tablespace shows .2% free in size,sysaux shows 3.7% free in size and Temp is almost full.system and sysaux both having Autoextend is on and maxbytes set as below:


NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
SYSAUX 34255872 29331328 4924544 85.6242339999402 3671040 67108832 51.0452513910539
SYSTEM 7736320 7718656 17664 99.7716743878226 10240 33554416 23.0560412674147
TEMP 33554416 33554416 0 100 0 33554416 100

1)Do i need to increase datafiles or set it's maxbytes to unlimited?
2)In other database maxbytes is 32GB,autoextend is also on and datafile shows 0.3% free in this case it will manage space automatically or i manually need to add datafile to it's system and sysaux tablespace?

Thanks in advance.

Connor McDonald
January 28, 2019 - 3:30 pm UTC

Can you re-post that with code tags so we can read it

SYSTEM Tablespace's datafile shows 0.2% free

Dipika Poraniya, January 29, 2019 - 5:10 am UTC

I have mentioned output of given "free.sql" script in this post.

NAME KBYTES USED FREE PCT_USED LARGEST MAX_SIZE PCT_MAX_USED
SYSAUX 34255872 29354688 4901184 85.692426 3671040 67108832 51.0452513
SYSTEM 7736320 7718656 17664 99.771674387 10240 33554416 23.056041
TEMP 33554416 33554416 0 100 0 33554416 100

Thank you.
Connor McDonald
February 06, 2019 - 12:44 am UTC

Yes but we can't *read* it. Thats *why* we have the code tags available for you

SQL> select * from scott.dept;

DEPTNO DNAME LOC
---------- -------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

versus

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.