orac

#!/bin/bash

# Program:

# Check Oracle Database Command.

# History:

# 2013/11/15 Liang Guojun First Release

case $1 in

#==============================================================================

# -help

#==============================================================================

-help)

echo " "

echo "Usage:";

echo " -help ---Print this help info."

echo "============================================="

echo " database general check"

echo "============================================="

echo " -log db <N> ---List database alert log last N rows."

echo " -active ---List active session."

echo " -longops ---List long run query."

echo " -tran ---List all the transactions."

echo " -param <pattern> ---List Oracle parameters."

echo " -param_ <pattern> ---List Oracle hidden parameters."

echo " -size <owner> <tabname> ---List size of tables/indexes."

echo " -segment <size> ---List Segment large than given size."

echo " -tbs info ---List tablespace basic information."

echo " -tbs rate ---List statistics tablespace usage rate."

echo " -tbs file ---List datafile."

echo " -objlike <owner> <pattern> ---List object by name pattern."

echo " -tablike <owner> <pattern> ---List table by name pattern."

echo " -tstat <owner> <tabname> ---List table statistics."

echo " -tabpart <owner> <tabname> ---List table partitions."

echo " -istat <owner> <tabname> ---List index statistics."

echo " -ipstat <owner> <tabname> ---List index partition statistics."

echo " -idxdesc <owner> <tabname> ---List index structure."

echo " -ddl <owner> <object_type> <name> ---List the create object sql."

echo " -flash ---List flashbackup infomation."

echo " -lock ---List lock infomation."

echo " -lockwait ---List lock wait infomation."

echo " -sqltext <SQL_ID> ---List SQL Text by SQL_ID."

echo " -sql spid <SPID> ---List SQL_ID/username/SQL_TEXT by OS spid."

echo " -sql plan <SQL_ID> [child_number] ---List SQL explain plan infomation."

echo " -sql hist <SQL_ID> ---List history of SQL explain plan infomation."

echo " -topevent ---List top event."

echo "============================================="

echo " RAC/ASM check"

echo "============================================="

echo " -asmfree ---List ASM disk user and free space."

echo " -adinfo ---List ASM disk comment information."

echo "============================================="

echo " Data Guard check"

echo "============================================="

echo " -dgarc ---List Data Guard archivelog applied status."

echo " -dgs ---List Data Guard status."

echo " -dgp ---List Data Guard process status."

echo " -dge ---List Data Guard archive dest error."

echo " -dggap ---List Data Guard GAP."

echo " -dglag ---List Data Guard lag."

echo " "

;;

#=====================

# End of -help

#=====================

#==============================================================================

# -log

#==============================================================================

-log)

case $2 in

#------------------------------------------------------------

# db

#------------------------------------------------------------

db)

sqlalert=`sqlplus -S / as sysdba <<EOF

col name format a18

col value format a55

select value from v\$diag_info where name ='Diag Trace';

exit;

EOF`

alertpath=`echo $sqlalert | cut -d " " -f 3`

export myoraclesido=$ORACLE_SID

tail -n $3 $alertpath/alert_$myoraclesido.log

;;

#---------------------

# End of adb

#---------------------

esac

;;

#=====================

# End of -log

#=====================

#==============================================================================

# -active

#==============================================================================

-active)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 999

col sid format 9999

col s# format 99999

col username format a10

col event format a30

col machine format a20

col p123 format a18

col wt format 999

col SQL_ID for a18

alter session set cursor_sharing=force;

SELECT /* XJ LEADING(S) FIRST_ROWS */

S.SID,

S.SERIAL# S#,

P.SPID,

NVL(S.USERNAME, SUBSTR(P.PROGRAM, LENGTH(P.PROGRAM) - 6)) USERNAME,

S.MACHINE,

S.EVENT,

S.P1 || '/' || S.P2 || '/' || S.P3 P123,

S.WAIT_TIME WT,

NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID

FROM V$PROCESS P, V$SESSION S

WHERE P.ADDR = S.PADDR

AND S.STATUS = 'ACTIVE'

AND P.BACKGROUND IS NULL;

exit;

EOF

;;

#=====================

# End of -active

#=====================

#==============================================================================

# -longops

#==============================================================================

-longops)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

col MESSAGE format a30

col opname for a20

col username for a20

select opname,TIME_REMAINING REMAIN,

ELAPSED_SECONDS ELAPSE,MESSAGE,

SQL_ID,sid,username

from v$session_longops where TIME_REMAINING >0;

exit;

EOF

;;

#=====================

# End of -longops

#=====================

#==============================================================================

# -tran

#==============================================================================

-tran)

sqlplus -S / as sysdba <<EOF

set linesize 120

col USERNAME format a12

col rbs format a12

col BLKS_RECS format a16

col START_TIME format a17

col LOGIO format 99999

col PHY_IO FORMAT 99999

COL CRGET FORMAT 99999

COL CRMOD FORMAT 99999

SELECT /* SHSNC */ /* RULE */

S.SID,S.SERIAL#,S.USERNAME, R.NAME RBS,

T.START_TIME,

to_char(T.USED_UBLK)||','||to_char(T.USED_UREC) BLKS_RECS ,

T.LOG_IO LOGIO,T.PHY_IO PHYIO,T.CR_GET CRGET,T.CR_CHANGE CRMOD

FROM V$TRANSACTION T, V$SESSION S,V$ROLLNAME R,

V$ROLLSTAT RS

WHERE T.SES_ADDR(+) = S.SADDR

AND T.XIDUSN = R.USN AND S.USERNAME IS NOT NULL

AND R.USN = RS.USN ;

exit;

EOF

;;

#=====================

# End of -tran

#=====================

#==============================================================================

# -param

#==============================================================================

-param)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

col NAME format a40

COL VALUE FORMAT A40

SELECT /* SHSNC */ NAME,ISDEFAULT,ISSES_MODIFIABLE SESMOD,

ISSYS_MODIFIABLE SYSMOD,VALUE

FROM V$PARAMETER

WHERE NAME LIKE '%' || LOWER('$2') || '%'

AND NAME <> 'control_files'

and name <> 'rollback_segments';

exit;

EOF

;;

#=====================

# End of -param

#=====================

#==============================================================================

# -param_

#==============================================================================

-param_)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

col NAME format a40

COL VALUE FORMAT A40

SELECT /* SHSNC */

P.KSPPINM NAME, V.KSPPSTVL VALUE

FROM SYS.X$KSPPI P, SYS.X$KSPPSV V

WHERE P.INDX = V.INDX

AND V.INST_ID = USERENV('Instance')

AND SUBSTR(P.KSPPINM,1,1)='_'

AND ('$2' IS NULL OR P.KSPPINM LIKE '%'||LOWER('$2')||'%');

exit;

EOF

;;

#=====================

# End of -param_

#=====================

#==============================================================================

# -size

#==============================================================================

-size)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

col owner format a10

col segment_name for a30

SELECT /*+ SHSNC */ OWNER,SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1048576 SIZE_MB,

MAX(INITIAL_EXTENT) INIEXT, MAX(NEXT_EXTENT) MAXEXT FROM DBA_SEGMENTS

WHERE SEGMENT_NAME = upper('$3')

AND ('$2' IS NULL OR UPPER(OWNER) = UPPER('$2'))

AND SEGMENT_TYPE LIKE 'TABLE%'

GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE

UNION ALL

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1048576 SIZE_MB,

MAX(INITIAL_EXTENT) INIEXT, MAX(NEXT_EXTENT) MAXEXT FROM DBA_SEGMENTS

WHERE (OWNER,SEGMENT_NAME) IN (

SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME=upper('$3') AND

('$2' IS NULL OR UPPER(OWNER) = UPPER('$2'))

UNION

SELECT OWNER,SEGMENT_NAME FROM DBA_LOBS WHERE TABLE_NAME=upper('$2') AND

('$2' IS NULL OR UPPER(OWNER) = UPPER('$2')))

GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;

exit;

EOF

;;

#=====================

# End of -size

#=====================

#==============================================================================

# -segment

#==============================================================================

-segment)

sqlplus -S / as sysdba <<EOF

set linesize 120

col USERNAME format a12

col MACHINE format a16

col TABLESPACE format a10

SELECT /* SHSNC */ /*+ RULE */

SEGMENT_TYPE,OWNER SEGMENT_OWNER,SEGMENT_NAME,

TRUNC(SUM(BYTES)/1024/1024,1) SIZE_MB

FROM DBA_SEGMENTS WHERE OWNER NOT IN ('SYS','SYSTEM')

GROUP BY SEGMENT_TYPE,OWNER,SEGMENT_NAME

HAVING SUM(BYTES) > TO_NUMBER(NVL('$2','100')) * 1048576

ORDER BY 1,2,3,4 DESC;

exit;

EOF

;;

#=====================

# End of -segment

#=====================

#==============================================================================

# -tbs

#==============================================================================

-tbs)

case $2 in

#------------------------------------------------------------

# info

#------------------------------------------------------------

info)

sqlplus -S / as sysdba <<EOF

set linesize 160

set pagesize 999

select a.tablespace_name,

a.type,

a.bigfile,

a.status,

a.extent_management,

a.segment_space_management

from ( select d.tablespace_name,

t.contents type,

t.status,

t.bigfile,

t.extent_management,

t.segment_space_management

from dba_data_files d, dba_tablespaces t

where d.tablespace_name = t.tablespace_name

group by d.tablespace_name,

t.contents,

t.status,

t.bigfile,

t.extent_management,

t.segment_space_management) a,

