if [ "$LOGNAME" = "oracle" ]; then
SQLPLUS_CMD="/ as sysdba";
else
SQLPLUS_CMD="/ as sysdba";
fi
case $1 in
si)
if [ "$LOGNAME" = "oracle" ]; then
sqlplus "/ as sysdba"
else
sqlplus "/ as sysdba"
fi
;;
sim)
sqlplus "/ as sysdba"
;;
# res)
# sqlplus -s "$SQLPLUS_CMD" << EOF
# set linesize 140
# set pagesize 2000
# set serveroutput on
# alter session set cursor_sharing=force;
# exec PRC_RSCTL_M('$2',$3);
# exit;
#EOF
# ;;
ke)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pages 500
col process for a10
col username for a10
col program for a35
col event for a30
col MACHINE for a25
col OSUSER for a10
col status for a8
--select sid,serial#,username,program,osuser,sql_id,event from v\$session where event#='$2' and osuser <> 'aee';
select sid,process,username,OSUSER,program,MACHINE,sql_id,event,status,BLOCKING_SESSION B_sess from v\$session where event#='$2' order by program, sql_id;
--select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where event#='$2' and osuser <> 'aee';
select 'ps -ef |grep '||to_char(spid)||'|grep LOCAL=NO|awk ''{print" -9 "\$2}''|xargs kill' kill_sh from v\$process p,v\$session s
where s.paddr=p.addr and type='USER' and s.event#='$2' and osuser <> 'aee';
exit
EOF
;;
log)
version=`sqlplus -v | awk '{if(length !=0) print $3}' |cut -c 1,2`
if [ $version == 10 ];then
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 100 pages 300
select 'tail -1000f '||value || '/' || 'alert_'|| b.instance_name || '.log' from v\$parameter a,v\$instance b where name='background_dump_dest';
exit
EOF
else
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 100 pages 300
select 'tail -1000f '|| a.value|| '/' || 'alert_'|| b.instance_name ||'.log' from v\$diag_info a,v\$instance b where a.name='Diag Trace' ;
exit
EOF
fi
;;
asmfree)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 130 pagesize 1000
select group_number,name,state,total_mb/1024,free_mb/1024 from v\$asm_diskgroup ;
exit
EOF
;;
undo)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pages 150
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
show parameter undo
col tablespace_name for a30
select t.tablespace_name,total_GB,free_GB,round(100*(1-free_GB/total_GB),3)||'%'"used_ts%"
from (select tablespace_name,sum(bytes)/1024/1024/1024 total_GB from dba_data_files group by tablespace_name) t,
(select tablespace_name,sum(bytes)/1024/1024/1024 free_GB from dba_free_space group by tablespace_name) f
where t.tablespace_name=f.tablespace_name(+) and t.tablespace_name like '%UNDO%' order by tablespace_name;
select tablespace_name,status,sum(bytes/1024/1024/1024) GB from dba_undo_extents group by tablespace_name,status;
select u.begin_time,u.end_time,t.name "undo_tbs_name",u.undoblks "blocks_used",u.txncount "transactions",u.maxquerylen "longest query",
u.expblkreucnt "expired blocks" from v\$undostat u , v\$tablespace t where u.undotsn=t.ts# and rownum <21
order by undoblks desc ,maxquerylen;
exit
EOF
;;
hplan)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 150 pagesize 1000
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select sql_id,sql_fulltext from v\$sql where sql_id ='$2';
set line 300
col sql_id for a15
col operation for a20
col options for a15
col object_owner for a15
col object_name for a20
col timestamp for a20
select sql_id , plan_hash_value, id, operation, options, object_owner, object_name, depth, cost, timestamp
from dba_hist_sql_plan where sql_id='$2' and TIMESTAMP=(sysdate-3) ;
set line 200
select a.instance_number,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
from dba_hist_sqlstat a,dba_hist_snapshot b where a.snap_id=b.snap_id and a.sql_id='$2'
order by instance_number,snap_id;
exit
EOF
;;
active)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 300
set pagesize 9999
col MACHINE for a30
col program for a55
col username for a10
col process for a8
col SPID for a8
col status for a8
col osuser for a10
col last_work_time for a19
col event for a30
select a.SID,a.process,b.SPID,a.username,a.osuser,a.machine,a.program,a.sql_id,a.event,
--to_date(sysdate,'yyyy-mm-dd hh24:mi:ss') current_time,
a.last_call_et
from v\$session a,v\$process b
where a.PADDR=b.addr and a.TYPE='USER' and a.status='ACTIVE'
order by 10;
exit
EOF
;;
sess)
sqlplus -s "$SQLPLUS_CMD" << EOF
set lines 300 pages 100 trimo on trims on echo off verify off recsep off feedb 4
col ora_pid format 999999
col os_pid format a8
col sid format 99999
col inst_id for 9999
col program format a20
col osuser format a8
col EVENT for a30
col LOGON_TIME for a18
col username for a15
col machine for a20
select s.sid,s.inst_id, p.spid os_pid,s.event,s.sql_id,s.status,
substr( s.program, 1, 20 ) program,to_char( s.logon_time, 'yyyy-mm-dd hh24:mi' ) logon_time, s.LAST_CALL_ET ,s.username, s.machine, s.osuser,s.BLOCKING_SESSION
from gv\$process p, gv\$session s
where p.addr = s.paddr and s.inst_id=p.inst_id and sid=TO_NUMBER('$2');
exit
EOF
;;
ksid)
sqlplus -s "$SQLPLUS_CMD" << EOF
set lines 300 pages 100 trimo on trims on echo off verify off recsep off feedb 4
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'||chr(10)||'!kill -9 '||p.spid from v\$session s ,v\$process p where s.paddr=p.addr and s.sid=TO_NUMBER('$2');
exit
EOF
;;
tempfree)
sqlplus -s "$SQLPLUS_CMD" << EOF
SET LINESIZE 500
SET PAGESIZE 1000
col TABLESPACE_NAME for a20
col SUM(MB) for 999999999
col USED(MB) for 999999999
col FREE(MB) for 999999999
SELECT d.tablespace_name "TABLESPACE_NAME",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999999') "SUM(MB)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'999999999') "USED(MB)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "USED(%)",
(a.bytes-t.bytes)/1024/1024 "FREE(MB)",
d.status "Status"
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.contents like 'TEMPORARY'
order by 4 desc;
exit
EOF
;;
tempfile)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col name format a60
col file# format 9999
col size_mb format 99999
alter session set cursor_sharing=force;
select /* SHSNC */ /*+ RULE */
f.file#, F.NAME, TRUNC(f.BYTES/1048576,2) SIZE_MB , f.CREATION_TIME, status
FROM V\$TEMPFILE F,V\$TABLESPACE T
WHERE F.ts#=T.ts# AND T.NAME = NVL(UPPER('$2'),'SYSTEM')
order by f.CREATION_TIME;
exit
EOF
;;
highpara)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 150
col sql_t format a50;
select substr(sql_text, 1, 50) as sql_t,
trim(program),
min(sql_id),
count(*)
from (select sql_text, a.sql_id, program
from v\$session a, v\$sqlarea b
where a.sql_id = b.sql_id
and a.status = 'ACTIVE'
and a.sql_id is not null
union all
select sql_text, a.PREV_SQL_ID as sql_id, program
from v\$session a, v\$sqlarea b
where a.sql_id is null
and a.PREV_SQL_ID = b.sql_id
and a.status = 'ACTIVE')
group by substr(sql_text, 1, 50), trim(program)
order by 4 desc,3;
exit
EOF
;;
event)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 150 pages 999
col EVENT for a40
col WAIT_CLASS for a15
select inst_id, event#, event,WAIT_CLASS, count(*) from gv\$session where wait_class# <> 6 group by inst_id,
event#, event,WAIT_CLASS order by 1,5 desc;
exit
EOF
;;
size)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col owner format a10
col segment_name for a30
alter session set cursor_sharing=force;
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('$2')
AND ('$3' IS NULL OR UPPER(OWNER) = UPPER('$3'))
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('$2') AND
('$3' IS NULL OR UPPER(OWNER) = UPPER('$3'))
UNION
SELECT OWNER,SEGMENT_NAME FROM DBA_LOBS WHERE TABLE_NAME=upper('$2') AND
('$3' IS NULL OR UPPER(OWNER) = UPPER('$3')))
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;
exit
EOF
;;
idxdesc)
sqlplus -s "$SQLPLUS_CMD" << EOF
col index_name for a30
col table_name for a25
col column_name for a20
col status for a10
col INDEX_TYPE for a10
col owner for a10
col LAST_ANALYZED for a25
set line 200
select indexes.owner,columns.TABLE_NAME,columns.INDEX_NAME,columns.COLUMN_NAME,columns.COLUMN_POSITION site,indexes.INDEX_TYPE,
indexes.UNIQUENESS,indexes.status,indexes.BLEVEL,indexes.LEAF_BLOCKS,indexes.NUM_ROWS,indexes.PARTITIONED,to_char(indexes.LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
from dba_ind_columns columns,dba_indexes indexes
where columns.INDEX_OWNER = indexes.owner
and columns.INDEX_NAME = indexes.INDEX_NAME
and columns.table_name=upper('$2')
order by indexes.owner,columns.INDEX_NAME,COLUMN_POSITION;
exit
EOF
;;
tsfree)
sqlplus -s "$SQLPLUS_CMD" << EOF
SET LINESIZE 500
SET PAGESIZE 1000
col FREE_SPACE(M) for 999999999
col USED_SPACE(M) for 999999999
col TABLESPACE_NAME for a20
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)", FREE_SPACE "FREE_SPACE(M)",
case when FREE_SPACE=REA_FREE_SPACE then null else ROUND((1 - NVL(REA_FREE_SPACE, 0) / SPACE) * 100, 2) end "REA_USED_RATE(%)",
case when FREE_SPACE=REA_FREE_SPACE then null else REA_FREE_SPACE end "REA_FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
( SELECT F1.TABLESPACE_NAME, F1.FREE_SPACE-NVL(F2.FREE_SPACE,0) REA_FREE_SPACE,F1.FREE_SPACE
FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
) F1,
(SELECT TS_NAME TABLESPACE_NAME, ROUND(SUM(SPACE)*8/1024,2) FREE_SPACE
FROM DBA_RECYCLEBIN GROUP BY TS_NAME
) F2
WHERE F1.TABLESPACE_NAME=F2.TABLESPACE_NAME(+)
) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1 - NVL(REA_FREE_SPACE, 0) / SPACE DESC;
exit
EOF
;;
ctsfree)
sqlplus -s "$SQLPLUS_CMD" << EOF
SET LINESIZE 500
SET PAGESIZE 1000
col name for a20
col FREE_SPACE(M) for 999999999
col USED_SPACE(M) for 999999999
col TABLESPACE_NAME for a20
SELECT D.CON_ID,C.NAME,D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)", FREE_SPACE "FREE_SPACE(M)",
case when FREE_SPACE=REA_FREE_SPACE then null else ROUND((1 - NVL(REA_FREE_SPACE, 0) / SPACE) * 100, 2) end "REA_USED_RATE(%)",
case when FREE_SPACE=REA_FREE_SPACE then null else REA_FREE_SPACE end "REA_FREE_SPACE(M)"
FROM
(SELECT CON_ID,TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
FROM cdb_DATA_FILES GROUP BY CON_ID,TABLESPACE_NAME) D,
( SELECT F1.CON_ID,F1.TABLESPACE_NAME, F1.FREE_SPACE REA_FREE_SPACE,F1.FREE_SPACE
FROM
(SELECT CON_ID,TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM cdb_FREE_SPACE GROUP BY CON_ID,TABLESPACE_NAME
) F1) F,
(select name,con_id from v\$containers) c
WHERE D.CON_ID = F.CON_ID AND D.CON_ID = C.CON_ID AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1, 1 - NVL(REA_FREE_SPACE, 0) / SPACE DESC;
exit
EOF
;;
tablespace)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120 pagesize 1000
alter session set cursor_sharing=force;
select /* SHSNC */
TABLESPACE_NAME TS_NAME,INITIAL_EXTENT INI_EXT,NEXT_EXTENT NXT_EXT,
STATUS,CONTENTS, EXTENT_MANAGEMENT EXT_MGR,ALLOCATION_TYPE ALLOC_TYPE
FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME;
exit
EOF
;;
datafile)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120 pagesize 1000
col name format a60
col file# format 9999
col size_mb format 99999
alter session set cursor_sharing=force;
select /* SHSNC */ /*+ RULE */
f.file#, F.NAME, TRUNC(f.BYTES/1048576,2) SIZE_MB , f.CREATION_TIME, status
FROM V\$DATAFILE F,V\$TABLESPACE T
WHERE F.ts#=T.ts# AND T.NAME = NVL(UPPER('$2'),'SYSTEM')
order by f.CREATION_TIME;
exit
EOF
;;
sqltext)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter session set cursor_sharing=force;
set pagesize 1000
SELECT /* SHSNC */ SQL_TEXT FROM V\$SQLTEXT
WHERE SQL_ID = to_char('$2')
ORDER BY PIECE;
exit
EOF
;;
plan)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter session set cursor_sharing=force;
set linesize 150 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char('$2'),NULL));
exit
EOF
;;
lock)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120 pagesize 1000
col type format a12
col hold format a12
col request format a12
col BLOCK_OTHERS format a16
alter session set cursor_sharing=force;
select /* SHSNC */ /*+ RULE */
sid,
decode(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',
type) type,
decode(lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
to_char(lmode)) hold,
decode(request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
to_char(request)) request,
ID1,ID2,CTIME,
decode(block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global',
to_char(block)) block_others
from v\$lock
where type <> 'MR' and to_char(sid) = nvl('$2',to_char(sid)) ;
exit
EOF
;;
lockwait)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 180 pagesize 1000
col HOLD_SID format 99999
col WAIT_SID format 99999
col type format a20
col hold format a12
col request format a12
alter session set cursor_sharing=force;
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
;;
objlike)
sqlplus -s "$SQLPLUS_CMD" << 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
alter session set cursor_sharing=force;
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 ('$3' IS NULL OR UPPER(OWNER) = UPPER('$3'))
AND OBJECT_NAME LIKE UPPER('%$2%');
exit
EOF
;;
tablike)
sqlplus -s "$SQLPLUS_CMD" << 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
alter session set cursor_sharing=force;
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 ALL_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND ('$3' IS NULL OR UPPER(OWNER) = UPPER('$3'))
AND OBJECT_NAME LIKE UPPER('%$2%');
exit
EOF
;;
tstat)
sqlplus -s "$SQLPLUS_CMD" << 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';
alter session set cursor_sharing=force;
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 ALL_TABLES
WHERE UPPER(OWNER)=NVL(UPPER('$3'),OWNER) AND TABLE_NAME=UPPER('$2')
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 ALL_TAB_PARTITIONS
WHERE UPPER(TABLE_OWNER)=NVL(UPPER('$3'),TABLE_OWNER) AND TABLE_NAME=UPPER('$2');
exit
EOF
;;
istat)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col OWNER format a10
col lkey format 999
col dkey format 999
col lev format 99
col anaday format a10
alter session set cursor_sharing=force;
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 ALL_INDEXES
WHERE UPPER(TABLE_OWNER)=NVL(UPPER('$3'),TABLE_OWNER)
AND TABLE_NAME=UPPER('$2');
exit
EOF
;;
ipstat)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col OWNER format a10
col lkey format 999
col dkey format 999
col lev format 99
col anaday format a10
alter session set cursor_sharing=force;
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 ALL_IND_PARTITIONS
WHERE UPPER(INDEX_OWNER)=NVL(UPPER('$3'),INDEX_OWNER)
AND INDEX_NAME=UPPER('$2');
exit
EOF
;;
objsql)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col vers format 999
alter session set cursor_sharing=force;
SELECT /* SHSNC */
HASH_VALUE, OPEN_VERSIONS VERS,
SORTS, EXECUTIONS EXECS,
DISK_READS READS, BUFFER_GETS GETS,
ROWS_PROCESSED ROWCNT
FROM V\$SQL WHERE EXECUTIONS > 10 AND HASH_VALUE IN
(SELECT /*+ NL_SJ */ DISTINCT HASH_VALUE
FROM V\$SQL_PLAN WHERE OBJECT_NAME=UPPER('$2')
AND NVL(OBJECT_OWNER,'A')=UPPER(NVL('$3','A')));
exit
EOF
;;
longops)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 200
col username for a15
col machine for a10
col event for a30
col username for a15
col OSUSER for a10
Select logon.sid,sess.username,sess.osuser,
sess.machine,
sess.sql_id,sess.event,logon.sofar "sofar(blk)",logon.totalwork "totalwork(blk)",round(time_remaining/60,2) "remain_time(min)",
round(logon.sofar/totalwork * 100, 2) "Complete(%)"
From v\$session_longops logon,v\$session sess where logon.sid=sess.sid and logon.SERIAL#=sess.SERIAL# and time_remaining>0
order by 9 desc;
exit
EOF
;;
tran)
sqlplus -s "$SQLPLUS_CMD" << 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
alter session set cursor_sharing=force;
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
;;
depend)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
alter session set cursor_sharing=force;
SELECT /* SHSNC */ TYPE,REFERENCED_OWNER D_OWNER,
REFERENCED_NAME D_NAME,REFERENCED_TYPE D_TYPE,
REFERENCED_LINK_NAME DBLINK, DEPENDENCY_TYPE DEPEND
FROM ALL_DEPENDENCIES
WHERE
UPPER(OWNER) = NVL(UPPER('$3'),OWNER)
AND NAME = UPPER('$2');
SELECT /* SHSNC */ REFERENCED_TYPE TYPE,OWNER R_OWNER,
NAME R_NAME, TYPE R_TYPE,DEPENDENCY_TYPE DEPEND
FROM ALL_DEPENDENCIES
WHERE
UPPER(REFERENCED_OWNER) = NVL(UPPER('$3'),REFERENCED_OWNER)
AND REFERENCED_NAME = UPPER('$2')
AND REFERENCED_LINK_NAME IS NULL;
exit
EOF
;;
latch)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
alter session set cursor_sharing=force;
SELECT /* SHSNC */ NAME FROM V\$LATCHNAME WHERE LATCH#=TO_NUMBER('$2');
exit
EOF
;;
hold)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col USERNAME format a16
col MACHINE format a20
alter session set cursor_sharing=force;
SELECT /* SHSNC */ /*+ RULE */
S.SID,S.SERIAL#,P.SPID,S.USERNAME,
S.MACHINE,S.STATUS
FROM V\$PROCESS P, V\$SESSION S, V\$LOCKED_OBJECT O
WHERE P.ADDR = S.PADDR AND O.SESSION_ID=S.SID
AND S.USERNAME IS NOT NULL
AND O.OBJECT_ID=TO_NUMBER('$2');
exit
EOF
;;
sort)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col USERNAME format a12
col MACHINE format a16
col TABLESPACE format a10
alter session set cursor_sharing=force;
SELECT /* SHSNC */ /*+ ordered */
B.SID,B.SERIAL#,B.USERNAME,B.MACHINE,A.BLOCKS,A.TABLESPACE,
A.SEGTYPE,A.SEGFILE# FILE#,A.SEGBLK# BLOCK#
FROM V\$SORT_USAGE A,V\$SESSION B
WHERE A.SESSION_ADDR = B.SADDR
order by A.BLOCKS desc;
exit
EOF
;;
desc)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col name format a30
col nullable format a8
col type format a30
alter session set cursor_sharing=force;
select /* SHSNC D5 */
COLUMN_ID NO#,COLUMN_NAME NAME,
DECODE(NULLABLE,'N','NOT NULL','') NULLABLE,
(case
when data_type='CHAR' then data_type||'('||data_length||')'
when data_type='VARCHAR' then data_type||'('||data_length||')'
when data_type='VARCHAR2' then data_type||'('||data_length||')'
when data_type='NCHAR' then data_type||'('||data_length||')'
when data_type='NVARCHAR' then data_type||'('||data_length||')'
when data_type='NVARCHAR2' then data_type||'('||data_length||')'
when data_type='RAW' then data_type||'('||data_length||')'
when data_type='NUMBER' then
(
case
when data_scale is null and data_precision is null then 'NUMBER'
when data_scale <> 0 then 'NUMBER('||NVL(DATA_PRECISION,38)||','||DATA_SCALE||')'
else 'NUMBER('||NVL(DATA_PRECISION,38)||')'
end
)
else
( case
when data_type_owner is not null then data_type_owner||'.'||data_type
else data_type
end )
end) TYPE
from all_tab_columns
where upper(owner)=UPPER(nvl('$3',owner)) AND TABLE_NAME=upper('$2')
order by 1;
exit
EOF
;;
segment)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col USERNAME format a12
col MACHINE format a16
col TABLESPACE format a10
alter session set cursor_sharing=force;
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
;;
seqlike)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col owner format a12
col MAX_VALUE format 999999999999
alter session set cursor_sharing=force;
SELECT /* SHSNC */ SEQUENCE_OWNER OWNER,SEQUENCE_NAME,
MIN_VALUE LOW,MAX_VALUE HIGH,INCREMENT_BY STEP,CYCLE_FLAG CYC,
ORDER_FLAG ORD,CACHE_SIZE CACHE,LAST_NUMBER CURVAL
FROM ALL_SEQUENCES
WHERE ('$3' IS NULL OR UPPER(SEQUENCE_OWNER) = UPPER('$3'))
AND SEQUENCE_NAME LIKE UPPER('$2');
exit
EOF
;;
tabpart)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col USERNAME format a12
col MACHINE format a16
col TABLESPACE format a10
alter session set cursor_sharing=force;
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 ALL_TAB_PARTITIONS
WHERE ('$3' IS NULL OR UPPER(TABLE_OWNER) = UPPER('$3'))
AND TABLE_NAME LIKE UPPER('$2')
ORDER BY 1;
exit
EOF
;;
view)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col TYPE_NAME format a30
alter session set cursor_sharing=force;
SELECT /* SHSNC */ OWNER,VIEW_NAME,
DECODE(VIEW_TYPE_OWNER,NULL,NULL,VIEW_TYPE_OWNER||'.'||VIEW_TYPE) TYPE_NAME
FROM ALL_VIEWS
WHERE ('$3' IS NULL OR UPPER(OWNER) = UPPER('$3'))
AND VIEW_NAME LIKE UPPER('$2')
AND OWNER NOT IN ('SYS','SYSTEM','CTXSYS','WMSYS');
exit
EOF
;;
param)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col NAME format a40
COL VALUE FORMAT A40
alter session set cursor_sharing=force;
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
;;
_param)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col NAME format a40
COL VALUE FORMAT A40
alter session set cursor_sharing=force;
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
;;
grant)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col GRANTEE format a12
col owner format a12
col GRANTOR format a12
col PRIVILEGE format a20
COL VALUE FORMAT A40
alter session set cursor_sharing=force;
SELECT /* SHSNC */ * FROM DBA_TAB_PRIVS
WHERE (OWNER=NVL(UPPER('$3'),OWNER) or '$3' IS NULL)
AND TABLE_NAME LIKE UPPER('$2');
exit
EOF
;;
unusable)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 120
col GRANTEE format a12
col owner format a12
col GRANTOR format a12
col PRIVILEGE format a20
COL VALUE FORMAT A40
alter session set cursor_sharing=force;
SELECT /* SHSNC */
'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;' UNUSABLE_INDEXES
FROM ALL_INDEXES
WHERE (TABLE_OWNER=UPPER('$2') OR '$2' IS NULL) AND STATUS='UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX '||IP.INDEX_OWNER||'.'||IP.INDEX_NAME||' REBUILD PARTITION '
||IP.PARTITION_NAME||' ONLINE;'
FROM ALL_IND_PARTITIONS IP, ALL_INDEXES I
WHERE IP.INDEX_OWNER=I.OWNER AND IP.INDEX_NAME=I.INDEX_NAME
AND (I.TABLE_OWNER=UPPER('$2') OR '$2' IS NULL) AND IP.STATUS='UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX '||IP.INDEX_OWNER||'.'||IP.INDEX_NAME||' REBUILD SUBPARTITION '
||IP.PARTITION_NAME||' ONLINE;'
FROM ALL_IND_SUBPARTITIONS IP, ALL_INDEXES I
WHERE IP.INDEX_OWNER=I.OWNER AND IP.INDEX_NAME=I.INDEX_NAME
AND (I.TABLE_OWNER=UPPER('$2') OR '$2' IS NULL) AND IP.STATUS='UNUSABLE';
exit
EOF
;;
invalid)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 150
col owner format a12
col object_name format a30
col created format a20
col last_ddl_time format a19
alter session set cursor_sharing=force;
SELECT /* SHSNC */
OBJECT_ID, OWNER,OBJECT_NAME,OBJECT_TYPE,
to_char(created,'yy-mm-dd hh24:mi:ss') created,
to_char(LAST_DDL_TIME,'yy-mm-dd hh24:mi:ss') last_ddl_time
FROM DBA_OBJECTS
WHERE STATUS='INVALID' AND ('$2' IS NULL OR OWNER=UPPER('$2'));
exit
EOF
;;
ddl)
sqlplus -s "$SQLPLUS_CMD" << EOF
set long 49000
set longc 9999
set line 150
set pagesize 10000
alter session set cursor_sharing=force;
SELECT dbms_metadata.get_ddl(upper('$3'),upper('$4'),upper('$2')) from dual;
exit
EOF
;;
dx)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 400;
col "waiter" format a8;
col "w_Machine" format a15;
col "h_HOLDER" format a8;
col "h_Machine" format a8;
SELECT s1.username waiter,
s1.machine w_Machine,
w.sid w_sid,
s1.serial# w_serial#,
s1.SQL_ID w_sql_id,
P1.spid w_PID,
S1.INST_ID w_NSTANCE,
s2.username h_HOLDER,
s2.machine h_Machine,
h.sid h_sid,
s2.serial# h_serial#,
s2.sql_id h_spid,
p2.spid h_PID,
S2.INST_ID h_INSTANCE,
S2.PROCESS h_process
FROM gv\$process P1, gv\$process P2,
gv\$session S1, gv\$session S2,
gv\$lock w, gv\$lock h
WHERE
(((h.LMODE != 0) and (h.LMODE != 1)
and ((h.REQUEST = 0) or (h.REQUEST = 1)))
and (((w.LMODE= 1) or (w.LMODE = 0))
and ((w.REQUEST != 1) and (w.REQUEST != 0))))
and w.type = h.type
and w.id1 = h.id1
and w.id2 = h.id2
and w.sid != h.sid
and w.sid = S1.sid
and h.sid = S2.sid
and S1.EVENT ='enq: DX - contention'
AND S1.paddr = P1.addr
AND S2.paddr = P2.addr
order by waiter,h.CTIME;
exit
EOF
;;
hcost)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pagesize 1000;
col "program" format a40;
col "event" format a30
col "username" format a15;
select /*+ rule */ distinct sess.username,nvl(decode(nvl(sess.module,sess.program),'SQL*Plus',sess.program,sess.module),sess.machine||':'||sess.process) program,sess.sid,sess.sql_id,p.spid,sess.event,plan.cost from v\$session sess,v\$sql_plan plan,v\$process p where sess.sql_id=plan.sql_id and plan.id=0 and cost>$2 and sess.status='ACTIVE' and p.addr=sess.paddr order by cost desc;
exit
EOF
;;
get_kill_sh)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 120;
set pages 9999;
select 'ps -ef|grep '||to_char(spid)||'|grep LOCAL=NO|awk ''{print " -9 "\$2}''|xargs kill' kill_sh
from v\$process p,v\$session s
where s.paddr=p.addr
and s.sql_id='$2';
exit
EOF
;;
hsql)
sqlplus -s "$SQLPLUS_CMD" << EOF
col username for a10
col program for a50
col event for a30
set line 300 pagesize 1000
select s.username,s.program,s.sql_id,s.event,p.spid,sql.cpu_time/1000000/decode(EXECUTIONS,0,1,EXECUTIONS) cpu,sql.BUFFER_GETS/decode(EXECUTIONS,0,1,EXECUTIONS) buff
from v\$sql sql,v\$session s,v\$process p
where s.sql_id=sql.sql_id
and s.status='ACTIVE'
and WAIT_CLASS#<>6
and s.paddr=p.addr
order by 6 desc;
exit
EOF
;;
frag)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pagesize 1000
set pagesize 300
col table_name for a35
col owner for a6
col tab_size for 999999.999999
col safe_space for 999999.999999
select owner,table_name,blocks*8/1024 TAB_SIZE,(AVG_ROW_LEN*NUM_ROWS+INI_TRANS*24)/(BLOCKS*8*1024)*100 used_pct,((BLOCKS*8*1024)-(AVG_ROW_LEN*NUM_ROWS+INI_TRANS*24))/1024/1024*0.9 safe_space
from dba_tables
where (owner like '__YY' or owner like '__ZW' or owner='COMMON')
AND blocks>1024*10
and (AVG_ROW_LEN*NUM_ROWS+INI_TRANS*24)/(BLOCKS*8*1024)*100<50
order by 4;
exit
EOF
;;
tsql)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300;
set pagesize 300;
col module for a30;
col PARSING_SCHEMA_NAME for a10;
select to_char(a.begin_time,'yyyymmdd hh24:mi'),to_char(a.end_time,'yyyymmdd hh24:mi'),a.INSTANCE_NUMBER,a.PARSING_SCHEMA_NAME,a.MODULE,a.SQL_ID,a.BUFFER_GETS_DELTA,a.CPU_TIME_DELTA/b.VALUE*100 cpu_pct
from (
select * from(
select ss.snap_id,sn.BEGIN_INTERVAL_TIME begin_time,sn.END_INTERVAL_TIME end_time,sn.INSTANCE_NUMBER,PARSING_SCHEMA_NAME,MODULE,SQL_ID,BUFFER_GETS_DELTA,CPU_TIME_DELTA,
RANK() OVER (PARTITION BY ss.snap_id,sn.INSTANCE_NUMBER ORDER BY CPU_TIME_DELTA DESC)rank
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
where sn.SNAP_ID=ss.snap_id
and sn.BEGIN_INTERVAL_TIME between sysdate-$2/24 and sysdate
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER)
where rank<6) a,DBA_HIST_SYSSTAT b
where a.snap_id=b.snap_id
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER
and b.STAT_ID=3649082374
order by 1,3 asc,8 desc;
exit
EOF
;;
free_ext)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 200 pagesize 1000;
select t.TABLESPACE_NAME,sum(D.bytes)/1024/1024/1024 "表空间大小(G)",t.free "最大连续段大小(G)"
from (
select TABLESPACE_NAME,max(free_space)free
from (
select f.TABLESPACE_NAME,f.FILE_ID,BLOCK_ID,sum(f.BYTES)/1024/1024/1024 free_space
from dba_free_space f,dba_tablespaces t
where t.TABLESPACE_NAME =f.TABLESPACE_NAME
and t.ALLOCATION_TYPE='SYSTEM'
and t.contents<>'UNDO'
and t.TABLESPACE_NAME not in('SYSAUX','SYSTEM','USERS','TIVOLIORTS')
group by f.TABLESPACE_NAME,f.FILE_ID,BLOCK_ID
)t
group by t.TABLESPACE_NAME)t,dba_data_files d
where t.tablespace_name=d.tablespace_name
group by t.TABLESPACE_NAME,t.free
having t.free<2;
exit
EOF
;;
parttab)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 200;
COL "owner" format a10
col "column_name" format a10
col "object" format a10
col "partitioning_type" format a20
col "data_type" format a15
SELECT C.*,D.DATA_TYPE FROM (select a.owner, a.name, a.column_name,a.OBJECT_TYPE,b.PARTITIONING_TYPE from DBA_PART_KEY_COLUMNS a, DBA_PART_TABLES b where a.owner=b.owner and a.NAME=b.TABLE_NAME) C ,DBA_TAB_COLS D WHERE C.owner=D.OWNER AND C.name=D.TABLE_NAME and c.column_name=d.COLUMN_NAME and UPPER(C.OWNER)=UPPER('$2') AND D.TABLE_NAME=UPPER('$3');
exit
EOF
;;
sqltuning)
if [ $2 = ]; then
echo "you don't input the seconde variable sql_id,please input sql_id in \$sql"
exit
else
sqlplus / as sysdba <<EOF
SET LONG 10000000 LONGCHUNKSIZE 1000000 LINESIZE 150 pagesize 0 serveroutput on SIZE 1000000
DECLARE
my_task_name VARCHAR2(30);
v_sqlid VARCHAR2(50);
BEGIN
v_sqlid:='$2';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK
(sql_id=>v_sqlid,
scope => 'comprehensive',
time_limit=>60,
task_name=>'my_sql_tuning',
description => 'Tuning Task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning');
END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning') FROM dual;
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning');
EXIT;
EOF
fi
;;
vio)
interval=$2
count=$3
Plat=`uname`
case $Plat in
"SunOS")
printf "%s\t%s\t%s\t%s\n" "r/s" "w/s" "kr/s" "kw/s"
sar -d $interval $count|awk 'BEGIN {i=0;j=0;n=0;m=0}
{if ($1=="tty" && NR != 1)
{printf "%s\t%s\t%s\t%s\n",i,j,n,m;i=0;j=0;n=0;m=0}
else {i=i+$1;j=j+$2;n=n+$3;m=m+$4}}'
;;
"HP-UX")
printf "%s\t%s\t%s\n" `date +%T` "r+w/s" "k(r+w)/s"
sar -d $interval $count|grep -v disk|awk 'BEGIN {i=0;j=0}
{if ($1 ~ ":" && i != 0 && NR != 1)
{printf "%s\t%s\t%s\n",$1,i,j;i=$5;j=$6/2}
else {i=i+$4;j=j+$5/2}}'
;;
"AIX")
printf "%s\t%s\t%s\t%s\n" "time stamp" "io/s" "kr/s" "kw/s"
iostat -T $interval $count |egrep 'hdisk|tty'| awk -v Int=$interval 'BEGIN {io=0;kr=0;kw=0} {if ($1=="tty:" && NR != 1) {akr=kr/Int;akw=kw/Int;printf "%s\t%s\t%s\t%s\n",time,io,akr,akw;io=0;kr=0;kw=0} else {io=io+$3;kr=kr+$5;kw=kw+$6;time=$7}}'
;;
esac
;;
hang)
lnodesFound=0
lsnodes > /dev/null 2>&1
if [ $? = 0 ]; then
lnodesFound=1
else
olsnodes > /dev/null 2>&1
if [ $? = 0 ]; then
lnodesFound=1
fi
fi
if [ $lnodesFound = 1 ]; then
sqlplus -s "$SQLPLUS_CMD" << EOF
set echo off
set feedback off
set arraysize 4
set pagesize 0
set pause off
set linesize 200
set verify off
set head off
spool /tmp/hanganalyze.tmp
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
!sleep 60
oradebug -g all hanganalyze 3
!sleep 60
oradebug -g all hanganalyze 3
spool off
exit
EOF
else
sqlplus -s "$SQLPLUS_CMD" << EOF
set echo off
set feedback off
set arraysize 4
set pagesize 0
set pause off
set linesize 200
set verify off
set head off
spool /tmp/hanganalyze.tmp
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
!sleep 60
oradebug hanganalyze 3
!sleep 60
oradebug hanganalyze 3
spool off
exit
EOF
fi
;;
ksql)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pagesize 1000
col username for a15
col program for a40
col event for a30
col osuser for a15
col MACHINE for a20
col CLIENT_INFO for a20
select sid,serial#,username,program,osuser,MACHINE,CLIENT_INFO,sql_id,event from v\$session where sql_id='$2';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where sql_id='$2';
set heading off
col kill_sh for a90
select 'ps -ef |grep '||to_char(spid)||'|grep LOCAL=NO|awk ''{print" -9 "\$2}''|xargs kill' kill_sh from v\$process p,v\$session s
where s.paddr=p.addr and type='USER' and s.sql_id='$2';
exit
EOF
;;
unlocku)
echo "alter user $2 account unlock;"
echo "Can you confirm?[y/n]"
read answer
if [ ${answer}"TEST" != "yTEST" ]
then
exit;
fi
sqlplus -s "$SQLPLUS_CMD" << EOF
alter user $2 account unlock;
exit
EOF
;;
ant)
echo "exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'$2',tabname=>'$3',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);"
echo "Can you confirm?[y/n]"
read answer
if [ ${answer}"TEST" != "yTEST" ]
then
exit;
fi
sqlplus -s "$SQLPLUS_CMD" << EOF
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'$2',tabname=>'$3',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);
exit
EOF
;;
chpw)
echo "alter user $2 identified by $3;"
echo "Can you confirm?[y/n]"
read answer
if [ ${answer}"TEST" != "yTEST" ]
then
exit;
fi
sqlplus -s "$SQLPLUS_CMD" << EOF
alter user $2 identified by $3;
exit
EOF
;;
refplan)
echo "grant select on $2.$3 to system;"
echo "Can you confirm?[y/n]"
read answer
if [ ${answer}"TEST" != "yTEST" ]
then
exit;
fi
sqlplus -s "$SQLPLUS_CMD" << EOF
grant select on $2.$3 to system;
exit
EOF
;;
tcsql)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 200
col username for a15
col event for a35
col program for a20 truncate
col cpu_p for 99.99
select ta.*,round(ta.cpu_time/tb.total_cpu * 100,1) cpu_usage from
(select s.username,s.program,s.event,s.sql_id,sum(trunc(m.CPU)) CPU_TIME,count(*) sum
--,sum(m.PHYSICAL_READS) P_READ,sum(LOGICAL_READS) L_READ
from v\$sessmetric m ,v\$session s
where ( m.PHYSICAL_READS >100
or m.CPU>100
or m.LOGICAL_READS >100)
and m.SESSION_ID = s.SID
and m.SESSION_SERIAL_NUM = s.SERIAL#
and s.status = 'ACTIVE'
and username is not null
group by s.username,s.program,s.event,s.sql_id
order by 5 desc) ta,(select sum(cpu) total_cpu from v\$sessmetric) tb
where rownum < 11;
exit
EOF
;;
esess)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pages 150
col process for a10
col username for a10
col program for a35
col event for a30
col MACHINE for a15
col OSUSER for a10
col status for a8
select sid,process,username,OSUSER,program,MACHINE,sql_id,event,status,BLOCKING_SESSION B_sess from v\$session where event#=$2
order by program, sql_id;
exit
EOF
;;
largeobj)
sqlplus -s "$SQLPLUS_CMD" << EOF
set pages 10000 linesize 300
col owner for a10
col tablespace_name for a20
col segment_type for a20
col segment_name for a30
col PARTITION_NAME for a30
select * from (
select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_m
from dba_segments
where tablespace_name in (upper('$2'))
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 6 desc) where rownum<=20;
exit
EOF
;;
stab)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set pages 10000 linesize 300
col owner for a10
col table_name for a30
col partition_name for a30
select owner,table_name,partition_name,num_rows,blocks,avg_row_len,sample_size,last_analyzed,stattype_locked
from dba_tab_statistics where (owner,table_name) in
(select object_owner,object_name from v\$sql_plan where sql_id = '$2' and object_type = 'TABLE');
exit
EOF
;;
sind)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set pages 10000 linesize 300
col owner for a10
col table_name for a30
col INDEX_NAME for a30
col COLUMN_NAME for a15
col COL_PST for 99
select a.owner,a.table_name,b.INDEX_NAME,a.COLUMN_NAME,b.COLUMN_POSITION COL_PST,a.NUM_DISTINCT,a.DENSITY,
a.NUM_NULLS,a.last_analyzed from dba_tab_columns a,(
select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns
where (INDEX_OWNER,INDEX_NAME) in (
select object_owner,object_name from v\$sql_plan where sql_id = '$2' and object_type = 'INDEX')) b
where a.owner = b.TABLE_OWNER and a.table_name = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME
order by a.owner,a.table_name,b.INDEX_NAME,b.COLUMN_POSITION;
exit
EOF
;;
lockwaitinfo)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pages 150
col username for a10
col program for a35
col event for a30
col MACHINE for a15
col OSUSER for a10
col spid for a8
col process for a8
select distinct a.sid,a.process,c.spid,a.username,a.program,a.MACHINE,a.sql_id,
a.event,a.BLOCKING_SESSION,a.status from v\$session a ,v\$session b,v\$process c
where a.sid=b.BLOCKING_SESSION and a.paddr=c.addr
order by program, sql_id;
exit
EOF
;;
klocker)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set pages 10000 linesize 300
select inst_id,'ps -ef |grep '||spid||'|grep LOCAL=NO|awk ''{print" -9 "\$2}''|xargs kill' kill_statement
from gv\$process where (inst_id,addr) in (
select a.inst_id, a.paddr
from gv\$session a,(select /*+ rule */ inst_id,SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv\$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv\$lock where request>0) and BLOCK <> 0 ) b
where a.sid =b.sid and a.inst_id = b.inst_id);
exit
EOF
;;
dict)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 200
set pagesize 2000
col TABLE_NAME for a30
col COMMENTS for a150
select TABLE_NAME,COMMENTS from dict where lower(TABLE_NAME) like '%$2%' order by 1;
exit
EOF
;;
pxsess)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 300 pages 150
col username for a10
col program for a30
col event for a30
col MACHINE for a15
col OSUSER for a10
col status for a8
col run_time for a20
col p3 for 999999999999999999
select a.sid,a.process,a.username,a.OSUSER,a.program,a.MACHINE,a.sql_id,a.event,a.status,b.DEGREE,b.qcsid
,a.last_call_et,TO_CHAR (TRUNC (a.last_call_et / 3600, 0))||' '||'HRS '||
TO_CHAR (TRUNC ((a.last_call_et - TRUNC(a.last_call_et / 3600, 0) * 3600) / 60, 0) ) ||' MINS' run_time
--,a.p1,a.p2,a.p3
from v\$session a ,v\$px_session b
where a.sid=b.sid ;
exit
EOF
;;
histogram)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 180
set pagesize 100
col TABLE_NAME for a20
col COLUMN_NAME for a20
col DATA_TYPE for a20
col OWNER for a15
SELECT OWNER,TABLE_NAME, COLUMN_NAME,NUM_DISTINCT,
NUM_NULLS, DATA_TYPE, DATA_LENGTH, LAST_ANALYZED,NUM_BUCKETS,HISTOGRAM
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = upper('$2') order by OWNER,NUM_DISTINCT;
exit
EOF
;;
execs)
sqlplus -s "$SQLPLUS_CMD" << EOF
set lines 150 pages 999;
col p_user for 99999 ;
col execs for 9999999 ;
select a.INSTANCE_NUMBER inst_id,to_char(b.END_INTERVAL_TIME,'yyyy-mm-dd hh24') time,SQL_ID,plan_hash_value,
sum(EXECUTIONS_DELTA) execs from dba_hist_sqlstat a, dba_hist_snapshot b
where a.sql_id='$2'
and a.snap_id = b.snap_id and a.instance_number=1
group by a.INSTANCE_NUMBER,to_char(b.END_INTERVAL_TIME,'yyyy-mm-dd hh24') ,SQL_ID,plan_hash_value
order by 2;
exit
EOF
;;
cbak)
sqlplus -s "$SQLPLUS_CMD" << EOF
set line 280
set pagesize 300
col START_TIME format a20
col END_TIME format a20
col status for a30
col hrs format 999.99
select SESSION_KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME, 'yyyy/mm/dd hh24:mi:ss') start_time,
to_char(END_TIME, 'yyyy/mm/dd hh24:mi:ss') end_time,
elapsed_seconds / 3600 hrs,inPUT_BYTES/1024/1024/1024 "INPUT_BYTES(GB)",
outPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES(GB)" ,round(outPUT_BYTES/1024/1024/elapsed_seconds,2) "SPEED (M/S)"
from V\$RMAN_BACKUP_JOB_DETAILS
where START_TIME > sysdate -7
order by session_key desc;
exit
EOF
;;
btran)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 300
col username for a15
col sql_id for a15
col PROGRAM for a35
col MACHINE for a20
col name for a15
col used_ublk for a15
col event for a30
col USED_UNDO for a15
select * from (
SELECT s.sid, s.username, s.PROGRAM, s.MACHINE,s.sql_id, u.name, t.used_ublk * 8192 / 1024 / 1024 || 'M' as USED_UNDO
,s.event FROM v\$transaction t, v\$rollstat r, v\$rollname u, v\$session s
WHERE s.taddr = t.addr
AND t.xidusn = r.usn
AND r.usn = u.usn
ORDER BY 7 desc
)
where rownum < 11;
exit
EOF
;;
pmon)
ps -ef | grep pmon | grep -v grep|grep -v /bin/sh
;;
tns)
ps -ef | grep tns | grep -v grep|grep -v /bin/sh
;;
dgarch)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 200
set pagesize 999
col status for a10
col name for a80
select THREAD# , SEQUENCE#, name,APPLIED ,to_char(COMPLETION_TIME,'yyyy-mm-dd hh24:mi:ss') COMPLETION_TIME
,status from gv\$archived_log where APPLIED<>'YES' order by 1,2 ;
exit
EOF
;;
dgstat)
sqlplus -s "$SQLPLUS_CMD" << EOF
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A16
COLUMN DATUM_TIME FORMAT A24
SELECT INST_ID,NAME, VALUE, DATUM_TIME
FROM gV\$DATAGUARD_STATS;
exit
EOF
;;
dgproc)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 200
col status for a30
select process,thread#,status,SEQUENCE# from
gv\$managed_standby order by 1,2;
exit
EOF
;;
dgmode)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 200
col name for a20
select inst_id,NAME, OPEN_MODE,DATABASE_ROLE,protection_mode,protection_level
from gv\$database;
exit
EOF
;;
dgstart)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter database recover managed standby database using current logfile disconnect from session;
exit
EOF
;;
dgstop)
sqlplus -s "$SQLPLUS_CMD" << EOF
alter database recover managed standby database cancel;
exit
EOF
;;
dir)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 150
col OWNER for a20
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a80
select * from dba_directories;
exit
EOF
;;
patch)
sqlplus -s "$SQLPLUS_CMD" << EOF
set linesize 999
col ACTION_TIME for a30
col ACTION for a10
col NAMESPACE for a10
col COMMENTS for a20
col VERSION for a20
col BUNDLE_SERIES for a30
select to_char(ACTION_TIME,'yyyy-mm-dd hh24:mi:ss') ACTION_TIME,ACTION,
NAMESPACE,VERSION,ID,COMMENTS from dba_registry_history;
exit
EOF
;;
monlist)
if [ "$1" = "monlist" -o "$1" = "monitor_list" ]; then
if [ "$2" = "" ]; then
topn=50
else
topn=$2
fi
sqlplus -s /NOLOG << ! | egrep -v "Session altered|Connected|rows selected|successfully"
connect / as sysdba
set linesize 200
set pages 30
col sql_text format A60 trunc
--wrap
col inst_id format 99
col username format a10 trunc
col sql_id format a14
col date_start format a20
col status format a6
col sql_exec_id format 999999999
col px_req format 999
col px_real format 999
spool ora_monlist_$filedate.log
select * from
(
select m.inst_id,m.username,m.sql_id, to_char(sql_exec_start, 'yyyymmdd HH24:MI:SS') date_start,
substr(status,1,5) as status, sql_exec_id,px_servers_requested px_req,px_servers_allocated as px_real,
round(m.elapsed_time/1000000) "Elapsed(s)", round(m.CPU_TIME/1000000) "Cpu(s)"
, sql_text
from gv\$sql_monitor m
where m.process_name='ora'--m.PX_SERVER# is null
--order by elapsed_time desc
order by sql_exec_start desc
)
where rownum<=$topn;
$ECHO
spool off
!
echo "order by elapsed_time,top $topn only"
exit 0
fi
;;
monsave)
if [ "$1" = "monsave" -o "$1" = "savemon" ]; then
if [ "$2" = "" ]; then
echo "ora monitor sql_id"
exit 0
fi
#if [ "$3" = "" -o "$3" = "active" -o "$3" = "ACTIVE" ]; then
# type=ACTIVE
#else
# type=TEXT
#fi
if [ "$3" = "text" -o "$3" = "TEXT" ]; then
type=TEXT
else
type=ACTIVE
fi
FILTER='Session altered|Connected|rows selected|successfully|MONITOR_REPORT|---------------------'
sqlplus -s /NOLOG << ! | egrep -v "$FILTER"
connect / as sysdba
set echo off
set linesize 10000 pages 6000
set long 20000000
set longchunksize 20000000
set trimout on trims on term off head off
spool $2_$type.html
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'$2',
report_level=>'ALL',
type=>'$type') as report
from dual;
spool off
!
exit 0
echo "ora monsave sql_id text or ora monsave sql_id (active is default)"
fi
;;
tophis)
if [ "$DBUSER" = "" ]; then
DBUSER="/ as SYSDBA"
fi
if [ "$1" = "tophis" ]; then
if [ "$2" = "" -o "$2" = "cpu" -o "$2" = "CPU" ]; then
orderby=cpu_s
elif [ "$2" = "read" ]; then
orderby=reads_k
elif [ "$2" = "time" ]; then
orderby=etime_s
fi
if [ "$3" = "" -o "$4" = "" ]; then
defeid=`echo "select trim(12345||max(snap_id)) as eid from dba_hist_snapshot ;" | sqlplus -s $DBUSER|grep 12345|cut -c 6-15`
let defbid=defeid-1
os=`uname`
if [ "$os" = "Linux" ];then
read -p "Please input begin snap_id[$defbid]:" bid
else
read bid?"Please input begin snap_id[$defbid]:"
fi
if [ -z "${bid}" ];then
bid=$defbid
fi
os=`uname`
if [ "$os" = "Linux" ];then
read -p "Please input end snap_id[$defeid]:" eid
else
read eid?"Please input end snap_id[$defeid]:"
fi
if [ -z "${eid}" ];then
eid=$defeid
fi
if [ ${bid} -ge ${eid} ];then
echo begin_snap_id:$bid end_snap_id:$eid input error!
exit 1
fi
else
bid=$3
eid=$4
fi
if [ "$5" = "" ]; then
deftopn=50
os=`uname`
if [ "$os" = "Linux" ];then
read -p "Please input top sql count[$deftopn]:" topn
else
read topn?"Please input top sql count[$deftopn]:"
fi
if [ -z "${topn}" ];then
topn=$deftopn
fi
else
topn=$5
fi
### if [ "$3" = "" -o "$4" = "" ]; then
### echo "ora tophis cpu|read|time [begin_snapid] [end_snapid](default is last two snap) "
### bid="(select max(snap_id)-1 from dba_hist_snapshot)"
### eid="(select max(snap_id) from dba_hist_snapshot)"
### else
### bid=$3
### eid=$4
### fi
#echo "instance_number=$INST"
filedate=`date +"%Y%m%d"`
ECHO=""
DEFNODISP="--"
DEFDISP=""
INST="1"
sqlplus -s /NOLOG << ! | egrep -v "Session altered|Connected|rows selected"
connect / as sysdba
set linesize 200 pagesize 30
col rn format 999
col min_schema format a10 trunc
col sqltext format a30 trunc
col sql_id format a14
col plans format 99
col app_s format 999
col conc_s format 9999
col clu_s format 9999
col plsql_s format 99999
col sorts format 999999
spool ora_tophis_${bid}_${eid}_${orderby}.log
with sql_stat as
(
select rownum as RN,v.* from
(select
round(sum(cpu_time_delta)/1e6) as CPU_S,
round(sum(DISK_READS_DELTA)/1000) as reads_k,
sql_id,
round(sum(ELAPSED_TIME_DELTA)/1e6) as etime_s,
case when sum(ELAPSED_TIME_DELTA)/1e3/greatest(sum(executions_delta),1) >=1
then round(sum(ELAPSED_TIME_DELTA)/1e3/greatest(sum(executions_delta),1))
else 1 end as avg_elap_ms,
min(PARSING_SCHEMA_NAME) as min_schema,
sum(executions_delta) as execs,
count(distinct plan_hash_value) as plans,
--max(plan_hash_value) as phv,
round(sum(BUFFER_GETS_DELTA)/greatest(sum(executions_delta),1)) as avg_BUF,
round(sum(ROWS_PROCESSED_DELTA)/greatest(sum(executions_delta),1),1) as avg_ROW
--substr(min(MODULE),1,10) as module,
--round(sum(IOWAIT_DELTA)/1e6) as sum_IO_s,
$DEFNODISP ,round(sum(APWAIT_DELTA)/1e6) as App_s
$DEFNODISP ,round(sum(CCWAIT_DELTA)/1e6) as ConC_s
$DEFNODISP ,round(sum(CLWAIT_DELTA)/1e6) as CLu_s
$DEFNODISP ,round(sum(PLSEXEC_TIME_DELTA)/1e6) as PLSQL_s
--round(sum(JAVEXEC_TIME_DELTA)/1e6) as sum_JAVA_s,
--round(sum(DIRECT_WRITES_DELTA)) as sum_DIRECT_W,
--round(sum(PHYSICAL_READ_REQUESTS_DELTA)) as sum_PHY_R_Req,---not support in 10g
--round(sum(PHYSICAL_WRITE_BYTES_DELTA)) as PHYSICAL_WB,
--round(sum(PHYSICAL_WRITE_REQUESTS_DELTA)) as sum_PHY_W_Req,--not support in 10g
--round(sum(PX_SERVERS_EXECS_DELTA)) as sum_PX_Svr,
$DEFNODISP ,round(sum(SORTS_DELTA)) as SORTS
from dba_hist_sqlstat a
where snap_id > $bid
and snap_id <=$eid--(select max(snap_id) from dba_hist_snapshot)
and INSTANCE_NUMBER=$INST
group by sql_id
order by $orderby desc
) v where rownum<=$topn
)
select ltrim(nvl( b.sql_text, (' ** SQL Text Not Available ** '))) SqlText
,a.*
from sql_stat a,(select distinct sql_id,replace(replace(to_nchar(SUBSTR(sql_text,1,50)),chr(10),''),chr(13),'') as sql_text
from dba_hist_sqltext) b
where a.sql_id=b.sql_id
order by rn;
$ECHO
--select max(snap_id) as max_snap_id from dba_hist_snapshot;
spool off
!
echo "usage: ora tophis [cpu]|<read>|<time> bid eid"
echo "Get top sql ,ordered by $orderby . Begin snap_id=$bid ; End snap_id=$eid . full nodes"
exit 0
fi
;;
ashsql|dashsql)
#echo $(TZ=EST-7EDT date +"%Y%m%d %H%M%S") --- get prev 1 hour
if [ "$2" = "" ]; then
defbtime=$(TZ=EST-7EDT date +"%H:%M")
defbtime2=$(TZ=EST-7EDT date +"%Y/%m/%d %H:%M")
#defbtime=`date +'%H:%M'`
os=`uname`
if [ "$os" = "Linux" ];then
read -p "Please input begin time,datetime format is [$defbtime](default) or [$defbtime2] :" btime
else
read btime?"Please input begin time,datetime format is [$defbtime](default) or [$defbtime2] :"
fi
if [ -z "${btime}" ];then
btime=$defbtime
#echo "No input,exit"
#exit 1
fi
defdur=30
os=`uname`
if [ "$os" = "Linux" ];then
read -p "Please input duration time ,default is [$defdur]:" dur
else
read dur?"Please input duration time ,default is [$defdur]:"
fi
if [ -z "${dur}" ];then
dur=$defdur
fi
else
btime=$2
dur=$3
fi
strlen=`echo $btime|wc -c|tr -d " "`
#echo $strlen
#exit
if [ $strlen -ne 6 -a $strlen -ne 17 ]; then
echo "date parameter format error. usage: ora ashsql 08:10 25 or ora ashsql \"2020/01/09 22:39\" 30 "
#echo "ora ashsql 08:10 25"
#echo "ora -i 1 ashsql \"2020/01/09 22:39\" 30"
exit 1
fi
#08:10 strlen=6
if [ $strlen = 6 ]; then
starttime="to_date(to_char(sysdate,'yyyy/mm/dd ')||'$btime','yyyy/mm/dd hh24:mi')"
fi
#2020/01/09 22:39 strlen=17
if [ $strlen = 17 ]; then
starttime="to_date('$btime','yyyy/mm/dd hh24:mi')"
fi
#echo $starttime
sqlplus -s /NOLOG << ! | egrep -v "Session altered|Connected|rows selected"
connect / as sysdba
set pagesize 30
set linesize 150
col sql_id format a15
col event format a40
spool ora_ashsql_$filedate.log
Pro active_session_history :
select * from
(select inst_id,sql_id,nvl(event,session_state) as event,count(*) as cnt from gv\$active_session_history
where sample_time between
$starttime
and $starttime+$dur/60/24
and (sql_id is not null or event is not null)
group by inst_id,sql_id,nvl(event,session_state) having count(*)>=1
order by cnt desc
) where rownum<=20;
$ECHO
Pro dba_hist_active_sess_history :
select * from
(select instance_number as inst_id,sql_id,event,count(*) as cnt from dba_hist_active_sess_history
where sample_time between
$starttime
and $starttime+$dur/60/24
and (sql_id is not null or event is not null)
group by instance_number,sql_id,event having count(*)>=1
order by cnt desc
) where rownum<=20;
$ECHO
spool off
!
echo "ash and dash group by sql_id,evnet ,from $btime, last $dur ,top 20 sql with event"
echo "ora ashsql <begin_time> <duration> [20]"
exit 0
;;
*)
echo
echo "Usage:";
echo " ora keyword [value1 [value2]] ";
echo " -----------------------------------------------------------------";
echo " si -- Login as OS User";
echo " ke [event#] -- kill event";
echo " log -- tail alert*.log";
echo " undo -- v\$undostat";
echo " hplan [sql_id] -- dba_hist_sql_plan";
echo " highpara -- get hight pararllel module";
echo " active -- Get Active Session";
echo " size tabname [owner] -- Get Size of tables/indexes";
echo " idxdesc tabname owner -- Display index structure";
echo " tsfree [tsname] -- Get Tablespace Usage";
echo " ctsfree [tsname] -- Get CDB Tablespace Usage";
echo " tempfree [tsname] -- Get TempTablespace Usage";
echo " tablespace tsname -- Tablespace Information";
echo " datafile tsname -- List data files by tablespace";
echo " tempfile tsname -- List tempdata files by tablespace";
echo " sqltext SQL_ID -- Get SQL Text by hash value";
echo " plan SQL_ID -- Get Execute Plan by SQL_ID";
echo " lock [sid] -- Get lock information by sid";
echo " lockwait -- Get lock requestor/blocker";
echo " lockwaitinfo -- Get blocker session information";
echo " objlike pattern [owner] -- Get object by name pattern";
echo " tablike pattern [owner] -- Get table by name pattern";
echo " tstat tabname owner -- Get table statistics";
echo " istat tabname owner -- Get index statistics";
echo " ipstat indname owner -- Get index partition statistics";
echo " objsql objname owner -- Get SQLs by object name";
echo " longops -- Get long run query";
echo " tran -- Get all the transactions";
echo " btran -- Get big transactions use undo top 10";
echo " depend objname [owner] -- Get dependency information";
echo " latch latch# -- Get latch name by latch id";
echo " hold objectid -- Who have lock on given object?";
echo " sort -- Who is running sort operation?";
echo " desc tabname [owner] -- Describe Table Structure";
echo " segment [size] -- Segment large than given size";
echo " seqlike pattern [owner] -- Get sequence by name pattern";
echo " tabpart tabname [owner] -- List table partitions";
echo " view pattern [owner] -- List view by name pattern";
echo " param pattern -- List Oracle parameters";
echo " _param pattern -- List Oracle hidden parameters";
echo " grant objname [owner] -- Get grant information";
echo " unusable [owner] -- List unusable indexes";
echo " invalid [owner] -- List invalid objects";
echo " ddl owner object_type name ---get the create object sql";
echo " event -- List all wait event";
echo " dx -- List all dxlock wait";
echo " hcost cost_value -- Get session info of cost more than cost_value";
echo " get_kill_sh sql_id username -- Get kill OS spid of sql_id and username";
echo " free_ext -- Get the max contiguous free space of tablespace";
echo " tsql hours -- Get top5 sql for the last n hours";
echo " frag -- Get fragment table";
echo " parttab owner tabname -- Get partition_table column";
# echo " res cpu n -- get top top_value process of consume by cpu";
# echo " res io n -- get top top_value process of consume by io";
# echo " res buff n -- get top top_value process of consume by io";
# echo " res mem v -- get process of consume pga_memery than mem_value v";
# echo " res drgee v -- get process of parallel than degree_value v";
# echo " res all n -- get top top_value process of sum consume by resource";
echo " sqltuning sql_id -- get sql tuning suggestion";
echo " sess SID -- get session infomation ";
echo " ksid SID -- kill session by sid ";
echo " vio interval count -- get disk io sumary stat";
echo " asmfree -- Get ASM Usage";
echo " hang -- excute hanganalyze (level 3)";
echo " ksql -- Kill session by sql id";
echo " klocker -- Kill locker session";
echo " unlocku username -- unlock user";
echo " ant owner tabname -- Analyze table";
echo " chpw username password -- Change password";
echo " refplan owner tabname -- Refresh plan";
echo " tcsql -- Top sql by CPU usage ";
echo " esess event# -- get session infomation by event#";
echo " largeobj tsname -- Query a large object on a specific tablespace";
echo " stab sql_id -- According to the SQL_ID query table statistics";
echo " sind sql_id -- According to the SQL_ID query index statistics";
echo " patch -- get patch info"
echo " dict -- Query data dictionary";
echo " pxsess -- Query parallel session";
echo " histogram tabname -- Query histogram statistical information";
echo " execs sql_id -- Query sql exec count";
echo " cbak -- check rman backup info";
echo " dir -- get directories info";
echo " monlist [50] -- lists top n (def=50) the statements that have been monitored";
echo " monsave <sqlid> [active]|text --Spool to <sql_id>_active|text.htm";
echo " tophis cpu|read|time [def max-1] [max] --top sql in dba_hist_sqlstat: ora tophis <bid> <eid> (default is last snapshot)";
echo " ashsql \"14:15\" 20 -- gv_active_session_history and dba_hist_active_sess_history ,group by sql_id and event";
# echo " pmon -- check pmon process";
# echo " dgarch -- check adg archivelog applyed info";
# echo " dgstat -- check adg delay status";
# echo " dgproc -- check adg process status";
# echo " dgmode -- get database or adg mode info";
# echo " dgstart -- start adg applyed use current log";
# echo " dgstop -- stop adg applyed";
echo " ----------------------------------------------------------------";
echo
;;
esac
