Systems Engineering and RDBMS

Oracle Schema Audit Script

Posted by decipherinfosys on October 23, 2008

Yesterday, we had covered a system audit script for Oracle. Today, we are going to provide an audit script for the Oracle Schema/User:

rem
rem user_audit.sql
rem
rem This script will collect audit information for the Schema that you want to collect information on
rem
rem NOTE:
rem Run this script as the schema owner

ALTER SESSION SET NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’
/

PROMPT Generating Oracle_Schema audit report …..
SET PAGES 5000 LINES 120 VERIFY OFF ECHO OFF TRIMSPOOL ON TRIMOUT ON FEED OFF TERMOUT OFF
SET MARKUP HTML ON SPOOL ON HEAD ‘<TITLE> user Audit Report </TITLE>’
SPOOL user_audit.html
PROMPT
PROMPT
SELECT ‘Schema AUDIT PERFOMED BY DB USER ‘||USER ||’ AT ‘ || SYSTIMESTAMP AS “Schema AUDIT INFORMATION”
FROM dual
/

PROMPT
PROMPT Collecting DBA Views Select Privileges…
PROMPT
PROMPT Describing v$pending_xatrans$
DESC v$pending_xatrans$
PROMPT Describing v$xatrans$
DESC v$xatrans$

PROMPT
PROMPT Collecting Schema Schema/User SYS Privileges…
PROMPT
COLUMN username HEADING ‘DB|Username’ FORMAT a30
COLUMN privilege HEADING ‘Privilege|Name’ FORMAT a40
COLUMN admin_option HEADING ‘Admin|Option’ FORMAT a6
SELECT username,
privilege,
admin_option
FROM user_sys_privs
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting Schema Schema/User Role Privileges…
PROMPT
COLUMN username HEADING ‘DB|Username’ FORMAT a30
COLUMN granted_role HEADING ‘Role|Granted’ FORMAT a30
COLUMN admin_option HEADING ‘Admin|Option’ FORMAT a6
COLUMN default_role HEADING ‘Default?’ FORMAT a8
SELECT username,
granted_role,
admin_option,
default_role
FROM user_role_privs
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting Explicit Privileges Received by User for other Owners Tables…
PROMPT
COLUMN grantor HEADING ‘Grantor’ FORMAT a15 WRAP
COLUMN owner HEADING ‘Table|Owner’ FORMAT a20 WRAP
COLUMN table_name HEADING ‘Table|Name’ FORMAT a30
COLUMN privilege HEADING ‘Privilege’ FORMAT a30 WRAP
COLUMN grantable HEADING ‘Grantable?’ FORMAT a10
SELECT grantor,
owner,
table_name,
privilege,
grantable
FROM user_tab_privs_recd
/
CLEAR COLUMNS

