Oracle PL/SQL Tutorial/System Tables Data Dictionary/session
Query v$session
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>
Query v$session for user name, sid and serial#
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>
Select $lock and $session for locked objects
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>