( select tablespace_name

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

union all

select a.tablespace_name,

a.type,

a.bigfile,

a.status,

a.extent_management,

a.segment_space_management

from ( select d.tablespace_name,

t.contents type,

t.status,

t.bigfile,

t.extent_management,

t.segment_space_management

from dba_temp_files d, dba_tablespaces t

where d.tablespace_name = t.tablespace_name

group by d.tablespace_name,

t.contents,

t.status,

t.bigfile,

t.extent_management,

t.segment_space_management) a,

( select tablespace_name

from v$temp_space_header

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+);

exit;

EOF

;;

#---------------------

# End of info

#---------------------

#------------------------------------------------------------

# rate

#------------------------------------------------------------

rate)

sqlplus -S / as sysdba <<EOF

set linesize 160

set pagesize 999

col TABLESPACE_NAME for a18

col TBS_TOTAL_MB for 9999999

col TBS_USED_MB for 9999999

col TBS_FREE_MB for 9999999

col TBS_RATE for a11

col EXTEND_MAX_MB for 9999999999999

col EXTEND_FREE_MB for 9999999999999

col EXTEND_RATE for a11

select a.tablespace_name,

round(current_size / 1024 / 1024, 1) TBS_TOTAL_MB,

round((current_size - b.free_bytes) / 1024 / 1024, 1) TBS_USED_MB,

round(b.free_bytes / 1024 / 1024, 1) TBS_FREE_MB,

round(((current_size - b.free_bytes) / current_size) * 100, 1) || '%' TBS_RATE,

round(a.max_size / 1024 / 1024, 1) EXTEND_MAX_MB,

round((a.max_size - (current_size - b.free_bytes)) / 1024 / 1024, 1) EXTEND_FREE_MB,

round(((current_size - b.free_bytes) / a.max_size) * 100, 1) || '%' EXTEND_RATE

from (select tablespace_name,

sum(ddf.bytes) current_size,

sum(case

when ddf.autoextensible = 'YES' THEN

DDF.MAXBYTES

ELSE

DDF.BYTES

END) max_size

from dba_data_files ddf

group by tablespace_name

union

select tablespace_name,

sum(ddf.bytes) current_size,

sum(case

when ddf.autoextensible = 'YES' THEN

DDF.MAXBYTES

ELSE

DDF.BYTES

END) max_size

from dba_temp_files ddf

group by tablespace_name) a,

(select dfs.tablespace_name, sum(dfs.bytes) free_bytes

from dba_free_space dfs

group by dfs.tablespace_name

union

select tfs.tablespace_name, sum(tfs.BYTES_FREE) free_bytes

from v$TEMP_SPACE_HEADER tfs

group by tfs.tablespace_name) b

where a.tablespace_name = b.tablespace_name(+);

exit;

EOF

;;

#---------------------

# End of rate

#---------------------

#------------------------------------------------------------

# file

#------------------------------------------------------------

file)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 9999

col file_id for 9999

col TABLESPACE_NAME for a20

col FILE_NAME for a70

col STATUS for a7

col FILE_STATUS for a10

col "AUTO" for a3

col "MB" for 999999999

select d.file_id,

d.tablespace_name,

t.status "STATUS",

d.file_name,

d.status FILE_STATUS,

d.AUTOEXTENSIBLE "AUTO",

d.BYTES / 1024 / 1024 "MB"

from dba_data_files d, dba_tablespaces t

where t.tablespace_name = d.tablespace_name

order by file_id;

exit;

EOF

;;

#---------------------

# End of file

#---------------------

#------------------------------------------------------------

# -tbs *

#------------------------------------------------------------

echo "commond not found!!!"

;;

esac

;;

#=====================

# End of -tbs

#=====================

#==============================================================================

# -objlike

#==============================================================================

-objlike)

sqlplus -S / as sysdba <<EOF

set linesize 120

col type format a16

col OWNER format a12

col status format a8

col CREATED format a10

col MODIFIED format a19

col OBJECT_NAME format a30

SELECT /* SHSNC */ OBJECT_TYPE TYPE,OBJECT_ID ID,OWNER,OBJECT_NAME,

TO_CHAR(CREATED,'YYYY/MM/DD') CREATED,

TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') MODIFIED,STATUS

FROM DBA_OBJECTS

WHERE OBJECT_TYPE IN ('CLUSTER','FUNCTION','INDEX',

'PACKAGE','PROCEDURE','SEQUENCE','SYNONYM',

'TABLE','TRIGGER','TYPE','VIEW')

AND ('$2' IS NULL OR UPPER(OWNER) = UPPER('$2'))

AND OBJECT_NAME LIKE UPPER('%$3%');

exit;

EOF

;;

