Pooled Connection Request timeout

There is probably some code that you have out there that is not properly disposing of some objects. This will cause Oracle to hold on to connections that are not actually in use causing you to run out of available connections in the pool. Restarting IIS solves it because it kills all those connections.

Keep seperate pool for every hosted application and monitor which application is taking resources and report the same to respective product programmer.

request channel timed out while waiting for a reply after

Check proxy setting on that machine.

Sometimes dns is not able to resolve address so try IP instead of using name in local ldaddonmodules config file.

External Table vs Global Temporary Table GTT

You want to read big size text file or any file from OS level in one go then use external table.

Global temp tables is used when you want to store data on fly in temporary table from your plsql cursor or vice versa and later insert into main tables.

GTT is faster in comparison to External table.

You can create index on GTT not on external table.

All depends on usage.

Web Mobile Login page security | Penetration Testing | VAPT

Don't allow special character in user ID box.

Never display Ora- or any .net message on error. It should be user defined.

Also Sensitive information like server details should not be transferred in plain text between client and server.

JSON format

JSON is a format to store and exchange data between client and server. It is very simple,light weight, compatible with all popular programming languages.

How third party web or mobile app connect with your backoffice database?

For that we develop an API and host at an application server IIS. Which will communicate (request and response) between client(third party mobile, web) and server(Backoffice database) through JSON format.

Change Oracle Profile idle Time | Application Will disconnect if it is IDLE | Sniped Session

select * from dba_profiles;

select profile, limit from DBA_PROFILES

where profile = 'DEFAULT'

and resource_name = 'IDLE_TIME';

select profile, limit from DBA_PROFILES

where profile <> 'DEFAULT'

and resource_name = 'IDLE_TIME';

alter profile BOPOLICY LIMIT IDLE_TIME 30;

alter profile DEFAULT LIMIT IDLE_TIME 30;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;

It is changed to 30 min of idle time. The application will disconnect if it is idle for 30.

Status of session will be marked as sniped.

TO clean sniped session, need to schedule job to kill sniped session. 

CREATE OR REPLACE PROCEDURE "FLUSH_SNIPED_SESSION" 

AS

BEGIN

    FOR X IN (

            SELECT inst_id,SID, SERIAL#

            FROM GV$SESSION

            WHERE USERNAME IS NOT NULL

             AND STATUS='SNIPED'

        ) LOOP

        EXECUTE IMMEDIATE 'alter system disconnect session '''|| X.SID

                     || ',' || X.SERIAL# || ',@' || X.inst_id || ''' immediate';

    END LOOP;

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_JOB(job_name        => 'FLUSH$SNIPED$SESSION',

                          job_type        => 'STORED_PROCEDURE',

                          job_action      => 'FLUSH_SNIPED_SESSION',

          start_date      => '10-MAR-13 10:00.00.00 AM ASIA/CALCUTTA',

                          repeat_interval => 'freq=minutely; interval=5;byhour=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',

                          end_date        => NULL,

                          enabled         => TRUE,

                          comments        => 'Flush Sniped Session Kshitij Agarwal');

END;

/

exec dbms_scheduler.run_job('FLUSH$SNIPED$SESSION');

convert date to number

select to_number(to_char(sysdate, 'ddmmyyyy')) from dual;