Wednesday, November 28, 2012

Reclaim Space in Tablespace using Shrink Command




Benefits regarding to Oracle Documentation :

  • Full table scans will take less time (a table scan will always scan up to the HWM even if space is not used)
  • Better index access takes place because of a smaller B-Tree
  • Space is freed up for other database objects
  • Space below the HWM is released and the HWM is moved down

 Thank you
Osama Mustafa

Tuesday, November 27, 2012


Open_Cursor Parameter , in this article i will discuss what this parameter do  , I used Oracle Documentation to describe this parameter but in simpler way .

In Oracle Documentation

Open_cursor : specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.

to check in database

SQL> show parameter Open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
As we see in the last Line , "its prevent Session from opening excessive number of cursor"  how is that !!!

Open_cursor located in shared_pool which is part of SGA ( library Cache) , The benefit of this parameter is to prevent Session clogging CPU with requests .

But what i mean when I set this Parameter to integer , let take the above example :

-Parameter is set to 300
-That mean each session can have  300 cursors

If this session fill the 300 What will happened !!!
 "ora-1000 maximum open cursors exceeded"
On Documentation Oracle Recommended to set this Parameter for high value  for Application usage , if the above error raised and you already set to high Value then make sure your developer is closing their cursor and this is common issue.

SQL> Show parameter cursor

NAME                                      TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                             string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                               integer     300
session_cached_cursors               integer     20

But what others Parameter mean,  what each parameter mean lets ask this question what if User or session run query include the same cursor , is it take another space in memory !!!

it dose not make sense , so in this case we use SESSION_CACHED_CURSOR.

regarding to oracle documentation

SESSION_CACHED_CURSOR : its Number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache.  

you have to know some truth about this parameter :
1) its cache only close cursor which mean if you cursor still opened how could it be cached !!!

2) you can set this parameter > OPEN_CURSOR , < OPEN_CURSOR Or equal OPEN_CUROSR , that indicate us between relation between this parameter ----> NOTHING.

3) if you are not setting this parameter ( Value 0 ) then no cursor will be cached for session . But Wait we said oracle used shared pool in cursor Yes , your cursor will be in SHARED_POOL but session have to find this cursor , so if you set to non zero first thing oracle will do is check SHARED_POOL ( Library cache ) and back to you with result .

After discuss point number 3 we see the main advantage for cache cursor which is better performance , enhance query execution time.

But the shared_pool has limited size so i can't cache all my cursor , now next parameter will avoid this


Boolean value (TURE|FALSE) each one of them mean something

FALSE : Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.

TRUE : Shared SQL areas are kept pinned in the shared pool , if you are using application contain lot of cursor it will be good to set it to enhance execution time ( to do that shared pool must be large enough to hold all open cursors simultaneously) .

 if i have 3 query each one want to use same cursor how oracle handle this , Parameter number 4 will control that.


CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

this parameter take three value ( FORCE | SIMILAR | EXACT ) i will describe each one of them , Oracle Documentation definition :  

-Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

-Similar Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

-Exact Only allows statements with identical text to share the same cursor.

you can't imagine how many document you will find if you search on google for this parameter , examples , article and tuning for it.

to set this parameter you have to make sure what you need , understand it.the best way to do that with trying to test it.

Thank you
Osama Mustafa

Saturday, November 24, 2012

Change Listener Port (Single Instance)

In this Topic i will discuss how to change listener ports , The Database 10gR2 , Operating System RHEL 5.7  this demonstration will work on any Database or platforms , The Simplest way to change listener Ports

Information :
DB Version :
OS : RHEL 5.7
Listener Name : LISTENER
Old Port : 1521
New Port : 1523

Step one :  Check Listener Status .

The Below screen describe The Old Status For Listener Notice the Port is 1521 (Default One)

Step two : Stop Listener  (lsnrctl stop)

Step three : Use netca command  to Change Listener Port Follow the screens

in below screen you can choose the listener port you want to change .

Step Four : After Close netca GUI listener will start automatically

Step Five : Go to $ORACLE_HOME to change tnsnames.ora that used Old Listener port to new port.

Listener.ora will change automatically via netca GUI so no need to update it .

Step Six : Database will not register atomically after doing the above steps , so you have to set LOCAL_LISTENER Parameter Via Sqlplus like the following , i will post lsrnctl status to confirm what i saying, also notice new port that has been changed .

Now , Sqlplus / as sysdba

I Used Scope=memory to test the connection first  , after test the connection successfully you can change scope = spfile.

Step Seven : Test Connection to make sure Every User will Able To connect .

Thank you
Osama Mustafa

Thursday, November 22, 2012

What Oracle Version Digit Mean

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.
Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).
Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Thank you
Osama Mustafa

Wednesday, November 21, 2012

ora-12705 cannot access nls data files or invalid environment specified

This error appear On Sql developer .

ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified

Solution :

