column timecol new_value timestamp

select to_char(sysdate,'DDMMYY_HHMISS') timecol

from dual;

Rem **********Start ENV Setting *********

set feedback off

set line 150

col SEGMENT_NAME format a15

col HOST_NAME format a20

COLUMN free_space_mb format 999999.90

COLUMN allocated_mb format 999999.90

COLUMN used_mb format 999999.90

col FILE_NAME format a40

col owner for a10

SET HEADING OFF

col usn format 99

col SEGMENT_NAME format a16

col TABLESPACE_NAME format a16

col STATUS format a16

col FILE_NAME format a51

column Sname form a40 heading 'Object Name'

column Stype form a15 heading 'Type'

column Size form 9,999 heading 'Size'

column Next form 99,999 heading 'Next'

column Tname form a15 heading 'TsName'

col name format a25

col value format a25

col username format a11

Rem **********End ENV Setting *********

spool output_&&timestamp

PROMPT *------------------------------------------------------------------------------------------*

PROMPT NAME OF THE DATABASE

PROMPT *------------------------------------------------------------------------------------------*

SELECT 'DATABASE NAME : '|| NAME, 'CREATED DATE : ' ||TO_CHAR(CREATED,'DD-MON-YYYY'),

'LOG MODE : ' ||LOG_MODE FROM V$DATABASE;

SELECT 'HOST NAME : '|| HOST_NAME|| ' VERSION : '|| VERSION ||' STARTUP TIME : '|| STARTUP_TIME FROM V$INSTANCE ;

prompt **********************************

prompt Current User Executed this script

prompt **********************************

show user

prompt

SET HEADING ON

prompt ************************

prompt Database UP Time

prompt ************************

select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"

from sys.v_$session

where sid=1 /* this is PMON */;

prompt

prompt ********************************************************

prompt Show database growth in Meg per month for the last year

prompt ********************************************************

select to_char(creation_time, 'RRRR Month') "Month",

sum(bytes)/1024/1024 "Growth in Meg"

from sys.v_$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

prompt

prompt *********************

prompt Os Space Utilization

prompt *********************

! df -k

PROMPT *------------------------------------------------------------------------------------------*

Prompt MEMORY RELATED MAIN PARAMETERS

PROMPT *------------------------------------------------------------------------------------------*

select a.NAME,decode(a.name,'db_block_buffers',(a.value*b.blk_size),

VALUE)/1024 "Size in KB",

decode(a.name,'db_block_buffers',(a.value*b.blk_size),

VALUE)/1024/1024 "Size in MB"

from v$parameter a,(select value as blk_size from v$parameter where name='db_block_size' )b

where name in('db_block_buffers',

'shared_pool_size','log_buffer','java_pool_size',

'large_pool_size','sort_area_size','db_cache_size')

order by 2;

prompt

SET HEADING OFF

PROMPT *------------------------------------------------------------------------------------------*

PROMPT TOTAL SAPCE ALLOTED TO DATABASE

PROMPT *------------------------------------------------------------------------------------------*

SELECT 'TODAY : ' || to_char(sysdate,'dd-mm-yyyy') ,

' MB : '|| TO_CHAR( ROUND(SUM(BYTES)/1024/1024,3 )) ,' GB : '|| TO_CHAR(ROUND(SUM(BYTES)/1024/1024/1024,3))

FROM DBA_DATA_FILES;

prompt

PROMPT *------------------------------------------------------------------------------------------*

PROMPT USED SPACE IN DATABASE

PROMPT *------------------------------------------------------------------------------------------*

select 'TODAY : ' || to_char(sysdate,'dd-mm-yyyy') ,

' MB : '|| TO_CHAR(ROUND(sum(bytes)/1024/1024,3)),' GB : '|| TO_CHAR(ROUND(sum(bytes)/1024/1024/1024,3))

from dba_segments;

prompt

PROMPT *------------------------------------------------------------------------------------------*

