Previous | Table of Contents | Next |
Listing 7.2 locks.sql shows all locks in the database.
SET LINESIZE 132 SET PAGESIZE 60 COLUMN OBJECT HEADING 'Database|Object' FORMAT a15 TRUNCATE COLUMN lock_type HEADING 'Lock|Type' FORMAT a4 TRUNCATE COLUMN mode_held HEADING 'Mode|Held' FORMAT a15 TRUNCATE COLUMN mode_requested HEADING 'Mode|Requested' FORMAT a15 TRUNCATE COLUMN sid heading 'Session|ID' COLUMN username HEADING 'Username' FORMAT a20 TRUNCATE COLUMN IMAGE HEADING 'active image' FORMAT a20 TRUNCATE SPOOL /tmp/locks SELECT c.sid, substr(object_name,1,20) OBJECT, c.username, substr(c.program,length(c.program)-20,length(c.program)) image, DECODE(b.type, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', b.type) lock_type, DECODE(b.lmode, 0, 'None', /* Mon Lock equivalent */ 1, 'Null', /* NOT */ 2, 'Row-SELECT (SS)', /* LIKE */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* SELECT */ 5, 'SELECT/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ to_char(b.lmode)) mode_held, DECODE(b.request, 0, 'None', /* Mon Lock equivalent */ 1, 'Null', /* NOT */ 2, 'Row-SELECT (SS)', /* LIKE */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* SELECT */ 5, 'SELECT/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ to_char(b.request)) mode_requested FROM sys.dba_objects a, sys.v_$lock b, sys.v_$session c WHERE a.object_id = b.id1 AND b.sid = c.sid AND OWNER NOT IN ('SYS','SYSTEM');
Listing 7.3 shows the output of locks.sql.
Listing 7.3 The results from locks.sql.
SQL> @locks Session Database Lock Mode Mode ID Object Username Active Ima Type Held Requested ---------- ------------ -------- ---------- ------- ----- -------- 2 DUAL Media Reco Share None 2 SYSTEM_PRIVI Media Reco Share None 2 TABLE_PRIVIL Media Reco Share None 2 STMT_AUDIT_O Media Reco Share None 2 V$CONTROLFIL Media Reco Share None 2 V$DATAFILE Media Reco Share None 2 V$LOG Media Reco Share None 2 V$THREAD Media Reco Share None 2 V$PROCESS Media Reco Share None 2 V$BGPROCESS Media Reco Share None 2 V$SESSION Media Reco Share None 2 V$LICENSE Media Reco Share None 2 V$TRANSACTIO Media Reco Share None 2 V$LATCH Media Reco Share None 2 V$LATCHNAME Media Reco Share None 2 V$LATCHHOLDE Media Reco Share None 2 V$RESOURCE Media Reco Share None 2 V$_LOCK Media Reco Share None 2 V$LOCK Media Reco Share None 2 V$SESSTAT Media Reco Share None 20 rows selected.
Choosing how to pair tables and indexes into tablespaces has a great impact on the performance of distributed databases. Since the designer has many choices, it is a good idea to explore the available options. In general, the following characteristics apply:
Previous | Table of Contents | Next |