怎么查询oracle隔离级别

隔离级别是指在多个用户同时访问数据库时，各自的操作应该相互隔离。Oracle数据库提供三种隔离级别：读未提交（read uncommitted）、读已提交（read committed）和可重复读（repeatable read）。在Oracle中默认的隔离级别是读已提交。本文将为读者介绍如何查询Oracle隔离级别。

查询Oracle当前会话的隔离级别

如果用户想要了解当前Oracle会话的隔离级别，可以通过以下语句进行查询：

SELECT s.sid, s.serial#, s.username, s.status, s.schemaname, s.osuser, s.machine, s.terminal,
       s.program, s.module, s.action, s.client_info, s.client_identifier, s.client_edition,
       s.client_driver, s.sql_id, s.last_call_et, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
       s.sql_exec_end, s.event, s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3,
       s.wait_class, s.wait_time, s.seconds_in_wait, s.state, s.queue_time, s.rac_instance,
       i.instance_name, i.host_name, i.version, i.startup_time, i.status AS inst_status,
       i.parallel, t.start_time, t.used_urec, t.start_scn, t.logon_time, t.last_call_et AS sess_duration,
       t.blocking_session_status, s.blocking_session, s.sql_fulltext,
       DECODE(s.command, 0, 'No Command', 1, 'Create Table', 2, 'Insert', 3, 'Select',
                          4, 'Create Cluster', 5, 'Alter Cluster', 6, 'Update', 7, 'Delete',
                          8, 'Drop Cluster', 9, 'Create Index', 10, 'Drop Index', 11, 'Alter Index',
                          12, 'Drop Table', 13, 'Create Sequence', 14, 'Alter Sequence', 15, 'Alter Table',
                          16, 'Drop Sequence', 17, 'Grant', 18, 'Revoke', 19, 'Create Synonym',
                          20, 'Drop Synonym', 21, 'Create View', 22, 'Drop View', 23, 'Validate Structure',
                          24, 'Create Procedure', 25, 'Alter Procedure', 26, 'Lock', 27, 'No Action',
                          28, 'Rename', 29, 'Comment', 30, 'Audit', 31, 'No Audit', 32, 'Create Database Link',
                          33, 'Drop Database Link', 34, 'Create Database', 35, 'Alter Database', 36, 'Create Rollback Segment',
                          37, 'Alter Rollback Segment', 38, 'Drop Rollback Segment', 39, 'Create Tablespace', 40, 'Alter Tablespace',
                          41, 'Drop Tablespace', 42, 'Alter Session', 43, 'Alter User', 44, 'Commit',
                          45, 'Rollback', 46, 'Savepoint', 47, 'PL/SQL Execute', 48, 'Set Transaction',
                          49, 'Alter System Switch Log', 50, 'Drop User', 51, 'Set Constraint', 52, 'Create Profile',
                          53, 'Drop Profile', 54, 'Alter Profile', 55, 'Drop Procedure', 56, 'Alter Resource Cost',
                          57, 'Create Snapshot Log', 58, 'Alter Snapshot Log', 59, 'Drop Snapshot Log', 60, 'Create Snapshot',
                          61, 'Alter Snapshot', 62, 'Drop Snapshot', 63, 'Create Type', 64, 'Drop Type',
                          65, 'Alter Role', 66, 'Alter Type', 67, 'Create Type Body', 68, 'Alter Type Body',
                          69, 'Drop Type Body', 70, 'Flush', 71, 'Package', 72, 'Package Body',
                          73, 'Create Directory', 74, 'Drop Directory', 75, 'Alter Directory', 76, 'Grant Directory',
                          77, 'Revoke Directory', 78, 'Create Library', 79, 'Create Java', 80, 'Alter Java',
                          81, 'Drop Java', 82, 'Create Operator', 83, 'Create Indextype', 84, 'Drop Indextype',
                          85, 'Alter Operator', 86, 'Assm Instance', 90, 'Noop', 91, 'Create User',
                          92, 'Alter User', 93, 'Create Role', 94, 'Drop Role', 95, 'Alter Sequence',
                          96, 'Alter Table .. Exchange Partition', 97, 'Alter Table .. Truncate Partition', 98, 'Alter Table .. Merge Partition',
                          99, 'Alter Table .. Modify Partition', 100, 'Alter Tablespace .. Rename Datafile', 101, 'Alter Tablespace .. Add Datafile',
                          103, 'Create Tablespace .. Add Datafile', 104, 'Set Constraint .. Enable',
                          105, 'Set Constraint .. Disable', 106, 'Create Materialized View', 107, 'Alter Materialized View',
                          108, 'Create Library', 109, 'Create Java', 110, 'Alter Java', 111, 'Drop Java',
                          112, 'Create Operator', 113, 'Create Indextype', 114, 'Drop Indextype',115, 'Alter Operator',
                          116, 'Create Tablespace', 121, 'Create Restore Point', 122, 'Drop Restore Point',
                          s.command) AS command_type
FROM   v$session s, v$instance i, v$transaction t
WHERE  s.audsid = USERENV('sessionid')
AND    s.inst_id = i.inst_id
AND    s.taddr = t.addr(+)

上述语句中包含了很多列，但是我们只需要查看`s.sql_fulltext`列的值，这个值可以显示当前会话最近执行的SQL语句。如果查询结果中显示了“SELECT @@GLOBAL.tx_isolation”或“SELECT @@tx_isolation”语句，那么隔离级别就是读已提交（read committed）。如果查询结果中显示了“SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”语句，那么隔离级别就是读未提交（read uncommitted）。如果查询结果中显示了“SET TRANSACTION ISOLATION LEVEL REPEATABLE READ”语句，那么隔离级别就是可重复读（repeatable read）。

查询Oracle系统级别的默认隔离级别

Oracle数据库中系统级别的默认隔离级别是读已提交（read committed），但是管理员可以通过更改系统参数来更改默认的隔离级别。如果用户想要查询Oracle数据库的默认隔离级别，可以使用以下SQL语句：

SELECT name, value
FROM   v$parameter
WHERE  name = 'transaction_isolation'

如果查询结果中显示了“READ COMMITTED”，那么默认隔离级别就是读已提交（read committed）。

总结

本文介绍了如何查询Oracle隔离级别。如果用户想要了解当前会话的隔离级别，可以通过查询当前会话最近执行的SQL语句来确定；而如果用户想要查询Oracle系统级别的默认隔离级别，则可以使用SQL语句查询系统参数来确定。最后需要提醒用户，无论是使用哪种隔离级别，都需要根据应用程序的需求进行选择。