PROMPT PHYSICAL FILE STRUCTURE

PROMPT *------------------------------------------------------------------------------------------*

select 'Control Files :' , count(*) from v$controlfile;

select 'Data Files :' , count(*) from v$datafile;

SELECT 'Log Groups :' , count(*) from V$LOG;

SELECT 'Log Members :' , count(*) from V$LOGFILE;

Select 'TEMP Files :' , count(*) from v$tempfile;

prompt

PROMPT *------------------------------------------------------------------------------------------*

PROMPT USERS

PROMPT *------------------------------------------------------------------------------------------*

SELECT 'USERS : ' || TO_CHAR(count(*)) from DBA_users;

SET HEADING ON

prompt

PROMPT *------------------------------------------------------------------------------------------*

PROMPT TABLESPACE INFORMATION

PROMPT *------------------------------------------------------------------------------------------*

select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,STATUS,CONTENTS,EXTENT_MANAGEMENT

from dba_tablespaces order by INITIAL_EXTENT;

PROMPT *------------------------------------------------------------------------------------------*

PROMPT USERS And Their TABLESPACE INFORMATION

PROMPT *------------------------------------------------------------------------------------------*

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE

from dba_users order by DEFAULT_TABLESPACE;

PROMPT *------------------------------------------------------------------------------------------*

PROMPT NUMBER OF TABLES PER TABLESPACE

PROMPT *------------------------------------------------------------------------------------------*

SELECT TABLESPACE_NAME,COUNT(TABLE_NAME) NO_OF_TABLES FROM DBA_TABLES

GROUP BY ROLLUP(TABLESPACE_NAME);

prompt

PROMPT *------------------------------------------------------------------------------------------*

PROMPT NUMBER OF INDEXES PER TABLESPACE

PROMPT *------------------------------------------------------------------------------------------*

SELECT TABLESPACE_NAME,COUNT(TABLE_NAME) NO_OF_TABLES FROM DBA_INDEXES

GROUP BY ROLLUP(TABLESPACE_NAME);

prompt

prompt ******************************************************************************

prompt Query to check which segments are within 5 extent short of the maximum extents

prompt ******************************************************************************

Select segment_name,segment_type,max_extents,extents

from dba_segments

where extents+5 >max_extents

and segment_type <>'CACHE'

order by extents;

prompt

prompt *********************************************************************************************************************

Prompt Following query shows to identify all the segments that are already using 95% of the Oracle blocks allocated to them.

prompt *********************************************************************************************************************

Select Owner,table_name,1-(empty_blocks/(empty_blocks+blocks)) "%Blocks Used"

from Dba_tables

where Owner !='SYS'

and 1-(empty_blocks/(empty_blocks+blocks)) > .95

order by 1;

prompt ******************************************************************************************************

prompt Query to Find Empty Blocks below HWM so as to avoid scanning empty blocks during FTS

prompt Needs Export,drop or truncate and then reimport the table OR Use Alter Table....Move to rebuild Table

prompt ******************************************************************************************************

Select owner,TABLE_NAME,blocks "Actual Blocks Used Below HWM",

Round((t.avg_row_len*t.num_rows)/s.block_size,0) "Est Blocks Needed Below HWM",

blocks - Round((t.avg_row_len*t.num_rows)/s.block_size,0) "Wasted Blocks Below HWM"

From dba_tables t,(select VALUE as block_size from v$parameter where NAME='db_block_size') s

where (blocks - Round((t.avg_row_len*t.num_rows)/s.block_size,0)) >1

order by 5;

prompt ***********************************************************

prompt Shows current size, used and freespace within the datafiles.

prompt ***********************************************************

select df.FILE_NAME,df.BYTES/1024/1024 allocated_mb,

((df.bytes/1024/1024)-nvl((sum(dfs.bytes)/1024/1024),0)) used_mb,

nvl((sum(dfs.bytes)/1024/1024),0) free_space_mb

