Oracle 比對table資料是否相同

select * from A1
 minus
select * from A2
Oracle的說明
會將兩個table欄位內容不同的資料顯示出來

Oracle 查詢結果增加千分位

FM是去空白用
select to_char(blocks*1024*8,'FM999,999,999,999'),a.*
from all_tables a

Oracle 判斷欄位資料連續

判斷兩筆資料中同一欄位時是否連續
Lead(欄位名稱) 取得下一筆資料該欄位的值
Over(partition by 欄位名稱 以該欄位為group by 標準 order by 欄位名稱 資料已該欄位排序
這是Oracle 的內建函數,別的DB不能用喔~

select * from (
select
rownum,lead(rownum) over(partition by OWNER order by rownum) rownext

  from dba_objects w
  ) a
  where rownum <> rownext-1

Excel-時間相減後轉成秒數

=TEXT(A2-A1,"[ss]")  可以強制把時間轉換為秒數 mm 則轉換為分鐘
不足數無條件捨去

A欄位
20110629 15:12
20110629 17:11
結果
119

查詢SQL執行狀況

可至V$SQLAREA 查詢
該V會顯示出所有執行過的SQL 的執行頻率,IO耗用..等等資訊
詳細資料如ORACLE對該TABLE的說明

有另一個V$SQL 這個View執行效率會比較好
差別在於V$SQLAREA會把不同版本的SQL Groupby 在一起
而V$SQL不會

Oracle DATA GUARD 模式

Maximum Availability

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance

This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.
This is the default protection mode.

Maximum Protection

This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.
Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.


Oracle 說明DataGuard文件

Oracle 修改Archive歸檔時間

archive_lag_target  單位是秒 預設是0(不啟動)
低於30分鐘可能影響資料庫效能
建議值落於 1800~7200之間
alter system set archive_lag_target=1800 scope=both;




oracle的說明 Oracle針對redo log的說明

Setting the ARCHIVE_LAG_TARGET Initialization Parameter

When you set the ARCHIVE_LAG_TARGET initialization parameter, you cause Oracle to examine an instance's current online redo log periodically. If the following conditions are met the instance will switch the log:

• The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + mexceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
• The current log contains redo records.

In an Oracle Real Application Clusters environment, the instance also nudges other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is more idle than the other instances (as when you are running a 2-node primary/secondary configuration of Oracle Real Application Clusters).

Initialization parameter ARCHIVE_LAG_TARGET specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or crash if the Data Guard environment is not configured in a no-data-loss mode. It also provides an upper limit of how long (in the number of seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.

The following initialization parameter setting sets the log switch interval to 30 minutes (a typical value).

ARCHIVE_LAG_TARGET = 1800

A value of 0 disables this time-based log switching functionality. This is the default setting.

You can set the ARCHIVE_LAG_TARGET initialization parameter even if there is no standby database. For example, the ARCHIVE_LAG_TARGET parameter can be set specifically to force logs to be switched and archived.

ARCHIVE_LAG_TARGET is a dynamic parameter and can be set with the ALTER SYSTEM SET statement.

Caution: The ARCHIVE_LAG_TARGET parameter must be set to the same value in all instances of an Oracle Real Application Clusters environment. Failing to do so results in unspecified behavior and is strongly discouraged.

Oracle 取得第幾週

to_char(mk_date,'YYYYWW')

取得ORACL Archived_log 大小

取得所歸檔的Archived Log 大小及歸檔時間
Select first_time,sum(blocks*block_size)
   FROM v$archived_log
   group by rollup(first_time)
欄位說明

linux 查詢歷史指令

.bash_history <==指令歷史檔存放位置
history 指令

n   ：要列出最近的 n 筆命令列表

-c  ：清除所有紀錄內容
-a  ：設定存放路徑,預設值 ~/.bash_history
-r  ：將 histfiles 的內容讀到目前這個 shell 的 history 記憶體中
-w  ：將目前的 history 記憶內容寫入 histfiles 中

Oracle Table大小查詢

取得每一個table所使用實體空間大小
在執行此query前
1.須先確定db_block_size大小
select value from v$parameter where name='db_block_size'
Oracle有關V$Parameter的說明
2.update所以資料表的統計值

ANALYZE TABLE TABLENAME COMPUTE STATISTICS

Oracle有關Analyze的說明


oracle有關all_tables的說明

select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,BLOCKS*8096 as blockusebyte,AVG_ROW_LEN,AVG_ROW_LEN*NUM_ROWS as rowusebyte
from all_tables
where owner<>'SYS' and owner<>'SYSTEM'
 and blocks is not null
order by blocks desc

查詢tablespace使用空間

可以取得每個table space目前使用空間資訊
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name

Oracle 版本查詢

SELECT BANNER FROM SYS.V_$VERSION

Oracle Hint

研究了很久在SQL中增加/*ordered */
會使查詢效能顯著提升
原來這叫做Oracle Hint

常見Oracle HINT的用法：

1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計資料,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計資料,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

5. /*+FULL(TABLE)*/
表明對表選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;

6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
AND EMP_NO=’SCOTT’;

7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;

10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過hash連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.

27. /*+CACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;

30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;

Oracle Lock機制

Oracle的鎖機制歸納總結

2010-06-09 作者：wallimn 來源：wallimn的blog

鎖是防止在兩個事務操作同一個資料源（表或行）時交互破壞資料的一種機制。Oracle採用封鎖技術保證併發操作的可串列性。Oracle的鎖分為兩大類：資料鎖（也稱DML鎖）和字典鎖。字典鎖是Oracle DBMS內部用於對字典表的封鎖。字典鎖包括語法分析鎖和DDL鎖，由DBMS在必要的時候自動加鎖和釋放鎖，用戶無機控制。 Oracle主要提供了5種資料鎖：共用鎖（Share Table Lock，簡稱S鎖）、排它鎖（Exclusive Table Lock，簡稱X鎖）、行級鎖（Row Share Table Lock，簡稱RS鎖）、行級排它鎖（Row Exclusive Table Lock，簡稱RX鎖）和共用行級排它鎖（Share Row Exclusive Table Lock，簡稱SRX鎖）。其封鎖粒度包括行級和表級。 1. 共用鎖（Share Table Lock，S）： 加鎖語法：Lock Table TableName In Share Mode; 允許的操作：一個共用鎖由一個事務控制，僅允許其他事務查詢被鎖定的表。一個有效的共用鎖明確地用Select … For update形式鎖定行，或執行Lock Table TableName In Share Mode語法鎖定整個表，不允許被其他事務更新。允許多個事務在同一個表上加共用鎖，這種情況下不允許在該表上加鎖的事務更新表（即使有一個事務控制的是形如Select Row … for update這樣行鎖也是不被允許的）。因此，僅有一個事務的一個共用鎖可以更新該表如果其他事務也有相同的事務在該表上的話。 禁止的操作：一個共用鎖由一個事務來控制，防止其他事務更新該表或執行下面的語句： LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE TableName IN ROW EXCLUSIVE MODE; 2. 排它鎖（Exclusive Table Lock，X）： 排它鎖是在鎖機制中限制最多的一種鎖類型，允許加排它鎖的事務獨自控制對表的寫許可權。 加鎖語法：Lock Table TableName In Exclusive Mode; 允許的操作：在一個表中只能有一個事務對該表實行排它鎖，排它鎖僅允許其他的事務查詢該表。 禁止的操作：擁有排外鎖的事務禁止其他事務執行其他任何DML類型的語句或在該表上加任何其他類型的鎖。 定義排它鎖的語法： LOCK TABLE TableName IN EXCLUSIVE MODE; 3. 行級鎖（Row Share Table Lock，RS）： 一個行級鎖（有時稱為Subshare Table Lock，簡稱SS，子共用鎖）需要該事務在被鎖定行的表上用update的形式加鎖。當有下面語句被執行的時候行級鎖自動加在操作的表上。 SELECT . . . FROM TableName. . . FOR UPDATE OF . . . ; LOCK TABLE TableName IN ROW SHARE MODE; 行級鎖（Row Share Table Lock）在鎖類型中是限制最少的，也是在表的併發程度中使用程度最高的。 允許的操作：行級共用鎖由一個事務控制，允許其他事務查詢、插入、更新、刪除或同時在同一張表上鎖定行。因此其他事務可以同時在同一張表上得到行級鎖、共用行級排它鎖、行級排它鎖、排它鎖。參見數據鎖的相容矩陣圖 禁止的操作：擁有行級鎖的事務不允許其他事務執行排它鎖，即： Lock Table TableName In Exclusive Mode; 4. 行級排它鎖（Row Exclusive Table Lock，RX）： 行級排它鎖（亦稱為Subexclusive Table Lock，簡稱SX，子排它鎖）通常需要事務擁有的鎖在表上被更新一行或多行。當有下面語句被執行的時候行級排它鎖被加在操作的表上。 INSERT INTO TableName. . . ; UPDATE TableName. . . ; DELETE FROM TableName. . . ; LOCK TABLE TableName IN ROW EXCLUSIVE MODE; 行級排它鎖比行級鎖稍微多一些限制。 允許的操作：在資料鎖的相容矩陣圖很容易看出行級排它鎖由一個事務擁有允許其他事務執行查詢、修改、插入、刪除或同時在同一張表上鎖定行。執有行級排它鎖的事務允許其他事務在同一張表上同時得到共用鎖和行級排它鎖。 禁止的操作：行級排它鎖由一個事務擁有防止其他事務手動鎖定表來排除其他事務的讀寫權。因此，其他事務不允許在同一張表上使用以下的語句來執行鎖事務。 LOCK TABLE table IN SHARE MODE; LOCK TABLE table IN SHARE EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE 5. 共用行級排它鎖（Share Row Exclusive Table Lock，SRX）： 共用行級排它鎖有時也稱共用子排它鎖（Share Subexclusive Table Lock，SSX），它比共用鎖有更多限制。定義共用行級排它鎖的語法為： Lock Table TableName In Share Row Exclusive Mode; 允許的操作：僅允許一個事務在某一時刻得到行級排它鎖。擁有行級排它鎖事務允許其他事務在被鎖定的表上執行查詢或使用Select … From TableName For update…來準確在鎖定行而不能更新行。 禁止的操作：擁有行級排它鎖的事務不允許其他事務有除共用鎖外的其他形式的鎖加在同一張表上或更新該表。即下麵的語句是不被允許的： LOCK TABLE TableName IN SHARE MODE; LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE TableName IN ROW EXCLUSIVE MODE; LOCK TABLE TableName IN EXCLUSIVE MODE; DML語句獲得的DML自動鎖（Oracle隱含鎖） DML自動鎖的相容矩陣圖： X：排它鎖、RS：行級共用鎖、RX：行級排它鎖、S、共用鎖、RSX：行級共用排它鎖 DML語句 有行級鎖 鎖類型 SELECT ... FROM table INSERT INTO table ... X RX UPDATE table ... X RX DELETE FROM table ... X RX SELECT ... FROM table ... FOR UPDATE OF ... X RS LOCK TABLE table IN ... ROW SHARE MODE RS ROW EXCLUSIVE MODE RX SHARE MODE S SHARE EXCLUSIVE MODE SRX EXCLUSIVE MODE X 資料字典中與鎖有關的表或視圖 V_$LOCKED_OBJECT V_$LOCKS_WITH_COLLISIONS V_$LOCK_ACTIVITY V_$LOCK_ELEMENT V_$_LOCK DBMS_LOCK V$DLM_ALL_LOCKS V$DLM_LOCKS V$ENQUEUE_LOCK V$GLOBAL_BLOCKED_LOCKS V$LOCK V$LOCKED_OBJECT V$LOCKS_WITH_COLLISIONS V$LOCK_ACTIVITY V$LOCK_ELEMENT V$_LOCK 解鎖及Kill Session: 使用下面的語法查出鎖並殺掉Session。 SELECT A.SID,A.SERIAL#,A.USERNAME,B.TYPE FROM V$SESSION A,V$LOCK B WHERE A.SID=B.SID; ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Lock 監視

取得目前session狀態,及是否lock優化版
速度快很多
select  /*+ ordered */ <==加上這個會快很多
   c.sid,
        c.serial#,
        b.ctime,
   lock_waiter.waiting_session,
   lock_blocker.holding_session,
   c.program,
   c.osuser,
   c.machine,
   c.process,
   decode(u.name,
      null,'',
      u.name||'.'||o.name
   ) object,
   c.username,
   decode
   (
      b.type,
      'BL', 'Buffer hash table instance lock',
      'CF', 'Control file schema global enqueue lock',
      'CI', 'Cross-instance function invocation instance lock',
      'CU', 'Cursor bind lock',
      'DF', 'Data file instance lock',
      'DL', 'direct loader parallel index create lock',
      'DM', 'Mount/startup db primary/secondary instance lock',
      'DR', 'Distributed recovery process lock',
      'DX', 'Distributed transaction entry lock',
      'FS', 'File set lock',
      'IN', 'Instance number lock',
      'IR', 'Instance recovery serialization global enqueue lock',
      'IS', 'Instance state lock',
      'IV', 'Library cache invalidation instance lock',
      'JQ', 'Job queue lock',
      'KK', 'Thread kick lock',
      'LA','Library cache lock instance lock (A..P=namespace);',
      'LB','Library cache lock instance lock (A..P=namespace);',
      'LC','Library cache lock instance lock (A..P=namespace);',
      'LD','Library cache lock instance lock (A..P=namespace);',
      'LE','Library cache lock instance lock (A..P=namespace);',
      'LF','Library cache lock instance lock (A..P=namespace);',
      'LG','Library cache lock instance lock (A..P=namespace);',
      'LH','Library cache lock instance lock (A..P=namespace);',
      'LI','Library cache lock instance lock (A..P=namespace);',
      'LJ','Library cache lock instance lock (A..P=namespace);',
      'LK','Library cache lock instance lock (A..P=namespace);',
      'LL','Library cache lock instance lock (A..P=namespace);',
      'LM','Library cache lock instance lock (A..P=namespace);',
      'LN','Library cache lock instance lock (A..P=namespace);',
      'LO','Library cache lock instance lock (A..P=namespace);',
      'LP','Library cache lock instance lock (A..P=namespace);',
      'MM', 'Mount definition global enqueue lock',
      'MR', 'Media recovery lock',
      'NA', 'Library cache pin instance lock (A..Z=namespace)',
      'NB', 'Library cache pin instance lock (A..Z=namespace)',
      'NC', 'Library cache pin instance lock (A..Z=namespace)',
      'ND', 'Library cache pin instance lock (A..Z=namespace)',
      'NE', 'Library cache pin instance lock (A..Z=namespace)',
      'NF', 'Library cache pin instance lock (A..Z=namespace)',
      'NG', 'Library cache pin instance lock (A..Z=namespace)',
      'NH', 'Library cache pin instance lock (A..Z=namespace)',
      'NI', 'Library cache pin instance lock (A..Z=namespace)',
      'NJ', 'Library cache pin instance lock (A..Z=namespace)',
      'NK', 'Library cache pin instance lock (A..Z=namespace)',
      'NL', 'Library cache pin instance lock (A..Z=namespace)',
      'NM', 'Library cache pin instance lock (A..Z=namespace)',
      'NN', 'Library cache pin instance lock (A..Z=namespace)',
      'NO', 'Library cache pin instance lock (A..Z=namespace)',
      'NP', 'Library cache pin instance lock (A..Z=namespace)',
      'NQ', 'Library cache pin instance lock (A..Z=namespace)',
      'NR', 'Library cache pin instance lock (A..Z=namespace)',
      'NS', 'Library cache pin instance lock (A..Z=namespace)',
      'NT', 'Library cache pin instance lock (A..Z=namespace)',
      'NU', 'Library cache pin instance lock (A..Z=namespace)',
      'NV', 'Library cache pin instance lock (A..Z=namespace)',
      'NW', 'Library cache pin instance lock (A..Z=namespace)',
      'NX', 'Library cache pin instance lock (A..Z=namespace)',
      'NY', 'Library cache pin instance lock (A..Z=namespace)',
      'NZ', 'Library cache pin instance lock (A..Z=namespace)',
      'PF', 'Password File lock',
      'PI', 'Parallel operation locks',
      'PS', 'Parallel operation locks',
      'PR', 'Process startup lock',
      'QA','Row cache instance lock (A..Z=cache)',
      'QB','Row cache instance lock (A..Z=cache)',
      'QC','Row cache instance lock (A..Z=cache)',
      'QD','Row cache instance lock (A..Z=cache)',
      'QE','Row cache instance lock (A..Z=cache)',
      'QF','Row cache instance lock (A..Z=cache)',
      'QG','Row cache instance lock (A..Z=cache)',
      'QH','Row cache instance lock (A..Z=cache)',
      'QI','Row cache instance lock (A..Z=cache)',
      'QJ','Row cache instance lock (A..Z=cache)',
      'QK','Row cache instance lock (A..Z=cache)',
      'QL','Row cache instance lock (A..Z=cache)',
      'QM','Row cache instance lock (A..Z=cache)',
      'QN','Row cache instance lock (A..Z=cache)',
      'QP','Row cache instance lock (A..Z=cache)',
      'QQ','Row cache instance lock (A..Z=cache)',
      'QR','Row cache instance lock (A..Z=cache)',
      'QS','Row cache instance lock (A..Z=cache)',
      'QT','Row cache instance lock (A..Z=cache)',
      'QU','Row cache instance lock (A..Z=cache)',
      'QV','Row cache instance lock (A..Z=cache)',
      'QW','Row cache instance lock (A..Z=cache)',
      'QX','Row cache instance lock (A..Z=cache)',
      'QY','Row cache instance lock (A..Z=cache)',
      'QZ','Row cache instance lock (A..Z=cache)',
      'RT', 'Redo thread global enqueue lock',
      'SC', 'System commit number instance lock',
      'SM', 'SMON lock',
      'SN', 'Sequence number instance lock',
      'SQ', 'Sequence number enqueue lock',
      'SS', 'Sort segment locks',
      'ST', 'Space transaction enqueue lock',
      'SV', 'Sequence number value lock',
      'TA', 'Generic enqueue lock',
      'TS', 'Temporary segment enqueue lock (ID2=0)',
      'TS', 'New block allocation enqueue lock (ID2=1)',
      'TT', 'Temporary table enqueue lock',
      'UN', 'User name lock',
      'US', 'Undo segment DDL lock',
      'WL', 'Being-written redo log instance lock',
      b.type
   ) lock_type,
   decode
   (
      b.lmode,
      0, 'None',           /* Mon Lock equivalent */
      1, 'Null',           /* N */
      2, 'Row-S (SS)',     /* L */
      3, 'Row-X (SX)',     /* R */
      4, 'Share',          /* S */
      5, 'S/Row-X (SRX)',  /* C */
      6, 'Exclusive',      /* X */
      to_char(b.lmode)
   ) mode_held,
   decode
   (
      b.request,
      0, 'None',           /* Mon Lock equivalent */
      1, 'Null',           /* N */
      2, 'Row-S (SS)',     /* L */
      3, 'Row-X (SX)',     /* R */
      4, 'Share',          /* S */
      5, 'S/Row-X (SSX)',  /* C */
      6, 'Exclusive',      /* X */
      to_char(b.request)
   ) mode_requested
from v$lock b,
     v$session c,
     sys.user$ u,
     sys.obj$ o,
     (select * from sys.dba_waiters) lock_blocker,
     (select * from sys.dba_waiters) lock_waiter
where b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username != 'SYS'
order by ctime desc, object, kaddr, lockwait