PROMPT
PROMPT num_rows: Number of rows in the table
PROMPT blocks: Number of used data blocks in the table
PROMPT empty_blocks: Number of empty (never used) data blocks in the table
PROMPT avg_space: Average amount of free space, in kilobytes, in a data block allocated to the table
PROMPT chain_cnt: Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID
PROMPT avg_row_len: Average length of a row in the table in bytes
PROMPT num_freelist_blocks: The number of blocks on the freelist
PROMPT sample_size: Sample size used in analyzing this table
PROMPT user_stats: Were the statistics entered directly by the user?
PROMPT
PROMPT blevel: B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
PROMPT leaf_blocks: Number of leaf blocks in the index
PROMPT distinct_keys: Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table
PROMPT
PROMPT Collecting Table Information…
PROMPT
COLUMN table_name HEADING ‘Table|Name’ FORMAT a30
COLUMN tablespace_name HEADING ‘Tablespace|Name’ FORMAT a15
COLUMN logging HEADING ‘Log?’ FORMAT a4
COLUMN pct_free HEADING ‘Pct|Free’ FORMAT 9999 JUST RIGHT
COLUMN pct_used HEADING ‘Pct|Used’ FORMAT 9999 JUST RIGHT
COLUMN ini_trans HEADING ‘Ini|Tran’ FORMAT 9999 JUST RIGHT
COLUMN freelists HEADING ‘Free|lists’ FORMAT 99999 JUST RIGHT
COLUMN num_freelist_blocks HEADING ‘Blocks|on|Freelists’ FORMAT 999999 JUST RIGHT
COLUMN table_cached HEADING ‘Cache?’ FORMAT a6
COLUMN buffer_pool HEADING ‘Buffer|Pool’ FORMAT a7
COLUMN row_movement HEADING ‘Row|Move?’ FORMAT a5
COLUMN monitoring HEADING ‘Monitor?’ FORMAT a8
SELECT table_name,
tablespace_name,
logging,
pct_free,
pct_used,
ini_trans,
freelists,
num_freelist_blocks,
cache AS table_cached,
buffer_pool,
(CASE WHEN row_movement = ‘ENABLED’ THEN ‘YES’ ELSE ‘NO’ END) AS row_movement,
monitoring
FROM user_tables
WHERE table_name NOT LIKE ‘BIN$%’
ORDER BY table_name ASC
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting Table Analyze Information…
PROMPT
COLUMN table_name HEADING ‘Table|Name’ FORMAT a30
COLUMN analyzed_date HEADING ‘Analyzed|Date’ FORMAT a11
COLUMN sample_size HEADING ‘Sample|Size’ FORMAT 99999999999
COLUMN num_rows HEADING ‘Total|Rows’ FORMAT 99999999999
COLUMN avg_row_len HEADING ‘Avg|Row|Len’ FORMAT 99999
COLUMN blocks HEADING ‘Total|Blks’ FORMAT 9999999
COLUMN empty_blocks HEADING ‘Empty|Blks’ FORMAT 99999
COLUMN avg_space HEADING ‘Avg|Space’ FORMAT 999999D99
COLUMN chain_cnt HEADING ‘Chain|Count’ FORMAT 9999999
COLUMN chain_pct HEADING ‘Chain|PCT’ FORMAT 999D99
SELECT table_name,
TO_CHAR(last_analyzed, ‘DD-MON-YYYY’) AS analyzed_date,
sample_size,
num_rows,
avg_row_len,
blocks,
empty_blocks,
(avg_space/1024) AS avg_space,
chain_cnt,
(chain_cnt/(CASE WHEN num_rows = 0 THEN 1 ELSE num_rows END))*100 AS chain_pct
FROM user_tables
WHERE temporary=’N’
AND table_name NOT LIKE ‘BIN$%’
ORDER BY TRUNC(last_analyzed) DESC, ((chain_cnt/(CASE WHEN num_rows = 0 THEN 1 ELSE num_rows END))*100) DESC
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting Index Information…
PROMPT
COLUMN table_name HEADING ‘Index|Name’ FORMAT a30
COLUMN index_type HEADING ‘Index|Type’ FORMAT a9
COLUMN tablespace_name HEADING ‘Tablespace|Name’ FORMAT a15
COLUMN uniqueness HEADING ‘Unq?’ FORMAT a4
COLUMN logging HEADING ‘Log?’ FORMAT a4
COLUMN pct_free HEADING ‘Pct|Free’ FORMAT 9999 JUST RIGHT
COLUMN ini_trans HEADING ‘Ini|Tran’ FORMAT 9999 JUST RIGHT
COLUMN freelists HEADING ‘Free|lists’ FORMAT 99999 JUST RIGHT
COLUMN buffer_pool HEADING ‘Buffer|Pool’ FORMAT a7
COLUMN user_stats HEADING ‘User|Stats’ FORMAT a5
COLUMN status HEADING ‘Status’ FORMAT a8
SELECT index_name,
(CASE WHEN index_type = ‘FUNCTION-BASED NORMAL’ THEN ‘FB NORMAL’
WHEN index_type = ‘FUNCTION-BASED BITMAP’ THEN ‘FB BITMAP’
ELSE index_type END) AS index_type,
tablespace_name,
(CASE WHEN uniqueness = ‘UNIQUE’ THEN ‘YES’ ELSE ‘NO’ END) AS uniqueness,
logging,
pct_free,
ini_trans,
freelists,
buffer_pool,
user_stats,
status
FROM user_indexes
WHERE index_name NOT LIKE ‘BIN$%’
ORDER BY table_name, index_name
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting Index Analyze Information…
PROMPT
COLUMN table_name HEADING ‘Index|Name’ FORMAT a30
COLUMN analyzed_date HEADING ‘Analyzed|Date’ FORMAT a11
COLUMN sample_size HEADING ‘Sample|Size’ FORMAT 99999999999
COLUMN num_rows HEADING ‘Total|Rows’ FORMAT 99999999999
COLUMN blevel HEADING ‘BLvl’ FORMAT 9999
COLUMN leaf_blocks HEADING ‘Lf|Blks’ FORMAT 9999
COLUMN distinct_keys HEADING ‘Distinct|Keys’ FORMAT 99999999999
COLUMN clustering_factor HEADING ‘Cluster|Factor’ FORMAT 99999999999
SELECT index_name,
TO_CHAR(last_analyzed, ‘DD-MON-YYYY’) AS analyzed_date,
sample_size,
num_rows,
blevel,
leaf_blocks,
distinct_keys,
clustering_factor
FROM user_indexes
WHERE index_type=’NORMAL’
AND temporary=’N’
AND index_name NOT LIKE ‘BIN$%’
ORDER BY last_analyzed DESC
/
CLEAR COLUMNS

