Oracle PL/SQL Tutorial/System Tables Data Dictionary/session

Материал из SQL эксперт
Перейти к: навигация, поиск

Query v$session

   <source lang="sql">

SQL> SQL> select username, sid, serial#, process, status

 2  from v$session
 3  where username is not null
 4  /

USERNAME SID SERIAL# PROCESS STATUS


---------- ---------- ------------ --------

sqle 32 1171 1624:1144 ACTIVE SQL> SQL></source>


Query v$session for user name, sid and serial#

   <source lang="sql">

SQL> SQL> SQL> column username format a15 word_wrapped SQL> column module format a15 word_wrapped SQL> column action format a15 word_wrapped SQL> column client_info format a30 word_wrapped SQL> SQL> select username||"("||sid||","||serial#||")" username,

 2         module,
 3         action,
 4         client_info
 5  from v$session
 6  where module||action||client_info is not null;

USERNAME MODULE ACTION CLIENT_INFO


--------------- --------------- ------------------------------

sqle(32,1171) SQL*Plus processing row owner=SYS

                               6547

SQL></source>


Select $lock and $session for locked objects

   <source lang="sql">

SQL> set echo on; SQL> SQL> create table t1 ( x int ); Table created. SQL> SQL> create table t2 ( x int ); Table created. SQL> SQL> insert into t1 values ( 1 ); 1 row created. SQL> SQL> insert into t2 values ( 1 ); 1 row created. SQL> SQL> select username,

 2         v$lock.sid,
 3             id1, id2,
 4         lmode,
 5             request, block, v$lock.type
 6  from v$lock, v$session
 7  where v$lock.sid = v$session.sid
 8    and v$session.username = USER
 9  /

USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY


---------- ---------- ---------- ---------- ---------- ---------- --

sqle 20 25832 1 3 0 0 TO sqle 20 26171 0 3 0 0 TM sqle 20 26172 0 3 0 0 TM sqle 20 589851 2897 6 0 0 TX 4 rows selected. SQL> select object_name, object_id from user_objects;

OBJECT_NAME OBJECT_ID


----------

ADDRESSTYPE 24496 ADDRESS_OBJ 15867 ADD_NEW_EMPLOYEE 14738 AOBJ 15944 AOBJ 15945 AUTHORS_SEL 25781 AUTHOR_FIRST_NAME_UPD 25820 AUTHOR_SEL 25798 BIN$dgfyftgCSZSBnu0LqVJzrQ==$0 25904 BIN$fKX+qNArQt+8GLZvNQJlrQ==$0 25901 BIN$zbJqSAxnQA2up7MHjGauHw==$0 25900 BIND_TEST 25769 CHANGEPRICE 26031 COMPILE_WARNING 25789 COMPUTE_DISCOUNTS 25731 DELETEEMPLOYEE 14633 DELETEMYEMPLOYEE 14636 EMPLOYEETYPE 24499 EMPLOYEETYPE 24500 EMPLOYEE_COUNT 25744 EMPLOYEE_TAB 14797 EMP_CHANGE_S 25936 EMP_DATA 25672 EMP_DEPT 14838 EMP_NAME_CHANGE 25966 EXITFUNC 14840 F_GETDIFF 25928 GETAREA 25925 HELLO 15128 HELLOFLE 15126 HIKE_PRICES 25730 INSERT_NUMBERS 14934 LOG_SHUTDOWN 25669 LOG_STARTUP 25666 MEASURE_USAGE 26103 MULTIPLE_CURSORS_PROC 14638 MYPROCEDURE 15863 MY_FIRST_PROC 25729 NAMED_BLOCK 25786 NEWYORKEMP 15452 P 25830 PEOPLE 25899 PERSONTYPE 24497 PKG 25930 PKG_TEST1 25932 PKG_TEST1 25931 PRODUCTTYPE 24498 P_HELLO 25913 P_HELLOTO 25914 P_PRINT 25924 P_SPLIT 25929 SESS_EVENT 25832 STATE_PACKAGE 14962 SWAP 15064 T1 26171 T2 26172 TEMP_ALL_OBJECTS 26112 TEMP_ALL_OBJECTS_IDX 26113 TEST_TIME 25740 TRANSACTION_TAB 14799 UNQ_1 14839 UPDATEEMPLOYEE 14695 UPDATEMYEMPLOYEE 14667 UPDATE_EMP 25736 64 rows selected. SQL> SQL> drop table t1; Table dropped. SQL> drop table t2; Table dropped. SQL> SQL></source>