ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Issue :

Error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys” when trying to truncate a table.

Error Message:

SQL> truncate table inventory_item;

truncate table inventory_item

               *

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

    

Oracle documentation says:

    

> oerr ora 02266

02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"

// *Cause: An attempt was made to truncate a table with unique or                                                                                                                

//         primary keys referenced by foreign keys enabled in another table.

//         Other operations not allowed are dropping/truncating a partition of a

//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.

// *Action: Before performing the above operations the table, disable the

//          foreign key constraints in other tables. You can see what

//          constraints are referencing a table by issuing the following

//          command:

//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Solution:

-- Find the referenced foreign key constraints.

SQL> select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'

  2  from all_constraints a, all_constraints b

  3  where a.constraint_type = 'R'

  4  and a.r_constraint_name = b.constraint_name

  5  and a.r_owner  = b.owner

  6  and b.table_name = 'INVENTORY_ITEM';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME||';'

---------------------------------------------------------------------------------------------------------

alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT disable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

alter table CTRLCTR.INVENTORY_ITEM_LOCATION disable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

alter table CTRLCTR.SERVICE_ORDER disable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

-- Disable them.

SQL> alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT disable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.INVENTORY_ITEM_LOCATION disable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.SERVICE_ORDER disable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

Table altered.

-- Run the truncate

SQL> truncate table inventory_item;

Table truncated.

-- Enable the foreign keys back

SQL> select 'alter table '||a.owner||'.'||a.table_name||' enable constraint '||a.constraint_name||';'

  2  from all_constraints a, all_constraints b

  3  where a.constraint_type = 'R'

  4  and a.r_constraint_name = b.constraint_name

  5  and a.r_owner  = b.owner

  6  and b.table_name = 'INVENTORY_ITEM';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLECONSTRAINT'||A.CONSTRAINT_NAME||';'

----------------------------------------------------------------------------------------------------

alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT enable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

alter table CTRLCTR.INVENTORY_ITEM_LOCATION enable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

alter table CTRLCTR.SERVICE_ORDER enable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

-- Enable them

SQL> alter table CTRLCTR.INVENTORY_ITEM_ATTACHMENT enable constraint FK_INV_ITM_ATTCHMNT_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.INVENTORY_ITEM_LOCATION enable constraint FK_INV_ITEM_LOC_INV_ITM_ID;

Table altered.

SQL> alter table CTRLCTR.SERVICE_ORDER enable constraint FK_SERVICE_ORDER_INV_ITEM_ID;

Table altered.

How to Change the Server Parameters Value

Server Parameters:

Oracle Server parameters allow you to modify many aspects of the Oracle server.

To Check the current value of parameter:

SQL>  select name,value from v$parameter where name='Some_Parameter';

Changing a Parameter value:

Use the ALTER SYSTEM command to set parameters:

1. ALTER SYSTEM set parameter = value SCOPE = MEMORY;
2. ALTER SYSTEM set parameter = value SCOPE = SPfile;
3. ALTER SYSTEM set parameter = value SCOPE = BOTH;

Where

• MEMORY - This affects the database now; but will not remain after a restart. 

• SPfile - This does not change the instance immediately, but will modify the SPfile takes effect after a restart.

• BOTH - change both the current instance and the spfile.

SYSTEM and SESSION:

=>Some parameters can be modified immediately with ALTER SYSTEM, 

=>Some may only be modified for a single session with ALTER SESSION. 

=>Static parameters must be modified with scope=SPfile

V$PARAMETER:

Column                                         Datatype                                          Description

=========                            =============                             =============

ISSES_MODIFIABLE              VARCHAR2(5)            Indicates whether the parameter can be                                                                                                    changed with ALTER SESSION (TRUE)                                                                                                or not (FALSE)

ISSYS_MODIFIABLE            VARCHAR2(9)              Indicates whether the parameter can be                                                                                                    changed with ALTER SYSTEM and when                                                                                              the change takes effect:

IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

Acton Plan:

===========

SQL> show parameter utl

SQL> alter system utl_file_dir='path_name' scope=spfile/both;