Category Archives: Scripts

Schedule AWR Report Generation

set echo off set head off set feed off

spool /orasys/home/oracle/Kishi/Logs/Awr_report_daily.lst; select ‘Define begin_snap= ‘|| snap_id from dba_hist_snapshot where EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=9 and EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’YYYY’) and EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’MM’) and EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’DD’);

SELECT ‘Define end_snap= ‘|| snap_id from dba_hist_snapshot where EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=18 and EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’YYYY’) and EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’MM’) and EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,’DD’);

select ‘define  report_type  = ‘ || ”’html”’ from dual; select ‘define  report_name  = /orasys/home/oracle/Kishi/Logs/AWR_REPORT_Daily.html’ from dual; select ‘define  inst_name    = ‘ || INSTANCE_NAME from v$instance; select ‘define  db_name    = ‘ || name from v$database; select ‘define  dbid = ‘ || dbid from v$database; select ‘define inst_num = ‘ || INSTANCE_NUMBER from v$instance; select ‘define  num_days     = 3’ from dual; select ‘@$ORACLE_HOME/rdbms/admin/awrrpti.sql’ from dual; spool off;

Database_Space_Scripts

–Free/used at tablespace level

rem ———————————————————————–
rem Filename: tsspace.sql
rem Purpose: Show Used/free space in Meg by tablespace name
rem Author: Kishore
rem ———————————————————————–

tti “Space Usage for Database in Meg”

SELECT Total.name “Tablespace Name”,
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/

tti off

–Free/used space at datafile level

REM ——————————————————————————
REM Filename: dffree.sql
REM Purpose: Shows current size, used & freespace within the datafiles.
REM Author: Kishore
REM ——————————————————————————

TTI “Allocated, Used & Free space within datafiles”

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) 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 v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

TTI off

–Used space at segment level

rem ———————————————————————–
rem Filename: usedsp.sql
rem Purpose: Used space in Meg by segment type
rem Author: Frank Naude, Oracle FAQ
rem ———————————————————————–

set pagesize 50000
set line 80

col “Total Used Meg” format 999,999,990
col “Data part” format 999,999,990
col “Index part” format 999,999,990
col “LOB part” format 999,999,990
col “RBS part” format 999,999,990

tti ‘Used space in Meg by segment type’

select sum(bytes)/1024/1024 “Total Used”,
sum( decode( substr(segment_type,1,5), ‘TABLE’, bytes/1024/1024, 0))
“Data part”,
sum( decode( substr(segment_type,1,5), ‘INDEX’, bytes/1024/1024, 0))
“Index part”,
sum( decode( substr(segment_type,1,3), ‘LOB’, bytes/1024/1024, 0))
“LOB part”,
sum( decode(segment_type, ‘ROLLBACK’, bytes/1024/1024, 0))
“RBS part”,
sum( decode(segment_type, ‘TEMPORARY’, bytes/1024/1024, 0))
“TEMP part”
from sys.dba_segments
/
tti off

tti “Total database size”

select sum(bytes)/1024/1024 “Total DB size in Meg”
from sys.v_$datafile
/
tti off

–Summary of database space history over a period of time

rem ———————————————————————–
rem Filename: spacehist.sql
rem Purpose: Save summary of database space history over time
rem Notes: Set JOB_QUEUE_PROCESSES to a value > 0 or schedule from
rem an external scheduler (corn, at…)
rem Date: 15-May-2002
rem Author: Frank Naude, Oracle FAQ
rem ———————————————————————–

—————————————————————————
— Create history table…
—————————————————————————
drop table db_space_hist;
create table db_space_hist (
timestamp date,
total_space number(8),
used_space number(8),
free_space number(8),
pct_inuse number(5,2),
num_db_files number(5)
);

—————————————————————————
— Stored proc to populate table…
—————————————————————————
create or replace procedure db_space_hist_proc as
begin
— Delete old records…
delete from db_space_hist where timestamp > SYSDATE + 364;
— Insert current utilization values…
insert into db_space_hist
select sysdate, total_space,
total_space-nvl(free_space,0) used_space,
nvl(free_space,0) free_space,
((total_space – nvl(free_space,0)) / total_space)*100 pct_inuse,
num_db_files
from ( select sum(bytes)/1024/1024 free_space
from sys.DBA_FREE_SPACE ) FREE,
( select sum(bytes)/1024/1024 total_space,
count(*) num_db_files
from sys.DBA_DATA_FILES) FULL;
commit;
end;
/
show errors