FROM dba_data_files df,dba_free_space dfs

where df.FILE_ID=dfs.FILE_ID

group by dfs.FILE_ID,df.FILE_NAME,df.bytes

order by 4;

rem SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,

rem ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,

rem NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

rem FROM v$datafile df, dba_free_space dfs

rem WHERE df.file# = dfs.file_id(+)

rem GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes

rem ORDER BY file_name;

prompt

prompt ***********************************************************

prompt Objects that cannot extend (no space in TS)

prompt ***********************************************************

select a.owner||'.'||a.segment_name "Sname",

a.segment_type "Stype",

a.bytes/1024/1024 "Size",

a.next_extent/1024/1024 "Next",

a.tablespace_name "TName"

from sys.dba_segments a

where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces

and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x...

select max(b.bytes)

from dba_free_space b

where a.tablespace_name = b.tablespace_name)

order by 3 desc;

prompt *****************************************************************************

prompt Rollback Segments their Status and Tablespace to which they belog to:

prompt ******************************************************************************

select s.SEGMENT_ID USN,s.SEGMENT_NAME,s.TABLESPACE_NAME,s.STATUS,f.FILE_NAME

from dba_rollback_segs s,dba_data_files f

where s.RELATIVE_FNO=f.FILE_ID;

prompt *****************************************************************************

prompt Rollback Segments their Status and Activity

prompt ******************************************************************************

select s.SEGMENT_NAME,st.EXTENTS,st.WRITES,st.WAITS,

st.OPTSIZE,st.HWMSIZE,st.SHRINKS,st.WRAPS,st.EXTENDS,st.STATUS

from dba_rollback_segs s,v$rollstat st

where s.SEGMENT_ID =st.usn

order by 2,3;

prompt *****************************************************************************

prompt This script shows the user that has performed the most physical

prompt disk reads. You use the columns sid and serial# as input into

prompt dbms_system.set_sql_trace_in_session to commence tracing the

prompt offending user.

prompt *****************************************************************************

col sql_text for a50 word_wrap

col username for a10

col OSUSER for a10

SELECT ses.sid, ses.serial#,ses.username, ses.osuser, ses.process,t.sql_text

FROM v$session ses, v$sess_io sio,v$sqlarea t

WHERE ses.sid = sio.sid

AND(ses.sql_address=t.address AND ses.sql_hash_value=t.hash_value)

AND nvl(ses.username,'SYS') not in ('SYS', 'SYSTEM')

AND sio.physical_reads = (SELECT MAX(physical_reads)

FROM v$session ses2, v$sess_io sio2

WHERE ses2.sid = sio2.sid

AND ses2.username

NOT IN ('SYSTEM', 'SYS'));

prompt *****************************************************************************

prompt This scripts provides you with a listing of all

prompt object types by user.

prompt *****************************************************************************

SELECT username,

