ORACLE Application Experience (2)

The ORACLE tutorial is: ORACLE Application Experience (2 ). ------ Who is accessing the database?
Select c. sid, c. serial #, c. username, a. object_id, B. object_name,
C. program, c. status, d. name, c. osuser
From v $ Locked_object,
All_objects B,
V $ session c,
Audit_actions d
Where a. object_id = B. object_id
And a. session_id = c. sid (+)
And c. command = d. action;

Alter system kill session & 1, & 2;

Select a. sid, a. serial #, a. username, a. status, a. program, B. name, a. osuser
From v $ session a, audit_actions B
Where a. command = B. action
And username = & 1;
------ Who is locked?
Select a. sid, a. serial #, a. username, A. LOCKWAIT, a. status, a. program, B. name
From v $ session a, audit_actions B
Where a. command = B. action
And lockwait is not null;
------ Who is locking the table?
Select a. sid, a. serial #, a. username, A. LOCKWAIT, a. status, a. program, B. name
From v $ session a, audit_actions B
Where a. command = B. action
And status = ACTIVE;

Select sid, serial #, object_name, row_wait_block #,
Row_wait_row #, row_wait_file #
From all_objects, v $ session
Where row_wait_obj # = object_id and type = USER
And lockwait is not null;

Select sl. username, sl. sid, sl. serial #
From v _ $ lock l1, v $ session s1
Where exists (select * from v _ $ lock l2, v $ session s2
Where l2.sid = s2.sid and l2.id1 = l1
And s2.lockwait = l2.kaddr
And request = 0
And l1.sid = s1.sid );

Select count (*) from v $ session;
Select count (*) from sys. v _ $ process;
Select count (*) from sys. v _ $ transaction;

ZYP_35.98

------ Check which packages should be fixed
Column owner format A10
Select owner, name, type,
Source_size + code_size + parsed_size + error_size BYPES
From dba_object_size
Where type = package body order by 4 DESC;

------ View the entity information of the tablespace owned by a user:
Select tablespace_name, owner, segment_name, segment_type
From dba_segments
Where owner-SyS
And segment_type _-ROLLBACK
Order by tablespace_name, owner, segment_name;

Break on owner on segment_name
COLUMN segment_name FORMAT A15
COLUMN tablespace_name FORMAT A15
COLUMN file_name FORMAT A20
Select a. owner, a. segment_name, B. tablespace_name, B. file_name,
Sum (a. bytes) bytes
From dba_extents a, dba_data_files B
Where a. file_id-b.file_id group by a. owner, a. segment_name,
B. tablespace_name, B. file_name;

------ The index of memory buffer usage efficiency is hit rate HITS:
Hits = Logical_reads/(logical_reads + physical_reads)
Logical_reads = db_block_gets + consistent_reads

Select cur. value db, con. value con, phy. value phy,
(Cur. value + con. value)/cur. value + con. value + phy. value) * 100 HITS
From v $ sysstat cur, v $ sysstat con, v $ sysstat phy
Where CUR. NAME = db block gets AND
CON. NAME = consistent gets AND
PHY. NAME = physical reads;

------ How to detect rollback segment competition?
Select class, count from v $ waitstat
Where class in
(System undo header, system undo block,
Undo header, undo block );

Select sum (value) from v $ sysstat where name in
(Db block gets, consistents gets );

If count/sum (value) is greater than 1%, you should consider adding a rollback segment.

------ Check which rollback segments of a transaction are:
COLUMN u FORMAT A15
COLUMN s FORMAT A15
COLUMN s FORMAT A80
Select osuser o, username u, segment_name s, sa. SQL _text
From v $ session s, v $ transaction t, dba_rollback_segs r, v $ sqlarea sa
Where s. taddr = t. addr and t. sidusn = r. segmant_id (+)
And s. SQL _address = sa. address (+ );

<