—————————————————————————
— Schedule the job using the DB Job System. This section can be removed if
— the job is sceduled via an external scheduler.
—————————————————————————
declare
v_job number;
begin
select job into v_job from user_jobs where what like ‘db_space_hist_proc%’;
dbms_job.remove(v_job);
dbms_job.submit(v_job, ‘db_space_hist_proc;’, sysdate,
‘sysdate+7’); — Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line(‘Job ‘||v_job||’ re-submitted.’);
exception
when NO_DATA_FOUND then
dbms_job.submit(v_job, ‘db_space_hist_proc;’, sysdate,
‘sysdate+7’); — Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line(‘Job ‘||v_job||’ submitted.’);
end;
/

—————————————————————————
— Generate a space history report…
—————————————————————————
select to_char(timestamp, ‘DD Mon RRRR HH24:MI’) “Timestamp”,
total_space “DBSize (Meg)”,
used_space “Free (Meg)”,
free_space “Used (Meg)”,
pct_inuse “% Used”,
num_db_files “Num DB Files”
from db_space_hist
order by timestamp;

–List segments that can not extend

rem ———————————————————————–
rem Filename: exterror.sql
rem Purpose: Segments that will cause errors when they try to extent!!!
rem Author: Frank Naude, Oracle FAQ
rem ———————————————————————–

prompt Objects that cannot extend (no space in TS)

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’

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
/

— THIS QUERY GIVES THE SAME RESULTS, BUT IS WAY TOO SLOW

— select a.owner, a.segment_name, b.tablespace_name,
— decode(ext.extents,1,b.next_extent,
— a.bytes*(1+b.pct_increase/100)) nextext,
— freesp.largest
— from dba_extents a,
— dba_segments b,
— (select owner, segment_name, max(extent_id) extent_id,
— count(*) extents
— from dba_extents
— group by owner, segment_name
— ) ext,
— (select tablespace_name, max(bytes) largest
— from dba_free_space
— group by tablespace_name
— ) freesp
— where a.owner=b.owner
— and a.segment_name=b.segment_name
— and a.owner=ext.owner
— and a.segment_name=ext.segment_name
— and a.extent_id=ext.extent_id
— and b.tablespace_name = freesp.tablespace_name
— and decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) > freesp.largest
— /

–Some segment sizing recommendations

rem ———————————————————————–
rem Filename: sizing.sql
rem Purpose: Give some segment sizing recommendations
rem Date: 04-Jul-1999
rem Author: Frank Naude, Oracle FAQ
rem ———————————————————————–

prompt Database block size:

select to_number(value) “Block size in bytes”
from sys.v_$parameter
where name = ‘db_block_size’
/

prompt Max number of possible extents (if not set to UNLIMITED)
prompt is db_block_size/16-7

select to_number(value)/16-7 “MaxExtents”
from sys.v_$parameter
where name = ‘db_block_size’
/

prompt The recommended min extent size is a multiple of
prompt db_block_size * db_file_multiblock_read_count. This gives
prompt the chunks Oracle ask from the OS when doing read-ahead
prompt with full table scans.

select to_number(a.value) * to_number(b.value) / 1024 “Min extent size in K”
from sys.v_$parameter a, sys.v_$parameter b
where a.name = ‘db_block_size’
and b.name = ‘db_file_multiblock_read_count’
/

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

rem ———————————————————————–
rem Filename: dbgrowth.sql
rem Purpose: Show database growth in Meg per month for the last year
rem DB Version: 8.0 or above
rem Note: File extending is not factored in as it’s not the available
rem in the dictionary.
rem Date: 19-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem ———————————————————————–

set pagesize 50000
tti “Database growth per month for last year”

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’)
/

tti off

–tables whose high water mark no# of used blocks

