Category: Database

V$Session_Wait VS V$Session_event

Posted on by Osama Mustafa in Database
Both of these tables are view In database , I will talk about what is the difference between them and Why some of record appear in V$Session_wait but not in V$session_event , Simple Topic But useful and good to know , Also Check the document that i post For Oracle that gave you structure for both of these view :

V$SESSION_WAIT 
displays the current or last wait for each session.

V$SESSION_EVENT
lists information on waits for an event by a session

This is simulation for both table appear what is the common between two view :

Conn SPP/SPP
 
select SID, EVENT from v$session_wait where event='DIAG idle wait';
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
 
select SID,EVENT from v$session_event where event ='DIAG idle wait' ;
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait

Also its good to know when you are using 10g or later you can use v$seesion which is  gives you real-time information, what is happening right now.

gives you real-time information, what is happening right now :


there’s different type of enqueue:wait in Oracle like the following :

    enq: TX - allocate ITL entry
 
    enq: TX - contention
 
    enq: TX - index contention
 
    enq: TX - row lock contention 
 

to check them you can query V$EVENT_NAME view provides a 
complete list of all the enq: wait events. 

But in V$session_wait you can check the following :


     P1: Lock TYPE (or name) and MODE



    P2: Resource identifier ID1 for the lock



    P3: Resource identifier ID2 for the lock

 Which is not found in v$session_event .

So We can say :

V$SESSION_WAIT 
displays the events for which sessions have just completed waiting or are currently waiting.
V$SESSION_EVENT 
is similar to V$SYSTEM_EVENT, but displays all waits for each session.

 Reference Document :
1-V$session_wait
2-V$session_event
3-Events

Thank you
Osama Mustafa

 

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1

Posted on by Osama Mustafa in Database

LOG_ARCHIVE_DEST 
is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .

LOG_ARCHIVE_DEST_n 
initialization parameter defines up to ten (where n = 1, 2, … 10) destinations in oracle 10gand thirty one (n=1,2….31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.
 

LOG_ARCHIVE_FORMAT 

Syntax : LOG_ARCHIVE_FORMAT = ‘log%t_%s_%r.arc’

is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:

%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

Reference Document :
1-Oracle Log_Archive_log Documentation.
2-Oracle Log_arcchive_Dest

Comprare Oracle Data Between two Schema

Posted on by Osama Mustafa in Database
While browsing On internet Today i found some script that will help you to compare data between two schema in Oracle for those who don’t love to use tools you can Download them from :

Script Number One
Script Number Two

There are Serveral Tools Used for Comparing such as :

1-Open Source application for comparing two tables of data
2-TOAD FOR ORACLE
3-Oracle SQL DEVELOPER 
4-Oracle Enterprise Manager (OEM).
5-Compare any Two Database http://www.dbsolo.com/schema_comparison.html .
6-RedGate to Compare Data, schema , Database .


Thank you 
Osama Mustafa

Some Way To Active Oracle Trace

Posted on by Osama Mustafa in Database
Reading to Oracle Documentation the trace is facility provides performance information on individual SQL statements. It generates the following statistics for each statement:

  • Parse, execute, and fetch counts
  • CPU and elapsed times
  • Physical reads and logical reads
  • Number of rows processed
  • Misses on the library cache
  • Username under which each parse occurred
  • Each commit and rollback

To enable Oracle Trace you can do the following :

 Option Number One :

alter session set sql_trace = true;

Disable it :

alter session set sql_trace = false;

Option Number two :

Activate
dbms_session.set_sql_trace (true);
Deactivate

dbms_session.set_sql_trace (false);

Option Number three:

Activate
dbms_support.start_trace (binds=>{true|false}, waits=>{true|false});
Deactivate

dbms_support.stop_trace;

Notice that package is not installed by default , Check
EVENT: 10046 “enable SQL statement tracing (including binds/waits)” [ID 21154.1]

There’s The most Common way to Trace your SQL and enable trace , there’s other way you can search on it via Google or Oracle Documentation , all you have to do is trying one of these options .

some Other Useful link :
1-Oracle Documentation 
2-Oracle traces description.
3-Enable Oracle Traces

Thank you
Osama

Oracle Default Users

Posted on by Osama Mustafa in Database
When you are creating Database , By Default there’s Users Created In Installation Phase , Check Them :

SYS
The SYS user owns all base tables and user-accessable view of the data dictionary (Oracle configuration information). No Oracle user should ever alter (update, delete, or insert) any rows or schema objects conatained in the SYS schema, because such activity can compromise data integrety. The security administrator must keep strict control of this central account. 
 
SYSTEM
The SYSTEM user is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. 
 
SYSMAN
The SYSMAN user represents the Enterprise Manager super admin account. This EM admin can create and modify other EM admin accounts as well as admin the database instance itself.
 
DBSNMP
The DBSNMP user is used by EM to monitor the database. EM uses this account to access performance stats about the database. The DBSNMP credentials sometimes referred to as the monitoring credentials. 
 
 
In addition to these users, a user can connect with diffrent levels of privileges, namely SYSDBA and SYSOPER. When you connect using “connect sys/passwd as sysdba” your connecting as the SYS user and requesting SYSDBA privs. Because the SYS user is the Oracle equivilent to the UNIX root user Oracle makes you specify the amount of control you have, which is why you’ll get an error if you try to connect without specifying the privs.
 
The big diffrence between SYSDBA and SYSOPER privs is that SYSDBA can do anything (just like root). The SYSOPER privs allow you just about the same amount of control but won’t allow you to look at user data. Both privs allow you to ALTER DATABASE, CREATE SPFILE, STARTUP or SHUTDOWN, ALTER DATABASE ARCHIVELOG, and includes RESTRICTED SESSION privs. However, only SYSDBA can CREATE or DROP DATABASE, and the ALTER DATABASE RECOVER options for SYSOPER are limited to complete recovery only. 
 
 
There’s Another Users you can find Them On This Link 
 
 
Thank you 
Osama mustafa

Find blocking sessions In Oracle

Posted on by Osama Mustafa in Database
  You can query the gv$lock and gv$session views to locate a blocking session in RAC.

Miladin Modrakovic offers this script to detect and kill RAC blocking sessions, using  GV$Session and GV$Lock.

 CREATE OR REPLACE PROCEDURE kill_blocker
AS
   sqlstmt   VARCHAR2 (1000);
BEGIN
   FOR x IN (SELECT gvh.SID sessid, gvs.serial# serial,
                    gvh.inst_id instance_id
               FROM gv$lock gvh, gv$lock gvw, gv$session gvs
              WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
                                             FROM gv$lock
                                            WHERE request = 0
                                           INTERSECT
                                           SELECT id1, id2
                                             FROM gv$lock
                                            WHERE lmode = 0)
                AND gvh.id1 = gvw.id1
                AND gvh.id2 = gvw.id2
                AND gvh.request = 0
                AND gvw.lmode = 0
                AND gvh.SID = gvs.SID
                AND gvh.inst_id = gvs.inst_id)
   LOOP
      sqlstmt :=
            ‘ALTER SYSTEM KILL SESSION ”’
         || x.sessid
         || ‘,’
         || x.serial
         || ‘,@’
         || x.instance_id
         || ””;
      DBMS_OUTPUT.put_line (sqlstmt);

      EXECUTE IMMEDIATE sqlstmt;
   END kill_blovk;
