SQL: supprimer toutes les données de toutes les tables disponibles

Je suis en utilisant oracle DB pour maintenir plus de 30 tables, comment puis-je supprimer toutes les données de toutes les tables? Je veux seulement supprimer les données, mais pas déposer les tables.

  • Cette question semble être hors-sujet, car il appartient à dba.stackexchange.com
InformationsquelleAutor Lily | 2009-05-06
  1. 29

    Générer un script pour tronquer (= supprimer toutes les lignes de) tous les tableaux:

    select 'truncate table ' || table_name || ';' from user_tables

    Puis d'exécuter le script.

    • J'ai fait modifier cette réponse, désolé à ce sujet mais la all_tables chose était assez dangereux.
    • très soigné solution, merci beaucoup
    • Juste pour débutants: notez que truncate est non-transactionnelle, c'est à dire qu'il ne peut pas être annulée.
    • Cela ne fonctionnera pas si il y a des étrangers-les contraintes de clé primaire définie.
    InformationsquelleAutor Andomar
  2. 32

    Il n'y a pas de commande " ALTER TABLE XXX DÉSACTIVER TOUTES les CONTRAINTES

    Je propose ceci;

    BEGIN
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
  FOR c IN (SELECT table_name FROM user_tables)
  LOOP
    EXECUTE IMMEDIATE ('truncate table ' || c.table_name);
  END LOOP;
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable constraint ' || c.constraint_name);
  END LOOP;
END;
    • cette solution n'est probablement pas le plus voté parce que les gens ne se soucient pas de lire toutes les réponses ici.
    • Cela devrait une meilleure solution. Vous pouvez également le personnaliser en fait, supprimer les TABLES et les CONTRAINTES plutôt que de les TRONQUER.
    InformationsquelleAutor michaldo
  3. 18

    Pour aborder la question des contraintes, quelque chose comme cela devrait fonctionner:

    BEGIN

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' ENABLE ALL CONSTRAINTS';
    END LOOP;
END;
    • alter table ... disable all constraints jette un ORA-01735: invalid ALTER TABLE option si il n'y a pas de contrainte défini pour la table, ce qui entraînerait le script à ne pas tronquer tous table si il y a au moins une table sans contrainte. Vous pourriez placer le execute immediate dans un begin .. exception bloc.
    InformationsquelleAutor DCookie
  4. 4

    L'inconvénient potentiel avec un truncate est qu'il peut s'échouer sur les contraintes d'intégrité référentielle. Si vous souhaitez désactiver les contraintes de clé étrangère d'abord, puis de faire le tronquer, puis de le ré-activer les contraintes.
    Le "plus" avec le clonage le schéma (exp et de la pmi), c'est que vous pouvez également supprimer et recréer de l'espace de table trop (que vous pouvez faire si vous voulez récupérer une partie de l'espace disque physique en tant que résultat de la suppression de toutes les données).

    InformationsquelleAutor Gary Myers
  5. 2

    Clone le schéma, puis déposer les anciennes tables?

    • +1: solution la plus Simple et de loin
    • Ou le "vrai DBA" façon de faire: la sauvegarde de la pleine DB sans les données, puis reconstruit à l'DB (... le serveur ... de la carte mère sur place ;).
    • Comment est-ce simple? Je ne suis pas administrateur de base de données, et il me donne aucune idée de ce que les commandes à exécuter.
    • IIRC, qui dépend du SGBD.
    • C'est un Oracle question . . .
    InformationsquelleAutor BCS
  6. 1

    J'ai créé cette procédure stockée, en utilisant les réponses mentionnées ci-dessus.
    Cela fonctionne parfaitement sans erreurs ou des exceptions.

        create or replace PROCEDURE DELETE_ALL_DATA
AS 
cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;
cursor r3 is select * from user_constraints;
cursor r4 is select * from user_tables;
BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
begin
if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
continue;
WHEN OTHERS 
THEN
continue;
end;
end loop;
END LOOP;
FOR T in (SELECT table_name FROM user_tables) LOOP
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
continue;
WHEN OTHERS 
THEN
continue;
end;
END LOOP;
FOR c1 IN r3
loop
for c2 in r4
loop
begin
if c1.table_name = c2.table_name and c1.status = 'DISABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable constraint ' || c1.constraint_name);
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
continue;
WHEN OTHERS 
THEN
continue;
end;
end loop;
END LOOP;
commit;
END DELETE_ALL_DATA;
    InformationsquelleAutor Ayush Aggarwal
  7. 1

    Une légère variation sur Andomar réponse à tronquer toutes les tables pour un utilisateur spécifique non seulement à ceux de l'utilisateur actuel:

    SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner = 'user/schema'

    Remplacer le user/schema peu au-dessus avec le nom de l'utilisateur/schéma (entre guillemets) qui vous intéresse.

    • merci de cette requête m'aider si bien. Je préfère cette requête si vous avez plus de 1 db/schema
    InformationsquelleAutor Forage
  8. 0

    Supprimer toutes les données de toutes les tables dans oracle

    DÉCLARER 
str VARCHAR2(100); 
COMMENCER 
POUR je EN 
(SÉLECTIONNEZ object_name DE user_objects OÙ type_objet='TABLE' 
) 
BOUCLE 
str : = " Truncate table '|| i.object_name; 
EXÉCUTION IMMÉDIATE str; 
DBMS_OUTPUT.PUT_LINE ("table de données supprimés :' || i.object_name); 
FIN DE LA BOUCLE; 
FIN;

    Pour plus d'info: http://www.oracleinformation.com/2014/10/delete-all-the-data-from-all-tables.html

    InformationsquelleAutor Brahmareddy K
  9. -3

    ces deux lignes de script sont les meilleurs

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
    • C'est le moyen le plus facile de tronquer toutes les tables dans Microsoft SQL Server base de données. Même si vous avez oublié de réactiver les contraintes après la suppression de données. Mais ... cette question est à propos d'Oracle ... pas de SQL Server.
    InformationsquelleAutor YOusaFZai