COUNT(DECODE(o.TYPE#, 2, o.obj#, '')) Tab,

COUNT(DECODE(o.TYPE#, 1, o.obj#, '')) Ind,

COUNT(DECODE(o.TYPE#, 5, o.obj#, '')) Syn,

COUNT(DECODE(o.TYPE#, 4, o.obj#, '')) Vew,

COUNT(DECODE(o.TYPE#, 6, o.obj#, '')) Seq,

COUNT(DECODE(o.TYPE#, 7, o.obj#, '')) Prc,

COUNT(DECODE(o.TYPE#, 8, o.obj#, '')) Fun,

COUNT(DECODE(o.TYPE#, 9, o.obj#, '')) Pck,

COUNT(DECODE(o.TYPE#,12, o.obj#, '')) Trg,

COUNT(DECODE(o.TYPE#,10, o.obj#, '')) Dep

FROM sys.obj$ o, sys.dba_users U

WHERE u.user_id = o.owner# (+)

GROUP BY username

order by 1;

SELECT username,COUNT(DECODE(o.TYPE#, 29, o.obj#, '')) "Java class",

COUNT(DECODE(o.TYPE#, 21, o.obj#, '')) "Lob",

COUNT(DECODE(o.TYPE#, 12, o.obj#, '')) "Trigger",

COUNT(DECODE(o.TYPE#, 13, o.obj#, '')) "Type",

COUNT(DECODE(o.TYPE#, 14, o.obj#, '')) "Type body",

COUNT(DECODE(o.TYPE#, 28, o.obj#, '')) "Java source",

COUNT(DECODE(o.TYPE#, 22, o.obj#, '')) "Lib",

COUNT(DECODE(o.TYPE#, 24, o.obj#, '')) "Queue"

FROM sys.obj$ o, sys.dba_users U

WHERE u.user_id = o.owner# (+)

GROUP BY username

order by 1;

prompt *****************************************************************************

prompt Objects need to be moved from System Tablespace

prompt *****************************************************************************

select Owner,SEGMENT_NAME,segment_type,TABLESPACE_NAME,bytes/1024/1024 "Size in MB"

from dba_segments

where owner not in ('SYS','SYSTEM')

and TABLESPACE_NAME='SYSTEM'

order by 5;

prompt *****************************************************************************

prompt Current Rollback Segment Usage

prompt *****************************************************************************

set line 200

column "Rollback Segment Name" format a24;

column "Oracle User Session" format a10;

col "Process ID" for 9999999

col "Oracle User Session" format a22

col sql_text format a50

set pagesize 9999

select r.name "Rollback Segment Name",

p.spid "Process ID",

s.username||'('||l.sid||')' "Oracle User Session",

sq.sql_text

from v$sqlarea sq, v$lock l, v$process p, v$session s, v$rollname r

where l.sid = p.pid(+)

and s.sid = l.sid

and trunc(l.id1(+) / 65536) = r.usn

and l.type(+) = 'TX'

and l.lmode(+) = 6

and s.sql_address = sq.address

and s.sql_hash_value = sq.hash_value

order by r.name;

prompt *****************************************************************************

Prompt This script lists tablespace disk storage information

prompt *****************************************************************************

SELECT SUBSTR(D.TABLESPACE_NAME,1,15) TSPACE,

D.FILE_ID FILE_ID,

D.BYTES / 1024 / 1024 TOT_MB,

D.BYTES / 4096 ORA_BLKS,

SUM(E.BLOCKS) TOT_USED,

ROUND(SUM(E.BLOCKS) / (D.BYTES / 4096), 4) * 100 PCT_USED

FROM SYS.DBA_EXTENTS E,

SYS.DBA_DATA_FILES D

WHERE D.FILE_ID = E.FILE_ID (+)

GROUP BY D.TABLESPACE_NAME, D.FILE_ID, D.BYTES

order by 6;

prompt *****************************************************************************

Prompt This script lists tablespace Usage Free and used information

prompt *****************************************************************************

SELECT TABLESPACE_NAME,

ROUND(SUM(TOTAL_MB)-SUM(FREE_MB)) MB_USED,

ROUND(SUM(TOTAL_MB)) MB_SIZE,

ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(TOTAL_MB)*100) PCT_FULL,

ROUND(SUM(MAX_MB) -(SUM(TOTAL_MB)-SUM(FREE_MB))) MB_FREE,

ROUND(SUM(MAX_MB)) MB_MAXSIZE,

ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(MAX_MB)*100) PCT_UTIL

FROM

(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,0 TOTAL_MB,0 MAX_MB

FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME

UNION

SELECT TABLESPACE_NAME,0 CURRENT_MB,SUM(BYTES)/1024/1024 TOTAL_MB,

SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/1024/1024 MAX_MB

FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)

GROUP BY TABLESPACE_NAME;

prompt

spool off

! df -k | tee -a `ls -tr output_* |tail -1`