END TEST;
/         

when the script  will generate it will execute Alter session to kill This Session .

Some Useful Link : 
1-Erik Wramner

Thank you
Osama mustafa
 

 

ORA-01502: index ‘%’ or partition of such index is in unusable state

Posted on by Osama Mustafa in Database
To Solve this error you could use more than one solution :

Solution One :

declare
begin
   for index_rec in (select owner, index_name
                     from dba_INDEXES
                     where status = ‘UNUSABLE’)
   loop                    
      execute immediate ‘alter index ‘ || index_rec.owner || ‘.’ ||
          index_rec.index_name || ‘ rebuild’;
   end loop;
end;

Solution two:

10g and above :

alter system set skip_unusable_indexes=true;

 thank you
Osama mustafa 

Find which User are Running SQL Query

Posted on by Osama Mustafa in Database
I will post this script here , and you can download it also :

SELECT 
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS, 
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC

To Download the Script Press Here

Thank you
Osama Mustafa

check character set in Oracle

Posted on by Osama Mustafa in Database
Simple Topic just to people who asked before why NLS_CHARACTER SET Not appearing in my V$PARAMETER View :

Just Use two Below Query Depend on your version 

SQL> select value from nls_database_parameters where 
parameter = 'NLS_CHARACTERSET' ;
 
VALUE
----------------------------------------
AR8MSWIN1256
 
 
Check the National Character set :
 
SQL> select value from nls_database_parameters where 
parameter = 'NLS_NCHAR_CHARACTERSET' ;

 Or you can Use :

SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ;

Thank you
Osama Mustafa

Oracle Performance – Statspack

Posted on by Osama Mustafa in Database
I mention before in my blog how to Generate AWR Reports But now How to Generate Statpack and how to use it !!!!


enable Statpack :

By default Statpack is disable to enable it do the following

1- Create tablespace with size at least 200MB
2-@?/rdbms/admin/spcreate

Disable it 

@?/rdbms/admin/spdrop
 
Statpack works on snapshot you can do the following with oracle snap shot :
 
Generate Snapshot : 

exec statspack.snap;
 
Or
 
exec statspack.snap(i_snap_level => 10, i_modify_parameter => 'true'); 

Levels Description :


Level 0 - This level captures general statistics, 
including rollback segment, row cache, SGA, system events, background 
events, session events, system statistics, wait statistics, lock 
statistics, and Latch information.

Level 5 - This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels. 

Level 6 - This level includes capturing SQL plan and SQL plan usage 
information for high resource usage SQL Statements, along with all data 
captured by lower levels. 

Level 7 - This level captures segment level statistics, including 
logical and physical reads, row lock, itl and buffer busy waits, along 
with all data captured by lower levels. 
 
Level 10 - This level includes capturing Child Latch statistics, along with all data captured by lower levels. 
 
Delete SnapShot 


@?/rdbms/admin/sppurge;
 
Check SnapShot on Database Level : 

col "Date/Time" format a30
select	snap_id
,       snap_level
,	to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
from	stats$snapshot
,	v$database
order by snap_id
/

 To Scheducle One Hour Generate Snapshot :

@?/rdbms/admin/spauto.sql
 
This script use DBMS_JOB to create new job . you can check them from view dba_jobs
 and to delete job "exec dbms_job.remove();"
 
 

Finally Generate statpack Reports : 

 
 @?/rdbms/admin/spreport.sql