1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Tracing a table cascade delete

Discussion in 'SQL PL/SQL' started by Roop, May 19, 2015.

  1. Roop

    Roop Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I have been asked to follow a table that a software app is deleting to make sure any reference to it's data values are removed. The table has 10 children many with more children and Foreign and primary keys some not null and some null allowed. Is there any easy way to trace the cascade? The table also has 7 triggers.

    Excuse me if it seems like a dumb question, my DBA knowledge isn't that great.

    Thanks for any help.

    Roop.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have 7 triggers? Are these doing the 'cascade' deletes? Why weren't the foreign keys declared 'on delete cascade' to let Oracle do the work? Trying to manage constraints using a transactional model won't be successful.


    If possible post the trigger code so we can see what they are doing. If these triggers ARE doing the deletes then a 10046 trace might show you the cascading delete 'in action'. You would start that with this command at the SQL> prompt:


    alter session set events='10046 trace name context forever, level 12';


    (The level 12 captures all bind and wait information, which you probably don't need. The example shows the command I regularly use to create such traces.). You might also want to set the tracefile_identifier so the generated trace file is easily found:


    alter session set tracefile_identifier='cascading_delete';


    This puts the 'cascading_delete' text in the tracefile name. To turn this off:


    alter session set events='10046 trace name context off';


    You find the trace file and run tkprof on it to generate a readable file, for example:


    $ tkprof mydb_ora_13416_cascade_delete.trc mydb_ora_13416_cascade_delete.out


    You can then open mydb_ora_13416_cascade_delete.out and read the report.


    I hope this helps.
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I'm not sure what you mean by this.

    1. Primary keys can never be NULL by definition

    2. A table with a column designated as a foreign key should contain values referencing a primary key in the parent table. Since the primary key can never be null, the foreign key column should never be NULL unless you are not enforcing the foreign key. If the foreign key is not enforced, then there can be records in the 'child' table that have no relationship to the 'parent' table, in which case a cascade delete from the parent table can not/will not reference these rows.
     
  4. Roop

    Roop Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks to both of you for your responses and clarification about 'keys'.

    The DB has a schema used in the oil ind. from software called OpenWorks. The table is VC_PICKSURF_NAME (Is VC=Variable Character?) That contains a name string which is assigned a numerical ID "PICK_SURF_ID".
    I agree why not let Oracle do the work with the Cascade Delete, but my boss who owns / develops software reckons a customer has complained or queried that the Pick Surface Name (interpreted rock boundary down a well bore) has not totally disappeared from the DB. He asked me to trace the delete. I'm an apps support guy not a DBA but have some understanding of RDBS's and limited SQL. He wants me to list the descending family tables and comment "Delete" or "Do nothing" against each one, which I'm finding hard to do, as in following his / the logic. He sent me the first few child tables as an example and wants me to complete the rest:

    (Child tables are tabbed and double tabbed etc, I'm asking a lot and understand if this is too much for anyone to explain to me)

    VC_PICKSURF_NAME
    EM_HORIZON_ALL PICK_SURF_ID - delete
    EM_CLEANING_RADIUS_ALL delete
    EM_FAULT_SURFACE_CONTACT_ALL delete
    EM_ISOCHORE_CONSTRAINT_ALL delete
    EM_ISOCHORE_CONSTRAINT_ALL delete
    EM_LAYER_DEFINITION_ALL delete (match on top_horizon_id)
    EM_LAYER_DEFINITION_ALL delete (match on base_horizon_id)
    EM_MISSING_TOP_ALL delete
    EM_PROPERTY_MAP_ALL Null -> other_surface_id
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is the problem with trying to 'out think' Oracle: developers replace non-transactional acts with transactional code and its inherent issues (blocking sessions, locked resources, etc.) which is why cascading deletes using transactional methods sometimes fail. Your manager can't eat his cake and have it to, and the evidence provided by the customers that the cascading deletes don't complete properly should clue him in that his process of choice on this is flawed and needs to be replaced.