Difference between revisions of "Oracle errors"

Revision as of 09:44, 1 February 2013

ORA-12514: TNS:listener does not currently know of service

Excellent description of how Oracle TNS listeners work and a checklist on how to fix this error

• [TNS — Ed Stevens]

ORA-12547: TNS lost contact

If

sqlplus <user>/<pass>

fails with above error, try

sqlplus <user>/<pass>@<db connection>

If this works, it is most probably a permissions error.
Re-run the

$ORACLE_HOME/root.sh

script that was run as part of the original installation. This will reset the permissions on some important files.

Database trigger to capture ORA errors

From ora-ssn.blogspot.be

CREATE TABLE stats$error_log (
        err_dt          TIMESTAMP,
        db_user         VARCHAR2(30),
        msg_stack       VARCHAR2(2000),
        sqltxt          VARCHAR2(1000))
tablespace users;

Now, create a trigger on the database server.

CREATE OR REPLACE TRIGGER log_server_errors
  AFTER SERVERERROR
  ON DATABASE
DECLARE
          v_sqltext VARCHAR2(1000);
          nl_sqltext ora_name_list_t;
  BEGIN
          -- Capture entire error text
          FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
            v_sqltext := v_sqltext || nl_sqltext(i);
          END LOOP;
          INSERT INTO STATS$ERROR_LOG
          (err_dt, db_user, msg_stack, sqltxt)
          VALUES
          (systimestamp,
           sys.login_user,
           dbms_utility.format_error_stack, v_sqltext);
  END log_server_errors;
/