ORA-02266: |
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”; |
alter table
table_name
DISABLE constraint
constraint_name;
lub
begin
for i in (select constraint_name, table_name from user_constraints) LOOP
execute immediate ‘alter table ‘||i.table_name||’ disable constraint ‘||i.constraint_name||”;
end loop;
end;
/
ORA-02297: |
cannot disable constraint (string.string) – dependencies exist |
| Cause: | an alter table disable constraint failed becuase the table has foriegn keys that are dpendent on this constraint. |
| Action: | Either disable the foreign key constraints or use disable cascade |
SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) – dependencies exist
Cause of the Problem
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.
Solution of the Problem
Two solutions exist for this problem.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.
Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.
SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM dba_constraints p
JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old 7: AND p.table_name = UPPER('&table_name')
new 7: AND p.table_name = UPPER('transaction')
Parent Table Child Table Parent Constraint Child Constraint ------------------------------ ------------------------------ ------------------------------ ------------------------------ TRANSACTION USER_SALARY_RECORD TRANSACTION_PK SYS_C005564 TRANSACTION TRANSACTION_DETAIL TRANSACTION_PK TRNSCTN_DTL_TRNSCTN_FK SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564; Table altered. SQL> alter table TRANSACTION_DETAIL disable constraint TRNSCTN_DTL_TRNSCTN_FK; Table altered. SQL> alter table transaction disable constraint TRANSACTION_PK; Table altered.
2)Disable the constraint with cascade option.
SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.
The six types of integrity constraint are described briefly here and more fully in “Semantics”:
- A
NOTNULLconstraint prohibits a database value from being null. - A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
- A primary key constraint combines a
NOTNULLconstraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. - A foreign key constraint requires values in one table to match values in another table.
- A check constraint requires a value in the database to comply with a specified condition.
- A
REFcolumn by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between theREFcolumn and the object it references.
ORA-02429: |
cannot drop index used for enforcement of unique/primary key |
| Cause: | user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key. |
| Action: | drop the constraint instead of the index. |
Trzeba znaleźć w PKs dany constraint i sprawdzić do jakiej tabeli się odwołuje. (ewentualnie usunąć)
ORA-02273: |
this unique/primary key is referenced by some foreign keys |
| Cause: | Self-evident. |
| Action: | Remove all references to the key before the key is to be dropped. |
It’s very easy to reproduce this situation:create table test1(test1_parent_id number,col1 varchar2 (30),constraint test1_pk primary key (test1_parent_id))/create table test2(test2_id number,test1_child_id number,col2 varchar2(30),constraint test2_fk foreign key (test1_child_id) references test1(test1_parent_id))/alter table test1 drop constraint test1_pk/ORA-02273: this unique/
select * from all_constraints
where constraint_type='R' and r_constraint_name='YOUR_CONSTRAINT';(wykasować child constraints)
ORA-29857: |
domain indexes and/or secondary objects exist in the tablespace |
| Cause: | An attempt was made to drop a tablespace which contains secondary objects and/or domain indexes. |
| Action: | Drop the domain indexes in his tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace. |
Przy kasowaniu przestrzeni tablic EXAMPLE (10g)
wcześniej należy:
DROP USER BI CASCADE;
DROP USER HR CASCADE;
DROP USER IX CASCADE;
DROP USER OE CASCADE;
DROP USER PM CASCADE;
DROP USER SH CASCADE;
Źródła:
http://www.dba-oracle.com/t_oracle_disable_constraints.htm
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://www.dba-oracle.com/t_constraints.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm
http://www.dba-oracle.com/t_alter_table_add_constraint_syntax_example.htm
http://oraclequirks.blogspot.com/2007/09/ora-02273-this-uniqueprimary-key-is.html
role w oracle:
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml