What is the importance of executing orainstRoot.sh and root.sh scripts in Oracle Standalone and RAC environment?

Hi,
As a part of post installation steps,we execute two scripts 'orainstRoot.sh' and 'root.sh' scripts and Oracle also suggest to backup the 'orainstRoot.sh' and 'root.sh' scripts.These two scripts we should execute as 'root' user as it displays after the Oracle software installation completes.

 Executing orainstRoot.sh script:
==================================

[oracle@localhost oraInventory]$ su
Password:
[root@localhost oraInventory]# pwd
/u02/app/oraInventory
[root@localhost oraInventory]# ls -ltr
total 28
drwxrwx--- 2 oracle oinstall 4096 Sep  4 07:09 logs
-rw-rw---- 1 oracle oinstall  293 Sep  4 07:09 oraInstaller.properties
drwxrwx--- 2 oracle oinstall 4096 Sep  4 07:09 oui
-rw-rw---- 1 oracle oinstall   37 Sep  4 07:29 install.platform
drwxrwx--- 2 oracle oinstall 4096 Sep  4 07:29 ContentsXML
-rwxrwx--- 1 oracle oinstall 1623 Sep  4 07:29 orainstRoot.sh
-rw-rw---- 1 oracle oinstall   56 Sep  4 07:29 oraInst.loc
[root@localhost oraInventory]# pwd
/u02/app/oraInventory
[root@localhost oraInventory]# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel),103(pkcs11)
[root@localhost oraInventory]# ./orainstRoot.sh
Changing permissions of /u02/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u02/app/oraInventory to oinstall.
The execution of the script is complete.
[root@localhost oraInventory]# cd ..
[root@localhost app]# pwd
/u02/app

Importance of running 'orainstRoot.sh' script:
The first Script that we run is 'orainstRoot.sh' which is located in $ORACLE_BASE/oraInventory(/u01/app/oracle/oraInventory) path.We execute 'orainstRoot.sh' script for the following purposes:

1)It creates the inventory pointer file (/etc/oraInst.loc),This file shows the inventory location and group it is linked to.
2)It Changes groupname of the oraInventory directory to oinstall group.

 Executing root.sh script:
=========================
[root@localhost app]# cd oracle/product/11.2.0/dbhome_1/
[root@localhost dbhome_1]# pwd
/u02/app/oracle/product/11.2.0/dbhome_1
[root@localhost dbhome_1]# ls -altr root.sh
-rwxr-x--- 1 oracle oinstall 518 Sep  4 07:24 root.sh
[root@localhost dbhome_1]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u02/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Importance of running 'root.sh' script:

The Second script that we run is 'root.sh' script which is located in $ORACLE_HOME (/u01/app/oracle/product/11.2.0/db_1) path.We execute 'root.sh' for the following purposes:

1)It will Creates a /etc/oratab file.This is the file which we use to make automatic Database shutdown and startup.
It is very important file.
2)It Sets the Oracle base and home environments.
3)It Sets an appropriate permission to the OCR base directory
4)It Creates the OCR backup and Network Socket directories.
5)It Modifies the ownership to 'root' user on the Oracle base and Cluster home filesystem.
6)It Configures the OCR and voting disks (only on the first node)
7)It Starts the Clusterware daemons.
8)It adds Clusterware daemons to the inittab file.
9)It Verifies whether the Clusterware is up on all nodes.
10)On the last node, initiates ./vipca in silent mode to configure nodeapps,
such as, GSD, VIP, and ONS for all the nodes.
11)It verifies the super user privileges.
12)It Creates a trace directory.The 'trace' directory is again very vital for generating trace file to keep a track on user sessions in case of any error,troubleshooting and diagnosis purpose.
13)It Generates OCR keys for the 'root' user.
14)It Adds daemon information to the inittab file
15)Starts up the Oracle High Availability Service Daemon (OHASD) process.
16)Creates and configures an ASM instance and starts up the instance.
17)Creates required ASM disk groups, if ASM is being used to put OCR and voting files.
18)Starts up the Cluster Ready Service Daemon (CRSD) process
19)Creates the voting disk file.
20)It Puts the voting disk on the Voting disk,if ASM type is selected.
21)It Displays voting disk details
22)Stops and restarts a cluster stack and other cluster resources on the local node
23)Backs up the OCR to a default location
24)It Installs the cvuqdisk-1.0.7-1 package
25)It Updates the Oracle inventory file.
26)Completes with the UpdateNodeList success operation.

When 'root.sh' is executed on the last node of the cluster,the following set of actions
are likely to be performed by the script:
1)It Sets Oracle base and home environmental variables.
2)The /etc/oratab file will be created
3)It Performs the super user privileges verification.
4)Adds trace directories
5)It Generates OCR keys for the 'root' user.
6)Adds a daemon to inittab
7)Starts the Oracle High Availability Service Daemon (OHASD) process.
8)It Stops/starts a cluster stack and other cluster resources on the local node
9)Performs a backup of the OCR file
10)Installs the cvuqdisk-1.0.7-1 package
11)Updates the Oracle inventory file.
12)Completes with UpdateNodeList success operation.




Hope it helps....

Enjoy Oracle DBA learning....


Best regards,

Rafi.

Proxy user in Oracle

Proxy user - A proxy user is a user who can assume the identity of other user/users.It is basically created for the application user to access the target user data without knowing its password.

Creating Proxy User:

SQL> create user proxy1 identified by proxy1;

User created.

SQL> alter user target grant connect through proxy1;

User altered.

SQL> select * from dba_proxies;

PROXY CLIENT AUT
------------------------------ ------------------------------ ---
AUTHORIZATION_CONSTRAINT ROLE PROXY_AUT
----------------------------------- ------------------------------ ---------
proxy1 target NO
PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE

Connecting with Proxy User:

C:\WINDOWS\system32>sqlplus sys@TESTDB AS SYSDBA

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 25 11:33:45 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn proxy1[target]
Enter password:
Connected.
SQL> SHOW USER
USER is "target"


Hope it helps...

Happy Oracle learning.

Best regards,

Rafi.

How index works in oracle

Hi,
Few days back kavita commented on my post to write about indexes so I found some time to write about indexes.We all know how important are these indexes specially when you have huge data and lot of trasactions are going on and how important they are when it comes to selecting the execution plan for the SQL statements and How important they are for changing the execution plan which optimizer chooses for giving you the desired result in least response time as possible,in order to enhance the performance of your applications.In this brief post I'm discussing how index works in oracle and how can we make use of these indexes to our maximum benefit.

Let me describe the indexes provided by oracle,
Oracle Database provides several indexing schemes.But,
In short there are only 2 types
1)B-tree(Balanced-tree) indexes
2)Bitmap indexes
These 2 types are subdivided as follows:

1)B-tree(Balanced-tree) indexes:

Features of B-tree(Balanced-tree) indexes are as follows:
-----------------------------------------------------------------------------------
These indexes are the standard index type. They are excellent for primary key and highly-selective indexes. Used as concatenated indexes, B-tree indexes can retrieve data sorted by the indexed columns. B-tree indexes have the following subtypes:
*Index-organized tables
An index-organized table differs from a heap-organized because the data is itself the index.

*Reverse key indexes
In this type of index, the bytes of the index key are reversed, for example, 103 is stored as 301. The reversal of bytes spreads out inserts into the index over many blocks.

*Descending indexes
This type of index stores data on a particular column or columns in descending order.

*B-tree cluster indexes
This type of index is used to index a table cluster key. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.

How B-tree indexes work:
-----------------------------------------
It is very important to know how this indexes actually work to use them to maximum benefit.The mechanism followed by balanced-tree indexes is as follows:

B-tree index has 3 parts:
1)Root
2)Branch blocks
3)Leaf blocks

*Root is the centre-point from where all the branch blocks and leaf blocks is connected.
=>A B-tree index has two types of blocks
1)Branch blocks
2)Leaf blocks

*Branch blocks for searching and leaf blocks that store values.
*Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block.
*The branch blocks contain a pointer to the child block containing the key.
The number of keys and pointers is limited by the block size.

