Table tronquée dans Oracle obtenir des erreurs
j'ai eu le problème, c'est que quand J'exécute la commande suivante dans Oracle, je rencontre l'erreur.
Truncate table mytable;
les Erreurs suivantes:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
j'ai trouvé ce mytable a des relations avec d'autres tables. C'est pourquoi la commande tronquer ne peut plus continuer. Comment supprimer des données de myTable avec les scripts SQL en utilisant la commande Truncate?
9 réponses
vous devez changer la déclaration tronquée pour supprimer les déclarations, plus lent et enregistré, mais c'est la façon de le faire quand les contraintes sont en place.
DELETE mytablename;
soit cela soit vous pouvez trouver les clés étrangères qui font référence à la table en question et les désactiver temporairement.
select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';'
from user_constraints
where R_CONSTRAINT_NAME='<pk-of-table>';
Où pk-of-table
est le nom de la clé primaire de la table tronqué
Exécuter la sortie de la requête ci-dessus. Lorsque cela a été fait, n'oubliez pas de les réactiver, il suffit de changer DISABLE CONSTRAINT
en ENABLE CONSTRAINT
le message d'erreur vous indique qu'il y a d'autres tables avec une contrainte de clé étrangère se référant à votre table.
Selon Oracle docs
vous ne pouvez pas tronquer la table parent d'une contrainte de clé étrangère. Vous devez désactiver la contrainte avant la troncation de la table.
syntaxe pour désactiver une clé étrangère est:
modifier la table table_name désactiver Contrainte constraint_name;
cette page offre une très bonne solution ...
ORA-02266: Clés uniques/primaires dans la table référencée par des clés étrangères activées
je suis ici la copie à partir de la Solution:
- trouver les contraintes de clé étrangère activées référencées et les désactiver.
- tronquer/supprimer du tableau .
en utilisant n'importe quel éditeur de texte .. il suffit de changer le désactiver pour activer la sortie de la requête , puis exécutez il.
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';' from all_constraints a, all_constraints b where a.constraint_type = 'R' and a.status='ENABLED' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.table_name = upper('YOUR_TABLE');
Question:
Error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys” when trying to truncate a table.
Message D'Erreur:
SQL> truncate table TABLE_NAME;
truncate table TABLE_NAME
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
la Solution: -- Trouver les contraintes de clé étrangère référencées.
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 = 'TABLE_NAME';
'ALTER TABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLE CONSTRAINT'||A.CONSTRAINT_NAME||';'
---------------------------------------------------------------------------------------------------------
alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT disable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION disable constraint CONSTRAINT_NAME;
-- Disable
alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT disable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION disable constraint CONSTRAINT_NAME;
-- Exécuter le tronquer
SQL> truncate table TABLE_NAME;
Table truncated.
-- Enable les clés étrangères
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 = 'TABLE_NAME';
'ALTER TABLE'||A.OWNER||'.'||A.TABLE_NAME||'ENABLE CONSTRAINT'||A.CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT enable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION enable constraint CONSTRAINT_NAME;
-- Enable
alter table SCHEMA_NAME.TABLE_NAME_ATTACHMENT enable constraint CONSTRAINT_NAME;
alter table SCHEMA_NAME.TABLE_NAME_LOCATION enable constraint CONSTRAINT_NAME;
Oracle 12c introduit une fonctionnalité pour tronquer une table qui est parent d'une contrainte d'intégrité référentielle ayant sur Supprimer la règle.
au Lieu de truncate table tablename;
utilisation:
TRUNCATE TABLE tablename CASCADE;
Oracle truncate table
documentation:
si vous spécifiez CASCADE, alors la base de données Oracle tronque toutes les tables enfants qui font référence à une table avec une contrainte référentielle de suppression de CASCADE activée. C'est une opération récursive tronquez toutes les tables d'enfant, les tables de granchild, et ainsi de suite, en utilisant les options spécifiées.
une approche typique pour supprimer beaucoup de lignes avec beaucoup de contraintes est la suivante:
- créer
mytable_new
avec toutes les colonnes mais sans contraintes (ou créer des contraintes désactivées); - copier toutes les données, vous avez besoin de
mytable
mytable_new
. - activer les contraintes sur
mytable_new
pour voir que tout est ok. - modifier toute contrainte faisant référence à
mytable
référencemytable_new
au lieu de cela et de voir que tout est OK. drop table mytable
.alter table mytable_new rename to mytable
.
c'est beaucoup plus rapide que de supprimer un million d'enregistrements avec beaucoup de contraintes lentes.
j'ai eu le même problème et j'ai trié par les scripts suivants.
begin
for i in (select constraint_name, table_name from user_constraints a where a.owner='OWNER' and a.table_name not in
(select b.table_name from user_constraints b where b.table_name like '%BIN%')
and a.constraint_type not in 'P')
LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/
truncate table TABLE_1;
truncate table TABLE_2;
begin
for i in (select constraint_name, table_name from user_constraints a where a.owner='OWNER' and a.table_name not in
(select b.table_name from user_constraints b where b.table_name like '%BIN%')
and a.constraint_type not in 'P')
LOOP
execute immediate 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
end loop;
end;
/
ce script désactivera d'abord toutes les contraintes. Tronque les données dans les tables, puis activer les contraintes.
j'Espère que ça aide.
cheers..
TRUNCATE TABLE TEST2 DROP ALL STORAGE;
cette instruction fonctionne réellement quand il y a une contrainte de clé étrangère appliquée sur A.la table
comme mentionné par le message d'erreur, vous ne pouvez pas tronquer une table qui est référencée par des clés étrangères activées. Si vous voulez vraiment utiliser le truncate
commande DDL, désactivez d'abord la contrainte de clé étrangère, exécutez la commande truncate, et activez-la de nouveau.
Référence: différence entre les commandes TRUNCATE, DELETE et DROP