Script to tune sorts

REM: Script to tune sorts
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************


Select name, value from v$sysstat
where name in (\\'sorts (memory)\\', \\'sorts (disk)\\');

--If large number of sorts require I/O to disk,increase the initialisation parameter SORT_AREA_SIZE

Script to Report SGA Memory Map

REM Purpose: Script to Report SGA Memory Map
REM -----------------------------------------------------------------------

set echo off
set feedback off
set linesize 512

prompt
prompt SGA Memory Map (overall)
prompt

column dummy noprint
column area format a20 heading 'Main SGA Areas'
column name format a20
column pool format a20
column bytes format 999,999,999,999
column sum(bytes) format 999,999,999,999

break on report
compute sum of sum(bytes) on report

SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes)
FROM v$sgastat
WHERE pool is null and
name = 'db_block_buffers'
group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes)
FROM v$sgastat
WHERE pool = 'shared pool'
group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes)
FROM v$sgastat
WHERE pool = 'large pool'
group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes)
FROM v$sgastat
WHERE pool = 'java pool'
group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes)
FROM v$sgastat
WHERE pool is null and
name = 'log_buffer'
group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes)
FROM v$sgastat
WHERE pool is null and
name = 'fixed_sga'
group by name
ORDER BY 4 desc;

column area format a20 heading 'Shared Pool Areas'

prompt
prompt SGA Memory Map (shared pool)
prompt

SELECT 'Shared Pool' area, name, sum(bytes)
FROM v$sgastat
WHERE pool = 'shared pool' and
name in ('library cache','dictionary cache','free memory','sql area')
group by name
union all
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes)
FROM v$sgastat
WHERE pool = 'shared pool' and
name not in ('library cache','dictionary cache','free memory','sql area')
group by pool
order by 3 desc;

Script to find Dictionary Hit ratio, Library Cache Hit ratio & SQLs with high disk reads.

REM: Script for Dictionary Hit ratio
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT (1 - (SUM (getmisses) / SUM (gets))) * 100 \"Hit Ratio\"
FROM v$rowcache;

*******************************************************************************
REM: Script for finding Library Cache Hit ratio
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT SUM (pins) / (SUM (pins) - SUM (reloads)) * 100 \"Hit Ratio\"
FROM v$librarycache;

*******************************************************************************
REM: Script to find sql with high disk reads.
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT disk_reads, sql_text
FROM v$sqlarea
WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

*******************************************************************************

Script to find TOP SQL

REM: Script to find top SQL
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT b.username username, a.disk_reads phyreads, a.executions noexec,
a.disk_reads
/ DECODE (a.executions, 0, 1, a.executions) rds_ex_ratio,
a.command_type, a.sql_text sqlqry
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 10000
ORDER BY a.disk_reads DESC;

Script to Get OS user name with terminal name

REM: Script to Get Os user name with terminal name
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;

Script to report constraints in for a table

REM: Script to report constraints in for a table
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

COL column_name format a30
SET linesize 999

SELECT c1.constraint_name, c2.column_name, c1.constraint_type, c1.delete_rule
FROM user_constraints c1, user_cons_columns c2
WHERE c1.table_name = UPPER ('&Table_Name')
AND c1.constraint_name = c2.constraint_name;

Query to see the Reserved, Used and Free space of your database

REM: Query to see the Reserved, Used and Free space of your database
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT r.tablespace_name, reserved_space \"RESERVED_SPACE(MB)\",
reserved_space - free_space \"USED_SPACE(MB)\",
free_space \"FREE_SPACE(MB)\"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;

Script to compile invalid objects in DB after refreshing

REM: Script to compile invalid objects in DB after refreshing
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set pagesize 0
set feedback off
set trimspool on
prompt
prompt Run the script as sysdba otherwise invalid objects will remain (ORA-01031 error)
prompt
prompt Run the script several times. It only takes long the first time
prompt
prompt check the progress of compilation by issueing from another session
prompt select count(*) \"invalid\" from dba_objects where status<>'VALID';
prompt
prompt hit to continue
pause
spool compile.lis
select 'alter '||object_type||' '||owner||'.\"'||object_name||'\" compile;'
from dba_objects
where status<>'VALID'
and object_type not in ('PACKAGE BODY','TYPE BODY','UNDEFINED','JAVA CLASS','SYNONYM')
union
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='PACKAGE BODY'
union
select 'alter type '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='TYPE BODY'
union
select 'alter materialized view '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='UNDEFINED'
union
select 'alter java class '||owner||'.\"'||object_name||'\" resolve;'
from dba_objects
where status<>'VALID'
and object_type='JAVA CLASS'
union
select 'alter synonym '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner<>'PUBLIC'
union
select 'alter public synonym '||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner='PUBLIC';
spool off
set feedback on
@compile.lis

