ORA-12518: TNS: listener could not hand off client connection

When you hit this error "ORA-12518: TNS: listener could not hand off client connection" ,Check the alert log.In my case the reason was , ORA 00020 – Maximum number of processes exceeded.When I queried the number of sessions, I can see that the number was more than 150, which I specified in my init.ora file.This prevents further connections to this service, resulting in ORA-12518: TNS: listener could not hand off client connection.

If my database runs in windows do this.

Work around :Restart the Oracle Service in windows

The reason for this error could be limited server resources or configuration. Try to check these

1. Database server memory were enough for new connection
2. Database connection parameters such as
       2.1 dedicated server, pls checked processes parameter or query from v$resource_limit where resource_name='processes'; checked max_utilization must less than Limit_value or
       2.2 shared server, pls verify large_pool_size or check dispatcher were enough for all connection.

3. You may check related log such as listener.log, alert log, trace file, etc

Oracle Index Rebuild and Table Write

A big table ( 10 GB ) which has an index on a number field. Index is almost same size as the table and I want to rebuild the index.The database is very active and the table is also very active read about 100K times in 20 minutes and few writes as well.Index rebuild may be needed when  there are a significant number of deletes, and also do significant index range scans . When index rebuild process, does oracle allow writing to the table ( insert into that table) ?

We can use the online keyword then DML can happen concurrently,if not, then not.

ALTER INDEX ... COALESCE instead of REBUILD.  It is transactional and can be executed concurrently with DML with much less risk (and less licensing) than ALTER INDEX ... REBUILD ... ONLINE

How to create Oracle Database Link

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Oracle Net must be installed on both the local and remote Oracle databases.

This is how you can create a database link .

CREATE DATABASE LINK prod_test
        CONNECT TO schema_user IDENTIFIED BY user_password
        USING '(DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=(PROTOCOL=TCP)(HOST=<target database host>)(PORT=<target database  port>))
                )
                (CONNECT_DATA=(SID=<target database ORACLE_SID>))
                )'
;

Oracle SQL Query to get Date Range as Single row Block

Here is an Oracle SQL Query to return rows , with each day as a single row range value. This query can be used in reporting , when things on different day has to be queried as a date range and as a single row.

create table test_acy(acy_cd varchar2(5),start_dt date,end_dte date);

insert into test_acy values('AB','01Jan2010','02Jan2010');
insert into test_acy values('AB','02Jan2010','03Jan2010');
insert into test_acy values('AB','03Jan2010','04Jan2010');
insert into test_acy values('AB','06Jan2010','07Jan2010');
insert into test_acy values('AB','09Jan2010','10Jan2010');
insert into test_acy values('AB','10Jan2010','11Jan2010');

insert into test_acy values('CD','11Jan2010','12Jan2010');

If My table has rows like this, and would like to get rows as

AB        01Jan2010        04Jan2010
AB        06Jan2010        07Jan2010
AB        09Jan2010        11Jan2010
CD        11Jan2010        12Jan2010.

SQL Query for this
SELECT   acy_cd,
         MIN(start_dt),
         MAX(end_dte)
FROM     (SELECT acy_cd,
                 start_dt,
                 end_dte,
                 SUM(win) OVER(PARTITION BY acy_cd ORDER BY start_dt)
                                                                    win_group
          FROM   (SELECT   acy_cd,
                           start_dt,
                           end_dte,
                           CASE
                              WHEN start_dt !=
                                     LAG(end_dte) OVER(PARTITION BY acy_cd ORDER BY start_dt)
                                 THEN 1
                              ELSE 0
                           END win
                  FROM     test_acy
                  ORDER BY acy_cd, start_dt))
GROUP BY acy_cd, win_group

OR

SELECT   acy_cd,
         MIN(start_dt) start_dt,
         MAX(end_dte) end_dte
FROM     (SELECT     LEVEL lvl,
                     t.*,
                     SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(t.start_dt,
                                                        'DDMONYYYY'),
                                                ','),
                            2,
                            9
                           ) path_root
          FROM       test_acy t
          CONNECT BY acy_cd = PRIOR acy_cd  AND
                     start_dt = PRIOR end_dte
          START WITH t.start_dt IN(SELECT t2.start_dt
                                   FROM   test_acy t2 LEFT OUTER JOIN test_acy t3
                                          ON t2.start_dt = t3.end_dte AND
                                            t2.acy_cd = t3.acy_cd
                                   WHERE  t3.end_dte IS NULL))
GROUP BY acy_cd, path_root
/

ACY_CD        START_DT        END_DTE

AB        1/6/2010        1/7/2010
AB        1/9/2010        1/11/2010
AB        1/1/2010        1/4/2010
CD        1/11/2010        1/12/2010



 
 

Move Oracle Datafile to a New location/Partition

You may want to move oracle datafiles to a new disk partition or a new location at some point of time, when your disk space is full or when you are doing a server administration.

Do the Steps shown below

1.Generate the move Data files command as shown below
        SELECT    'ALTER DATABASE RENAME FILE'
                       || NAME
                       || ' TO  <new location>'
                       || NAME
                FROM   v$datafile
2.Shutdown the database
3.Copy or move the data file to its new location. On Unix this can be done with the "dd" command.
        Example in Unix , you can use the dd command :
         dd if=/old/location of=/new/location bs=4096
4. Start up the database in mount state
        startup mount;
5.Move the datafile - Use the statements generated in first step for this.
6. ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
7. Open the database
ALTER DATABASE OPEN;



 
 

Oracle Views - Updatable Views

An updatable in Oralce is a  view  you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

·        Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.

·        The view must not contain any of the following constructs:

A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY