Home » SQL & PL/SQL » SQL & PL/SQL » How to know the dependencies (oracle 10g)
How to know the dependencies [message #427948] Mon, 26 October 2009 09:19 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
Hi All

I want to delete the data in some table but it may be having the child and the grand child records. What is the best way to know the dependencies and delete them in oracle?

I want that when we execute the code then at the time execution of the procedure I should not get any exception. So I want to make sure that I am deleting the data in the reverse sequential order. i.e deleting Grand child and then child and then parent.



Thanks
Re: how to know the dependencies [message #427955 is a reply to message #427948] Mon, 26 October 2009 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
why not set your foreign keys to ON DELETE CASCADE?
Re: how to know the dependencies [message #427958 is a reply to message #427948] Mon, 26 October 2009 09:35 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
We have not set any ON DELETE CASCADE to the foreign keys in our database structure.

We need to have this purge job which will delete the data but at the same time I will have to evaluate the dependencies and handle them.


Thanks
Re: how to know the dependencies [message #427966 is a reply to message #427958] Mon, 26 October 2009 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We have not set any ON DELETE CASCADE to the foreign keys in our database structure.

So do it now or explore user/all/dba_constraints.

Regards
Michel

[Updated on: Mon, 26 October 2009 09:53]

Report message to a moderator

Re: How to know the dependencies [message #427972 is a reply to message #427948] Mon, 26 October 2009 09:59 Go to previous messageGo to next message
begin2007
Messages: 4
Registered: July 2009
Junior Member
change the table name and try:

select a.owner,
a.table_name,
b.column_name,
C.OWNER,
c.table_name,
d.column_name
from user_constraints a
left join user_cons_columns b
on a.constraint_name = b.constraint_name
left join user_constraints C
ON C.R_CONSTRAINT_NAME = a.constraint_name
left join user_cons_columns d
on c.constraint_name = d.constraint_name
where a.constraint_type = 'P'
and a.table_name = 'XXXXX'
order by a.table_name;
Re: How to know the dependencies [message #427973 is a reply to message #427972] Mon, 26 October 2009 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to answer.
Please read OraFAQ Forum Guide to know how to format your post and queries.

Regards
Michel
Re: How to know the dependencies [message #428038 is a reply to message #427972] Mon, 26 October 2009 23:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Unfortunately, this will only show the dependencies one level deep.
If you really want to display all dependencies in a single query, you need to use CONNECT BY (hierarchy).
Re: How to know the dependencies [message #428050 is a reply to message #428038] Tue, 27 October 2009 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Tue, 27 October 2009 05:47
Unfortunately, this will only show the dependencies one level deep.
If you really want to display all dependencies in a single query, you need to use CONNECT BY (hierarchy).

See these scripts:
http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1236
http://www.dba-village.com/dba/village/dvp_scripts.ScriptDetails?ScriptIdA=1237

Regards
Michel


[Updated on: Tue, 27 October 2009 00:46]

Report message to a moderator

Re: How to know the dependencies [message #428082 is a reply to message #427948] Tue, 27 October 2009 03:12 Go to previous messageGo to next message
jigs230678
Messages: 3
Registered: October 2009
Location: Mumbai
Junior Member


Check USER_DEPENDENCIES data dictionary view which gives all dependency details for each table.
Re: How to know the dependencies [message #428088 is a reply to message #428082] Tue, 27 October 2009 03:48 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Below querry might help to achieve your requirement.
select * from all_dependencies where referenced_name='MECH_ROYALTY_MONTHLY_SMRY' and  type='TABLE';


Thanks,
Sunil Kr Gaurav
Re: How to know the dependencies [message #428096 is a reply to message #428088] Tue, 27 October 2009 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Back to the original question, it is dependency on data and not on objects that is required, the view is %contraints and not %dependencies.

Regards
Michel
Re: How to know the dependencies [message #428181 is a reply to message #427948] Tue, 27 October 2009 07:43 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
Can someone please suggest the modification to the query from begin2007 using the CONNECT BY clause?


Thanks All
Re: How to know the dependencies [message #428200 is a reply to message #428181] Tue, 27 October 2009 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try so far?
Take care that there can be loop in constraints, how will you handle this in your code?

Regards
Michel

[Updated on: Tue, 27 October 2009 08:14]

Report message to a moderator

Re: How to know the dependencies [message #428501 is a reply to message #428181] Wed, 28 October 2009 09:27 Go to previous messageGo to next message
begin2007
Messages: 4
Registered: July 2009
Junior Member
b_chugh wrote on Tue, 27 October 2009 07:43
Can someone please suggest the modification to the query from begin2007 using the CONNECT BY clause?


Thanks All



if the dependencies level more than 3 ,it is difficult to get the dependencies. below sql get the 3 level dependencies ,but not show the dependence columns and the performance is bad.

create table tmp1
(
i1 number(10) primary key,
n1 varchar(20)
);

create table tmp12
(
i1 number(10) references tmp1,
n12 varchar(20)
);

create table tmp11
(
i11 number(10) primary key,
n11 varchar(20)
);

create table tmp2
(
i1 number(10) references tmp1,
i11 number(10) references tmp11,
n1 varchar(20),
f2 varchar(20) primary key
);

create table tmp3
(
i3 number(10) ,
n3 varchar(20),
f2 varchar(20) references tmp2
);


create table tmp4
(
i4 number(10) ,
n4 varchar(20),
f2 varchar(20) references tmp2
);


SQL> select ref
2 from (select substr(b.tab_ref,
3 1,
4 length(b.tab_ref) - length(b.table_name) - 2) || '->' ||
5 substr(sys_connect_by_path(a.table_name, '->'), 3) ref,
6 b.tab_ref
7 from user_constraints a,
8 (select *
9 from (select substr(sys_connect_by_path(table_name, '->'), 3) tab_ref,
10 table_name
11 from user_constraints t
12 start with table_name = 'TMP1'
13 and t.constraint_type = 'P'
14 connect by prior constraint_name = r_constraint_name)
15 where tab_ref <> table_name) b
16 start with a.table_name = b.table_name
17 and a.constraint_type = 'P'
18 connect by prior a.constraint_name = a.r_constraint_name)
19 where instr(ref, tab_ref) > 0
20 union
21 select ref
22 from (select substr(b.tab_ref,
23 1,
24 length(b.tab_ref) - length(b.table_name) - 2) || '->' ||
25 substr(sys_connect_by_path(a.table_name, '->'), 3) ref,
26 b.tab_ref
27 from user_constraints a,
28 (select *
29 from (select substr(sys_connect_by_path(table_name, '->'),
30 3) tab_ref,
31 table_name
32 from user_constraints t
33 start with table_name = 'TMP1'
34 and t.constraint_type = 'P'
35 connect by prior constraint_name = r_constraint_name)
36 where tab_ref <> table_name) b
37 start with a.table_name = b.table_name
38 and a.constraint_type = 'R'
39 connect by a.constraint_name = prior a.r_constraint_name
40 and a.table_name<> substr(tab_ref,1,instr(tab_ref,'->')-1))
41 where instr(ref, tab_ref) > 0
42 /

REF
--------------------------------------------------------------------------------
TMP1->TMP12
TMP1->TMP2
TMP1->TMP2->TMP11
TMP1->TMP2->TMP3
TMP1->TMP2->TMP4

[Updated on: Wed, 28 October 2009 09:28]

Report message to a moderator

Re: How to know the dependencies [message #428505 is a reply to message #428501] Wed, 28 October 2009 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: How to know the dependencies [message #428510 is a reply to message #428505] Wed, 28 October 2009 10:05 Go to previous messageGo to next message
begin2007
Messages: 4
Registered: July 2009
Junior Member

sorry ,i am a freshman here,do not know how to format the post.

create table tmp1
(
i1 number(10) primary key,
n1 varchar(20)
);

create table tmp12
(
i1 number(10) references tmp1,
n12 varchar(20)
);

create table tmp11
(
i11 number(10) primary key,
n11 varchar(20)
);

create table tmp2
(
i1 number(10) references tmp1,
i11 number(10) references tmp11,
n1 varchar(20),
f2 varchar(20) primary key
);

create table tmp3
(
i3 number(10) ,
n3 varchar(20),
f2 varchar(20) references tmp2
);


create table tmp4
(
i4 number(10) ,
n4 varchar(20),
f2 varchar(20) references tmp2
);


select ref
  from (select substr(b.tab_ref,
                      1,
                      length(b.tab_ref) - length(b.table_name) - 2) || '->' ||
               substr(sys_connect_by_path(a.table_name, '->'), 3) ref,
               b.tab_ref
          from user_constraints a,
               (select *
                  from (select substr(sys_connect_by_path(table_name, '->'), 3) tab_ref,
                               table_name
                          from user_constraints t
                         start with table_name = 'TMP1'
                                and t.constraint_type = 'P'
                        connect by prior constraint_name = r_constraint_name)
                 where tab_ref <> table_name) b
         start with a.table_name = b.table_name
                and a.constraint_type = 'P'
        connect by prior a.constraint_name = a.r_constraint_name)
 where instr(ref, tab_ref) > 0
 union
 select ref
   from (select substr(b.tab_ref,
                       1,
                       length(b.tab_ref) - length(b.table_name) - 2) || '->' ||
                substr(sys_connect_by_path(a.table_name, '->'), 3) ref,
                b.tab_ref
           from user_constraints a,
                (select *
                   from (select substr(sys_connect_by_path(table_name, '->'),
                                       3) tab_ref,
                                table_name
                           from user_constraints t
                          start with table_name = 'TMP1'
                                 and t.constraint_type = 'P'
                         connect by prior constraint_name = r_constraint_name)
                  where tab_ref <> table_name) b
          start with a.table_name = b.table_name
                 and a.constraint_type = 'R'
         connect by  a.constraint_name = prior a.r_constraint_name
                and  a.table_name<> substr(tab_ref,1,instr(tab_ref,'->')-1))
  where instr(ref, tab_ref) > 0
 
 

Re: How to know the dependencies [message #429213 is a reply to message #428510] Mon, 02 November 2009 10:09 Go to previous messageGo to next message
manchalravi
Messages: 15
Registered: June 2007
Junior Member
I have a workaround for this. This might work for your requirement.

CREATE TABLE TOBJ_DEPENDENCIES (OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(50),
OBJ_OWNER VARCHAR2(50),
OBJ_TYPE VARCHAR2(50),
PARENT NUMBER,
STATUS NUMBER)
/
CREATE SEQUENCE seq_dept_sequence START WITH 1
/

spool sp_get_dependencies_working.log

prompt
prompt Run the following procedure for required object/table in this case. P_PARENT will be NULL
prompt ======================================
prompt
CREATE OR REPLACE PROCEDURE GERMIT.sp_get_dependencies(p_object_name IN VARCHAR2,
p_owner IN VARCHAR2,
p_parent IN NUMBER DEFAULT NULL)
IS
var_obj_name VARCHAR2(50) := upper(TRIM(p_object_name));
var_owner VARCHAR2(25) := upper(TRIM(p_owner));

--var_level NUMBER := p_parent;
var_parent NUMBER := 0;

var_ref_object VARCHAR2 (50);
var_ref_owner VARCHAR2 (50);
var_ref_parent NUMBER;
var_obj_type VARCHAR2 (50);
BEGIN
--EXECUTE IMMEDIATE ('TRUNCATE TABLE tobj_dependencies');

BEGIN
SELECT OBJECT_ID INTO var_parent FROM TOBJ_DEPENDENCIES
WHERE OBJECT_NAME = var_obj_name
AND OBJ_OWNER = var_owner
AND PARENT = p_parent;
EXCEPTION
WHEN OTHERS THEN
-- var_parent := 0;
SELECT OBJECT_TYPE INTO var_obj_type FROM all_OBJECTS
WHERE OBJECT_NAME = var_obj_name
AND owner = var_owner
AND rownum = 1;
INSERT INTO TOBJ_DEPENDENCIES VALUES (seq_dept_sequence.nextval, var_obj_name, var_owner, var_obj_type, NULL, 1);
COMMIT;
SELECT OBJECT_ID INTO var_parent FROM TOBJ_DEPENDENCIES
WHERE OBJECT_NAME = var_obj_name
AND OBJ_OWNER = var_owner
AND PARENT IS NULL;
END;

--SELECT seq_dept_sequence.nextval INTO var_level FROM dual;

INSERT INTO TOBJ_DEPENDENCIES
SELECT SEQ_DEPT_SEQUENCE.NEXTVAL, referenced_name,
referenced_owner, referenced_type,
var_parent, CASE WHEN referenced_type IN ('TABLE', 'SEQUENCE', 'NON-EXISTENT', 'PACKAGE')
THEN 1 ELSE 0
END
FROM all_Dependencies
WHERE OWNER = var_owner
AND NAME = var_obj_name
AND referenced_owner NOT IN ('PUBLIC', 'SYS', 'SYSTEM')
AND referenced_name <> var_owner;
--dbms_output.put_line('SQL count for '|| var_owner|| '.'|| var_obj_name || ' is: ' ||SQL%ROWCOUNT);

UPDATE TOBJ_DEPENDENCIES SET STATUS = 1 WHERE OBJECT_NAME = var_obj_name
AND OBJ_OWNER = var_owner
AND PARENT = p_parent;
COMMIT;

BEGIN
SELECT OBJECT_NAME, OBJ_OWNER, PARENT INTO var_ref_object, var_ref_owner, var_ref_parent
FROM TOBJ_DEPENDENCIES
WHERE OBJ_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION')
AND STATUS = 0
AND ROWNUM = 1;
dbms_output.put_line('Checking for child object...');

sp_get_dependencies (var_ref_object, var_ref_owner, var_ref_parent);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--dbms_output.put_line('Execution completed...');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception:'||SQLERRM);
END sp_get_dependencies;
/


Now run the following to get the dependecnies and you can make delete statments very easily.
SELECT LEVEL||'. '||rpad('*',(LEVEL)*4,'*')||' '||OBJECT_NAME
FROM TOBJ_DEPENDENCIES
CONNECT BY PRIOR OBJECT_ID = PARENT
START WITH PARENT IS NULL
/

Hope this should solve your problem.
Re: How to know the dependencies [message #429215 is a reply to message #429213] Mon, 02 November 2009 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 28 October 2009 15:44
Unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: How to know the dependencies [message #429217 is a reply to message #429215] Mon, 02 November 2009 10:21 Go to previous message
manchalravi
Messages: 15
Registered: June 2007
Junior Member
Sorry for that.

CREATE TABLE tobj_dependencies (object_id NUMBER,
object_name VARCHAR2(50),
obj_owner VARCHAR2(50),
obj_type VARCHAR2(50),
PARENT NUMBER,
status NUMBER)
/
CREATE SEQUENCE seq_dept_sequence START WITH 1
/
SPOOL sp_get_dependencies_working.log

PROMPT
PROMPT Run the following procedure for required object/table in this case. P_PARENT will be NULL
PROMPT ======================================
PROMPT

CREATE OR REPLACE PROCEDURE germit.sp_get_dependencies (
   p_object_name   IN   VARCHAR2,
   p_owner         IN   VARCHAR2,
   p_parent        IN   NUMBER DEFAULT NULL
)
IS
   var_obj_name     VARCHAR2 (50) := UPPER (TRIM (p_object_name));
   var_owner        VARCHAR2 (25) := UPPER (TRIM (p_owner));
--var_level NUMBER := p_parent;
   var_parent       NUMBER        := 0;
   var_ref_object   VARCHAR2 (50);
   var_ref_owner    VARCHAR2 (50);
   var_ref_parent   NUMBER;
   var_obj_type     VARCHAR2 (50);
BEGIN
--EXECUTE IMMEDIATE ('TRUNCATE TABLE tobj_dependencies');
   BEGIN
      SELECT object_id
        INTO var_parent
        FROM tobj_dependencies
       WHERE object_name = var_obj_name
         AND obj_owner = var_owner
         AND PARENT = p_parent;
   EXCEPTION
      WHEN OTHERS
      THEN
-- var_parent := 0;
         SELECT object_type
           INTO var_obj_type
           FROM all_objects
          WHERE object_name = var_obj_name AND owner = var_owner
                AND ROWNUM = 1;

         INSERT INTO tobj_dependencies
              VALUES (seq_dept_sequence.NEXTVAL, var_obj_name, var_owner,
                      var_obj_type, NULL, 1);

         COMMIT;

         SELECT object_id
           INTO var_parent
           FROM tobj_dependencies
          WHERE object_name = var_obj_name
            AND obj_owner = var_owner
            AND PARENT IS NULL;
   END;

--SELECT seq_dept_sequence.nextval INTO var_level FROM dual;
   INSERT INTO tobj_dependencies
      SELECT seq_dept_sequence.NEXTVAL, referenced_name, referenced_owner,
             referenced_type, var_parent,
             CASE
                WHEN referenced_type IN
                             ('TABLE', 'SEQUENCE', 'NON-EXISTENT', 'PACKAGE')
                   THEN 1
                ELSE 0
             END
        FROM all_dependencies
       WHERE owner = var_owner
         AND NAME = var_obj_name
         AND referenced_owner NOT IN ('PUBLIC', 'SYS', 'SYSTEM')
         AND referenced_name <> var_owner;

--dbms_output.put_line('SQL count for '|| var_owner|| '.'|| var_obj_name || ' is: ' ||SQL%ROWCOUNT);
   UPDATE tobj_dependencies
      SET status = 1
    WHERE object_name = var_obj_name
      AND obj_owner = var_owner
      AND PARENT = p_parent;

   COMMIT;

   BEGIN
      SELECT object_name, obj_owner, PARENT
        INTO var_ref_object, var_ref_owner, var_ref_parent
        FROM tobj_dependencies
       WHERE obj_type IN ('VIEW', 'PROCEDURE', 'FUNCTION')
         AND status = 0
         AND ROWNUM = 1;

      DBMS_OUTPUT.put_line ('Checking for child object...');
      sp_get_dependencies (var_ref_object, var_ref_owner, var_ref_parent);
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;
--dbms_output.put_line('Execution completed...');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception:' || SQLERRM);
END sp_get_dependencies;
/

--Now run the following to get the dependecnies and you can make delete statments very easily.
SELECT     LEVEL || '. ' || RPAD ('*', (LEVEL) * 4, '*') || ' ' || object_name
      FROM tobj_dependencies
CONNECT BY PRIOR object_id = PARENT
START WITH PARENT IS NULL
/
Previous Topic: error_message : ORA-06508: PL/SQL: could not find program unit being called
Next Topic: dbms_crypto_toolkit_types (merged)
Goto Forum:
  


Current Time: Sat Dec 10 08:41:12 CST 2016

Total time taken to generate the page: 0.10061 seconds