tbsinfo.sql

---- 

---- Pending

---- Alarm used 90%.

----- Version: 1.2.2

----- Tables' Extents 

----- IO Stat (Reads and Write) per datafile
----- Created : 28/02/2014

----- Updated : 11/06/2014

----- Author: Eini Trujillo

set lin 140

set pages 1001

set verif off

set echo off

SET FEEDBACK off

set timing off

set time off

COL Used_Pct FORMAT A8 heading "Used Pct"

col file_name for a64

col segment_name for a28 heading  "Segment Name"

col partition_name FORMAT A30 heading "Partition Name"

col segment_type FORMAT A18 heading  "Segment Type"

col mon for a12 heading  "Year-Month"

col used  for 9,999,999 heading  "Used "

col QUOTA  for a15 heading  "Quota "

col owner for a12 heading  "Owner"

col USERNAME  for a24 heading  "User "

col size_mb for 9,999,999 heading  "Size MB"

col cnt_tables for 9,999 heading  "Number of|Tables "

col cnt_index for 9,999 heading  "Number of|Indexes "

col cnt_others for 9,999 heading  "Number of|The Other Segments "

column tm new_value file_time noprint

undefine parTABLESPACE

----- obtiene la fecha de la tabla

select to_char(sysdate, 'YYYYMMDDHH24MISS') tm from dual ;
prompt http://studioracle.blogspot.com/2014/02/summary-of-tablespace.html

ACCEPT parTABLESPACE PROMPT 'Tablespace Name :'

spool tbs_&&parTABLESPACE._&file_time..log

prompt --- Tablespaces

select a.TABLESPACE_NAME "Tablespace",round(a.Asignado,2) "Size MB",round(nvl(f.Libre,0)) "Free MB", round((a.Asignado - nvl(f.Libre,0)),2) "Used MB",

round((((a.Asignado - nvl(f.Libre,0))*100)/a.Asignado),2)  || ' %' Used_Pct

from (SELECT TABLESPACE_NAME, sum(bytes/1024/1024) Asignado FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) a,

(SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024)  Libre FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) f

where  a.TABLESPACE_NAME=f.TABLESPACE_NAME(+) AND a.TABLESPACE_NAME=upper('&&parTABLESPACE') AND f.TABLESPACE_NAME(+)=upper('&&parTABLESPACE');

select STATUS "Status",LOGGING "Logging",EXTENT_MANAGEMENT "Extent Managment",ALLOCATION_TYPE "Allocation Type",

SEGMENT_SPACE_MANAGEMENT "Segment Space Management",(NEXT_EXTENT/1024) "Next Extent KB",CONTENTS "Contents" ,BLOCK_SIZE "Block Size"

from dba_tablespaces

WHERE TABLESPACE_NAME = upper('&&parTABLESPACE');

prompt --- Datafiles

SET FEEDBACK ON

Select file_name, round(bytes/1024/1024) "Size MB", NVL(s.Libre_MB,0) "Free MB",  TO_CHAR(round(100-((NVL(s.Libre_MB,0)*100)/(bytes/1024/1024)))) || ' %' Used_Pct

from dba_data_files d, (select s.file_id, round(sum(bytes/1024/1024)) Libre_MB

from DBA_FREE_SPACE s

where TABLESPACE_NAME=upper('&&parTABLESPACE')

group by s.file_id) s

where d.file_id = s.file_id(+) and TABLESPACE_NAME=upper('&&parTABLESPACE')

order by file_name;

prompt --- Segments

select owner,segment_name ,partition_name,segment_type ,EXTENTS,(bytes/1024/1024) "Size MB"

from dba_segments where TABLESPACE_NAME=upper('&&parTABLESPACE')

ORDER BY BYTES;

SET FEEDBACK OFF

col value for 999,999,999,999,999,999

prompt --- Statistic

SELECT STATISTIC_NAME "Statistic Name",sum(VALUE) "Value"

FROM

   V$SEGMENT_STATISTICS c

WHERE

    TABLESPACE_NAME=upper('&&parTABLESPACE')

   group by STATISTIC_NAME

   order by 2 desc ; 

  

col name for a42 heading "Name"

col phyrds for 999,999,999,999 heading "Reads"

col phywrts for 999,999,999,999 heading  "Writes"

col phyblkrd for 999,999,999,999 heading  "Blocks | Read"

col phyblkwrt for 999,999,999,999 heading  "Blocks | Written"

col readtime  for 9,999.99 heading "AV | Reads/Time"

col writetime for 9,999.99 heading  "AV | Writes/Time"

prompt --- IO Stats for DB. Ordered by Write Time + Read Time

select df.name ,

fs.phyrds,fs.phyblkrd,

round((fs.readtim / decode(fs.phyrds,0,-1,fs.phyrds)),2) readtime,

fs.phywrts, fs.phyblkwrt , round((fs.writetim / decode(fs.phywrts,0,-1,fs.phywrts)),2) writetime

from v$datafile df, v$filestat fs

where df.file#=fs.file# and  df.ts# = (select ts# from v$tablespace where name='&&parTABLESPACE')

order by fs.writetim+readtim;  

  

prompt

prompt --- Monthly Summary (Tables and Indexes)

COLUMN DUMMY NOPRINT

BREAK ON DUMMY

COMPUTE SUM label 'Total' OF  Cnt_Tables Cnt_Index   ON DUMMY

    select null DUMMY, to_char( O.CREATED,'yyyy-mm') mon,

    count( decode (O.OBJECT_TYPE,'TABLE',1,'TABLE PARTITION',1,'TABLE SUBPARTITION',1))  Cnt_Tables,

    count( decode (O.OBJECT_TYPE,'INDEX',1,'INDEX PARTITION',1,'INDEX SUBPARTITION',1))  Cnt_Index

    from dba_segments s, dba_objects o

    where s.tablespace_name= upper('&&parTABLESPACE')

    and s.segment_name=o.object_name and s.owner=o.owner and nvl( O.SUBOBJECT_NAME,1)=nvl(S.PARTITION_NAME,1) and O.OBJECT_TYPE=S.SEGMENT_TYPE

    group by to_char( O.CREATED,'yyyy-mm');

    clear breaks

    CLEAR COMPUTES  

   

prompt --- Quotas

SELECT USERNAME,(bytes/1024/1024) used,decode(MAX_BYTES,-1,'UNLIMITED',to_char((MAX_BYTES/1024/1024),'9,999,999.00')) QUOTA

from dba_ts_quotas where TABLESPACE_NAME=upper('&&parTABLESPACE');   

 

  

undefine parTABLESPACE  

spool off