scripts

Script:To calculate the size of Database

 
The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.
select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) “total_GB_size”
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e
select a.data_size+b.temp_size+c.redo_size “total_size”
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c

Procedure to check Database growth on daily basis….

Procedure to check DB growth
1.Create a Table By the Name DB_GROWTH…with following details…
CREATE TABLE DB_GROWTH
( DAY DATE,
DATABASE_SIZE_GB NUMBER,
DAILY_GROWTH_GB NUMBER);
2.create or replace PROCEDURE database_growth
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d
ay,’dd-mon-yy’)=to_date(SYSDATE -1,’dd-mon-yy’);
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size – yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
3.Submit in DBMS_JOBS
variable jobno number;
begin
dbms_job.submit(
:jobno,
‘database_growth ;’,
trunc(sysdate+1) + 4/24,
‘trunc(sysdate+1) + 4/24′
);
commit;
end;
/

Script for Database Information

set feedback off
set termout on
set pagesize 56
set linesize 800
ttitle off
spool out.log
set heading off
set verify off
column today NEW_VALUE p_currdate noprint
select TO_CHAR(SYSDATE,’fmMonth ddth, yyyy’) today from dual;
clear breaks
clear computes
clear columns
set heading off
column name heading ” justify center format a74
select ‘Database Name (SID): ‘ || name “name” from v$database;
prompt
prompt
prompt Version/SGA Information:
set heading off
select * from v$version;
column sgatot justify left format 9,999,999,999
select ‘Total System Global Area as of &p_currdate.:’ hd1, sum(value) sgatot, ‘bytes’ hd2 from v$sga;
–
– ************* TABLESPACES/DATAFILES **************
–
prompt
prompt
prompt Tablespaces and Datafiles:
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15
column file_id heading ‘File|ID’ justify center format 9999
column file_name heading ‘Datafile’ justify center format a60 word_wrapped
column size Heading ‘Size|in MB.’ justify center format 9,99990.99
break on tablespace_name skip 1 on report skip 2
compute sum label ‘TS SIZE:’ of size on tablespace_name
compute sum label ‘DB SIZE:’ of size on report
select tablespace_name,file_id,file_name,bytes/1024/1024 “size” from dba_data_files order by tablespace_name, file_id, file_name;
–
– ************* SPACE USAGE **************
–
prompt Space usage (as of &p_currdate.):
prompt
clear breaks
clear computes
clear columns
set heading on
column tspace heading ‘Tablespace’ justify left format a20 truncated
column tot_ts_size heading ‘Size|in MB.’ justify left format 9,99999999990
column free_ts_size heading ‘Used|in MB.’ justify right format 9,9999999990
column tbusedpct heading ” justify left format a6
column tbfreepct heading ” justify left format a6
break on report
compute sum label ‘Totals:’ of tot_ts_size free_ts_size on report
col tspace form a25 Heading “Tablespace”
col tot_ts_size form 99999999999999 Heading “Size (Mb)”
col free_ts_size form 99999999999999 Heading “Free (Mb)”
col ts_pct form 999 Heading “% Free”
col ts_pct1 form 999 Heading “% Used”
SELECT df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;
–
– ************* REDO LOG FILES **************
–
prompt
prompt
prompt Online Redo Logfiles:
clear breaks
clear computes
clear columns
column member heading ‘Logfile’ justify center format a60 word_wrapped
column group heading ‘Group|Number’ justify center format 9999
column size heading ‘Size|in MB.’ justify center format 999990.99
select f.member “member”,f.group# “group”,l.bytes/1024/1024 “size” from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;
–
– ************* CONTROL FILES ************** –
prompt
prompt
prompt Control files:
clear breaks
clear computes
clear columns
column name heading ‘File Name’ format a60 word_wrapped
select name from v$controlfile;
–
– ************* ROLLBACK SEGMENTS ************** –
prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.):
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15 truncated
column segment_name heading ‘Seg|Name’ justify center format a7
column status heading ‘Status’ justify center format a8
column initial_extent heading ‘Initial|(in M)’ justify center format 99990.9
column next_extent heading ‘Next|(in M)’ justify center format 99990.9
column min_extents heading ‘Min|Ext’ justify center format 99990
column max_extents heading ‘Max|Ext’ justify center format 999999999990
column pct_increase heading ‘Pct|Inc’ justify center format 99990
column rbsize heading ‘Curr Size|(in M)’ justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select r.tablespace_name,r.segment_name,r.status,
r.initial_extent/1024/1024 “initial_extent”,
r.next_extent/1024/1024 “next_extent”,
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 “rbsize”
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;
–
– ************* PARAMETERS **************
–
prompt
prompt Parameters (non-defaults):
clear breaks
clear computes
clear columns
column name heading ‘Name’ format a35 word_wrapped
column pvalue heading ‘Value’ format a50 word_wrapped
select name, rtrim(value) “pvalue” from v$parameter order by name;
–
– ************* USER DETAILS **************
–
prompt
prompt user details :
clear breaks
clear computes
clear columns
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

Tablespace creation script

set pagesize 100
set heading off
set feedback off
select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N',null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/

Script : Segments with highest I/O activity

This script will list the top 10 segments in the database that have the most number of
physical reads against them.
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||’.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in (‘physical reads’)
order by total_physical_reads desc)
where rownum <=10;

Script : Top SQL (Buffer Gets)

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads
set serverout on size 1000000
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
order by buffer_gets desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–’||’ ‘||’—————————————————’);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Script : Top SQL (Physical Reads)

This script will list the top 5 SQL statements sorted by the most number of physical reads
set serverout on size 1000000
set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select disk_reads,substr(sql_text,1,4000)
from v$sqlarea
order by disk_reads desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–’||’ ‘||’—————————————————-’);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Script : Latch Contention (top 5 latches)

This script will display the top 5 latches with the most sleeps.
set linesize 120
col name format a30
select * from
(select name, gets,misses, sleeps
from v$latch
order by sleeps desc)
where rownum < 6;