#=====================

# End of -objlike

#=====================

#==============================================================================

# -tablike

#==============================================================================

-tablike)

sqlplus -S / as sysdba <<EOF

set linesize 120

col OWNER format a12

col status format a8

col CREATED format a10

col MODIFIED format a19

col OBJECT_NAME format a30

SELECT /* SHSNC */ OBJECT_ID ID,OWNER,OBJECT_NAME,

TO_CHAR(CREATED,'YYYY/MM/DD') CREATED,

TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') MODIFIED,STATUS

FROM DBA_OBJECTS

WHERE OBJECT_TYPE = 'TABLE'

AND ('$2' IS NULL OR UPPER(OWNER) = UPPER('$2'))

AND OBJECT_NAME LIKE UPPER('%$3%');

exit;

EOF

;;

#=====================

# End of -tablike

#=====================

#==============================================================================

# -tstat

#==============================================================================

-tstat)

sqlplus -S / as sysdba <<EOF

set linesize 150

col owner format a10

col partname format a30

col INIEXT format 99999

col nxtext format 99999

col avgspc format 99999

col ccnt format 999

col rowlen format 9999

col ssize format 9999999

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

SELECT /* SHSNC */

OWNER,NULL PARTNAME,

NUM_ROWS NROWS, BLOCKS, AVG_SPACE AVGSPC,CHAIN_CNT CCNT, AVG_ROW_LEN ROWLEN,

SAMPLE_SIZE SSIZE,LAST_ANALYZED ANADATE

FROM DBA_TABLES

WHERE UPPER(OWNER)=NVL(UPPER('$2'),OWNER) AND TABLE_NAME=UPPER('$3')

UNION ALL

SELECT /* SHSNC */

TABLE_OWNER OWNER,PARTITION_NAME PARTNAME,

NUM_ROWS NROWS, BLOCKS, AVG_SPACE AVGSPC,CHAIN_CNT CCNT, AVG_ROW_LEN ROWLEN,

SAMPLE_SIZE SSIZE,LAST_ANALYZED ANADATE

FROM DBA_TAB_PARTITIONS

WHERE UPPER(TABLE_OWNER)=NVL(UPPER('$2'),TABLE_OWNER) AND TABLE_NAME=UPPER('$3');

exit;

EOF

;;

#=====================

# End of -tstat

#=====================

#==============================================================================

# -tabpart

#==============================================================================

-tabpart)

sqlplus -S / as sysdba <<EOF

set linesize 120

col USERNAME format a12

col MACHINE format a16

col TABLESPACE format a10

SELECT /* SHSNC */ PARTITION_POSITION NO#,PARTITION_NAME,TABLESPACE_NAME TS_NAME,

INITIAL_EXTENT/1024 INI_K, NEXT_EXTENT/1024 NEXT_K,PCT_INCREASE PCT,

FREELISTS FLS, FREELIST_GROUPS FLGS

FROM DBA_TAB_PARTITIONS

WHERE ('$2' IS NULL OR UPPER(TABLE_OWNER) = UPPER('$2'))

AND TABLE_NAME LIKE UPPER('$3')

ORDER BY 1;

exit;

EOF

;;

#=====================

# End of -tabpart

#=====================

#==============================================================================

# -istat

#==============================================================================

-istat)

sqlplus -S / as sysdba <<EOF

set linesize 120

col OWNER format a10

col lkey format 999

col dkey format 999

col lev format 99

col anaday format a10

SELECT /* SHSNC */

TABLE_OWNER OWNER, INDEX_NAME,

BLEVEL LEV, LEAF_BLOCKS LBLKS,TRUNC(NUM_ROWS) NROWS,

DISTINCT_KEYS DROWS,

CLUSTERING_FACTOR CLSFCT,SAMPLE_SIZE SSIZE,

TO_CHAR(LAST_ANALYZED,'YYYY/MM/DD') ANADAY,

PARTITIONED PAR

FROM DBA_INDEXES

WHERE UPPER(TABLE_OWNER)=NVL(UPPER('$2'),TABLE_OWNER)

AND TABLE_NAME=UPPER('$3');

exit;

EOF

;;

#=====================

# End of -istat

#=====================

#==============================================================================

# -ipstat

#==============================================================================

-ipstat)

sqlplus -S / as sysdba <<EOF

set linesize 120

col OWNER format a10

col lkey format 999

col dkey format 999

col lev format 99

col anaday format a10

SELECT /* SHSNC */

PARTITION_NAME,

BLEVEL LEV, LEAF_BLOCKS LBLKS,TRUNC(NUM_ROWS) NROWS,

DISTINCT_KEYS DROWS,

CLUSTERING_FACTOR CLSFCT,SAMPLE_SIZE SSIZE,

TO_CHAR(LAST_ANALYZED,'YYYY/MM/DD') ANADAY