PROMPT
PROMPT Cache information on Sequences
PROMPT
COLUMN sequence_name HEADING ‘Sequence|Name’ FORMAT a30
COLUMN cache_size HEADING ‘Cache|Size’ FORMAT 999,999,999
SELECT /*+rule*/
sequence_name,
cache_size
FROM user_sequences
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting SKU Component Index Information on ITEM_MASTER and PIX_SYNC tables…
PROMPT
COLUMN table_name HEADING ‘Table Name’ form a30
COLUMN index_name HEADING ‘Index Name’ form a30
COLUMN column_name HEADING ‘Column|Name’ form a30
SELECT /*+rule*/
a.table_name,
a.index_name,
b.column_name,
(CASE WHEN a.uniqueness = ‘UNIQUE’ THEN ‘YES’ ELSE ‘NO’ END) AS uniqueness
FROM user_indexes a inner join user_ind_columns b on b.index_name = a.index_name
WHERE a.table_name in (‘ITEM_MASTER’, ‘PIX_SYNC’)
AND a.index_name NOT LIKE ‘BIN$%’
ORDER BY a.table_name, a.index_name, b.column_position ASC
/
CLEAR COLUMNS

PROMPT
PROMPT Collecting Locn_hdr Component Index Information On LOCN_HDR…
PROMPT
COLUMN table_name HEADING ‘Table Name’ form a30
COLUMN index_name HEADING ‘Index Name’ form a30
COLUMN column_name HEADING ‘Column|Name’ form a30
SELECT /*+rule*/
a.table_name,
a.index_name,
b.column_name,
(CASE WHEN a.uniqueness = ‘UNIQUE’ THEN ‘YES’ ELSE ‘NO’ END) AS uniqueness
FROM user_indexes a inner join user_ind_columns b on b.index_name = a.index_name
WHERE a.table_name = ‘LOCN_HDR’
AND a.index_name NOT LIKE ‘BIN$%’
ORDER BY a.table_name, a.index_name, b.column_position ASC
/
CLEAR COLUMNS

PROMPT
PROMPT DB Connection Info
PROMPT
COLUMN program HEADING ‘Program’ FORMAT a80
COLUMN CNT HEADING ‘Count’ FORMAT 999,999,999,999
SELECT program, COUNT(*) AS cnt
FROM v$session
WHERE username NOT IN(‘SYS’, ‘SYSTEM’)
GROUP BY program
HAVING COUNT(*) > 1
ORDER BY cnt DESC
/
CLEAR COLUMNS

PROMPT
PROMPT DB Objects with parallel degree set
PROMPT
COLUMN owner HEADING ‘Owner’ FORMAT a30
COLUMN table_name HEADING ‘Table Name’ FORMAT a30
COLUMN index_name HEADING ‘Index Name’ FORMAT a30
COLUMN degree HEADING ‘Degree’ FORMAT a10
COLUMN instances HEADING ‘Instances’ FORMAT a10
COLUMN name HEADING ‘Parallel Operations’ FORMAT a20
SELECT owner, table_name, DEGREE, INSTANCES
FROM dba_tables
WHERE (TRIM (DEGREE) NOT IN (‘1′,’0’) OR TRIM (INSTANCES) NOT IN (‘1′,’0’))
AND owner NOT IN (‘SYSTEM’,’SYS’,’OUTLN’,’DBSNMP’,’OPS$ORACLE’,’EXPORT_USER’,’IMPORT_USER’)
/

SELECT owner, index_name, degree, instances
FROM dba_indexes
WHERE (TRIM (DEGREE) NOT IN (‘1′,’0’) OR TRIM (INSTANCES) NOT IN (‘1′,’0’))
AND owner NOT IN (‘SYSTEM’,’SYS’,’OUTLN’,’DBSNMP’,’OPS$ORACLE’,’EXPORT_USER’,’IMPORT_USER’)
/

SELECT name,value FROM v$sysstat WHERE NAME LIKE ‘Parallel%’
/
CLEAR COLUMNS

PROMPT
PROMPT Segment Statistics
PROMPT
COLUMN statistic_name HEADING ‘Statistic|Name’ FORMAT a30
COLUMN object_name HEADING ‘Object|Name’ FORMAT a30
COLUMN object_type HEADING ‘Object|Type’ FORMAT a25
COLUMN value HEADING ‘Value’ FORMAT 999,999,999,999
SELECT statistic_name, object_name, object_type, value
FROM v$segment_statistics
WHERE value > 100
AND owner = USER
AND LOWER(statistic_name) IN (‘itl waits’, ‘buffer busy waits’)
ORDER BY statistic_name, value DESC
/
CLEAR COLUMNS

SPOOL OFF
EXIT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: