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

PRE REQUISITE TABLES

REM FILE NAME: crea_tab.sql

REM LOCATION: SetUp
REM FUNCTION: Create required views and tables for the DBMS_REVEALNET package
REM TESTED ON: 7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1
REM
REM This is a part of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C) 2001 Quest Software
REM All rights reserved.
REM
REM******************** Knowledge Xpert for Oracle Administration
********************

DROP TABLE dba_temp;

CREATE TABLE dba_temp (
NAME VARCHAR2(64),
VALUE NUMBER,
rep_order NUMBER)
STORAGE (INITIAL 16k NEXT 16k);

DROP TABLE temp_size_table;

CREATE TABLE temp_size_table (
table_name VARCHAR2(64),
blocks NUMBER)
STORAGE (INITIAL 16k NEXT 16k);

DROP TABLE hit_ratios;

CREATE TABLE hit_ratios (
check_date DATE NOT NULL,
check_hour NUMBER NOT NULL,
db_block_gets NUMBER,
CONSISTENT NUMBER,
phy_reads NUMBER,
hitratio NUMBER,
period_hit_ratio NUMBER,
period_usage NUMBER,
users NUMBER)
STORAGE (INITIAL 16k NEXT 16k);

CREATE UNIQUE INDEX hr_index ON hit_ratios (

check_date,
check_hour)
STORAGE (INITIAL 16k NEXT 16k);

REM You must have direct select grants on the undelying tables
REM for these views to be generated.

CREATE OR REPLACE VIEW free_space (

TABLESPACE,
file_id,
pieces,
free_bytes,
free_blocks,
 largest_bytes,
largest_blks,
fsfi
)
AS
SELECT tablespace_name, file_id, COUNT (*), SUM (bytes), SUM (blocks),
MAX (bytes), MAX (blocks),
SQRT (MAX (blocks) / SUM (blocks))
* (100 / SQRT (SQRT (COUNT (blocks))))
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id;

REM
REM FUNCTION: create views required for rbk1 and rbk2 reports.
REM
REM exit
CREATE OR REPLACE VIEW rollback1
AS
SELECT d.segment_name, extents, optsize, shrinks, aveshrink, aveactive,
d.status
FROM v$rollname n, v$rollstat s, dba_rollback_segs d
WHERE d.segment_id = n.usn(+) AND d.segment_id = s.usn(+);

CREATE OR REPLACE VIEW rollback2

AS
SELECT d.segment_name, extents, xacts, hwmsize, rssize, waits, wraps,
EXTENDS
FROM v$rollname n, v$rollstat s, dba_rollback_segs d
WHERE d.segment_id = n.usn(+) AND d.segment_id = s.usn(+);
REM FUNCTION: Creates summary of v_$sqlarea and dba_users for use in
REM sqlmem.sql and sqlsummary.sql reports
REM
REM
CREATE OR REPLACE VIEW sql_summary
AS
SELECT username, sharable_mem, persistent_mem, runtime_mem
FROM sys.v_$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id;
REM
REM trans_per_rollback view gives a quick look at who is doing what to rollbacks
REM
CREATE OR REPLACE VIEW trans_per_rollback (
NAME, sid, pid, TRANSACTION, terminal)
AS
SELECT r.NAME, l.sid, p.spid, NVL (p.username, 'no transaction'), p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+) / 65536) = r.usn
AND l.TYPE(+) = 'TX'
AND l.lmode(+) = 6;
REM
REM proc_count view
REM provides line count data for procedures
REM
CREATE OR REPLACE VIEW proc_count
AS
SELECT owner, NAME, TYPE, COUNT (*) lines
FROM dba_source
GROUP BY owner, NAME, TYPE
/

CREATE TABLE dba_running_stats (

NAME VARCHAR2(64),
VALUE NUMBER,
rep_order NUMBER,
meas_date DATE,
delta NUMBER)
STORAGE(INITIAL 1m NEXT 1m PCTINCREASE 0)
/
DROP TABLE revealnet_kept_objects;
CREATE TABLE revealnet_kept_objects
(
object_name VARCHAR2(128),
CONSTRAINT pk_revealnet_kept_objects
PRIMARY KEY (object_name)
USING INDEX
TABLESPACE &&index_tablespace
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0))
TABLESPACE &&data_tablespace
STORAGE (INITIAL 16k NEXT 16k);
--
-- Column object_name stores the names of objects that the user wishes to pin in
the
-- Shared SQL area
--
DROP TABLE revealnet_update_tables;
CREATE TABLE revealnet_update_tables
(
main_table VARCHAR2(30) NOT NULL,
table_name VARCHAR2(30) NOT NULL,
column_name VARCHAR2(30) NOT NULL,
CONSTRAINT pk_revealnet_update_tables
PRIMARY KEY (main_table,table_name,column_name)
USING INDEX
TABLESPACE &&index_tablespace
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0))
STORAGE (INITIAL 56k NEXT 56k PCTINCREASE 0)
TABLESPACE &&data_tablespace;

/
-- Column definitions for revealnet_update_tables are as follows:
--
-- main_table holds the name of the table that the update
-- cascades from.
--
-- table_name holds the name(s) of the tables to cascade the update
-- into.
--
-- column_name is the name of the column in the target table(s) to
-- updateDBMS version
--
-- view contend is used by dbms_revealnet.running_stats
--
CREATE OR REPLACE VIEW contend
AS
SELECT CLASS, SUM (COUNT) waits, SUM (TIME) elapsed_time
FROM v$waitstat
GROUP BY CLASS;
--
--
--
CREATE OR REPLACE VIEW sql_garbage
AS
SELECT b.username users, SUM ( a.sharable_mem
+ a.persistent_mem) garbage,
TO_NUMBER (NULL) good
FROM sys.v_$sqlarea a, dba_users b
WHERE (a.parsing_user_id = b.user_id AND a.executions <= 1)
GROUP BY b.username
UNION
SELECT DISTINCT b.username users, TO_NUMBER (NULL) garbage,
SUM ( c.sharable_mem
+ c.persistent_mem) good
FROM dba_users b, sys.v_$sqlarea c
WHERE (b.user_id = c.parsing_user_id AND c.executions > 1)
GROUP BY b.username;
--
-- end of crea_tab.sql
--

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

ALL INVALID OBJECTS IN DATABASE

COLUMN object_name FORMAT A30 HEADING 'Object|Name'

COLUMN owner FORMAT a10 HEADING 'Object|Owner'
COLUMN last_time FORMAT a20 HEADING 'Last Change|Date'
SET LINES 80 FEEDBACK OFF PAGES 0 VERIFY OFF
START title80 'Invalid Database Objects'
SPOOL rep_out/inv_obj
SELECT owner, object_name, object_type,
TO_CHAR (last_ddl_time, 'DD-MON-YY hh:mi:ss') last_time
FROM dba_objects
WHERE status = 'INVALID'
/
SET LINES 80 FEEDBACK ON PAGES 22 VERIFY ON
CLEAR COLUMNS
TTITLE OFF

OBJECT CREATION AND MODIFICATION DATE

COLUMN created format a19

COLUMN modified format a19
COLUMN object_type format a10
COLUMN object_name format a15
BREAK on owner on object_type
SET verify off feedback off lines 80 pages 57
@title132 'Object Modification and Creation Dates'
SPOOL rep_out\object
SELECT owner, object_name, TO_CHAR (created, 'dd-mon-yy hh:mi:ss') created,
 TO_CHAR (last_ddl_time, 'dd-mon-yy hh:mi:ss') modified, object_type
FROM dba_objects
WHERE owner LIKE UPPER ('%&owner%')
AND object_name LIKE UPPER ('%&object_name%')
ORDER BY owner, object_type
/
SPOOL off
UNDEF owner
UNDEF object_name
CLEAR columns
CLEAR breaks
SET verify on feedback on pages 22

SIZE OF SCHEMA AND THEIR DEFAULT TABLESPACE

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

select username,default_tablespace from dba_users;

select a.owner,sum(a.bytes)/1024/1024/1024 as size_gig, b.created from dba_segments

a,dba_users b where a.owner=b.username and a.owner in (select username from
dba_users) group by a.owner,b.created order by b.created;

FILE SIZES AND LOCATIONS

CLEAR computes
COLUMN FILE_NAME FORMAT A70
COLUMN TABLESPACE_NAME FORMAT A25
COLUMN MEG FORMAT 99,999.90
START title80 'DATABASE DATAFILES'
SPOOL rep_out\datafile
BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT
COMPUTE SUM OF MEG ON TABLESPACE_NAME
COMPUTE sum of meg on REPORT
SELECT tablespace_name, file_name, bytes/1024/1024/1024 GB
FROM dba_data_files
ORDER BY tablespace_name
/

SPOOL OFF
CLEAR columns
CLEAR computes

FILE STORAGE STATS

COLUMN file_name format a23 heading 'File|Name'

COLUMN file_id format 9999999 heading 'File|Number'
COLUMN tablespace_name format a10 heading 'Tablespace'
COLUMN bytes format 9999.99 heading 'Megs'
COLUMN status heading 'File|Status'
COLUMN value new_value block_size noprint
REM
SELECT VALUE
FROM sys.v_$parameter
WHERE NAME = 'db_block_size';
COLUMN blocks format 99,999 heading '&block_size byte|Blocks'
SET feedback off echo off lines 79 pages 57
START title80 'File Storage Statistics Report'
SPOOL rep_out\file_sta
REM
SELECT file_name, file_id, status, tablespace_name,
bytes / (1024 * 1024) bytes, blocks
FROM sys.dba_data_files
ORDER BY file_id
/
SPOOL off
CLEAR columns
TTITLE off

FREE SPACE IN TABLESPACE

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

col "Tablespace" for a22

col "Used GB" for 99,999,999
col "Free GB" for 99,999,999
col "Total GB" for 99,999,999

select df.tablespace_name "Tablespace",