Script to view and count number of sessions to the database

REM: Script to view and count number of sessions to the database
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set pages 100
set lines 3000

col machine format a25
col username format a15
col program format a25
break on 1;

set heading off;
select
'Sessions on database '|| d.name ||' having instance name '|| i.instance_name
from v$database d,v$instance i;
set heading on;

compute sum label 'Total Sessions' of sessions on 1
select
1,username,machine,program,count(*) sessions
from v$session
group by username,machine,program
order by username,sessions desc;

Script to tune buffer cache hit ratio

REM: Script to tune buffer cache hit ratio
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************


DECLARE
DB_BLK NUMBER(10,2);
CONS_GET NUMBER(10,2);
PHY_RDS NUMBER(10,2);
FINAL NUMBER(10,2);
begin
Select value INTO DB_BLK From v$sysstat Where name in (\\'db block gets\\');
Select value INTO CONS_GET From v$sysstat Where name in (\\'consistent gets\\');
Select value INTO PHY_RDS From v$sysstat Where name in (\\'physical reads\\');
FINAL:=(1-(PHY_RDS/(DB_BLK+CONS_GET)))*100;
if final>90 then
dbms_output.put_line(\\'The BUFFER CACHE HIT RATIO IS OK \\'||final);
end if;
if final<90 then
dbms_output.put_line(\\'increaSe the initialisation parameter DB_CACHE_SIZE \\'||final);
end if;
end;
/

Script to tune library cache

REM: Script to tune library cache
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************


DECLARE
hit NUMBER(10,2);
reload number(10,2);
begin
Select ((sum(pinhits) / sum(pins))*100) into hit From v$librarycache Where namespace in (\\'SQL AREA\\', \\'TABLE/PROCEDURE\\',\\'BODY\\', \\'TRIGGER\\');
select ((sum(reloads)/sum(pins))*100) into reload From v$librarycache Where namespace in (\\'SQL AREA\\', \\'TABLE/PROCEDURE\\', \\'BODY\\', \\'TRIGGER\\');
IF hit<85 then
dbms_output.put_line(\\'The hit ratio should be at least 85% (i.e. 0.85). this is ok \\'||hit);
end if;
IF hit>85 then
dbms_output.put_line(\\'THE hit ratio >85%. check shared pool and open cursor parameter \\'||hit);
end if;
dbms_output.put_line(\\'*************************************************************************\\');
if reload>.02 then
dbms_output.put_line(\\'The reload percent should be very low, 2% (i.e. 0.02) or less. this is ok \\'||reload);
end if;
if reload<.02 then
dbms_output.put_line(\\'The reload >2% \\'||reload);
end if;
end;
/

Exporting Entire Oracle Database backup using a Shell script

REM: Exporting Entire Oracle Database backup using a Shell script
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

#!/bin/sh
# Exporting Entire Oracle Database backup

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=
filename='FULL_'
v_date=\"`date +%Y%m%d`\"

echo 'Exporting Entire Oracle Database ....'
${ORACLE_HOME}/bin/exp / full=y file=${filename}${v_date}.dmp log=${filename}${v_date}.log

echo 'Transferring the Oracle Export Dump file to Remote location ....'

smbclient // -U % -c \"put ${filename}${v_date}.dmp\"
smbclient // -U % -c \"put ${filename}${v_date}.log\"

IT security audit - Information on Users,Privileges & Roles assigned

IT security audit
------------------
REM: This script generates the information about the user's
REM: privileges and roles and privileges assign to those
REM: roles, it is very usefull when doing IT security audit
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

select lpad(' ', 3*level) || granted_role Users and roles privileges
from (
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username in (select username from dba_users)
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;

Script to measure the Buffer Cache Hit Ratio

Script to measure the Buffer Cache Hit Ratio
---------------------------------------------
rem :
rem Purpose:Measure the Buffer Cache Hit Ratio
rem Note:Hit ratio based tuning is not recommended!
rem -----------------------------------------------------------------------

-- Get initial Buffer Hit Ratio reading...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) \"Cache Hit Ratio\"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/

-- Let's artificially increase the buffer hit ratio...
DECLARE
v_dummy dual.dummy%TYPE;
BEGIN
FOR I IN 1..1000 LOOP
SELECT dummy INTO v_dummy FROM dual;
END LOOP;
END;
/

-- Let's measure it again...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) \"Cache Hit Ratio\"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/