REM ########################################################################
REM ## Author : Sunil Kumar
REM ## Senior DBA
REM ## Email : sunilagarwal@hotmail.com
REM ## sunil.kumar@thehartford.com
REM ##
REM ## Script to determine highwater mark of tables. It is essential
REM ## to run ANALYZE_SCHEMA utility or ANALYZE TABLE commands before
REM ## running this script for accurate statistics.
REM ##
REM ## It displays all the tables whose high water mark no# of used
REM ## blocks.
REM ##
REM ## How To Run:
REM ## SQL>@highwtr
REM ## It will ask for the owner (press enter for all owners) and
REM ## the table name (press enter for all tables).
REM ##
REM ########################################################################

set verify off
column owner format a10
column alcblks heading ‘Allocated|Blocks’ just c
column usdblks heading ‘Used|Blocks’ just c
column hgwtr heading ‘High|Water’ just c
break on owner skip page

select
a.owner,
a.table_name,
b.blocks alcblks,
a.blocks usdblks,
(b.blocks-a.empty_blocks-1) hgwtr
from
dba_tables a,
dba_segments b
where
a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in(‘SYS’,’SYSTEM’)
and a.blocks (b.blocks-a.empty_blocks-1)
and a.owner like upper(‘&owner’)||’%’
and a.table_name like upper(‘&table_name’)||’%’
order by 1,2
/

set verify on
clear columns
clear breaks

–Check a TS is fragmentd and type of fragmentation

REM ——————————————————————————
REM Filename: tsfrag.sql
REM Purpose: Shows whether the TBS is fragmented and the type of fragmentation.
REM Author: Kam Muhamad Sirajdin [mdsirajdin@yahoo.com]
REM ——————————————————————————

TTI “Tablespace Fragmentation Details”

SELECT dfsc.tablespace_name tablespace_name,
DECODE (
dfsc.percent_extents_coalesced,
100,
(DECODE (
GREATEST ((SELECT COUNT (1)
FROM dba_free_space dfs
WHERE dfs.tablespace_name = dfsc.tablespace_name), 1),
1,
‘No Frag’,
‘Bubble Frag’
)
),
‘Possible Honey Comb Frag’
)
fragmentation_status
FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;

TTI off

–Index fragmentation status for a schema

/* ************************************************************* */
/* Index Fragmentation Status (idsfrag.sql): */
/* */
/* This script will report the index fragmentation status */
/* for a schema. */
/* */
/* Note: – Do not run this scrip during peak processing hours!!! */
/* – This script will fail for locked tables. */
/* */
/* ************************************************************* */

prompt — Drop and create temporary table to hold stats…
drop table my_index_stats
/
create table my_index_stats (
index_name varchar2(30),
height number(8),
del_lf_rows number(8),
distinct_keys number(8),
rows_per_key number(10,2),
blks_gets_per_access number(10,2)
)
/

prompt — Save script which we will later use to populate the above table…
insert into my_index_stats
select NAME, HEIGHT, DEL_LF_ROWS, DISTINCT_KEYS, ROWS_PER_KEY,
BLKS_GETS_PER_ACCESS
from INDEX_STATS
— Note this open line…

save /tmp/save_index_stats.sql replace

prompt
prompt — Spool listing with validate commands…
col line1 newline
col line2 newline
col line3 newline
set pagesize 0
set echo off
set termout off
set trimspool on
set feed off
set linesize 200
spool /tmp/validate_indexes.sql
select ‘prompt Process table ‘||owner||’.’||table_name||
‘, index ‘||index_name||’…’ line1,
‘validate index ‘||owner||’.’||index_name||’;’ line2,
‘@/tmp/save_index_stats.sql’ line3
from sys.dba_indexes where owner = ‘SCOTT’
order by table_name, index_name
/
spool off
set termout on
set feed on

prompt
prompt — Run script to validate indexes…
@/tmp/validate_indexes.sql

prompt — Print nice report…
set pagesize 50000
set trimspool on
col height format 99999
col del_rows format 9999999
col rows/key format 999999.9
spool idxfrag.lst
select INDEX_NAME, HEIGHT, DEL_LF_ROWS “DEL_ROWS”, DISTINCT_KEYS “DIST KEYS”,
ROWS_PER_KEY “ROWS/KEY”,
BLKS_GETS_PER_ACCESS “BLKS/ACCESS”
from MY_INDEX_STATS
/
spool off

— Cleanup
drop table my_index_stats
/
! rm /tmp/validate_indexes.sql
! rm /tmp/save_index_stats.sql

prompt
prompt Report is in idxfrag.lst
prompt Done!!!