*The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries.

*The branch blooks in one sense is for storing the key value and the leaf block is for sorting as per rowid(As in case of lock if the key is exact the lock will open fast or else it will take time and sorting with rowid is the fastest method to sort data in compare to rownum).

*If the database scans the index for a value, then it will find this value in n I/Os where n is the height of the B-tree index(The height of the index is the number of blocks required to go from the root block to a leaf block).
This is the basic principle behind Oracle Database indexes.

2)Bitmap indexes:

Feautures of Bitmap indexes:
-------------------------------------------------------------------------

*Bitmap and bitmap join indexes
=>In a bitmap index, an index entry uses a bitmap to point to multiple rows.
In contrast, a B-tree index entry points to a single row.This is major difference between
the B-tree and Bitmap indexes.
=>A bitmap join index is a bitmap index for the join of two or more tables.

*Function-based indexes
This type of index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression.
B-tree or bitmap indexes can be function-based.

*Application domain indexes
This type of index is created by a user for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file.

How Bitmap indexes work:
--------------------------------------------

*In the Bitmap index the values are stored in the form of 0's and 1's as you see below:

Eg:Bitmap structure might look like this:

Value Row 1 Row 2 Row 3 Row 4 Row 5 Row 6 Row 7
M 1 0 1 1 1 0 0
F 0 1 0 0 0 1 1

Where M:male and F:Female represent status for gender

*Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value.
*A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index
although it uses a different internal representation.

*Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause.
*Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This technique improves response time in case of Bitmap indexes.


Difference between B-tree(Balanced tree) and Bitmap indexes:
-------------------------------------------------------------------------------------

1) B-tree indexes index entry points to a single row
Where as,In case of Bitmap indexes index entry point to multiple rows.

2)B-tree indexes are more suitable for high cardinality columns(i.e the number of distinct values is large compared to the number of table rows) eg:ename.
Where as,Bitmap indexes are more suitable for low cardinality columns(i.e the number of distinct values is small compared to the number of table rows) eg:Male or female status for gender.

3)B-tree indexes are used in OLTP environment (eg:Banking Database where there will be huge trasactions taking place).
Where as,Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion(eg:Datawarehouse Database of huge size).

4)In the B-tree indexes actually values are stored in leaves(which is a part of B-tree indexstructure).
Where as,In Bitmap indexes the values are stored in Bits(0's and 1's).

5)In the OLTP environment where there are lot of DML activities are happening it is more advantageous to use B-tree indexes.
Where as,In the OLAP(Datawarehousing) environment or DSS(Decision support system) where we have to perform read only(selecting the data) activities Bitmap indexes are more recommended.

This document is prepared to use indexes to their Best.Hope it helps.


Best regards,

Rafi.

scn and checkpoint

Hi,
How the scn and checkpoint are related to each other is described in detail in the below link.This link explain you how oracle perform recovery by using this.Its superve explaination from sandeep.I really like the explaination in the below link.

http://www.dbapool.com/articles/1029200701.html



Best regards,

Rafi.

STATSPACK and AWR Statistics Comparison(**An Important interview question**)

STATSPACK and AWR Statistics Comparison

The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not. The following information on statistics is specific to those stored in the AWR that are not part of the STATSPACK:

1)STATSPACK does not store the Active Session History (ASH) statistics available in the AWR dba_hist_active_sess_history view. The ASH allows DBAs to perform time-series analyses of wait events for a particular session history.

2)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary , help the DBA build time-series reports for important database performance metrics such as Total Time Waited or Response Time Per Txn.

The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.

3)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.

4)

Dedicated or Shared Server Connections

Dedicated or Shared Connections:

Hi ,
       When to choose dedicated & when to choose shared connections?
This can be best described with the below explaination:

The user connected with dedicated server connection has the dedicated resource that is whether or not the user is doing work the connection remains allocated to that user.Thus if your user is not working and if there is less network resource then this user is blocking another user to connect or to execute the user request by making this user works queued.This connection is preferable when you have lot of resource or to some administrative user like "SYS" , "SYS" for administrative works and for batch jobs,


