Author Archive
Using dba_jobs_running it will show the all jobs executing. SQL> select djr.sid, djr.job, djr.failures, djr.this_date, djr.this_sec, dj.what from dba_jobs_running djr, dba_jobs dj where djr.job = dj.job; SID JOB FAILURES THIS_DATE THIS_SEC ———- ———- ———- ——————– ——– WHAT —————————————————————————————————- 68 21 0 19-feb-2012 21:09:34 21:09:34 dbms_lock.sleep(100);
Using dbms_job one can schedule a job, in the following example schedules a job to run every 5 minutes starting from current time. The next interval can be fixed time or it can be based on the time when the last run finished, in the example below the interval is time when the last run/time [...]
Using $ORACLE_HOME/rdbms/admin/awrsqrpt.sql one can generate execution plan from AWR by passing start snap id, end snap id and SQL ID. It will ask for most of the same inputs like awrrpt.sql, type of report (html/text), start snap id, end snap id, SQL id and report name and create a output in the file specified. Example: [...]
If you have forgotten the root password and you need to reset it on CentOS, one can reset it by following steps: a) At the splash screen during boot time, press any key which will take you an interactive menu. b) Then select a Linux version you wish to boot and press “a” to append [...]
On Linux: Using uname with -m option as show below [root@localhost ~]# uname -m x86_64 On Windows: Refer to following link for windows http://support.microsoft.com/kb/827218
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 [...]
Sample code that shows using BULK COLLECT and performance of using different values for LIMIT. declare cursor l_cur is select * from scott.emp; type emp_tbl is table of l_cur%rowtype index by pls_integer; l_emp emp_tbl; limit_in number; i number; begin limit_in := &limit_param; open l_cur; loop fetch l_cur bulk collect into l_emp limit limit_in; for i [...]
Using the following steps one can setup vim/gvim to run on windows. 1) Add the following new variable VIM on your system. Example: VIM=C:\apps\vim\vim73 (Where C:\apps\vim\vim73 is where vim/gvim is installed) 2) Copy the file %VIM%\vimrc_example.vim as _vimrc in %VIM% folder 3) Add the following 2 lines at the end in %VIM%\_vimrc so it sets [...]
Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user. Example: The following trigger [...]
In “ALTER SYSTEM” one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed. Example: SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ'; SQL> select value, update_comment from v$parameter where name = 'open_cursors'; VALUE [...]