totalusedspace "Used GB",
(df.totalspace - tu.totalusedspace) "Free GB",
df.totalspace "Total GB"
from
(select tablespace_name,
round(sum(bytes) / (1024*1024*1024)) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

FREE SPACE IN DATAFILE

**********************
set linesize 300
COLUMN tablespace_name format a40 heading 'Tablespace|Name'
COLUMN file_name format a90 heading 'File|Name'

SELECT
a.tablespace_name, a.file_id,
a.file_name,
a.bytes/1024/1024/1024 allocated_GB,
b.free_GB
FROM
dba_data_files a,
 (SELECT file_id, SUM(bytes/1024/1024/1024) free_GB
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

SET ECHO OFF

SET MARKUP HTML ON SPOOL ON

select t.TABLESPACE_NAME,
round(sum(d.bytes)/1024/1024/1024) Tot_Size_GB,
count(*) numfile,
round(sum(d.user_bytes)/1024/1024/1024) Used_GB,
round((32-sum(round(d.bytes/1024/1024/1024,2))/count(*)) * count(*),2) FreeGB
from dba_data_files d ,dba_tablespaces t
where t.tablespace_name=d.tablespace_name
group by t.TABLESPACE_NAME;

IDENTIFY CHAINED ROWS FOR ALL SPECIFIED USER TABLE

ACCEPT tabown prompt 'Enter table owner: '

SET termout off feedback off verify off echo off heading off pages 999
SET embedded on
COLUMN value new_value db noprint
SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_name';
SPOOL rep_out\auto_chn.gql
SELECT 'start chaining &tabown '
|| table_name
FROM dba_tables
WHERE owner = UPPER ('&tabown')
/
SPOOL rep_out\auto_chn
START rep_out\auto_chn.gql
SPOOL off
UNDEF tabown
SET termout on feedback 15 verify on pagesize 20 linesize 80 space 1
SET embedded off
HO del rep_out\auto_chn.gql
PAUSE Press enter to continue

TABLESPACE USAGE

CREATE OR REPLACE PROCEDURE space

AS
CURSOR datafile1
IS
 SELECT tablespace_name, file_id, MAX (bytes) largeextent,
SUM (bytes) totalfreespace
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id
ORDER BY tablespace_name, file_id;

CURSOR datafile2 (fileid INTEGER)

IS
SELECT NAME, bytes
FROM v$datafile
WHERE file# = fileid;

dbname VARCHAR2 (8);

BEGIN
SELECT NAME
INTO dbname
FROM v$database;

DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ( 'Space Report for Database '
|| dbname);
DBMS_OUTPUT.put_line ('-----------------------------------');
DBMS_OUTPUT.put_line ('');

FOR FILE IN datafile1

LOOP
DBMS_OUTPUT.put_line (
'Tablespace Name: '
|| FILE.tablespace_name
);
--DBMS output...Name:'|file... < commented out. didn't pass syntax
check.
DBMS_OUTPUT.put_line ('');

FOR filedata IN datafile2 (FILE.file_id)

LOOP
DBMS_OUTPUT.put_line (
'Data File Name: '
|| filedata.NAME
);
DBMS_OUTPUT.put_line (
'Total File Size (KB): '
|| ROUND (filedata.bytes / 1024, 0)
);
DBMS_OUTPUT.put_line (
'Free Space in File (KB): '
|| ROUND (FILE.totalfreespace / 1024, 0)
);
DBMS_OUTPUT.put_line (
'% Free Space in File: '
|| ROUND (( (FILE.totalfreespace / filedata.bytes)
* 100
), 0)
|| '%'
);
DBMS_OUTPUT.put_line (
'Largest Extent (KB): '
|| ROUND (FILE.largeextent / 1024, 0)
);
 DBMS_OUTPUT.put_line ('');
END LOOP;
END LOOP;
END;
/

TABLE STATS

SET pages 56 lines 130 newpage 0 verify off echo off feedback off
REM
COLUMN owner format a12 heading "Table Owner"
COLUMN table_name format a20 heading "Table"
COLUMN tablespace_name format a20 heading "Tablespace"
COLUMN num_rows format 999,999,999 heading "Rows"
COLUMN blocks format 999,999 heading "Blocks"
COLUMN empty_blocks format 999,999 heading "Empties"
COLUMN space_full format 999.99 heading "% Full"
COLUMN chain_cnt format 999,999 heading "Chains"
COLUMN avg_row_len format 99,999,999,999 heading "Avg Length (Bytes)"
REM
START title132 "Table Statistics Report"
SPOOL rep_out\tab_stat
BREAK ON OWNER SKIP 2 ON TABLESPACE_NAME SKIP 1;
SELECT owner, table_name, tablespace_name, num_rows, blocks, empty_blocks,
100
* ( (num_rows * avg_row_len)
/ (( GREATEST (blocks, 1)
+ empty_blocks
) * 2048
)
)
space_full,
chain_cnt, avg_row_len
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, tablespace_name;
SPOOL off
SET pages 22 lines 80 newpage 1 verify on echo off feedback on
CLEAR columns
CLEAR breaks
TTITLE off

OBJECTS THAT NEED RECOMPILING

@title80 "Objects that need Recompilation"

COLUMN obj# heading Object|Number
COLUMN name heading Object|Name
COLUMN owner# heading Owner|Number
SPOOL rep_out\hanging
SELECT DISTINCT o2.obj#, o2.NAME, o2.owner#
FROM sys.obj$ o, sys.dependency$ d, sys.obj$ o2
WHERE o.obj# = d.p_obj#
AND o.stime != d.p_timestamp
AND d.d_obj# = o2.obj#
AND o2.status != 5
ORDER BY o2.obj#
/
SPOOL off
CLEAR columns
TTITLE off

DATABASE LINK STATS

SET pages 58 lines 130 verify off term off

START title132 "Db Links Report"
SPOOL rep_out\db_links
COLUMN host format a60 heading "Connect String Used"
COLUMN owner format a15 heading "Creator of DB Link"
COLUMN db_link format a10 heading "DB Link Name"
COLUMN username format a15 heading "Connecting User"
COLUMN password format a15 heading "Password"
COLUMN create heading "Date Created"
SELECT host, owner, db_link, username, created
FROM dba_db_links
ORDER BY owner, HOST;
SPOOL off
SET pages 22 lines 80 verify on termout on
TTITLE off
CLEAR columns

REDOLOG PHYSICAL FILE

COLUMN group# format 999999

COLUMN member format a50
SET lines 80 pages 60 feedback off verify off
START title80 'Redo Log Physical Files'
BREAK on group#
SPOOL c:\db_logs
SELECT *
FROM sys.v_$logfile
ORDER BY group#
/
SPOOL off
CLEAR columns
CLEAR breaks
TTITLE off
SET pages 22 feedback on verify on

REDOLOG THREADS

COLUMN current_group# heading Current|Group#

COLUMN Checkpoint_change# heading Checkpoint|Change#
COLUMN checkpoint_time heading Checkpoint|Time
COLUMN open_time heading Open|Time
COLUMN thread# heading Thread#
COLUMN status heading Status
COLUMN enabled heading Enabled
COLUMN groups heading Groups
COLUMN Instance heading Instance
COLUMN sequence# heading Sequence#
SET lines 132 pages 59
START title132 'Redo Thread Report'
SPOOL rep_out\db_thrd
SELECT *
FROM sys.v_$thread
ORDER BY thread#;
SPOOL off
SET lines 80 pages 22
TTITLE off
CLEAR columns

DATAFILE ARCHIVE STATUS

COLUMN name format a43

COLUMN arc heading 'Backup Status'
SET lines 132 pages 59 feedback off
START title132 "Database File Backup Status"
SPOOL rep_out\backup
SELECT NAME, a.status, DECODE (b.status, 'Active', 'Backup', 'Normal') arc,
enabled, bytes, change#, TIME ARCHIVE
FROM sys.v_$datafile a, sys.v_$backup b
WHERE a.file# = b.file#;
SPOOL off
CLEAR columns
TTITLE off

CURRENT DATABASE PARAMETERS

SET NEWPAGE 0 VERIFY OFF
SET PAGES 10000 lines 131
COLUMN name format a37
COLUMN value format a30
COLUMN description format a40 word_wrapped
START title132 "INIT.ORA PARAMETER LISTING"
SPOOL rep_out\db_parm
SELECT NAME, VALUE, description
FROM v$parameter
ORDER BY NAME;
SPOOL OFF
CLEAR COLUMNS
SET VERIFY ON termout on PAGES 22 lines 80
UNDEF output

SESSION MEMORY USAGE (AT RUN TIME)

@title132 'Current Session Memory'

SPOOL c:\memory
SELECT NVL (username, 'SYS-BKGD') username, sess.sid,
SUM (VALUE)
|| ' bytes' "Current session memory"
FROM v$session sess, v$sesstat stat, v$statname NAME
WHERE sess.sid = stat.sid
AND stat.statistic# = NAME.statistic#
AND NAME.NAME LIKE 'session % memory'
GROUP BY username, sess.sid
/
SPOOL off
TTITLE off

TOP N SQL DISK READ

ACCEPT access_level PROMPT "Enter a value for high disk reads: "
COLUMN parsing_user_id FORMAT 9999999 HEADING 'User Id'
COLUMN executions FORMAT 9999 HEADING 'Exec'
COLUMN sorts FORMAT 99999 HEADING 'Sorts'
COLUMN command_type FORMAT 99999 HEADING 'CmdT'
COLUMN disk_reads FORMAT 999,999,999 HEADING 'Block Reads'
COLUMN sql_text FORMAT a40 HEADING 'Statement' WORD_WRAPPED
SET LINES 130 VERIFY OFF FEEDBACK OFF echo off
START title132 'SQL Statements With High Reads'
SPOOL rep_out/sqldrd
SELECT parsing_user_id, executions, sorts, command_type, disk_reads, sql_text
FROM v$sqlarea
WHERE disk_reads > &&access_level
ORDER BY disk_reads;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON

SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,

SQL_FullText SQLFullText
FROM
(
SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,
SQL_FullText, Operation, Options,
Row_Number() OVER
(Partition By sql_text ORDER BY Disk_Reads * Executions DESC)
KeepHighSQL
FROM
(
SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,
Max(Executions) OVER (Partition By sql_text) Executions,
t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
FROM v$sql t, v$sql_plan p
WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
AND t.Executions > 1
)
ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=5;

SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/

SQL AREA MEMORY USAGE

COLUMN areas HEADING Used|Areas

COLUMN sharable FORMAT 999,999,999 HEADING Shared|Bytes
COLUMN persistent FORMAT 999,999,999 HEADING Persistent|Bytes
COLUMN runtime FORMAT 999,999,999 HEADING Runtime|Bytes
COLUMN username FORMAT A15 HEADING "User"
SPOOL c:\mem
SET PAGES 59 LINES 80
BREAK ON REPORT
COMPUTE SUM OF sharable ON REPORT
COMPUTE SUM OF persistent ON REPORT
COMPUTE SUM OF runtime ON REPORT
SELECT username, SUM (sharable_mem) sharable, SUM (persistent_mem) persistent,
SUM (runtime_mem) runtime, COUNT (*) areas
FROM sql_summary
GROUP BY username
ORDER BY 2;
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
SET PAGES 22 LINES 80
TTITLE OFF

CONTENTION OF RESOURCES IN BUFFER BUSY WAIT

SET VERIFY OFF FEEDBACK OFF

SET PAGES 58
SET LINES 79
START TITLE80 "AREA OF CONTENTION REPORT"
SPOOL c:\contention
SELECT CLASS, SUM (COUNT) total_waits, SUM (TIME) total_time
FROM v$waitstat
GROUP BY CLASS;
SPOOL OFF
SET verify on feedback on pages 22 lines 80
TTITLE off

ALL DATABASE LOCKS

COLUMN osuser format a15 heading 'User'

COLUMN session_id heading 'SID'
COLUMN mode_held format a20 heading 'Mode|Held'
COLUMN mode_requested format a20 heading 'Mode|Requested'
COLUMN lock_id1 format a10 heading 'Lock|ID1'
COLUMN lock_id2 format a10 heading 'Lock|ID2'
COLUMN type heading 'Type|Lock'
SET feedback off echo off pages 59 lines 131
START title132 'Report on All Locks'
SPOOL rep_out\db_locks
SELECT NVL (a.osuser, 'SYS') osuser, b.session_id, TYPE, mode_held,
mode_requested, lock_id1, lock_id2
 FROM v$session a, dba_locks b
WHERE a.sid = b.session_id
ORDER BY 2
/
SPOOL off
CLEAR columns
SET feedback on echo on pages 22 lines 80

IDENTIFY USERS OF FULL TABLE SCANS

DROP VIEW full_table_scans;

CREATE VIEW full_table_scans

AS
SELECT ss.username
|| '('
|| se.sid
|| ') ' "User Process",
SUM (DECODE (NAME, 'table scans (short tables)', VALUE)) "Short Scans",
SUM (DECODE (NAME, 'table scans (long tables)', VALUE)) "Long Scans",
SUM (DECODE (NAME, 'table scan rows gotten', VALUE)) "Rows Retrieved"
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND ( NAME LIKE '%table scans (short tables)%'
OR NAME LIKE '%table scans (long tables)%'
OR NAME LIKE '%table scan rows gotten%'
)
AND se.sid = ss.sid
AND ss.username IS NOT NULL
GROUP BY ss.username
|| '('
|| se.sid
|| ') ';

COLUMN "User Process" FORMAT a20;

COLUMN "Long Scans" FORMAT 999,999,999;
COLUMN "Short Scans" FORMAT 999,999,999;
COLUMN "Rows Retreived" FORMAT 999,999,999;
COLUMN "Average Long Scan Length" FORMAT 999,999,999;
TTITLE ' Table Access Activity By User '
SELECT "User Process", "Long Scans", "Short Scans", "Rows Retrieved"
FROM full_table_scans
ORDER BY "Long Scans" DESC;

COUNT OF OPEN CURSOR PER USER

COLUMN user_name heading User

COLUMN num heading Number|Of|Cursors
SET echo off
START title80 'Open Cursors By User'
SET lines 80 pages 59
SPOOL rep_out\open_cur
SELECT user_name, COUNT (*) num
FROM sys.v_$open_cursor
GROUP BY user_name;
SPOOL off
CLEAR columns
TTITLE off
SET pages 22

DATA DICTIONARY CACHE CONDITION

SET PAGESIZE 59
SET LINESIZE 79 echo off
COLUMN parameter FORMAT A20
COLUMN type FORMAT a10
COLUMN percent FORMAT 999.99 HEADING "%";
START title80 "DATA DICTIONARY CACHE STATISTICS"
SPOOL rep_out/dd_cache
SELECT parameter, TYPE, gets, getmisses,
(getmisses / gets * 100) PERCENT, COUNT, usage
FROM v$rowcache
WHERE gets > 100 AND getmisses > 0
ORDER BY parameter;
SPOOL OFF

LIBRARY CACHE REPORT

COLUMN namespace heading "Library Object"

COLUMN gets format 999999 heading " Gets"
COLUMN gethitratio format 999.99 heading " Get Hit%"
COLUMN pins format 999999 heading " Pins"
COLUMN pinhitratio format 999.99 heading " Pin Hit%"
COLUMN reloads format 999999 heading " Reloads"
COLUMN invalidations format 999999 heading " Invalidations"
COLUMN db format a10
SET pages 58 lines 80
START title80 "Library Caches Report"
SPOOL rep_out\libcache
SELECT namespace, gets, gethitratio * 100 gethitratio, pins,
pinhitratio * 100 pinhitratio, reloads, invalidations
FROM v$librarycache
/
SPOOL off
SET pages 22 lines 80
TTITLE off
UNDEF output

SHARED SQL UTILIZATION

COLUMN garbage format 9,999,999,999 heading 'Non-Shared SQL'

COLUMN good format 9,999,999,999 heading 'Shared SQL'
COLUMN good_percent format 999.99 heading 'Percent Shared'
SET feedback off
BREAK on report
COMPUTE sum of garbage on report
COMPUTE sum of good on report
COMPUTE avg of good_percent on report
@title80 'Shared Pool Utilization'
SPOOL rep_out\sql_garbage
SELECT a.users, a.garbage, b.good,
(b.good / ( b.good
+ a.garbage
)
) * 100 good_percent
FROM sql_garbage a, sql_garbage b
WHERE a.users = b.users AND a.garbage IS NOT NULL AND b.good IS NOT NULL
/
SPOOL off

BUFFER CACHE HIT RATIOS AND USAGE B/W TWO DATES

SET verify off pages 58 newpage 0

ACCEPT check_date1 PROMPT 'Enter Start Date (dd-mon-yy format): '
ACCEPT check_date2 PROMPT 'Enter End Date (dd-mon-yy format): '
START title80 "HIT RATIO AND USAGE FOR &&CHECK_DATE1 TO &&CHECK_DATE2"
SPOOL rep_out\ratiosum
SELECT check_date, check_hour, period_hit_ratio, period_usage, users
FROM hit_ratios
WHERE check_date BETWEEN '&&CHECK_DATE1' AND '&&CHECK_DATE2'
ORDER BY check_date, check_hour;
SPOOL OFF
TTITLE off

ROLE REPORT

SET flush off term off pagesize 58 linesize 78 echo off

COLUMN grantee heading 'User or Role'
COLUMN admin_option heading 'Admin?'
BREAK on grantee
START title80 'System Privilege Report'
SPOOL rep_out\db_role
SELECT grantee, PRIVILEGE, admin_option
FROM sys.dba_sys_privs
ORDER BY grantee;
SPOOL off
SET flush on term on pagesize 22 linesize 80
CLEAR columns
CLEAR breaks
TTITLE off

SESSION EVENTS OF A USER

COLUMN sid HEADING Sid

COLUMN event HEADING Event FORMAT a40
COLUMN total_waits HEADING Total|Waits
COLUMN total_timeouts HEADING Total|Timeouts
COLUMN time_waited HEADING Time|Waited
COLUMN average_wait HEADING Average|Wait
COLUMN username HEADING User
BREAK ON username
START title132 "Session Events By User"
SPOOL rep_out\events
SET LINES 132 PAGES 59
SELECT username, event, total_waits, total_timeouts, time_waited,
average_wait
FROM sys.v_$session_event a, sys.v_$session b
WHERE a.sid = b.sid
ORDER BY 1;
SPOOL OFF
CLEAR COLUMNS
CLEAR BREAKS
SET LINES 80 PAGES 22
TTITLE OFF

USERS LOGIN TIMES

COLUMN sids format a10 heading "Sid,Ser#"

COLUMN username format a15 heading Username
COLUMN ltime format a20 heading "Login Time"
COLUMN program format a30 heading Program
START title80 'User Login Times'
SPOOL rep_out\login_tm
SELECT s.sid
|| ','
|| n.serial# sids, n.username, n.status "Status",
n.program,
TO_CHAR (
SYSDATE
 - ( hsecs
- s.VALUE
) / (24 * 3600 * 100),
'MM/DD/YYYY HH24:MI:SS'
) ltime
FROM sys.v_$sesstat s, sys.v_$session n, sys.v_$timer
WHERE s.statistic# = 13 AND s.sid = n.sid
ORDER BY 2, 5;
SPOOL off
TTITLE off

USERS OF FULL TABLE SCANS

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

DROP VIEW full_table_scans;

CREATE VIEW full_table_scans

AS
SELECT ss.username
|| '('
|| se.sid
|| ') ' "User Process",
SUM (DECODE (NAME, 'table scans (short tables)', VALUE)) "Short Scans",
SUM (DECODE (NAME, 'table scans (long tables)', VALUE)) "Long Scans",
SUM (DECODE (NAME, 'table scan rows gotten', VALUE)) "Rows Retrieved"
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND ( NAME LIKE '%table scans (short tables)%'
OR NAME LIKE '%table scans (long tables)%'
OR NAME LIKE '%table scan rows gotten%'
)
AND se.sid = ss.sid
AND ss.username IS NOT NULL
GROUP BY ss.username
|| '('
|| se.sid
|| ') ';

COLUMN "User Process" FORMAT a20;

COLUMN "Long Scans" FORMAT 999,999,999;
COLUMN "Short Scans" FORMAT 999,999,999;
COLUMN "Rows Retreived" FORMAT 999,999,999;
COLUMN "Average Long Scan Length" FORMAT 999,999,999;
TTITLE ' Table Access Activity By User '
SELECT "User Process", "Long Scans", "Short Scans", "Rows Retrieved"
FROM full_table_scans
ORDER BY "Long Scans" DESC;

LOAD TABLE STAT AND ANALYZE ALL TABLES

SET pages 0
REM set feedback off
REM set echo off termout off
REM set verify off
SPOOL rep_out\do_stat.sql
SELECT 'analyze table '
|| owner
|| '.'
|| table_name
|| ' estimate statistics;'
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
START rep_out\do_stat.sql

FREE SPACE IN UNDOTABLESPACE

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

select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%' ) allocated,
( select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name like 'UND%') free,
( select sum(bytes)/1024/1024 from dba_undo_extents
where tablespace_name like 'UND%') USed
from dual;

FREE SPACE IN TEMP TABLESPACE

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

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type",

d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024,
0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from
dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

TEMPORARY TABLESPACE USAGE BY SESSIONS

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

SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id as Instance,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program, a.status, a.sql_id
FROM gv$session a, gv$sort_usage b, gv$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id
ORDER BY temp_size desc;

REDUCE HIGH WATER MARK FROM TABLE

SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

alter table TAB_NAME enable row movement;

alter table TAB_NAME shrink space;

USERS DETAILS
*************

select substr(username,1,20) "Username",

substr(user_id,1,10) "User ID",
substr(password,1,20) "Password",
substr(account_status,1,20) "Account Status",
substr(Lock_date,1,11) "Lock date",
substr(Expiry_date,1,11) "Expiry date",
substr(Default_tablespace,1,15) "Def Tablespace",
substr(Created,1,10) "Created",
substr(Profile,1,10) "Assgn Profile",
substr(Initial_RSRC_Consumer_Group,1,20) "Consumer Grp",
substr(External_Name,1,10) "Ext Name"
from sys.dba_users
order by username;

USERS ON WHOM AUDIT IS ENABLED

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

SELECT USER_NAME, FAILURE FROM SYS.DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION = 'CREATE

SESSION';

select user_name,audit_option,success,failure from SYS.DBA_STMT_AUDIT_OPTS;

select * from DBA_PRIV_AUDIT_OPTS order by user_name;

select * from DBA_STMT_AUDIT_OPTS order by user_name;

PURGING AUDIT INFORMATION

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

delete from sys.aud$;

USERS WHO ARE USING UNDO

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

COLUMN sid_serial FORMAT A20

COLUMN username FORMAT A20
COLUMN program FORMAT A30
COLUMN undoseg FORMAT A20
COLUMN undo FORMAT A20
SET LINESIZE 120

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial,

NVL(s.username, '(oracle)') AS username,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' AS undo
FROM v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

REMOTE SESSION TRACING

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

ALTER SESSION SET max_dump_file_size = unlimited;

EXEC DBMS_MONITOR.session_trace_enable(session_id =>85, serial_num=>511,

waits=>TRUE, binds=>TRUE);

EXEC DBMS_MONITOR.session_trace_disable (session_id => 85, serial_num => 511);

tkprof clastst2_ora_557262.trc translated.txt explain=test/test

table=sys.plan_table sys=no waits=yes

$ORACLE_HOME/bin/tkprof claslive_ora_1319046.trc translated_new.txt

explain=qamberdb/oracle9!@claslive sys=no waits=yes

use tkprof

http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php

To audit Sessions:

audit create session;

Query by Audit Type:

SELECT A.USERNAME,
OS_USERNAME,
A.TIMESTAMP,
A.RETURNCODE,
TERMINAL,
USERHOST
FROM DBA_AUDIT_SESSION A
WHERE USERHOST = <replace with iAS servername> ;

By User

SELECT USERNAME,OBJ_NAME,ACTION_NAME , TIMESTAMP

FROM DBA_AUDIT_TRAIL WHERE USERNAME = 'SCOTT';

Check for users sharing database accounts

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

select count(distinct(terminal)),username
from dba_audit_session
having count(distinct(terminal))>1
group by username;

Attempts to access the database at unusual hours

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

SELECT username, terminal, action_name, returncode,

TO_CHAR (TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR (logoff_time, 'DD-MON-YYYY HH24:MI:SS')
FROM dba_audit_session
WHERE TO_DATE (TO_CHAR (TIMESTAMP, 'HH24:MI:SS'), 'HH24:MI:SS') <
TO_DATE ('08:00:00', 'HH24:MI:SS')
OR TO_DATE (TO_CHAR (TIMESTAMP, 'HH24:MI:SS'), 'HH24:MI:SS') >
TO_DATE ('19:30:00', 'HH24:MI:SS');

Attempts to access the database with non-existent users

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

SELECT username, terminal, TO_CHAR (TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS')

FROM dba_audit_session
WHERE returncode 0
AND NOT EXISTS (SELECT 'x'
FROM dba_users
WHERE dba_users.username = dba_audit_session.username);

user with expired account

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

select * from dba_users where account_status like 'EXPIRED%' and created < sysdate
- 15 and expiry_date < sysdate - 15;
BULK INSERT
***********

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF t1%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS
SELECT *
FROM t1;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
forall i in 1..l_tab.count
insert into t2 values l_tab(i);
EXIT WHEN l_tab.count = 0;
-- Process contents of collection here.
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE c_data;
END;
/

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF t1%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS
select * from t1 where archive_date < sysdate-90;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
forall i in 1..l_tab.count
insert into t2 values l_tab(i);
EXIT WHEN l_tab.count = 0;
-- Process contents of collection here.
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE c_data;
END;
/

declare
type rowid_array is table of rowid;

ids rowid_array;
 cursor cur is
select rowid as id
from CUSTOMERS
where ACTIVE='Y';

begin
open cur;
loop
fetch cur bulk collect into ids limit 1000;
exit when ids.count = 0;

forall c in ids.first .. ids.last

update CUSTOMERS set ACTIVE='N';

commit;
end loop;
end;

INSERT /*+ Append*/ INTO some_table (a, b) VALUES (1, 2)

DECLARE
c_limit PLS_INTEGER := 100;

CURSOR employees_cur
IS
SELECT employee_id
FROM employees
WHERE department_id = department_id_in;

TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE;

l_employee_ids employee_ids_t;
BEGIN
OPEN employees_cur;

LOOP
FETCH employees_cur
BULK COLLECT INTO l_employee_ids
LIMIT c_limit; -- This will make sure that every iteration has 100
records selected

EXIT WHEN l_employee_ids.COUNT = 0;

FORALL indx IN 1 .. l_employee_ids.COUNT SAVE EXCEPTIONS

UPDATE employees emp -- Updating 100 records at 1 go.
SET emp.salary =
emp.salary + emp.salary * increase_pct_in
WHERE emp.employee_id = l_employee_ids(indx);
commit;
END LOOP;

EXCEPTION
WHEN OTHERS
THEN
 IF SQLCODE = -24381
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
-- Caputring errors occured during update
DBMS_OUTPUT.put_line (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ‘: ‘
|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);

--<You can inset the error records to a table here>

END LOOP;
ELSE
RAISE;
END IF;
END;

ANALYZE TABLE
*************

Analyze Table ARCHIVER_CASABLANCA.TRANSACTION_LOG_KEY Compute Statistics For Table

For All Indexes For All Indexed Columns ;

USERS LOCKING INDEXES

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

select oracle_username || ' (' || s.osuser || ')' username

, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id;

USERS WITH THEIR ROLES

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

SELECT * FROM dba_role_privs;

IDENTIFY DUPLICATE ROWS IN TABLE

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

select vendor_name, count(vendor_name)

from apps.AP_SUPPLIER_LOG_NJI
group by vendor_name
having count (vendor_name) > 1;

IDENTIFY LOCKED SESSIONS AND ROOT BLOCKERS

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

select l1.sid, ' IS BLOCKING ', l2.sid

from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;

DETERMINE IF RMAN JOB IS RUNNING AND HOW MUCH HAS BEEN COMPLETED
****************************************************************

select sid, start_time, totalwork sofar,(sofar/totalwork) * 100 pct_done from

v$session_longops where totalwork > sofar AND opname NOT LIKE '%aggregate%' AND
opname like 'RMAN%';

select sid,spid,client_info,event,seconds_in_wait,p1, p2, p3 from v$process p,

v$session s where p.addr = s.paddr and client_info like 'rman channel=%';
file reduction size script
**************************

select a.tablespace_name, a.file_name, ceil( (nvl(hwm,1)*8192)/1024/1024 ) "Mo"

from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id
) b
where a.file_id = b.file_id(+)
order by tablespace_name, file_name;

OBJECTS TO BE MOVED TO RELEASE HIGH WATER MARK

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

select
file_id,
block_id,
block_id + blocks - 1 end_block,
owner,
segment_name,
partition_name,
segment_type
from
dba_extents
where
tablespace_name = 'D_DYN1'
union all
select
file_id,
block_id,
block_id + blocks - 1 end_block,
'free' owner,
'free' segment_name,
null partition_name,
null segment_type
from
dba_free_space
where
tablespace_name = 'D_DYN1' AND FILE_ID=2
order by
1,2
/

UNSUCCESSFUL LOGIN ATTEMPT

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

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),

returncode from dba_audit_trail where returncode > 0 and username='FAISALDB'
and returncode in(28000,1017) order by to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') ;

---- returncode 28000 is account locked

---- return code 1017 invalid password
RESOURCE LIMITS
***************

select * from v$resource_limit;

RECOVERY FROM BLOCK CORRUPTCORRUPTION

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

identify corrupt objects

=========================
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) s_blk#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blk_corrupt
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >=
c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block s_blk#
, header_block e_blk#
, 1 blk_corrupt
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name,
c.file#
, greatest(f.block_id, c.block#) s_blk#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blk_corrupt
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >=
c.block#
order by file#, s_blk#;

SELECT distinct 'alter index BASELIII.'||e.SEGMENT_NAME||' rebuild online parallel

6;'
--DISTINCT owner, segment_name,e.SEGMENT_TYPE
FROM v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN
e.block_id and e.block_id+e.blocks-1
where e.segment_type='INDEX' and owner='BASELIII';

clear v$database_block_corruption
=================================

execute dbms_backup_restore.resetCfileSection(35);
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 29
and 555297 between block_id AND block_id + blocks - 1

run {
allocate channel d1 type disk;
backup check logical validate datafile 210, 260, 262;
release channel d1;
}

rman> BLOCKRECOVER DATAFILE 56 BLOCK 219876;

file # 61, block # 217924

file # 56, block # 219876)

file # 59, block # 232452

REM Create the repair table in a given tablespace:

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'WF_LOCAL_ROLES',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'APPS_TS_TX_DATA');
END;
/

REM Identify corrupted blocks for schema.object (it also can be done at partition
level with parameter PARTITION_NAME):

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION

from REPAIR_TABLE;

REM Mark the identified blocks as corrupted ( Soft Corrupt - reference Note
1496934.1 )
DECLARE num_fix INT;
BEGIN
num_fix := 0;
 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/

HOW TO CALCULATE IOPS OF DATABASE

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

define name='physical read total IO requests'

define inst_no=1
define slotsize=900
define rangesize=500
define start_peak_hour=8
define end_peak_hour=15
col range head " IOPS Range" form a20
col noofslots head "No Of|Occurences" form 999,999
select lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6) range,
sum(noofslots) noofslots
from (
select st/(extract(DAY from (dest - dsst)) * 86400 + extract(HOUR from
(dest - dsst)) * 3600+ extract(MINUTE from (dest - dsst)) * 60+ extract(SECOND from
(dest - dsst))) vpersec,round((extract(DAY from (dest - dsst)) * 86400 +
extract(HOUR from (dest - dsst)) * 3600+ extract(MINUTE from (dest - dsst)) * 60+
extract(SECOND from (dest - dsst)))/&slotsize) noofslots
from (
select /*+ full(st) full(ss) use_hash(st)*/
to_char(ss.BEGIN_INTERVAL_TIME,'DAY DD-MM-YYYY HH24:MI:SS') est,
lag(to_char(ss.BEGIN_INTERVAL_TIME,'DAY DD-MM-YYYY HH24:MI:SS'))
over(order by ss.snap_id) sst,
ss.BEGIN_INTERVAL_TIME dest,
lag(ss.BEGIN_INTERVAL_TIME) over(order by ss.snap_id) dsst,
st.value-lag(st.value,1,0) over(order by ss.snap_id) st
from sys.WRH$_SYSSTAT st,
sys.WRM$_SNAPSHOT ss
 where st.snap_id=ss.snap_id
and ss.INSTANCE_NUMBER = &inst_no
and st.INSTANCE_NUMBER = &inst_no
and ss.dbid=st.dbid
and st.stat_id in (
select stat_id
from v$statname
where upper(name)=upper(trim('&name'))
)
and ss.snap_id in (
select /*+push_subq*/
snap_id
from sys.WRM$_SNAPSHOT
where INSTANCE_NUMBER = &inst_no
and to_char(BEGIN_INTERVAL_TIME, 'HH24') between
&start_peak_hour and &end_peak_hour
)
)
where sst is not null
and st > 0
and to_char(to_date(sst, 'DAY DD-MM-YYYY HH24:MI:SS'),'dd-mon-yy')
= to_char(to_date(est, 'DAY DD-MM-YYYY HH24:MI:SS'),'dd-mon-yy')
)
group by lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6)
order by lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6)
/