Try to add following lines to %SQL_DEV_HOME%\sqldeveloper\bin\sqldeveloper.conf
AddVMOption -Duser.language=en

us depend on your language .
en :  England and so on ....

Thank you
Osama Mustafa


i took this procedure from OTN forum  to convert BLOB to CLOB

v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)

v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
RETURN v_clob;
END blob_to_clob;
 Example :
Select blob_to_clob(blob_column) from table_name;

Monday, November 19, 2012

Check DataGaurd Role Primary/Standby

Which Database is the primary Database , Which One Is the Standby

Simple Query Will Answer this , The Scenario Like the following I have Data Guard I want to check which one Of these database are Primary Or Standby How Can I do that :

SQL > select database_role from v$database;


The above Output Indicate that you are Now On Primary Database, Different Output for Standby

SQL > select database_role from v$database;


There's More than One Way
SQL> SELECT controlfile_type FROM V$database;

Output On Primary Database :


On Standby :


Thank you
Osama Mustafa

WARNING: Subscription for node down event still pending

Warning Appear like the following : 
[oracle@sun3 ~]$ cat /u01/app/oracle/product/10.2.0/network/log/listener1.log
19-NOV-2012 14:00:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=sqlplus@sun3)(HOST=sun3)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST= * establish * orcl1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
19-NOV-2012 14:01:02 * ping * 0
19-NOV-2012 14:08:34 * service_update * orcl1 * 0
19-NOV-2012 14:18:37 * service_update * orcl1 * 0
WARNING: Subscription for node down event still pending
19-NOV-2012 14:23:32 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=sun3)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
19-NOV-2012 14:23:59 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=sqlplus@sun3)(HOST=sun3)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST= * establish * orcl1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
19-NOV-2012 14:28:40 * service_update * orcl1 * 0
19-NOV-2012 14:30:38 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=sqlplus@sun3)(HOST=sun3)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST= * establish * orcl1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
19-NOV-2012 14:38:43 * service_update * orcl1 * 0
WARNING: Subscription for node down event still pending

In Listener.ora add the following :


Thank you
osama mustafa

Saturday, November 17, 2012

Change Listener Default Name

Step One :

ps -ef | grep tns

oracle    4214     1  0 22:51 ?        00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

Step two :

lsnrctl stop LISTENER
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version - Production on 17-NOV-2012 22:55:41

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

The command completed successfully

Step Three :

Go to $ORACLE_HOME/network/admin and modify Listener.ora

[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/

    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

Step Four :

lsnrctl start OSAMA

Step Five :

SQL> show parameter local_listener

SQL> alter system set local_listener='(address=(protocol=tcp)(host=xx.xx.xx.xx)(port=1521))';
SQL> alter system register;

Thank you
Osama Mustafa

Thursday, November 15, 2012

Dealing With Oracle Traces

This article using Oracle Database 11g , I will post for 10g Later .

How Could I change name for Oracle Trace :

alter session set tracefile_identifier = 'some_id';

    SQL> alter session set tracefile_identifier = 'osama';

    Session altered.

    SQL> oradebug tracefile_name


maximum size of Oracle Trace File

-By Set max_dump_file_size parameter
- alter session set max_dump_file_size = unlimited;

Finding Oracle Trace File for current session :

     SELECT value
    FROM v$diag_info
    WHERE name = 'Default Trace File';

Finding Oracle Trace File for Current Database Process

     SELECT pid, program, tracefile
    FROM v$process;

To find all trace files for the current instance:


and you could use ADRCI features.

Thank you
Osama mustafa


In this article i will discuss the Difference between these parameter and how to use them ,  MEMORY_TARGET & MEMORY_MAX_TARGET  parameters appear in 11g .

I will depend on oracle documentation to describe these parameter :

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.

The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.


you can manage SGA and PGA together rather than managing them separately.

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

  • SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
  • SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
  • SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
  • PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

You can have some Description  On

 Thank you
Osama Mustafa

ORADEBUG to Display Trace Name and Location

The Below Example Show you how I did it :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release - Production on Thu Nov 15 17:46:00 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace; --any Statement to Generate Trace File.

Database altered.

SQL> oradebug tracefile_name

Thank you
Osama Mustafa

Wednesday, November 14, 2012

Register Listener In Database

Listener listens to new connections who is trying to connect to DB server. If the listener goes down, new users would not be able to connect to DB server. But still, already connected users would be able to do their work normally.

Listener   waiting  requests  from Clients to connect to the Instance. By default, the Listener name is (amazingly enough)“Listener” (but you can call it anything you like). It listens for connection requests on aparticular port (the default port number in 8.0 and above is 1521, but once again you canset this to any valid port number if you wish). A client knows where to contact the Listener (the machine it’s running on, and the port it’s listening on) because  a local configuration file, called “tnsnames.ora”, gives it the necessary information. More advanced configurations can dispense with the  tnsnames.ora .

How  to  Register  Listener  In  Database  

1.) Static Instance Registration
2.) Dynamic Instance Registration

