script to get blocking Sessions details in Oracle


Though there are various ways to get info about blocking sessions, but i would like to have it documented here as well..

Sourcehttp://www.orskl.com/blocking-sessions-in-oracle/

1. A full query to get list of blocking sessions with BLOCKER, WAITER and OBJECT being blocked:
It works on Single Instance and RAC set-up as well.

col blk_sess format a11
col wtr_sess format a11
col blocker format a10
col waiter format a10
col duration format a9
col blocked_object format a50
select /*+ rule */
a.inst_id ||’,’ || a.sid || ‘,’ || a.serial# blk_sess,
a.username blocker,
h.type,
b.inst_id||’,’||b.sid || ‘,’ || b.serial# wtr_sess,
b.username waiter,
o.owner || ‘.’ || o.object_name ||
nvl2 (subobject_name, ‘.’ || subobject_name, null) blocked_object,
lpad (to_char (trunc (w.ctime / 3600)), 3, ‘0’) || ‘:’ ||
lpad (to_char (mod (trunc (w.ctime / 60), 60)), 2, ‘0’) || ‘:’ ||
lpad (to_char (mod (w.ctime, 60)), 2, ‘0’) duration
from gv$lock h, gv$lock w, gv$session a, gv$session b, dba_objects o
where h.block != 0
and h.lmode != 0
and h.lmode != 1
and w.request != 0
and w.id1 = h.id1
and w.id2 = h.id2
and h.sid = a.sid
and w.sid = b.sid and h.inst_id = a.inst_id
and decode (w.type, ‘TX’, b.row_wait_obj#,
‘TM’, w.id1)
= o.object_id
order by w.ctime desc;

2. A simple query to find out blocking in the database:

SELECT
l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions
FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2

3. To get limited information on blocking sessions you can use:

SELECT s1.username || ‘@’ || s1.machine
|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2

Advertisements

How to See hidden Parameters and remove them from SPFILE


sometimes when we set hidden parameters, ofcourse after considering the up and down sides of setting that parameter..If you want to see the value of that hidden parameter and you use The “show parameter” command in SQL*Plus Ideally  shows us only the regular parameters and excludes the hidden ones. It is important to know, though, that  only if a hidden parameter was explicitly set in the database, the “show parameter” will show it. It will not show any hidden parameter with a default untouched value.

Following script can be used to return the name of the  all (+hidden parameter also), its current value and its default value:

select par.ksppinm name,
val.ksppstvl value,
val.ksppstdf def_val
from x$ksppi par,
x$ksppcv val
where par.indx=val.indx
order by 1;

Note:  if you want to remove a hidden parameter, remove it. Don’t set it to its default value.

Instead of creating a pfile from spfile, removing  the entry and recreating the spfile OR just changing  the value to the default value you can use the below command to remove hidden parameter

alter system reset hidden_parameter_name scope=spfile sid='*';

Note: The “reset” command works only when writing to the spfile.

Listing only active archive log destinations


select name, value, null status, null error
from v$parameter
where name = 'log_archive_dest'
and value is not null
union all
select p.name, p.value, s.status, s.error
from v$parameter p , V$ARCHIVE_DEST_STATUS s
where name like 'log_archive_dest%'
and s.dest_name = upper(p.name)
and p.name not like '%state%'
and p.value is not null
and 'enable' = (
select lower(p2.value)
from v$parameter p2
where p2.name = substr(p.name,1,instr(p.name,'_',-1)) || 'state' || substr(p.name,instr(p.name,'_',-1))
)
union all
select p.name, p.value, s.status, s.error
from v$parameter p, V$ARCHIVE_DEST_STATUS s
where p.name like 'log_archive_dest_stat%'
and s.dest_name = upper(p.name)
and lower(p.value) = 'enable'
and (
select p2.value
from v$parameter p2
where name = substr(p.name,1,16) || substr(p.name,instr(p.name,'_',-1))
) is not null

NAME VALUE STATUS ERROR
-------------------------------------------------------------------------------- ------------------------------ --------- -----------------------------------------------------------------
log_archive_dest_1 LOCATION=/n02/oraarch1/orq152a VALID
 valid_for=(ONLINE_LOGFILES,al
 l_roles) db_unique_name=ORQ152
 A

log_archive_dest_2 SERVICE=orq152b LGWR ASYNC AFF VALID
 IRM REOPEN=10 NET_TIMEOUT=30 v
 alid_for=(online_logfiles,prim
 ary_role) db_unique_name=orq15
 2b


NAME VALUE STATUS ERROR
-------------------------------------------------------------------------------- ------------------------------ --------- -----------------------------------------------------------------
log_archive_dest_3 LOCATION=/n02/oraarch1/orq152a VALID
 _standby valid_for=(STANDBY_LO
 GFILES,STANDBY_ROLE) DB_UNIQUE
 _NAME=orq152a

reference: http://jkstill.blogspot.in/

