CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ERRLOG

Version 11.1
 
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;
 
Related Topics
Constraints
Insert
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [150 users online]    © 2010 psoug.org