Tablespace Alert

 TableSpace Alert

select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME like 'FACT_ZONE_INTUSG%';

A.)  Check Table space Usage

-- Oracle Query ---

SELECT m.tablespace_name, m.used_percent, (m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free 
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p 
WHERE p.name='statistics_level' 
and p.value!='BASIC' 
AND t.contents NOT IN ('TEMPORARY', 'UNDO') 
AND t.tablespace_name = m.tablespace_name

 select USED_SPACE from DBA_TABLESPACE_USAGE_METRICS where TABLESPACE_NAME='FACT_ZONE_INTUSG_DAT'; ouput in block -- show parameter block size

--------Table space usage--------

SELECT ts.tablespace_name, "File Count",

TRUNC("SIZE(MB)", 2) "Size(MB)",

TRUNC(fr."FREE(MB)", 2) "Free(MB)",

TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",

df."MAX_EXT" "Max Ext(MB)",

(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"

FROM (SELECT tablespace_name,

SUM (bytes) / (1024 * 1024) "FREE(MB)"

FROM dba_free_space

GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)

"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+)

ORDER BY ts.tablespace_name;

__________________________________________

set linesize 200

col name format a20

select (select tablespace_name

from dba_tablespaces

where tablespace_name = b.tablespace_name

) name

,round(kbytes_alloc/1024, 2) mbytes

,round((kbytes_alloc-nvl(kbytes_free,0))/1024, 2) used

,round(nvl(kbytes_free,0)/1024, 2) free

,round(((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100, 2) pct_used

,round(nvl(largest,0)/1024, 2) largest

,round(nvl(kbytes_max,kbytes_alloc)/1024, 2) max_size

,round(decode(kbytes_max,0,0,((kbytes_alloc-nvl(kbytes_free,0))/kbytes_max)*100),2) pct_max_used

,(select extent_management

from dba_tablespaces

where tablespace_name = b.tablespace_name) extent_management

,(select segment_space_management

from dba_tablespaces

where tablespace_name = b.tablespace_name) segment_space_management

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 2;

___________________________________________________

set pages 49999 lin 120

col tablespace_name for a32 tru

col "Total GB"  for 999,999.9

col "GB Used"   for 999,999.9

col "GB Free"   for 99,999.9

col "Pct Free"  for 999.9

col "Pct Used"  for 999.9

comp sum of "Total GB"  on report

comp sum of "GB Used"   on report

comp sum of "GB Free"   on report

break on report

Select A.Tablespace_Name, B.Total/1024/1024/1024 "Total GB",

       (B.Total-a.Total_Free)/1024/1024/1024 "GB Used",

       A.Total_Free/1024/1024/1024 "GB Free",

       (A.Total_Free/B.Total) * 100 "Pct Free",

       ((B.Total-A.Total_Free)/B.Total) * 100 "Pct Used"

  From (Select Tablespace_Name, Sum(Bytes) Total_Free

          From Sys.Dba_Free_Space

         Group By Tablespace_Name     ) A

     , (Select Tablespace_Name, Sum(Bytes) Total

          From Sys.Dba_Data_Files

         Group By Tablespace_Name     ) B

Where A.Tablespace_Name LIKE '%KCIDW_DATA%'

  And A.Tablespace_Name = B.Tablespace_Name

Order By 1

/

B.)   Taking care of TS Alert

1.) select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 90; not working in 9i twistdb

     select tablespace_name,use from dba_hist_tbspc_space_usage where used_percent > 90;

2.) select FILE_NAME,file_id,TABLESPACE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='DATA';    - datafiles

     select FILE_NAME,file_id,TABLESPACE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from dba_temp_files where TABLESPACE_NAME='DATA';    -tempfiles

               ALTER TABLESPACE TEMP01 ADD TEMPFILE '/oradata/traxjjp/temp02.dbf' SIZE 5G;             --temp ts

              ALTER TABLESPACE IONPROGRESS_DATA ADD DATAFILE '/DATA/RTMDEV/DATAFILE/ionprogress_data03.dbf' size 15G; --  data ts

              alter database datafile '/var/opt/instrprddb3/oradata/INSTRPRD/dbf/perfstat_03.dbf' resize 8G;                                   --Resize datafile  if space is available

             ALTER DATABASE DATAFILE '/DATA/RTMDEV/DATAFILE/ionprogress_data.2687.923135383' AUTOEXTEND OFF;               -- Add a new datafile

             ALTER DATABASE DATAFILE '/DATA/RTMDEV/DATAFILE/ionprogress_data03.dbf' AUTOEXTEND ON maxsize 24G;              - Add a new datafile with autoextend on and limit max size