ORA-00001 – But no name

constraintoracle

We have a huge table with nested tables and such. We are using Bulk Collect & FORALL to insert.

When loading data, I am getting this error:

ORA-00001: unique constraint (.) violated

There is no constraint name……so, how can I find out 1) What or where is causing the error, and, 2) Which record it is?

I know you can keep exceptions with FORALL, but we are doing this in lots of 500, so it is hard to find out the actual record, and what constraint / nested table it is, etc.

I'm looking for any help anyone can give.

Many thanks!

Best Answer

A possible bug for not displaying the constraint name with bulk DML and SAVE EXCEPTIONS clause:

Bug 7210333 : CONSTRAINT NAME IN SQLERRM IS NOT OUTPUT WHEN SAVE EXCEPTIONS IS USED

To catch the records causing the error, you could use DML error logging as well:

create table t1(c1 number unique);

begin
  dbms_errlog.create_error_log('T1', 'T1_ERRORS');
end;
/

Insert data:

declare
  type t_varray is varray(4) of number;
  l_varray t_varray := t_varray(1, 2, 2, 3);
begin
  forall i in 1..l_varray.count
  insert into t1 values (l_varray(i)) 
    log errors into t1_errors
    reject limit unlimited;
end;
/

Actual data:

select * from t1;

        C1
----------
         1
         2
         3

Rows violating the constraint with row data and constraint name:

select * from t1_errors;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                ORA_ERR_ROWID$       ORA_ERR_OPTYP$       ORA_ERR_TAG$         C1
--------------- ------------------------------------------------------------ -------------------- -------------------- -------------------- ----------
              1 ORA-00001: unique constraint (BP.SYS_C0013693) violated                           I                                         2

Related Solutions

Encountering exception ORA-01555

Gaius is right, use DBA_TABLES for NUM_ROWS and DBA_SEGMENTS for size:

select owner,table_name,num_rows,last_analyzed from dba_tables;

The num_rows count is as of LAST_ANALYZED date, which should be close enough even without running DBMS_STATS.

For sizing:

select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;

or (depending what level of details you need):

select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;

For LOBs you'll need to join it to DBA_LOBS, for indexes to DBA_INDEXES, for tables to DBA_TABLES. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:

select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';

Your solution would really work only with small DB as it would not be feasible to read all data on large DB.

I am hesitant to post how to address ORA-1555 as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention in your database (the ixora link is relevant to a DB without auto undo management).

Unable to restore database in Oracle 10g

Looks like there is already a job on the imported database with that job number. Just recreate the job on the import database.

The job looks to be a queue so you could, depending on your business procedures, probably run the new job to test that everything is working.

Best Answer

Related Solutions

Encountering exception ORA-01555

Unable to restore database in Oracle 10g

Related Question