Lets Discuss These Method and Start With Static Instance Registration :

Its basic method , and use $ORACLE_HOME\NETWORK\ADMIN\listener.ora its look like

        (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
And When You Fire lsnrctl status , instance Name Appear with Unknown like the following :

Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance.

Dynamic Instance Registration (service registration):
in this Way the PMON is Responsible about Register Listener ,

Benefit for this way like the following :

1.) Simplified configuration  :  Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note :  The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time fail over  : Because the listener always knows the state of the instances, service registration facilitates automatic fail over of the client connect request to a different instance if one instance is down.
In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.  To ensure service registration works properly .
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL2" has 1 instance(s).
  Instance "ORCL2", status READY, has 1 handler(s) for this service...
The command completed successfully
 To register Database name with listener in this way you could use :


Thank you
Osama Mustafa 

Tuesday, November 13, 2012

ORA-01102: cannot mount database in EXCLUSIVE mode

Error happened when try open database :

SQL> startup
ORACLE instance started.

Total System Global Area 267227136 bytes
Fixed Size 2212496 bytes
Variable Size 205524336 bytes
Database Buffers 54525952 bytes
Redo Buffers 4964352 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

 In alert.log
sculkget: lock held by PID: 12359
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12359
ORA-1102 signalled during: ALTER DATABASE MOUNT...
On OS level :

[oracle@localhost trace]$ ps -ef | grep pmon

oracle 10222 1 0 10:27 ? 00:00:00 ora_pmon_ORCL
oracle 2522 1 0 Nov09 ? 00:00:06 ora_pmon_orcl

Use :

 Kill -9 10222 2522

1-After that fire export ORACLE_SID= (watch case sensitive )
2-sqlplus / as sysdba
3-startup ;

Thank you
Osama mustafa

Some Photo Of Duabi Gitex

Thank you
Osama mustafa

Friday, November 9, 2012

Multiple DataFiles and Mutliple Tablespace

As Any Database administrator you are managing your database check the size of your tablespace and datafiles , try to get better performance But why we are using Mutliple Tablespace and datafiles :

Advantage multiple tablespaces :

Control disk space allocation for database data
Assign specific space quotas for database users
Control availability of data by taking individual tablespaces online or offline
Perform partial database backup or recovery operations
Allocate data storage across devices to improve performance

The Same For Multiple DataFiles :
put each data file on a separate disk array. This decreases contention between disks.

Thank you
Osama Mustafa

extract cpio file

Some Oracle Installation file is .CPIO

what this file mean : extension is a UNIX and its file archive .

How to Extract this file :

$ cat cpio_file | cpio -idmv

Simple Topic But Useful .

Thank you
Osama mustafa

Wednesday, November 7, 2012

Session memory For Oracle User

To check the Memory Usage for Each Seesion :

select username,name,value
from v$session join v$sesstat using (sid)
join v$statname using (statistic#)
where name = 'session pga memory' and username='';

Example :

USERNAME              NAME                                   VALUE
----------------------------------------------------------- ----------
SCDB                       session pga memory                    699884

SCDB                       session pga memory                     651376

Thank you
Osama mustafa

Sunday, November 4, 2012

V$Session_Wait VS V$Session_event

Both of these tables are view In database , I will talk about what is the difference between them and Why some of record appear in V$Session_wait but not in V$session_event , Simple Topic But useful and good to know , Also Check the document that i post For Oracle that gave you structure for both of these view :

displays the current or last wait for each session.

lists information on waits for an event by a session

This is simulation for both table appear what is the common between two view :

select SID, EVENT from v$session_wait where event='DIAG idle wait';
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
select SID,EVENT from v$session_event where event ='DIAG idle wait' ;
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
Also its good to know when you are using 10g or later you can use v$seesion which is  gives you real-time information, what is happening right now.

gives you real-time information, what is happening right now :

there's different type of enqueue:wait in Oracle like the following :

    enq: TX - allocate ITL entry
    enq: TX - contention
    enq: TX - index contention
    enq: TX - row lock contention 

to check them you can query V$EVENT_NAME view provides a 
complete list of all the enq: wait events. 

But in V$session_wait you can check the following :

     P1: Lock TYPE (or name) and MODE

    P2: Resource identifier ID1 for the lock

    P3: Resource identifier ID2 for the lock

 Which is not found in v$session_event .

So We can say :

displays the events for which sessions have just completed waiting or are currently waiting.
is similar to V$SYSTEM_EVENT, but displays all waits for each session.

 Reference Document :

Thank you
Osama Mustafa



is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .

initialization parameter defines up to ten (where n = 1, 2, ... 10) destinations in oracle 10g and thirty one (n=1,2....31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.


Syntax : LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'

is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:

%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

Reference Document :
1-Oracle Log_Archive_log Documentation.
2-Oracle Log_arcchive_Dest