break on report
compute sum of Value on report

select METRIC_NAME,avg(AVERAGE) as "Value"

from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write
Total IO Requests Per Sec')
group by METRIC_NAME;

IDENTIFY FRAGMENTED FREE SPACE IN TABLESPACE

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

select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name

from dba_extents where tablespace_name = 'USERS'
union all
select file_id, block_id, block_id+blocks-1, 'free' from dba_free_space where
tablespace_name = 'USERS' order by file_id, first_block
/

DIFFERENCE IN DAYS
******************

touch filecomparison_01.txt

file1_time=$(date +%s -d"$( ls -l EVL-38762.log | awk ' { print $(NF-3)" "$(NF-2)"

"$(NF-1); }')")
file2_time=$(date +%s -d"$( ls -l filecomparison.txt | awk ' { print $(NF-3)" "$
(NF-2)" "$(NF-1); }')")
(( diff_time = (file2_time - file1_time)/86400 ))
echo $diff_time

FILE DELETION SCRIPT BASED ON DAYS SPECIFIED IN DUMP FILE

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

#!/bin/sh
for i in `ls -tr *.*`
do
echo "$i"
file1_time=$(date +%s -d"$( ls -l $i | awk ' { print $(NF-3)" "$(NF-2)" "$(NF-
1); }')")
file2_time=$(date +%s -d"$( ls -l filecomparison_01.txt | awk ' { print $(NF-3)" "$
(NF-2)" "$(NF-1); }')")
(( diff_time = (file2_time - file1_time)/86400 ))
echo $diff_time
a="${i##*_}"
short=${a:0:2}
if [ $diff_time -gt $short ]
then
echo "File $i needs to be deleted"
else
echo "No Task"
fi
done