FROM DBA_IND_PARTITIONS

WHERE UPPER(INDEX_OWNER)=NVL(UPPER('$2'),INDEX_OWNER)

AND INDEX_NAME=UPPER('$3');

exit;

EOF

;;

#=====================

# End of -ipstat

#=====================

#==============================================================================

# -idxdesc

#==============================================================================

-idxdesc)

sqlplus -S / as sysdba <<EOF

SET linesize 500

set pagesize 999

col INDEX_COL FOR a30

col INDEX_TYPE FOR a22

col INDEX_NAME FOR a40

col table_name FOR a32

SELECT B.OWNER||'.'||B.INDEX_NAME INDEX_NAME,

A.INDEX_COL,B.INDEX_TYPE||'-'||B.UNIQUENESS INDEX_TYPE,B.PARTITIONED

FROM (SELECT TABLE_OWNER,TABLE_NAME,INDEX_NAME, SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')), 2) INDEX_COL

FROM (SELECT TABLE_OWNER, TABLE_NAME,INDEX_NAME, COLUMN_NAME,

ROW_NUMBER() OVER(PARTITION BY TABLE_OWNER, TABLE_NAME, INDEX_NAME

ORDER BY TABLE_OWNER, INDEX_NAME, COLUMN_POSITION, COLUMN_NAME) RN

FROM DBA_IND_COLUMNS

WHERE TABLE_NAME = UPPER('$3')

AND TABLE_OWNER = UPPER('$2'))

START WITH RN = 1

CONNECT BY PRIOR RN = RN - 1

AND PRIOR TABLE_NAME = TABLE_NAME

AND PRIOR INDEX_NAME = INDEX_NAME

AND PRIOR TABLE_OWNER = TABLE_OWNER

GROUP BY TABLE_NAME, INDEX_NAME, TABLE_OWNER

ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME

) A,

(SELECT * FROM DBA_INDEXES WHERE TABLE_NAME = UPPER('$3') AND TABLE_OWNER = UPPER('$2')) B

WHERE A.TABLE_OWNER = B.TABLE_OWNER

AND A.TABLE_NAME = B.TABLE_NAME

AND A.INDEX_NAME =B.INDEX_NAME;

exit;

EOF

;;

#=====================

# End of -idxdesc

#=====================

#==============================================================================

# -ddl

#==============================================================================

-ddl)

sqlplus -S / as sysdba <<EOF

set long 49000

set longc 9999

set line 150

set pagesize 10000

SELECT dbms_metadata.get_ddl(upper('$3'),upper('$4'),upper('$2')) from dual;

exit;

EOF

;;

#=====================

# End of -ddl

#=====================

#==============================================================================

# -flash

#==============================================================================

-flash)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 9999

col "ORDER_ " for 99

col NAME for a40

col VALUE for a48

WITH flashback_database_log AS

(SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes,

retention_target retention_target_minutes,

flashback_size / 1048576 flashback_size_mb,

estimated_flashback_size / 1048576 estimated_flashback_size_mb

FROM v$flashback_database_log),

flashback_database_logfile AS

(SELECT COUNT(*) logs,

SUM(BYTES / 1048576) size_mb,

MIN(first_time) oldest_log,

MAX(first_time) latest_log

FROM v$flashback_database_logfile),

flashback_usage AS

