OraFAQ Forum: Server Administration » tablespace monitoring script

Home » RDBMS Server » Server Administration » tablespace monitoring script - used% should show 100%

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tablespace monitoring script - used% should show 100% [message #604009]

Sun, 22 December 2013 13:04

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Hi Team,

I need to deploy below tablespace monitoring script in live env. As per the requirement, if a tablespace reaches 100% and further if not able to allocate extent, it should show as 100% as PCT_USED in the query output. Please guide me to implement this. As of now, USERS tablespace is not having size to INSERT rows further. i am just assuming that since we don't have further space on this tablespace, it should show PCT_USED as 100% in the first query output, so that DBA can do resize the tablespace during work hours.

Please guide me, Is it possible to show 100% as PCT_USED ?

SQL> begin
  2     for i in 1..50 loop
  3     insert into table55 values(1,'kes');
  4     commit;
  5     end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table TEST_USER1.TABLE55 by 128 in tablespace USERS
ORA-06512: at line 3

--Tablespace script with filter >= 85%

SQL> select
  2          a.tablespace_name ,
  3          tbsize "TBSIZE(MB)" ,
  4          tbsize-tbfree "TBUSED(MB)",
  5          tbfree "TBFREE(MB)",
  6          ROUND(b.tbfree/a.tbsize*100,2)||' %' "PCT_FREE",
  7          100- ROUND(b.tbfree/a.tbsize*100,2)||' %' "PCT_USED"
  8  from
  9          ( select tablespace_name,sum(bytes)/1024/1024 tbsize
 10                  from dba_data_files
 11                  group by tablespace_name) a,
 12          ( select tablespace_name,sum(bytes)/1024/1024 tbfree,
 13                   max(bytes)/1024/1024 Largest
 14                  from dba_free_space
 15                  group by tablespace_name) b
 16  where a.tablespace_name=b.tablespace_name
 17  and  100-ROUND(b.tbfree/a.tbsize*100,2) >= 85
 18  order by 5  ;

TABLESPACE_NAME    TBSIZE(MB) TBUSED(MB) TBFREE(MB) PCT_FREE                                   PCT_USED
------------------ ---------- ---------- ---------- ------------------------------------------ --------
USERS                       7     6.3125      .6875 9.82 %                                     90.18 %

--Over all tablespace report without threshold value

SQL> select
  2          a.tablespace_name ,
  3          tbsize "TBSIZE(MB)" ,
  4          tbsize-tbfree "TBUSED(MB)",
  5          tbfree "TBFREE(MB)",
  6          ROUND(b.tbfree/a.tbsize*100,2)||' %' "PCT_FREE",
  7          100- ROUND(b.tbfree/a.tbsize*100,2)||' %' "PCT_USED"
  8  from
  9          ( select tablespace_name,sum(bytes)/1024/1024 tbsize
 10                  from dba_data_files
 11                  group by tablespace_name) a,
 12          ( select tablespace_name,sum(bytes)/1024/1024 tbfree,
 13                   max(bytes)/1024/1024 Largest
 14                  from dba_free_space
 15                  group by tablespace_name) b
 16  where a.tablespace_name=b.tablespace_name
 17  order by 5  ;

TABLESPACE_NAME    TBSIZE(MB) TBUSED(MB) TBFREE(MB) PCT_FREE                                   PCT_USED
------------------ ---------- ---------- ---------- ------------------------------------------ --------
SYSAUX                    400   329.8125    70.1875 17.55 %                                    82.45 %
SYSTEM                   1200   601.9375   598.0625 49.84 %                                    50.16 %
UNDO_T1                   200      44.25     155.75 77.88 %                                    22.12 %
USERS                       7     6.3125      .6875 9.82 %                                     90.18 %
TEST_TBS1                 188     2.5625   185.4375 98.64 %                                    1.36 %

5 rows selected.

[Updated on: Sun, 22 December 2013 13:06]

Report message to a moderator

Re: tablespace monitoring script - used% should show 100% [message #604010 is a reply to message #604009]

Sun, 22 December 2013 13:21

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Your query will not report full tablespaces, because they will have no rows in dba_free_space: you need to think about an outer join. Furthermore, you are not considering whether autoextend has been enabled for any datafiles.

Report message to a moderator

Re: tablespace monitoring script - used% should show 100% [message #604011 is a reply to message #604010]

Sun, 22 December 2013 18:36

kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI

Senior Member

Thank you very much John. Iam able to get my result now. yes, i had disabled autoextend off already.
Thanks again John..

Note: you can ignore the pipe symbol delimit. it is my project specific.

SQL> select
  2          '<|'||a.tablespace_name "Tablespace Name",
  3          '|'||tbsize "TBSIZE(MB)" ,
  4          '|'||nvl(tbfree,0) "TBFREE(MB)",
  5          '|'||nvl(ROUND(b.tbfree/a.tbsize*100,2),0)||' %' "PCT_FREE",
  6          '|'||(100- nvl(ROUND(b.tbfree/a.tbsize*100,2),0))||' %' ||'|>'  "PCT_USED"
  7  from
  8          ( select tablespace_name,sum(bytes)/1024/1024 tbsize
  9                  from dba_data_files
 10                  group by tablespace_name) a,
 11          ( select tablespace_name,nvl(round(sum(bytes)/1024/1024,2),0) tbfree
 12                  from dba_free_space
 13                  group by tablespace_name) b
 14  where b.tablespace_name(+)=a.tablespace_name
 15  and  100-nvl(ROUND(b.tbfree/a.tbsize*100,2),0) >= 85
 16  order by 100- nvl(ROUND(b.tbfree/a.tbsize*100,2),0) desc ;

Tablespace Name                  TBSIZE(MB) TBFREE(MB) PCT_FREE   PCT_USED
-------------------------------- ---------- ---------- ---------- ----------
<|TBS_BIG1_22DEC                 |4         |0         |0 %       |100 %|>
<|USERS                          |7         |.69       |9.86 %    |90.14 %|>

Report message to a moderator

Previous Topic:	Oracle 11g Installation (merged)
Next Topic:	Oracle configuration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 12:11:20 CST 2025