DROP UNUSED COLUMN FROM COMPRESS TABLESPACE

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

Step-1: Run this statement, and then execute the statements in output.
**********************************************************************

select 'alter table '||table_name||' modify partition '||PARTITION_NAME||'

nocompress;' from user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-2: Run this statement, and then execute the statements in output.
*********************************************************************

select 'alter table '||table_name||' move partition '||PARTITION_NAME||' tablespace

users;' from user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-3: Execute this command to move table to noncompress tablespace e.g. users
*******************************************************************************

select 'alter table '||table_name||' move nocompress tablespace users;' from

user_tables where table_name='INSTRUMENTMASTER';

Step-4: Execute this to mark column as unused.

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

alter table INSTRUMENTMASTER set unused column GUARANTOR;

Step-5: Drop unused columns

***************************
alter table INSTRUMENTMASTER drop unused columns;

Step-6: Run this statement, and then execute the statements in output.
**********************************************************************

select 'alter table '||table_name||' modify partition '||PARTITION_NAME||'

compress;' from user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-7: Run this statement, and then execute the statements in output.
**********************************************************************

select 'alter table '||table_name||' move partition '||PARTITION_NAME||' tablespace

compress_tb;' from user_tab_partitions where table_name = 'INSTRUMENTMASTER';

Step-8: Run this statement, and then execute the statements in output.
**********************************************************************

select 'alter table '||table_name||' move compress tablespace compress_tb;' from

user_tables where table_name='INSTRUMENTMASTER';

Step-9: Run this statement, and then execute the statements in output.
**********************************************************************

select 'alter index '||INDEX_NAME||' rebuild online;' from user_indexes where

table_name='INSTRUMENTMASTER';

RETERIVE DATA FROM TABLE USING FLASHBACK

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

SELECT * FROM matthiash

AS OF TIMESTAMP
TO_TIMESTAMP('2013-05-03 14:00:00', 'YYYY-MM-DD HH24:MI:SS');

RESIZE DATAFILES TO MINIMUM SIZE

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

set linesize 400

col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576
hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents
GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

RESIZING DATAFILES
******************

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save

FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576
hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents
GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

DATABASE GROWTH ANALYSIS

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

SET LINESIZE 200

SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB'
"Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) /
ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) -
ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '%
MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-
min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-
min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 /
1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-
min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select
sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 /
1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
=================================================================

Declare
v_BaselineSize number(20);
v_CurrentSize number(20);
v_TotalGrowth number(20);
v_Space number(20);
cursor usageHist is
select a.snap_id,
SNAP_TIME,
sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
from
(select SNAP_ID,
sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
from DBA_HIST_SEG_STAT
group by SNAP_ID
having sum(SPACE_ALLOCATED_TOTAL) <> 0
order by 1 ) a,
(select distinct SNAP_ID,
to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
from DBA_HIST_SNAPSHOT) b
where a.snap_id=b.snap_id;
Begin
select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
select sum(bytes) into v_CurrentSize from dba_segments;
v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