(SELECT file_type,

ROUND(mb_used, 2) mb_used,

ROUND(mb_reclaimable, 2) mb_reclaimable,

DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used,

DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable,

number_of_files,

total_mb db_recovery_file_dest_mb,

flashback_retention_target,

oldest_record,

ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec

FROM (SELECT SUM(DECODE(NAME,

'db_recovery_file_dest_size',

VALUE / 1048576,

0)) total_mb,

SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target

FROM v$parameter

WHERE NAME IN ('db_recovery_file_dest_size',

'db_flashback_retention_target')),

(SELECT 'FLASHBACKLOG' file_type,

NVL(SUM(BYTES) / 1048576, 0) mb_used,

sum(CASE

WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN

bytes / 1048576

ELSE

END) mb_reclaimable,

COUNT(*) number_of_files,

MIN(first_time) oldest_record

FROM (select bytes,

lead(first_time) over(order by first_time asc) last_time,

first_time

from v$flashback_database_logfile) fla_log,

(SELECT value value

FROM v$parameter

WHERE name = 'db_flashback_retention_target') tgt

UNION

SELECT 'BACKUPPIECE' file_type,

NVL(SUM(BYTES / 1048576), 0) mb,

SUM(CASE

WHEN dl.rectype = 13 THEN

(BYTES / 1048576)

ELSE

END) reclaimable_mb,

COUNT(*) no_of_files,

MIN(start_time) oldest_record

FROM v$backup_piece bp, x$kccagf dl

WHERE is_recovery_dest_file = 'YES'

AND deleted = 'NO'

AND bp.recid = dl.recid(+)

AND dl.rectype(+) = 13

UNION

SELECT 'ARCHIVELOG' file_type,

NVL(SUM(blocks * block_size) / 1048576, 0) mb,

SUM(CASE

WHEN dl.rectype = 11 THEN

(LOG.blocks * LOG.block_size / 1048576)

ELSE

END) reclaimable_mb,

COUNT(*) no_of_files,

MIN(first_time) oldest_record

FROM v$archived_log log, x$kccagf dl

WHERE deleted = 'NO'

AND is_recovery_dest_file = 'YES'

AND dl.recid(+) = log.recid

AND dl.rectype(+) = 11

UNION

SELECT 'ONLINELOG' file_type,

SUM(BYTES / 1048576) mb,

0 reclaimable,

COUNT(*) no_of_files,

MIN(first_time) oldest_record

FROM v$logfile lf,

(SELECT group#, BYTES, first_time

FROM v$standby_log

UNION

SELECT group#, BYTES, first_time FROM v$log) l

WHERE l.group# = lf.group#

AND lf.is_recovery_dest_file = 'YES'

UNION

SELECT 'IMAGECOPY',

NVL(SUM(blocks * (block_size / 1048576)), 0) mb,

0 reclaimable_mb,

COUNT(*) no_of_files,

MIN(creation_time) oldest_record

FROM v$datafile_copy

WHERE deleted = 'NO'

AND is_recovery_dest_file = 'YES'

UNION

SELECT 'CONTROLFILE',

NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb,

0 reclaimable,

COUNT(*) no_of_files,

NULL oldest_record

FROM v$controlfile

WHERE is_recovery_dest_file = 'YES'))

SELECT order_, NAME, VALUE

FROM (SELECT 0 order_, NAME, VALUE

FROM v$parameter

WHERE NAME LIKE 'db_recovery_file%'

UNION

SELECT 3,

'oldest flashback log (minutes)',

TO_CHAR(ROUND(oldest_log_minutes, 2))

FROM flashback_database_log

UNION

SELECT 1,

'retention target (minutes)',

UNION

SELECT 2,

'estimated size for flashback logs (MB)',

TO_CHAR(ROUND(retention_target_minutes, 2))

FROM flashback_database_log

TO_CHAR(ROUND(estimated_flashback_size_mb, 2))

FROM flashback_database_log

UNION

SELECT 2, 'Current flashback log count', TO_CHAR(logs)

FROM flashback_database_logfile

UNION

SELECT 3,

'Most recent flashback log (minutes)',

TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2))

FROM flashback_database_logfile

UNION

SELECT 4,

'Total size of all files in MB',

TO_CHAR(ROUND(SUM(mb_used), 2))

FROM flashback_usage

UNION

SELECT 5,

'Total size of reclaimable files in MB',

TO_CHAR(ROUND(SUM(mb_reclaimable), 2))

FROM flashback_usage

UNION

SELECT 6,

'unused space in MB',

TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used)))

FROM flashback_usage)

ORDER BY order_, NAME;

exit;

EOF

;;

#=====================

# End of -flash

#=====================

#==============================================================================

# -lock

#==============================================================================

-lock)

sqlplus -S / as sysdba <<EOF

set linesize 180

set pagesize 999

col OS_USER_NAME for a20

col USER_NAME for a16

col LOCK_TYPE for a16

col OBJECT for a20

col LOCK_MODE for a16

col OWNER for a16

col SID for 999999

col SERIAL_NUM for 99999

SELECT /*+ RULE */

LS.OSUSER OS_USER_NAME,

LS.USERNAME USER_NAME,

DECODE(LS.TYPE,

'RW',

'Row wait enqueue lock',

'TM',

'DML enqueue lock',

'TX',

'Transaction enqueue lock',

'UL',

'User supplied lock') LOCK_TYPE,

O.OBJECT_NAME OBJECT,

DECODE(LS.LMODE,

NULL,

'Row Share',

'Row Exclusive',

'Share',

'Share Row Exclusive',

'Exclusive',

NULL) LOCK_MODE,

O.OWNER,

LS.SID,

LS.SERIAL# SERIAL_NUM

FROM SYS.DBA_OBJECTS O,

(SELECT S.OSUSER,

S.USERNAME,

L.TYPE,

L.LMODE,

S.SID,

S.SERIAL#,

L.ID1,

L.ID2

FROM v$SESSION S, v$LOCK L

WHERE S.SID = L.SID) LS

WHERE O.OBJECT_ID = LS.ID1

AND O.OWNER <> 'SYS'

ORDER BY O.OWNER, O.OBJECT_NAME;

exit;

EOF

;;

#=====================

