| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmserlg.sql |
| First Available |
10.2 |
| Dependencies |
| DBMS_ASSERT |
DBMS_SQL |
KUPD$DATA |
| DBMS_OUTPUT |
DBMS_UTILITY |
PLITBLM |
|
| Note: |
... LOG ERRORS - effectively it turns array processing into single row processing, so it
adds an expense at the moment of inserting, even though it saves you the overhead of an array rollback if a duplicate gets into the data.
~ Jonathan Lewis / comp.databases.oracle.server / 13-Aug-2006 |
| Security Model |
Execute is granted to PUBLIC |
| |
| CREATE_ERROR_LOG |
Variable Definition |
dbms_errlog.create_error_log(
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2 := NULL,
err_log_table_owner IN VARCHAR2 := NULL,
err_log_table_space IN VARCHAR2 := NULL,
skip_unsupported IN BOOLEAN := FALSE); |
CREATE TABLE t AS
SELECT *
FROM all_tables
WHERE 1=2;
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (owner, table_name)
USING INDEX;
ALTER TABLE t
ADD CONSTRAINT cc_t
CHECK (blocks < 11);
col blocks format 99999
SELECT blocks, COUNT(*)
FROM all_tables
GROUP BY blocks
HAVING COUNT(*) > 1
ORDER BY 2,1;
INSERT /*+ APPEND */ INTO t
SELECT *
FROM all_tables;
SELECT COUNT(*) FROM t;
------------------------------
exec dbms_errlog.create_error_log('T');
desc err$_t
INSERT /*+ APPEND */ INTO t
SELECT *
FROM all_tables
LOG ERRORS
REJECT LIMIT UNLIMITED;
SELECT COUNT(*) FROM t;
COMMIT;
SELECT COUNT(*) FROM t;
SELECT COUNT(*) FROM err$_t;
set linesize 121
col table_name format a30
col blocks format a7
col ora_err_mesg$ format a60
SELECT ora_err_mesg$, table_name, blocks
FROM err$_t; |
Manually error log creation |
Oracle allows you to manually create your own error
log table so that you can control the columns that are captured. For
example:
conn uwclass/uwclass
CREATE TABLE error_log_servers (
ora_err_number$ NUMBER,
ora_err_mesg$ VARCHAR2(2000),
ora_err_rowid$ ROWID,
ora_err_optyp$ VARCHAR2(2),
ora_err_tag$ VARCHAR2(2000));
INSERT INTO servers (srvr_id) VALUES (1)
LOG ERRORS INTO ERROR_LOG_SERVERS;
col ora_err_mesg$ format a10
col ora_err_tag$ format a50
SELECT * FROM error_log_servers;
DROP TABLE error_log_servers PURGE;
CREATE TABLE error_log_servers (
ora_err_number$ NUMBER,
ora_err_mesg$ VARCHAR2(2000),
ora_err_rowid$ ROWID,
ora_err_optyp$ VARCHAR2(2),
ora_err_tag$ VARCHAR2(2000),
srvr_id
NUMBER(10));
INSERT INTO servers (srvr_id) VALUES (1)
LOG ERRORS INTO ERROR_LOG_SERVERS;
SELECT * FROM error_log_servers;
And it has been suggested in the OTN forums that this
capability might be useful, in its most generic form, for creating a single
generic error table rather than one table for each base table being accessed
for DML.
From my perspective the point of having an error log table is to allow you
to see the values in the row, or at least the primary key, and to fix the
problem. Knowing that an exception took place, but you can not track it down
and do anything about it, is not a great idea. So stick to the default usage
and you will have a far superior implementation. |
|
