Author: Shervin Sheidaei

In this article we will discuss some common problems faced by most Oracle database administrators and the solutions to resolve the issues.

Oracle Tip 1: Finding maximum number of concurrent users

The maximum number of concurrent user sessions since last database startup can be found by running below command

SQL> SELECT * FROM V$LICENSE;

The SESSIONS_HIGHWATER column in the Oracle view V$LICENSE will show the highest number of concurrent users.

Below line in Oracle alert log can help you determine the maximum number of concurrent users after each shutdown.

Shutting down instance (normal)
License high water mark = 3

Oracle Tip 2: Finding enough Redo Log Files for Oracle Database

The best way to determine the number of enough redo log files for your Oracle database is to look for online redo logs in action and then size them.

Another option is to start with 5 redo log groups of size 100 MB each. If you do not see any CHECKPOINT NOT COMPLETE messages in the Alert Log then keep the things going otherwise you will have to increase the size of online redo log groups.

You may also need to increase the size of online redo logs if your redo log group switches too often. If your online redo logs are not switching too often then you can reduce the size of your online redo logs.

Oracle Tip 3: Tracking Too Many Open Cursors Problem

Sometimes it happens that our Oracle Alert Log reports too many open cursors and the cursor in PL/SQL never get close. You can track this problem by running below lines of code.

SELECT SADDR, SID, USER_NAME, ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT
FROM V$OPEN_CURSOR
WHERE SID IN
(SELECT SID FROM V$OPEN_CURSOR GROUP BY SID HAVING COUNT(*)>&THRESHOLD);

Oracle Tip 4: Resolving Listener Crash in Windows

Metalink provides a number of options for resolving the problem where Oracle listener gets crashed whenever a connection is established on windows. Below command can help you fix the problem after running and restarting the Windows box.

netsh winsock reset catalog

The netsh winsock reset catalog command is available in Windows XP SP2 and is used to reset the Winsock catalog to its default configuration. This can be useful if network connectivity is lost due to installation of a malformed LSP. However you should be careful in using this command because you will need to reinstall any previously-installed LSPs.

Oracle Tip 5: Stop a Running 10g Job

In Oracle releases prior to 10g you could stop all running jobs by simply setting JOB_QUEUE_PROCESSES to zero. However Oracle 10g has introduced many advanced features in the SCHEDULER_JOB. Oracle 10g allows you to schedule job either in DBA_JOBS or DBA_SCHEDULER_JOBS. If you set JOB_QUEUE_PROCESSES to zero then it will stop the jobs in DBA_JOBS only and the jobs in scheduler will be still running .

You can run below command for finding all running jobs in Oracle 10g scheduler.

SELECT owner, job_name, state
FROM DBA_SCHEDULER_JOBS

Now you can stop jobs in DBA_SCHEDULER_JOB by running below script for each job in scheduler.

BEGIN
SYS.DBMS_SCHEDULER.DISABLE( '"MySchemaName?"."MyJobName?"' );
END; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   What if your database lock gets blocked??
   Reveal the shades of Oracle result_cache!!
   Great Tips on Table Recovery with RMAN Backup!!
   The OS debate: Oracle on Linux - Oracle on Windows