Tag: session

General DBA, trace

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 the tracefile: … *** 2012-05-28 14:35:25.005 Processing Oradebug command ‘dump … Continue reading How to get dump or list parameters set at session level?

Alex LimaLeave a comment
General DBA

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 157 210 Continue reading How to see current utilization of processes/sessions and max utilization?

Alex LimaLeave a comment
General DBA, role

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 roles in the current session SQL> set role test11, delete_catalog_role; … Continue reading How to list current active roles for a user in a session?

Alex LimaLeave a comment
General DBA

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 * from temp1; no rows selected — Global temporary table which … Continue reading Global temporary tables

Alex LimaLeave a comment
SQL Server, Tuning

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, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' … Continue reading List locks held by a session

Alex LimaLeave a comment
General DBA, trace

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 SCOTT 0 INACTIVE SYSTEM 34 INACTIVE .. Continue reading How to find the last time a session performed any activity?

Alex LimaLeave a comment