get sql_id from sql_text
SYS@ AS SYSDBA> select * from dual;
D
-
X
SYS@AS SYSDBA> select /* MYCOMMENT */ * from dual;
D
-
X
SYS@ AS SYSDBA> COL SQL_TEXT format a45
SYS@AS SYSDBA> select /* MYCOMMENT1 */ sql_id, substr(sql_text,1,200) sql_text
from v$sql
where upper(sql_text) like '%MYCOMMENT%'
and sql_text not like '%/* MYCOMMENT1 */%' ;
2 3 4
SQL_ID SQL_TEXT
------------- ---------------------------------------------
d28gvf7mr4px4 select /* MYCOMMENT */ * from dual
SYS@AS SYSDBA>
Find Tablespace growth size in number of days.
set feed off
set pages 1000 lines 180
column “tablespace_name” heading “Tablespace | Name” format a20
select
to_char (sp.begin_interval_time,’dd-mm-yyyy’) days,
ts.tsname,
max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_mb,
max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_mb
from
dba_hist_tbspc_space_usage tsu,
dba_hist_tablespace_stat ts,
dba_hist_snapshot sp,
dba_tablespaces dt
where
tsu.tablespace_id= ts.ts#
and
tsu.snap_id = sp.snap_id
and
ts.tsname = dt.tablespace_name and
ts.tsname not in (‘SYSAUX’,’SYSTEM’) and ts.tsname=’INTERFACE’
group by
to_char (sp.begin_interval_time,’dd-mm-yyyy’), ts.tsname
order by ts.tsname, days;
to_char (sp.begin_interval_time,’dd-mm-yyyy’) days,
ts.tsname,
max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_mb,
max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_mb
from
dba_hist_tbspc_space_usage tsu,
dba_hist_tablespace_stat ts,
dba_hist_snapshot sp,
dba_tablespaces dt
where
tsu.tablespace_id= ts.ts#
and
tsu.snap_id = sp.snap_id
and
ts.tsname = dt.tablespace_name and
ts.tsname not in (‘SYSAUX’,’SYSTEM’) and ts.tsname=’INTERFACE’
group by
to_char (sp.begin_interval_time,’dd-mm-yyyy’), ts.tsname
order by ts.tsname, days;
—Query shows where purging done
set linesize 120
column name format a15
column variance format a20
alter session set nls_date_format=’yyyy-mm-dd’;
with t as (
select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and su.tablespace_id = ts.ts#
and ts.name =upper(‘&TABLESPACE_NAME’)
and ts.name = dt.tablespace_name )
select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,
case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb – b.used_size_gb)
when e.used_size_gb = b.used_size_gb then ‘***NO DATA GROWTH’
when e.used_size_gb < b.used_size_gb then ‘******DATA PURGED’ end variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;
=======================================
~~~~~~~~~~~~~~~~~~~~~~~~
set lines 150
set pages 900
col start for a20
col end for a20
col status format a11
col input_bytes_display format a10
col output_bytes_display format a10
col status_weight format 99999
SELECT
to_char(start_time,'YYYY.MM.DD HH24:MI:SS') "Start" ,
to_char(end_time,'YYYY.MM.DD HH24:MI:SS') "End",
status,
status_weight,
input_type,
input_bytes_display,
output_bytes_display
FROM
V$RMAN_BACKUP_JOB_DETAILS
WHERE
( start_time between (SYSDATE - 8) and (SYSDATE ))
order by
start_time;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This script will report on all backups – full, incremental and archivelog backups –
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This script will report all on full and incremental backups, not archivelog backups –
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col STATUS format a9ACKUP, Scripts
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RMAN Incremental Report Generation Query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set pages 100 lines 200 feedback off markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
spool c:\backup.html append
select host_name,instance_name from v$instance;
select
(select host_name from v$instance) AS "Host_NAME",
(Select name from v$database) as "DB_NAME",
start_time,end_time,elapsed_seconds/60/60 as "DURATION(HOURS)", INPUT_TYPE,
(r.status) as status,(b.incremental_level) as incremental_level
from v$RMAN_BACKUP_JOB_DETAILS r
inner join
(select distinct session_stamp,incremental_level from v$backup_set_details) b on
r.session_stamp = b.session_stamp where incremental_level is not null
and r.start_time > sysdate - 7
and INPUT_TYPE <>'ARCHIVELOG' order by 3;
spool off;
exit
**************blocking locks*****************
select * from dba_waiters
set linesize 132
select to_char( sysdate, 'DD-Mon-YY HH:MI' ) "Date and Time" from dual ;
select name "Database" from v$database ;
select blocking_session, sid, serial#, wait_class, seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session ;
exit ;
select (select username
from gv$session
where sid = a.sid) blocker
, a.sid
, ' is blocking '
, (select username
from gv$session
where sid = b.sid) blockee
, b.sid
from gv$lock a
, gv$lock b
where a.block = 1
-- and b.request= 0
and a.id1 = b.id1
and a.id2 = b.id2
select l1.sid, ' IS BLOCKING ', l2.sid
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
column oracle_username format a15
column os_user_name format a15
column object_name format a37
column object_type format a37
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID
No comments:
Post a Comment