When there is shared server the user connection is shared, that is user is connecting by dispatchers and when a user is idle his resource can be used by other users, thus lessen the load on system, this is more likely in a environment where user is mostly idle, like in a order entry system.

In a shared server there also dedicated connection possible for admin user.

If your environment is OLTP then shared is preferable because the tasks need short time.

Whether is Warehouse, OLAP, Data Mining the users are running time consuming and heavy load query where it is best to use dedicated server.

All things depends on your satisfaction and needs, if you satisfied with the load , user user is satisfied , you have lot resource, you can go for dedicated.

If you have less resource but to support more concurrent user shared is best.



Best regards,

Rafi.

Undo and Redo concept ===>Aman's at its Best

Why not use Redo log for consistent read  by Aman:

Its not a stupid question as anything related to the functioning of Oralce database is not stupid. But the understanding of yours about redo is wrong. I do know that its termed that redo contains both the images of old and new but its actually not the correct thing. What redo contains is only change vectors which simply put, are just the changed entries of those places in the block which have got modified. This is true for both Data blocks which contain your data and Undo blocks as well which contain your old image. Think about it, what is an Undo Segment, its just like any other segment with the only difference that its maintained and modified by Oracle itself. When you modify a value, for example 100 to 200, the redo entery or the changed entry 200 is maintained in the Redo Buffer and from there, to the redo log file. The old entry must also modify the Undo Segment's some block and has to write itself there so there is a modification involved for that also which is again nothing but an update issued to the Undo segment. So the image changed for the Undo is also maintained in the Redo. Now, if let's suppose you lose both of your data block and the undo block, which means you lose the files of these two, using the redo you can recover both! Taht's what actually means by when it is said that redo contains both old and new image.

Now, if you have understood the above said, you must know that actual old/consistent image is maintained in the Undo segment only using which the consistent copy is created. Redo is used for the recovery whereas, Undo is used for the Read Consistacy.

HTH

Aman....

Well, you haven't understood still I guess. Okay, let's take a little deep dive.

When you do an update to an Oracle data block, whch means you change a salary value from 100 to 200, a change is done over the actual value(here 100). For this change, a Change Vector is generated which is actually an information that tells to oracle that this thing has happened and a code representation of the Update command for this value is generated. This Change Vector gets logged in the redo log buffer. For a transaction, multiple changes are logged into a redo stream which isnothing but the collection of the change vectors of that transaction. So the first thing you need to remember from now is that with the DML, the entire block doesn't get logged into the log buffer. Think about it, how it is possible in anyway since the size of the log buffer, in general is much smaller compared to other memory areas. If we would log a full block inthe log buffer, with few blocks only, it would touch its maximum size. So this is something you need to understand and remember.



Now if you have understood what I just said, you should understand this as well that the Undo blocks are no different from the data blocks. Both contain segments only but for the Undo blocks, the control is not with you. Only Oracle knows how to go and make changes to them! That's all what is different for them other than data blocks. So if there would be an update happening to the Undo segment to maintain the old image, from the above example 100, it would also be doing the same behavior that the normal table Update statment would do to the EMP table. So again, the same thing would be going to the redo stream. That's what I explained that in the redo, both the entries for the change of your data and the change for the Undo image are maintained and that's what the meaning of both Old and New values in the redo since using it , both can be recovered.



If all what above said is clear to you, you should have got an answer for your question that the recovery is done using the Redo's change vectors which are applied to the data files( Undo is also stored in a datafile which is a part of a tablespace) . If the Undo required is not available for the recovery of the uncommitted data, that would also be recreated using the Redo only which would recover the Undo as well , regenerating all of its contents! So you do need to care about the Redo/Archive only as the rest is going to be taken care by the Change Vectors stored in them!



I hope this all makes some sense for you and would help.



HTH

Aman....

 
  Thanks Aman  for the excellent explanation.
 
Best regards,
Rafi

LOCKING BY TOM'S MUST READ ONCE