# End of -lock

#=====================

#==============================================================================

# -lockwait

#==============================================================================

-lockwait)

sqlplus -S / as sysdba <<EOF

set linesize 180

set pagesize 999

col HOLD_SID format 99999

col WAIT_SID format 99999

col type format a20

col hold format a12

col request format a12

SELECT /* SHSNC */ /*+ ORDERED USE_HASH(H,R) */

H.SID HOLD_SID,

R.SID WAIT_SID,

decode(H.type,

'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalidation',

'LS', 'Log Start or Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table',

'TC', 'Thread Checkpoint',

'SS', 'Sort Segment',

'JQ', 'Job Queue',

'PI', 'Parallel operation',

'PS', 'Parallel operation',

'DL', 'Direct Index Creation',

H.type) type,

decode(H.lmode,

0, 'None', 1, 'Null',

2, 'Row-S (SS)', 3, 'Row-X (SX)',

4, 'Share', 5, 'S/Row-X (SSX)',

6, 'Exclusive', to_char(H.lmode)) hold,

decode(r.request, 0, 'None',

1, 'Null', 2, 'Row-S (SS)',

3, 'Row-X (SX)', 4, 'Share',

5, 'S/Row-X (SSX)',6, 'Exclusive',

to_char(R.request)) request,

R.ID1,R.ID2,R.CTIME

FROM V$LOCK H,V$LOCK R

WHERE H.BLOCK = 1 AND R.REQUEST > 0 AND H.SID <> R.SID

and H.TYPE <> 'MR' AND R.TYPE <> 'MR'

AND H.ID1 = R.ID1 AND H.ID2 = R.ID2 AND H.TYPE=R.TYPE

AND H.LMODE > 0 AND R.REQUEST > 0 ORDER BY 1,2;

exit;

EOF

;;

#=====================

# End of -lockwait

#=====================

#==============================================================================

# -sqltext

#==============================================================================

-sqltext)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

SELECT /* SHSNC */ SQL_TEXT FROM V$SQLTEXT

WHERE SQL_ID = to_char('$2')

ORDER BY PIECE;

exit;

EOF

;;

#=====================

# End of -sqltext

#=====================

#==============================================================================

# -sql

#==============================================================================

-sql)

case $2 in

#------------------------------------------------------------

# spid

#------------------------------------------------------------

spid)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 999

col username for a18

col sql_text for a80

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select ses.username, ses.LOGON_TIME, sql.sql_text

from v$session ses, v$process pro, v$sql sql

where ses.paddr = pro.addr

and ses.SQL_HASH_VALUE = sql.HASH_VALUE

and ses.SQL_ADDRESS = sql.address

and pro.spid = '$3';

exit;

EOF

;;

#---------------------

# End of spid

#---------------------

#------------------------------------------------------------

# plan

#------------------------------------------------------------

plan)

sqlplus -S / as sysdba <<EOF

set pagesize 999

set linesize 180

alter session set STATISTICS_LEVEL = ALL;

select * from table(dbms_xplan.display_cursor('$3',$4,'advanced'));

exit;

EOF

;;

#---------------------

# End of plan

#---------------------

#------------------------------------------------------------

# hist

#------------------------------------------------------------

hist)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

set linesize 140

col plan_hash_value format 9999999999

col id format 999

col operation format a16

col options format a8

col object_owner format a10

col object_name format a30

col optimizer format a10

col cost format 9999999

col access_predicates format a10

col filter_predicates format a10

SELECT plan_hash_value,

id,

LPAD (' ', DEPTH) || operation operation,

options,

object_owner,

object_name,

optimizer,

cost,

access_predicates,

filter_predicates

FROM dba_hist_sql_plan

WHERE sql_id = '$3'

ORDER BY plan_hash_value, id;

exit;

EOF

;;

#---------------------

# End of hist

#---------------------

esac

;;

#=====================

# End of -sql

#=====================

#==============================================================================

# -topevent

#==============================================================================

-topevent)

sqlplus -S / as sysdba <<EOF

set linesize 120

col "Event" for a34

col "Waits" for 9999999999999

col "Time" for 9999999999999

col "Avg" for 999999999

col pctwa for 999999

col "Wait Class" for a20

SELECT event "Event", totwa "Waits", twsec "Time", avgms "Avg",

ROUND(RATIO_TO_REPORT(twsec) OVER () * 100,1) pctwa , wclas "Wait Class"

FROM

