Ittichai at 20:06, 18 March 2011

2011-03-18T20:06:00Z

Ittichai at 01:04, 15 March 2011

2011-03-15T01:04:04Z

Ittichai: Created page with "== What is SQL Error Logging? == * Log error into database table, eliminate spool to OS file * The logging table can be manually defined. == Demo == * Check Error Logging

 ..."

2011-03-15T01:03:40Z

Created page with "== What is SQL Error Logging? == * Log error into database table, eliminate spool to OS file * The logging table can be manually defined. == Demo == * Check Error Logging <pre> ..."

New page

== What is SQL Error Logging? ==
* Log error into database table, eliminate spool to OS file
* The logging table can be manually defined.

== Demo ==
* Check Error Logging
<pre>
SQL> show errorlogging
errorlogging is OFF
</pre>

* Enable Error Logging. A logging table is created on the current user.
<pre>
SQL> set errorlogging on

SQL> show errorlogging
errorlogging is ON TABLE [SCHEMA].SPERRORLOG
</pre>

* Describe the logging table
<pre>
SQL> desc SPERRORLOG
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT VARCHAR2(1024)
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
</pre>

* Try to create errors
<pre>
-- Generate Error #1
SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

-- Generate Error #2
SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

SQL> commit;
</pre>

* Check the SQL Error Logging.
<pre>
SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME STATEMENT MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL ORA-01476: divisor is equal to
zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
VARCHR2(1) option
</pre>

* Set an identifier so it’s easier to locate your logs.
<pre>
SQL> set errorlogging on identifier 'REL1'
</pre>

* Generate Error #3.
<pre>
SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
*
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

</pre>

* Check the SQL Error Logging with identifier='REL1‘
<pre>
SQL> SELECT username, statement, message, IDENTIFIER FROM sperrorlog
where IDENTIFIER='REL1';

USERNAME STATEMENT MESSAGE IDENT
---------- ------------------------------ ------------------------------ -----
AUDIT_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
NULL tifier
</pre>

* Truncate the Error Logging. All records will be gone.
<pre>
SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;

No rows selected
</pre>

* Disable Error Logging. Log off will automatically disable it.
<pre>
SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF
</pre>

[[Category:Database_Features]]
[[Category:Oracle_11g]]

SQL Error Logging - Revision history

Ittichai at 20:06, 18 March 2011

Ittichai at 01:04, 15 March 2011

Ittichai: Created page with "== What is SQL Error Logging? == * Log error into database table, eliminate spool to OS file * The logging table can be manually defined. == Demo == * Check Error Logging
..."

@@ Line 110: / Line 110: @@
 </pre>
 [[Category:Database_Features]]
 [[Category:Oracle_11g]]

@@ Line 4: / Line 4: @@
 == Demo ==
-* Check Error Logging
+* Check Error Logging.
 <pre>
 SQL> show errorlogging
@@ Line 10: / Line 10: @@
 </pre>
-* Enable Error Logging. A logging table is created on the current user.
+* Enable Error Logging. A logging table is created under the current user.
 <pre>
 SQL> set errorlogging on