ORA-31603: object "FKSL6L09UUXICDX66MNPLQ0DI63" of type CONSTRAINT not found in schema &qu-CodePudding

I'm trying to get all constraint DDL from ALL_CONSTRAINTS using SQL PLUS. But ORA-31603 arise. Like this constraint there have more constraint where name are same but their Owner are different.

I try this by following query

-- Run this script in SQL*Plus.

-- don't print headers or other crap
set heading off;
set echo off;
set pagesize 0;      

-- don't truncate the line output
-- trim the extra space from linesize when spooling
set long 99999;      
set linesize 32767;  
set trimspool on;    

-- don't truncate this specific column's output
col object_ddl format A32000;

spool AIBLNGZDB_CONSTRAINT_ddl.sql;

SELECT dbms_metadata.get_ddl('CONSTRAINT', constraint_name, owner) || ';' AS object_ddl
FROM ALL_CONSTRAINTS
WHERE 
      OWNER = 'AIBLNGZDB'
--  AND OBJECT_TYPE IN (
--      'CONSTRAINT'
----     'INDEX'
----    , 'SEQUENCE'
----    , 'VIEW'
--  )
ORDER BY
    OWNER
--  , OBJECT_TYPE
;

spool off;

SET LINESIZE 500

CodePudding user response：

Consider joining all_constraints to all_objects. That should also help with dropped constraints (as they are contained in recycle bin, and you can't get their DDL):

SQL> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
BIN$7gsQxw2vJ/LgU8lkAQo5GQ==$0
BIN$7gsQxw2rJ/LgU8lkAQo5GQ==$0
SYS_C00106654
<snip>

SQL>   SELECT    DBMS_METADATA.get_ddl ('CONSTRAINT', c.constraint_name, c.owner)
  2           || ';' AS object_ddl
  3      FROM all_constraints c
  4     WHERE c.owner = 'SCOTT'
  5  ORDER BY c.owner;
ERROR:
ORA-31603: object "BIN$7gsQxw2vJ/LgU8lkAQo5GQ==$0" of type CONSTRAINT not found
in schema "SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1



no rows selected

SQL>

But, joining all_objects makes a difference:

SQL>   SELECT    DBMS_METADATA.get_ddl ('CONSTRAINT', c.constraint_name, c.owner)
  2           || ';' AS object_ddl
  3      FROM all_constraints c
  4           JOIN all_objects o
  5              ON     o.owner = c.owner
  6                 AND o.object_name = c.table_name
  7     WHERE c.owner = 'SCOTT'
  8  ORDER BY c.owner;

OBJECT_DDL
--------------------------------------------------------------------------------

  ALTER TABLE "SCOTT"."STUDENTS" ADD PRIMARY KEY ("ROLLNOSTUD")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USER_DATA"  ENABLE;


  ALTER TABLE "SCOTT"."DEPARTMENT" ADD PRIMARY KEY ("DEPT_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USER_DATA"  ENABLE;
<snip>

CodePudding user response：

There are several difficulties with extracting constraint DDL. As Littlefoot pointed out, you need to exclude recycle bin objects and REF_CONSTRAINTS. You may also need to exclude the other constraint types "O" ("With read only, on a view"), and "V" ("With check option, on a view"). Those two other constraint types will show up as part of a view's DDL, so they can be ignored in the below query.

--Reference constraints:
select dbms_metadata.get_ddl('REF_CONSTRAINT', constraint_name) || ';' as object_ddl
from all_constraints
where owner = 'AIBLNGZDB'
    and constraint_type = 'R'
    and constraint_name not like 'BIN$%'

union all

--Other kinds of constraints.
select dbms_metadata.get_ddl('CONSTRAINT', constraint_name) || ';' as object_ddl
from all_constraints
where owner = 'AIBLNGZDB'
    and constraint_type not in ('O', 'V', 'R')
    and constraint_name not like 'BIN$%';