(SELECT

NVL(substr(event,1,30),' ') event,

AVG(total_waits) totwa,

ROUND(AVG(time_waited_micro)/1000000, 0) twsec,

ROUND(SUM(time_waited_micro)/

SUM(total_waits)/1000, 2) avgms,

NVL(substr(wait_class,1,13),' ') wclas

FROM Gv$SYSTEM_EVENT

WHERE WAIT_CLASS <> 'Idle'

GROUP BY event, wait_class

HAVING SUM(total_waits) > 0

UNION ALL

SELECT

'CPU time' event,

0 totwa,

AVG(icput) twsec,

0 avgms,

' ' wclas

FROM

(

select inst_id, sum(VALUE)/100 icput

FROM Gv$SYSSTAT

WHERE name in ('CPU used when call started',

'CPU used by this session')

group by inst_id) ORDER BY twsec desc) WHERE ROWNUM <6;

exit;

EOF

;;

#=====================

# End of -topevent

#=====================

#==============================================================================

# -adinfo

#==============================================================================

-adinfo)

for i in `asmcmd lsdg |grep -v Name | awk -F' ' '{print $NF}'|cut -d/ -f1`; do echo "";echo "Diskgroup : $i";asmcmd lsattr -l -G $i ;done

;;

#=====================

# End of -adinfo

#=====================

#==============================================================================

# -asmfree

#==============================================================================

-asmfree)

sqlplus -S / as sysdba <<EOF

set linesize 120

set pagesize 999

col name for a20

col total_mb for 9999999999999

col free_mb for 9999999999999

col pct_used for 999999999

select name, total_mb/1024, free_mb/1024, ROUND((1- (free_mb / total_mb))*100, 2) pct_used from v$asm_diskgroup;

exit;

EOF

;;

#=====================

# End of -asmfree

#=====================

#==============================================================================

# -dgarc

#==============================================================================

-dgarc)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 999

col SEQUENCE# for 999999

col APPLIED for a10

col ARCHIVED for a8

col FIRST_TIME for a20

col NEXT_TIME for a20

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from (select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG order by 1 desc) where rownum<40 order by 1;

exit;

EOF

;;

#=====================

# End of -dgarc

#=====================

#==============================================================================

# -dgs

#==============================================================================

-dgs)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 999

col PROCESS for a10

col DB_UNIQUE_NAME for a12

col DATABASE_ROLE for a12

col OPEN_MODE for a12

col PROTECTION_MODE for a20

col PROTECTION_LEVEL for a20

col SWITCHOVER_STATUS for a20

SELECT DB_UNIQUE_NAME,DATABASE_ROLE , OPEN_MODE,

PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS

FROM V$DATABASE;

col STATUS for a20

col CURRENT_TARGET for a20

col THRESHOLD for 9999999999

col PRESENT for a20

SELECT FS_FAILOVER_STATUS STATUS, FS_FAILOVER_CURRENT_TARGET CURRENT_TARGET, FS_FAILOVER_THRESHOLD THRESHOLD,

FS_FAILOVER_OBSERVER_PRESENT PRESENT

FROM V$DATABASE;

exit;

EOF

;;

#=====================

# End of -dgs

#=====================

#==============================================================================

# -dgp

#==============================================================================

-dgp)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 999

col PROCESS for a10

col CLIENT_PROCESS for a10

col SEQUENCE# for 999999999

col STATUS for a15

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

exit;

EOF

;;

#=====================

# End of -dgp

#=====================

#==============================================================================

# -dge

#==============================================================================

-dge)

sqlplus -S / as sysdba <<EOF

set linesize 140

set pagesize 999

col dest_id for 999

col error for a80

SELECT DEST_ID,ERROR FROM V$ARCHIVE_DEST where rownum<3;

exit;

EOF

;;

#=====================

# End of -dge

#=====================

#==============================================================================

# -dggap

#==============================================================================

-dggap)

sqlplus -S / as sysdba <<EOF

set lines 200 pages 100

column db_name format a9

column database_role format a13

column thread# format 9999

break on thread# skip 1

select db_name,thread#,database_role,sequence# from

(select name db_name from v$database),

(select 'Primary' database_role,thread#,sequence# from (select unique thread#, max(sequence#) over (partition by thread#) as sequence# from v$archived_log where standby_dest='NO' and archived='YES'))

union all

select db_name,thread#,database_role,sequence# from

(select name db_name from v$database),

(select 'Standby' database_role,thread#,sequence# from (select unique thread#, max(sequence#) over (partition by thread#) as sequence# from v$archived_log where standby_dest='YES' and archived='YES'))

order by thread#,database_role;

exit;

EOF

;;

#=====================

# End of -dggap

#=====================

#==============================================================================

# -dglag

#==============================================================================

-dglag)

sqlplus -S / as sysdba <<EOF

set lines 200 pages 100

column value format a26

select value from v$dataguard_stats where name='apply lag';

exit;

EOF

;;

#=====================

# End of -dglag

#=====================

#==============================================================================

# $1 *

#==============================================================================

echo " "

echo "commond not found!!!"

echo "use 'orac -help' learn more about usage"

echo " "

;;

esac