Ask The Real Tom

March 12, 2010



Quick Help: TNS – 12519 TNS: no appropriate service handler found

Filed under: oracle — Tags: , , , , — admin @ 4:35 pm

If you get a “TNS – 12519 TNS: no appropriate service handler found” the very first thing to check is the max session/processes.

select * from v$resource_limit

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                       47             300        300        300
sessions                                        52             309        335        335
enqueue_locks                                   34              91       5008       5008
enqueue_resources                               53             260       2514  UNLIMITED
ges_procs                                        0               0          0          0
ges_ress                                         0               0          0  UNLIMITED
ges_locks                                        0               0          0  UNLIMITED
ges_cache_ress                                   0               0          0  UNLIMITED
ges_reg_msgs                                     0               0          0  UNLIMITED
ges_big_msgs                                     0               0          0  UNLIMITED
ges_rsv_msgs                                     0               0          0          0
gcs_resources                                    0               0          0          0
gcs_shadows                                      0               0          0          0
dml_locks                                       59             454       1472  UNLIMITED
temporary_table_locks                            0              23  UNLIMITED  UNLIMITED
transactions                                  1409            1442        368  UNLIMITED
branches                                         0              16        368  UNLIMITED
cmtcallbk                                        0               3        368  UNLIMITED
sort_segment_locks                               0              40  UNLIMITED  UNLIMITED
max_rollback_segments                           29              58        368      65535
max_shared_servers                               1               1  UNLIMITED  UNLIMITED
parallel_max_servers                             0               8          8       3600

As it is visible at MAX_UTILIZATION for processes the maximum processes was once reached.

Just increase it an restart your database.

alter system set processes=600

February 24, 2010



Extensive PL/SQL Exception Handling

Filed under: Uncategorized — Tags: , , , , — admin @ 12:09 pm

If pl/sql exception get catched, it can be difficult to get the cause of the error.

To work around this the exception handler should log what was going wrong.

Suggestion

DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );

Powered by WordPress