Tablespace usage

[kpate]

Is there any way one can monitor auto extend tablespace closely.Let say i have a tablespace users have an initial extent to 50M and next extent of 50M and max size to 2G.So is there any way to show correct % free space left .If my tablespace is having 50M of data then instead of showing 0% free (1.95G) remaining....

[OraDave]

COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a16 HEADING "Tablespace Name"
COLUMN total FORMAT 999,999,999 HEADING "Total"
COLUMN used FORMAT 999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
BREAK ON report
COMPUTE sum OF total 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 total,
kbytes_alloc-NVL(kbytes_free,0) used,
NVL(kbytes_free,0) free,
((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
FROM ( SELECT SUM(bytes)/1024 Kbytes_free,
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


hope this helps
you can add the table space in the where clause of the in line view

[dhar_kiran]

Is there any way one can monitor auto extend tablespace closely.Let say i have a tablespace users have an initial extent to 50M and next extent of 50M and max size to 2G.So is there any way to show correct % free space left .If my tablespace is having 50M of data then instead of showing 0% free (1.95G) remaining....

SQL> SELECT ((maxblocks* 8192)/(1024*1024*1024)) - (bytes/(1024*1024*1024)) "Free Size(GB)" from dba_data_files where tablespace_name ='USERS';

8192 is db_block_size, you can check it and replace it.
USERS is the tablespace of which u want to find free space. u can also replace it.

[kpate]

THAT STILL DOESN'T WORK.lET SAY I HAVE A TABLESPACE USERS WITH ONE DATAFILE FILE SIZE IS 30 M AND INCREMENT BY 30 M WITH MAX SIZE IS 2 GIG.SO IF I RUN ABOVE SCRIPTS I WILL GET BELOW RESULTS:

%
Tablespace Name Total Used Free Used
USERS 30,720 29,896 824 97.3

Iwould like to see free from max size of datafile i.e (2G-29.8M) = 1970M

[tamilselvan]

KPATE,
Post your query.

Tamil

[marist89]

  1  select
  2     df.file_id,
  3     tablespace_name,
  4     df.bytes/1024/1024 "current size(mb)",
  5     fs.free_b/1024/1024 "free current(mb)",
  6     (df.bytes - fs.free_b)/1024/1024 "current used(mb)",
  7     df.maxbytes/1024/1024 "max size(mb)",
  8     (df.maxbytes - df.bytes + fs.free_b)/1024/1024 "total free(mb)"
  9  from dba_Data_files df,
 10     (
 11        select
 12           file_id,
 13           sum(bytes) free_b
 14        from dba_free_space
 15        group by file_id) fs
 16* where fs.file_id = df.file_id
SQL> /

   FILE_ID TABLESPACE_NAME current size(mb) free current(mb) current used(mb) max size(mb) total fre
---------- --------------- ---------------- ---------------- ---------------- ------------ ---------
         1 SYSTEM                       440            3.625          436.375   32767.9844     32331.6094
         2 UNDOTBS1                      25          17.4375           7.5625   32767.9844     32760.4219
         3 SYSAUX                       220          12.5625         207.4375   32767.9844     32560.5469
         4 USERS                          5           1.6875           3.3125   32767.9844     32764.6719
         5 EXAMPLE                      150          70.0625          79.9375   32767.9844     32688.0469

[tamilselvan]

marist89,

4 USERS 5 1.6875 3.3125 32767.9844 32764.6719

In a 5 MB tablespace, you got 32764 MB free space.

How is it possible?

Tamil

[marist89]

because my autoexend maxsize is around 32767M.

Out of my 5M in the current datafile, 1.6M is free and 3.4M is used. My "potential free" space is my autoextend maxsize - the current datafile size + the free space that is currently in the datafile (actually, I could have just done maxsize - used for potentially free).