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
] 
Blog
Search
Help
Members
Register
Login
Home
