How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode=first_rows; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc Contents of…

How to see current utilization of processes/sessions and max utilization?

Using the following SQL one can find the current number of processes and sessions connected and also max utilization so one can check if you need to increase the values of the parameter SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in (‘processes’,’sessions’); Output: RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION —————————— ——————- ————— processes 146 196 sessions…

How to list current active roles for a user in a session?

Using SESSION_ROLES it will retrieve the current roles that are active for a user in a session. Example: SQL> connect test/… SQL> select * from session_roles; ROLE —————————— DELETE_CATALOG_ROLE Using “SET ROLE” one can change the roles active in the current session. Example: SQL> select * from session_roles; ROLE —————————— TEST11 Enable specific set of…

Global temporary tables

— Global temporary table which keeps records till session create global temporary table temp1( year number ) on commit preserve rows; SQL> insert into temp1 values ( 11 ); 1 row created. SQL> select * from temp1; YEAR ———- 11 — reconnect to session and check records SQL> connect /as sysdba Connected. SQL> select *…

List locks held by a session

To list locks held by a current session select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, object_name(rsc_objid) tablename, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x,…

How to find the last time a session performed any activity?

In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database. select username, floor(last_call_et / 60) "Minutes", status from v$session where username is not null –– to ignore background process order by last_call_et; USERNAME Minutes STATUS —————————— ———- ——– SYS 0 ACTIVE…