How to find an Oracle exceptions table?

I was given a script that has this in it:

alter table TABLE_1 add constraint pk_1 primary key(ID_1)
         using index
         tablespace schema1_ind
   exceptions into junk;

The problem is, when I run it, it throws this error: ORA-02445: exceptions table not found

I'm not very familiar with exceptions tables, and there is very little information that I can find online. I found this one bit of documentation from Oracle,https://docs.oracle.com/cd/A58617_01/server.804/a58312/newch2h5.htm which says this:

ORA-02445: exceptions table not found
Cause: The explicitly or implicitly declared exceptions table does not exist.
Action: If the correct exceptions table name was used, then create the table and retry the enable command.

and I also found this https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/EXCEPTIONS.html:

EXCEPTIONS contains information on violations of integrity constraints. This table is created by the utlexcpt.sql script.

But, very weirdly, when I run a different script that also uses "junk", it runs without an error:

alter table TABLE_2 add constraint PK_2
    primary key(ID_2)
        using index tablespace schmea1_ind2
    storage (initial 8M NEXT 2M)
    exceptions into junk;

These scripts all run in a certain order, and the bottom one runs first, succeeds without an issue, a bunch of other scripts run, and then the top one tries to run and fails.

The problem is, I do not know how to find this 'junk' exceptions table. I've tried running the first script by itself, and it works, but afterwards when I look in DBA_OBJECTS, there is nothing called 'junk'. Nothing at all. Nowhere does anything create or drop a table named 'junk'.

Can anyone explain why I am getting this 'exceptions table not found' error? Where are exceptions tables, if not in dba_objects? Why does the first statement fail, but the second succeeds?

Any knowledge at all about exceptions tables would be helpful, as I have found very little documentation.

Solution

But, very weirdly, when I run a different script that also uses "junk", it runs without an error

That's probably because there are no duplicates in that table. Unusually Oracle doesn't complain that the exceptions table doesn't exist unless it needs to use it.

This fiddle demonstrates that; if the alter table is run when the main table is empty or only has unique data then it doesn't complain; once there is a duplicate PK value then it does.

The problem is, I do not know how to find this 'junk' exceptions table

It doesn't seem to exist, so you need to create it. Using the version from the article Bohemian linked to:

create table junk
(
row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30)
);

... your statement then gets "ORA-02437: cannot validate (schema.PK_1) - primary key violated".

fiddle

The exception table structure is shown in the documentation, with the default name used by the utlexcpt.sql script (and larger columns than the version above). And you can read more about the concept elsewhere in the documentation, under the "Handling Constraint Exceptions" heading.