Link to home
Create AccountLog in
Avatar of sdruss
sdruss

asked on

Need Oracle Database SQL Script to Cleanup Database

Need an Oracle SQL script to delete records based on an "id".  Deletion will need to remove rows from 50+ tables, with many parent-child foreign key relationships, so order is important.  Need help obtaining correct order (i.e. need to delete row in child table before delete of row in parent).  Need good method to get dependencies.

Script should prompt for an "id" then traverse through script and delete rows.  Not necessarily looking for a stored procedure, believe script with embedded sql commands should suffice.  How do I automate this?  Suspect I will need to query user_constrainits and user_cons_columns table to get child dependencies.  Looking for something that is auto generated resulting with a file that contains:

    DELETE FROM aaa
        where id = '&&id';

   DELETE FROM bbb
        where id = '&&id';

   DELETE FROM ccc
        where id = '&&id';
            .
            .
            .
            .
            .
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Tom Kyte has written the below script to retrieve dependencies between tables.
The only flaw with it is the sensibility to loops (if you run this against the HR sample schema that cames with Oracle the last statement will fail because the relationships between employee and department).

Original Link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:661009003696

CREATE global TEMPORARY TABLE temp_constraints
AS
  SELECT a.table_name,
    a.constraint_name pkey_constraint,
    b.constraint_name fkey_constraint,
    b.r_constraint_name
  FROM user_constraints a,
    user_constraints b
  WHERE 1=0;
  
  ALTER TABLE temp_constraints MODIFY fkey_constraint NULL;
  
  DELETE FROM temp_constraints;
  
  INSERT INTO temp_constraints
  SELECT table_name,
    constraint_name,
    NULL,
    NULL
  FROM user_constraints
  WHERE constraint_type = 'P';
  
  INSERT INTO temp_constraints
  SELECT a.table_name,
    a.constraint_name pkey_constraint,
    b.constraint_name fkey_constraint,
    b.r_constraint_name
  FROM user_constraints a,
    user_constraints b
  WHERE a.table_name    = b.table_name
  AND a.constraint_type = 'P'
  AND b.constraint_type = 'R';
  
  SELECT * FROM temp_constraints;
  
  SELECT rpad( '*', (level-1)*2, '*' )
    || table_name table_name
  FROM temp_constraints
    START WITH fkey_constraint      IS NULL
    CONNECT BY prior pkey_constraint = r_constraint_name;

Open in new window


Hope this helps.
I read further the link above and find something that would help you. I have changed to use the temp table, but you can look the original:

BEGIN
  FOR x IN
  (SELECT 'alter table '
    || table_name
    || ' disable constraint '
    || fkey_constraint stmt
  FROM temp_constraints
  WHERE fkey_constraint IS NOT NULL
  )
  LOOP
    dbms_output.put_line( x.stmt );
    -- execute immediate x.stmt;
  END LOOP;
  FOR x IN
  (SELECT 'delete from '
    || table_name
    || ' where id = ''&&id'';' stmt
  FROM temp_constraints
  )
  LOOP
    dbms_output.put_line( x.stmt );
    --           execute immediate x.stmt;
  END LOOP;
  FOR x IN
  (SELECT 'alter table '
    || table_name
    || ' enable constraint '
    || fkey_constraint stmt
  FROM temp_constraints
  WHERE fkey_constraint IS NOT NULL
  )
  LOOP
    dbms_output.put_line( x.stmt );
    --execute immediate x.stmt;
  END LOOP;
END;

Open in new window

Avatar of sdruss
sdruss

ASKER

Thanks.  Think this is on the right track.  

As every table does not have an "id" column so don't need to include every table, think only tables with an "id" column - then get those dependencies, then generate the set of delete statements.  Also, need DBMS_OUTPUT to write results to file.
Avatar of sdruss

ASKER

wpcortes,

Used UTL_FILE to write generated results to file.  However, need to filter results as I only need tables that contain "id" columns.  Please help.  Thanks !
You can change the 2 inserts on the first script to do the following:

INSERT INTO temp_constraints
  SELECT table_name,
    constraint_name,
    NULL,
    NULL
  FROM user_constraints
  WHERE constraint_type = 'P'
    AND table_name in (select table_name from all_tab_columns
where column_name like '%ID%');
  
  INSERT INTO temp_constraints
  SELECT a.table_name,
    a.constraint_name pkey_constraint,
    b.constraint_name fkey_constraint,
    b.r_constraint_name
  FROM user_constraints a,
    user_constraints b
  WHERE a.table_name    = b.table_name
  AND a.constraint_type = 'P'
  AND b.constraint_type = 'R'
 AND a.table_name in (select table_name from all_tab_columns
where column_name like '%ID%');

