14) # "VERIFY LONG OPERATIONS"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "-- ${RED} VERIFY LONG OPERATIONS ${BLACK} --"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
if [ ${DBVERSION} = "12" ]
then
PDB=$(sqlplus -S /nolog <<EOF
conn / as sysdba
set pagesize 0 linesize 32767 feedback off verify off heading off echo off
SELECT NAME FROM V\$CONTAINERS ORDER BY CON_ID, NAME;
quit;
END)
# echo -e "\n${PDB}" | tail +2
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} -- SELECT YOUR DATABASE [ CDB$ROOT / PDB ] ${BLACK} --"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
select SET_PDB in ${PDB}
do
PPDB=${SET_PDB}
break 1
done
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} " -- YOUR DATABASE SELECTED IS: ${RED} ${PPDB} ${BLACK} --"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
sqlplus -S /nolog <<EOF
conn / as sysdba
alter session set container=${PPDB};
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
prompt # ACTIVE SESSIONS AND SQL STATEMENTS #
prompt ##############################################################
col USERNAME for a15
col SQL_TEXT for a100
col machine for a35
col osuser for a15
select S.USERNAME
, s.sid
, s.serial#
, s.machine
, s.osuser
, s.seconds_in_wait as "waiting seg"
, t.sql_id
, sql_text
from v\$sqltext_with_newlines t, V\$SESSION s
where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
order by s.sid, t.piece;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OBJECT_NAME for a30
select object_name
, object_type
, session_id
, type
, lmode
, request
, block
, ctime
from v\$locked_object, all_objects, v\$lock
where v\$locked_object.object_id = all_objects.object_id AND v\$lock.id1 = all_objects.object_id AND v\$lock.sid = v\$locked_object.session_id
order by session_id, ctime desc, object_name;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a35
col UNITS for a10
col MESSAGE for a80
col sql_id for a15
select distinct * from (select opname, target, sofar, sql_id, totalwork, units, elapsed_seconds, message from v\$session_longops order by start_time desc) where rownum <= 10;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a30
SELECT opname
, target
, osuser
, sl.sql_id
, sl.sql_hash_value
, elapsed_seconds
, time_remaining
FROM v\$session_longops sl
inner join v\$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL# WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col USERNAME for a15
col sql_fulltext for a75
col TARGET for a30
SELECT s.username
, sl.sid
, sq.executions
, sl.last_update_time
, sl.sql_id
, sl.sql_hash_value
, opname
, target
, elapsed_seconds
, time_remaining
, sq.sql_fulltext
FROM v\$session_longops sl
INNER JOIN v\$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v\$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col units for a15
col opname for a30
SELECT sql_id
, opname
, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') as started
, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now
, trunc(((((86400*(sysdate-start_time))/60)/60)/24)/7) weeks
, trunc((((86400*(sysdate-start_time))/60)/60)/24) days
, trunc(((86400*(sysdate-start_time))/60)/60)-24*(trunc((((86400*(sysdate-start_time))/60)/60)/24)) hours
, trunc((86400*(sysdate-start_time))/60)-60*(trunc(((86400*(sysdate-start_time))/60)/60)) minutes
, trunc(86400*(sysdate-start_time))-60*(trunc((86400*(sysdate-start_time))/60)) seconds
, sofar
, totalwork
, units
, round(elapsed_seconds/60/60,2) as seconds
, round(time_remaining/60/60,2) as remaining
FROM v\$session_longops
WHERE sofar != totalwork;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
prompt # ACTIVE SESSIONS AND SQL STATEMENTS #
prompt ##############################################################
col USERNAME for a15
col SQL_TEXT for a100
col machine for a35
col osuser for a15
select S.USERNAME
, s.sid
, s.serial#
, s.machine
, s.osuser
, s.seconds_in_wait as "waiting seg"
, t.sql_id
, sql_text
from v\$sqltext_with_newlines t, V\$SESSION s
where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
order by s.sid, t.piece;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OBJECT_NAME for a30
select object_name
, object_type
, session_id
, type
, lmode
, request
, block
, ctime
from v\$locked_object, all_objects, v\$lock
where v\$locked_object.object_id = all_objects.object_id AND v\$lock.id1 = all_objects.object_id AND v\$lock.sid = v\$locked_object.session_id
order by session_id, ctime desc, object_name;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a35
col UNITS for a10
col MESSAGE for a80
col sql_id for a15
select distinct * from (select opname, target, sofar, sql_id, totalwork, units, elapsed_seconds, message from v\$session_longops order by start_time desc) where rownum <= 10;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a30
SELECT opname
, target
, osuser
, sl.sql_id
, sl.sql_hash_value
, elapsed_seconds
, time_remaining
FROM v\$session_longops sl
inner join v\$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL# WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col USERNAME for a15
col sql_fulltext for a75
col TARGET for a30
SELECT s.username
, sl.sid
, sq.executions
, sl.last_update_time
, sl.sql_id
, sl.sql_hash_value
, opname
, target
, elapsed_seconds
, time_remaining
, sq.sql_fulltext
FROM v\$session_longops sl
INNER JOIN v\$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v\$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col units for a15
col opname for a30
SELECT sql_id
, opname
, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') as started
, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now
, trunc(((((86400*(sysdate-start_time))/60)/60)/24)/7) weeks
, trunc((((86400*(sysdate-start_time))/60)/60)/24) days
, trunc(((86400*(sysdate-start_time))/60)/60)-24*(trunc((((86400*(sysdate-start_time))/60)/60)/24)) hours
, trunc((86400*(sysdate-start_time))/60)-60*(trunc(((86400*(sysdate-start_time))/60)/60)) minutes
, trunc(86400*(sysdate-start_time))-60*(trunc((86400*(sysdate-start_time))/60)) seconds
, sofar
, totalwork
, units
, round(elapsed_seconds/60/60,2) as seconds
, round(time_remaining/60/60,2) as remaining
FROM v\$session_longops
WHERE sofar != totalwork;
quit;
EOF
fi
No comments:
Post a Comment