dbms_output.put_line('SNAP_TIME Database Size(MB)');

for row in usageHist loop

v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
dbms_output.put_line(row.SNAP_TIME || ' ' || to_char(v_Space) );
end loop;
end;

USER SESSION IDLE TIME

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

set serveroutput on
DECLARE

CURSOR session_cursor IS
SELECT username, sid, last_call_et
FROM v$session
WHERE username IS NOT NULL AND username NOT IN ('SYS','SYSTEM')
ORDER BY last_call_et;

num_mins NUMBER;
num_mins_sec NUMBER;
wait_secs NUMBER;
num_hours NUMBER;
num_hours_min NUMBER;
wait_mins NUMBER;
num_days NUMBER;
num_days_hours NUMBER;
wait_hours NUMBER;
wait_char_mins VARCHAR2(4);
wait_char_secs VARCHAR2(4);
BEGIN

DBMS_OUTPUT.PUT_LINE(chr(10));

FOR idle_time IN session_cursor LOOP

-- Total number of seconds waited...

num_mins := trunc(idle_time.last_call_et/60);
num_mins_sec := num_mins * 60;
wait_secs := idle_time.last_call_et - num_mins_sec;

-- Total number of minutes waited...

num_hours := trunc(num_mins/60);
num_hours_min := num_hours * 60;
wait_mins := num_mins - num_hours_min;

-- Total number of hours waited...

num_days := trunc(num_hours/24);
num_days_hours := num_days * 24;
wait_hours := num_hours - num_days_hours;

DBMS_OUTPUT.PUT('User '||idle_time.USERNAME||'('||idle_time.SID||') has been

idle for '||num_days||' day(s) '||wait_hours||':');

IF wait_mins < 10 THEN

wait_char_mins := '0'||wait_mins||'';
DBMS_OUTPUT.PUT(''||wait_char_mins||':');
ELSE
DBMS_OUTPUT.PUT(''||wait_mins||':');
END IF;

IF wait_secs < 10 THEN

wait_char_secs := '0'||wait_secs||'';
DBMS_OUTPUT.PUT(''||wait_char_secs||'');
ELSE
DBMS_OUTPUT.PUT(''||wait_secs||'');
END IF;

DBMS_OUTPUT.NEW_LINE;

END LOOP;

END;

PERCENTAGE OF SQL QUERY JOBS

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

SELECT
opname,
target,
ROUND( ( sofar/totalwork ), 4 ) * 100 Percentage_Complete,
start_time,
CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min,
FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;