Dba_Data_Files vs Dba_Segments vs Dba_Free_Space


Dba_data_files: The dba_data_files means total size of the data file.

DBA_SEGMENTS: The dba_segments means used size of the data file.

DBA_FREE_SPACE: The dba_free_space means free size of the data file.

DBA_DATA_FILES = DBA_SEGMENTS + DBA_FREE_SPACE + Oracle overhead (header, bitmap… in few KBs)

lets take an example:

SQL> CREATE TABLE sample_table (USERID NUMBER, USERNAME VARCHAR2(20), USERPWD VARCHAR2(10),EMAILID VARCHAR2(20), FIRSTNAME VARCHAR2(10), LASTNAME VARCHAR2(10), ISACTIVE NUMBER) tablespace TR;

Table created.

SQL> INSERT INTO sample_table
2 SELECT srl,
3 name,
4 pwd,
5 LOWER(SUBSTR(name, 1, 10)) || ‘@abc.com’,
6 SUBSTR(name, 1, 10),
7 SUBSTR(name, 11, 20),
8 1
9 FROM (
10 SELECT level srl,
11 dbms_random.string(‘U’, 20) name,
12 dbms_random.string(‘A’, 10) pwd
13 FROM DUAL
14 CONNECT BY LEVEL <= 1000);

1000 rows created.

SQL> commit;

Commit complete.

SQL> Select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name having tablespace_name=’TR’ order by 1;

TABLESPACE_NAME SUM(BYTES)/(1024*1024*1024)
—————————— —————————
TR                                                .09765625

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name having tablespace_name=’TR’ order by 1;

TABLESPACE_NAME SUM(BYTES)/(1024*1024*1024)
—————————— —————————
TR                                       .00012207

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name having tablespace_name=’TR’ order by 1;

TABLESPACE_NAME SUM(BYTES)/(1024*1024*1024)
—————————— —————————
TR                                      .096557617

Find size of a table and Associated segments ( index , lob )


COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
owner, table_name,segment_type, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, bytes ,segment_type
FROM dba_segments  WHERE segment_type = ‘TABLE’
UNION ALL
SELECT i.table_name, i.owner, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name AND   s.owner = i.owner
AND   s.segment_type = ‘INDEX’
UNION ALL
SELECT l.table_name, l.owner, s.bytes ,segment_type
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = ‘LOBSEGMENT’
UNION ALL
SELECT l.table_name, l.owner, s.bytes ,segment_type
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = ‘LOBINDEX’)
WHERE owner =’&owner’
and table_name=’&table’
GROUP BY owner,table_name,segment_type
ORDER BY SUM(bytes) desc
/

Enter value for owner: SYS
old 22: WHERE owner =’&owner’
new 22: WHERE owner =’SYS’
Enter value for table: T1
old 23: and table_name=’&table’
new 23: and table_name=’T1′

Flash Recovery Area Usage

OWNER TABLE_NAME SEGMENT_TYPE SIZE_GB
———- ——————————– —————— ———-
SYS           T1                     TABLE                                1.2
SYS            T1                     INDEX                                1

Reference: http://ora10gadmin.blogspot.sg/

Flash Recovery Area Usage


SET PAGESIZE 66;
SET LINESIZE 80;

REPHEADER PAGE CENTER ‘Flash Recovery Area Usage’;

COLUMN FILE_TYPE FORMAT a20;
COLUMN FILE_TYPE HEADING ‘File Type’;

COLUMN USED_MB HEADING ‘Used MBytes’;
COLUMN USED_MB FORMAT 9999999999.99;
COLUMN RECLAIMABLE_MB HEADING ‘Reclaimable Mbytes’;
COLUMN RECLAIMABLE_MB FORMAT 99999999999.99;

COLUMN NUMBER_OF_FILES HEADING ‘Number of files’;

BREAK ON REPORT
COMPUTE SUM LABEL ‘Totals:’ OF USED_MB RECLAIMABLE_MB ON REPORT;

SELECT
rau.file_type,
rfd.space_used * rau.percent_space_used / 1024 / 1024 as USED_MB,
rfd.space_reclaimable * rau.percent_space_reclaimable / 1024 / 1024 as RECLAIMABLE_MB,
rau.number_of_files as NUMBER_OF_FILES
FROM
v$recovery_file_dest rfd, v$flash_recovery_area_usage rau;

File Type       Used MBytes       Reclaimable Mbytes        Number of files
——————– ————– —————— —————
CONTROL FILE     .00                      .00                                     0
REDO LOG             .00                       .00                                     0
ARCHIVED LOG     1901.39              .00                                     5
BACKUP PIECE      13114.49               .00                                   1
IMAGE COPY            .00                      .00                                    0
FLASHBACK LOG .  00                       .00                                    0
FOREIGN ARCHIVED LOG .00          .00                                    0
AUXILIARY DATAFILE C .00               .00                                   0
OPY

————– ——————
Totals:                         15015.88           .00

8 rows selected.