Hi All,
Please read the below details to know about locking better:
Hi Tom,
Here are the few questions on Locking.
1)How do I use v$lock to figure out lock details ? Details like
whos has locked,on which table,who else is waiting, and what type
of locking (shared or exlusive ?), since when it is locked.
2)Why and when exclusive/shared locks are used ? Could you give me
example please ?
Thanks for the answer, I understand better.
3)Could you tell me significance of v$mystat and v$locked_object ?
Thank you .
4) Do we have page level locking in Oracle ?
Thank you
Thanks in advance,
Gururaj
and we said...1) Here is a small example showing how this happens. We will use three V$ tables in order
to see how this works; V$TRANSACTION, V$SESSION, and V$LOCK. V$TRANSACTION contains an
entry for every active transaction. V$LOCK contains an entry for all locks held as well
as locks being waited on. V$SESSION shows us the sessions logged in. We'll start by
starting a transaction in one session and looking at the state of the system at that
point:
tkyte@TKYTE816> update dept set deptno = deptno+10;
4 rows updated.
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160
The interesting things to note here are:
ć The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the
definition of the V$LOCK table in the Oracle Server Reference, you will find that LMODE=6
is an exclusive lock. A value of 0 in the request means we are not making a request ¡V we
have the lock.
ć There is only one row in this table. This V$LOCK table is more of a queuing table than
a lock table. Many people expect there would be four rows in V$LOCK since we have four
rows locked. What you must remember however is that Oracle does not store a master list
of every row locked anywhere. To find out if a row is locked, we must go to that row.
ć I took the ID1 and ID2 columns, and performed a bit of bit manipulation on them.
Oracle needed to save three 16bit numbers, but only had two columns in order to do it.
So, the first column ID1 holds two of these numbers. By dividing by 2^16 with
trunc(id1/power(2,16)) rbs and by masking out the high bits with
bitand(id1,to_number('ffff','xxxx'))+0 slot, I am able to get the two numbers that are
hiding in that one number back out.
ć The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my
transaction ID.
Now I'll start another session using the same user name, update some rows in EMP, and
then try to update DEPT:
tkyte@TKYTE816> update emp set ename = upper(ename);
14 rows updated.
tkyte@TKYTE816> update dept set deptno = deptno-10;
I am now blocked in this session. If we run the V$ queries again, we see:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
TKYTE 9 2 46 160 0 6
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
2 46 160
What we see here is that a new transaction has begun ¡V (3,82,163) is the transaction ID.
It has two rows in V$LOCK this time. One row represents the locks that it owns (where
LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. This is a
request for an exclusive lock. The interesting thing to note here is that the
RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock.
We can easily see that the transaction with SID=8 is blocking the transaction with SID=9.
Now, if we commit in SID = 8 the above changes:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request, block
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
that request row has gone ¡V it disappeared the instant the other session gave up its
lock. That request row was the queuing mechanism. The database is able to wake up the
blocked sessions the instant the transaction is completed. Note that the above gives us a
very easy way to see blockers and waiters:
tkyte@TKYTE816> select
(select username from v$session where sid=a.sid) blocker,
2 a.sid,
3 ' is blocking ',
4 (select username from v$session where sid=b.sid) blockee,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and a.id2 = b.id2
11 /
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
-------- ---------- ------------- -------- ----------
TKYTE 8 is blocking TKYTE 9
simply by doing a self-join of V$LOCK with itself (I ran this query before committing the
session with SID=8).
2) exclusive lock -- I updated a row. no one else can update it until I commit. I have
an X lock on that row and only one person at a time can have an X lock. an X lock
provides serialization to a resource.
A shared lock -- when I update a table, I take a shared lock on the DEFINITION of the
table. Everyone else can do that as well (more then one session can get a shared lock on
the table definition). So, more than one person at a time can update the table. If you
wanted to ALTER the table, you would need an X lock on the defintion. You cannot get an
X lock when there are shared locks so you wait until there are no shared locks.
3) mystat has the statistics (cpu use, blocks read, cursors opened, etc) for your session
only. v$locked_object shows you all of the locks in the system.
4) no, never.