Difference between revisions of "SQL Error Logging"
Jump to navigation
Jump to search
(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> ...") |
|||
(One intermediate revision by the same user not shown) | |||
Line 4: | Line 4: | ||
== Demo == | == Demo == | ||
− | * Check Error Logging | + | * Check Error Logging. |
<pre> | <pre> | ||
SQL> show errorlogging | SQL> show errorlogging | ||
Line 10: | Line 10: | ||
</pre> | </pre> | ||
− | * Enable Error Logging. A logging table is created | + | * Enable Error Logging. A logging table is created under the current user. |
<pre> | <pre> | ||
SQL> set errorlogging on | SQL> set errorlogging on | ||
Line 110: | Line 110: | ||
</pre> | </pre> | ||
+ | == See Also == | ||
+ | http://oraexplorer.com/2011/03/oracle-11g-sql-error-logging/ | ||
[[Category:Database_Features]] | [[Category:Database_Features]] | ||
[[Category:Oracle_11g]] | [[Category:Oracle_11g]] |
Latest revision as of 15:06, 18 March 2011
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.
SQL> show errorlogging errorlogging is OFF
- Enable Error Logging. A logging table is created under the current user.
SQL> set errorlogging on SQL> show errorlogging errorlogging is ON TABLE [SCHEMA].SPERRORLOG
- Describe the logging table
SQL> desc SPERRORLOG Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB
- Try to create errors
-- 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;
- Check the SQL Error Logging.
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
- Set an identifier so it’s easier to locate your logs.
SQL> set errorlogging on identifier 'REL1'
- Generate Error #3.
SQL> alter table EMP modify ABC NOT NULL; alter table EMP modify ABC NOT NULL * ERROR at line 1: ORA-00904: "ABC": invalid identifier
- Check the SQL Error Logging with identifier='REL1‘
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
- Truncate the Error Logging. All records will be gone.
SQL> set errorlogging on truncate SQL> SELECT * FROM sperrorlog; No rows selected
- Disable Error Logging. Log off will automatically disable it.
SQL> set errorlogging OFF SQL> show errorlogging errorlogging is OFF
See Also
http://oraexplorer.com/2011/03/oracle-11g-sql-error-logging/