Open in new window


Hope this helps.
Avatar of sdruss

ASKER

wpcortes thanks for the quick response.

Having foreign-key child table constraint violations all of the place - major headache.

Unfortunately disable/enable of the constraints prior to the delete statements may not be appropriate in my environment.  Considering somewhat new approach to add "cascade on delete" to the foreign keys.  

Is it possible with an "ALTER TABLE" statement to modify and include the "cascade on delete" phrase to an existing foreign key?  What are the pros and cons of adding the cascade on the child tables?  I could add cascade via ALTER statement do my deletes, and then remove cascade.  What is the syntax on adding via an ALTER TABLE statements?
Avatar of sdruss

ASKER

Need PL/SQL fragment to get dependencies on parent tables with an "id" column?  In other words need foreign keys so that I can use "on delete cascade" to avoid child violations when deleting rows.  Probably need multiple loops or nested sub-query to get all children of parent with an "id" column.
Avatar of sdruss

ASKER

Help.  Anyone.  Please chime in.
Please use this:

exec dbms_utility.get_dependency('TABLE', 'DWHADMIN','TESTTAB'); 

Open in new window


or try this:
select 
 lpad('.',7*(level-1),'.')||ud.name name 
 from 
 user_dependencies ud 
 start with rownum=1 
 connect by nocycle 
 prior trim(ud.name)=trim(ud.referenced_name) 
 and prior trim(ud.type)=trim(ud.referenced_type); 

Open in new window


A note on the 2 is that you need to have referential integrity on your model.
Avatar of sdruss

ASKER

Guess I was not specific enough I need table depedencies parent-child, child-parent relationships.  The above are good and show other object dependencies: views, etc..  Is there an easy way to determine if table has a parent?  Can you alter an existing table and modify with "ON DELETE CASCCADE?
Yes, you can modify it to use on delete cascade. It's ok if you want to delete the dependent records.
Avatar of sdruss

ASKER

May want to use above code in stored procedure.  Can't seem to get the "global temporary table created in the stored procedure.  I scanned experts-exchange web-site I gleamed this:  

      EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_constraints as
            select aaa from bbb';

Can't seem to get this to work.  Also, anything I should look out for in converting above to package.procedure.
Avatar of sdruss

ASKER

Could you demonstrate how to do the above query without a temp table.  Looking on the web nothing good to say about temporary tables in Oracle.  Thanks!
Avatar of sdruss

ASKER

Please help!

Would like to use a cursor, rather than temporary table.  Need help with this:

CURSOR my_cursor IS
      SELECT a.table_name,
            a.constraint_name pkey_constraint,
            b.constraint_name fkey_constraint,
            b.r_constraint_name
      FROM user_constraints a,
            user_constraints b
      WHERE a.table_name    = b.table_name
          AND a.constraint_type = 'P'
          AND b.constraint_type = 'R'
         AND a.table_name in (select table_name from all_tab_columns
        where column_name like '%ID%');

    FOR i in my_cursor
    LOOP
         -- need help with delete statement here
    END LOOP;
What you need there is:

declare a variable at the beggining of your script:

v_string VARCHAR2(100);

Open in new window


Then inside your loop:

v_string := 'delete from ' || i.table_name || ' where id = ''&&id'';';
execute immediate v_string;

Open in new window

You can replace &&id by another variable in your script, like that:

v_string := 'delete from ' || i.table_name || ' where id = ' || v_id || ';';
execute immediate v_string;

Open in new window

Avatar of sdruss

ASKER

I added spooling to the top of my generated delete script.  My spooled output only has a long list of:
             20 records deleted.
             60 records deleted.
             32 records deleted.
                   .
                   .
                   .

How do also I get the name of the table the records were deleted from in my spooled results file?   Need to verify how many records were delete from which table.
ASKER CERTIFIED SOLUTION
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of sdruss

ASKER

Thinking more about:

  set termout on
  set verify on
Avatar of sdruss

ASKER

Wpcortes or anyone.  How would I use the above delete query as input into data pump (i.e. expdp).  This is somewhat of a complex query.  Suspect I would use the query parameter in the expdp command.  Need the ability to reverse my delete process above if required.
My understanding is that to do that, you would need then to understand the data pump format and change the script to generate in that said format.
Anyway, we are getting away of the original question here. Can you please award the points related to your original question and open a new one specific for this?

Regards,
Walter.
Avatar of sdruss

ASKER

Definitely some unanswered questions.