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…

Example shows how “DISABLE TABLE LOCK” works

Following example shows what happens when locks are disabled on the TABLE. -- disable lock on a table SQL> alter table t disable table lock; Table altered. -- shows one can’t drop table as table locks are disable SQL> drop table t; drop table t * ERROR at line 1: ORA-00069: cannot acquire lock —…

How to trace a running process?

Using strace one can trace the system calls being executed by a running process. To stop the strace press control-C To display the system calls being executed $ strace -p <pid> or To display a summary of system calls being executed $ strace -cfo <logfile> -p <pid> Example: In this example smon process was being…

ORA-00353: during startup of instance from archiver process

When starting one of the databases we received the ORA-00353 error. It indicates that archiver process couldn’t archive one of the redo logs due to corruption. Oracle tried to read both the logmembers from the same group but they both were corrupted and instance crashes when trying to open it. ARC0: STARTING ARCH PROCESSES COMPLETE…

How to see the oldest flashback available?

Using the following query one can see the flashback data available. SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME, (sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f; CURRENT_TIME OLDEST_FLASHBACK HIST_MIN —————- —————- ———- 2012-04-25 07:34 2012-04-25 05:48 106.066667