[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

how to know if a table is being used?

hi
i want to know if a table is being referred in my schema ir not. whether in views or any other join table structure.
is it possible?
thanks
0
samir25
Asked:
samir25
  • 4
  • 3
2 Solutions
 
sujith80Commented:
Use this query.


select * from user_dependencies where referenced_name = upper('<your table name>');
0
 
schwertnerCommented:
There are many views taht can help you.
To analyze a table and all references from it or to it I use the script in the sniplet.


SQL> execute an_tab('schema_name_here', 'table_name_here');
 
CREATE OR REPLACE PROCEDURE an_tab(p_schema VARCHAR2, p_table VARCHAR2) IS
--
        CURSOR table_columns (v_userid VARCHAR2, v_tabname VARCHAR2) IS
             SELECT col.column_name, SUBSTR(col.data_type,1,10)||'(' 
                 || case when col.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then col.char_length else col.data_length end
                 || case when col.CHAR_USED = 'B' then ' Bytes' when col.CHAR_USED = 'C' then ' Characters' else '' end || ')' || ' '
                 || com.comments "TYPE"
             FROM dba_tab_columns col, dba_col_comments com
             WHERE col.owner = UPPER(p_schema) AND col.table_name = UPPER(p_table)
               AND col.owner = com.owner(+) AND col.table_name = com.table_name (+) AND col.column_name = com.column_name(+);
--
        CURSOR column_details (v_userid VARCHAR2, v_tabname VARCHAR2) IS
             SELECT column_name, SUBSTR(data_type,1,10) || ' ' || char_length || '  '
                    || DATA_LENGTH ||' Bytes ' || CHAR_LENGTH ||' ' 
                    || case when CHAR_USED = 'B' then ' Bytes' else ' Characters' end as "TYPE"
             FROM dba_tab_columns
             WHERE owner = UPPER(p_schema) AND table_name = UPPER(p_table)
                   AND data_type in ('CHAR', 'VARCHAR2','NCHAR','NVARCHAR2');
--
	CURSOR cons_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS 
	  SELECT * FROM sys.dba_constraints 
	  WHERE owner = v_userid 
	    AND constraint_type in ('P','U','C','R') 
            AND table_name = v_tabname 
	  ORDER BY constraint_type; 
--
	CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS 
	  SELECT * FROM sys.dba_cons_columns 
	  WHERE owner = con_owner 
	    AND constraint_name = con_name 
	  ORDER BY position; 
--
	CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS 
	  SELECT a.* 
	  FROM sys.dba_indexes a, sys.dba_ind_columns b, sys.dba_cons_columns c 
	  WHERE c.constraint_name = con_name 
            AND a.owner = ind_own 
            AND b.index_owner = ind_own 
	    AND c.owner = b.index_owner 
	    AND c.position = 1 
	    AND c.table_name = b.table_name 
	    AND c.column_name = b.column_name 
	    AND b.index_name = a.index_name; 
--
	CURSOR ref_cur (v_userid VARCHAR2, v_tabname VARCHAR2)  IS 
            select c1.owner||'.'||c1.table_name ||'   FOREIGN KEY ('|| c2.column_name
                || ')  REFERENCES '|| c3.owner||'.'||c3.table_name||'('
                || c3.column_name ||')' AS fk_def
            from
                dba_constraints  c1,
                dba_cons_columns c2,
                dba_cons_columns c3
            where
                   c1.owner = v_userid       --'LSMEDICAL' 
               AND c1.constraint_type = 'R'  -- in ('P','U','C','R') 
               AND c1.constraint_name = c2.constraint_name
               AND c1.owner = c2.owner
               AND c1.r_constraint_name = c3.constraint_name
               AND c1.r_owner = c3.owner
               AND c3.table_name = v_tabname;  --'EVENT_CURRENT' 
--
         CURSOR indx_info (v_userid VARCHAR2, v_tabname VARCHAR2)  IS 
              SELECT  SUBSTR(i.index_name, 1,30)  || '  ' || 
                      SUBSTR(ic.column_name,1,20) || ' ' ||
                      ic.column_position  || '  ' ||
                      ic.column_length || '  ' ||
                      i.index_type AS indx_info
              FROM    dba_indexes i, dba_ind_columns ic
              WHERE   i.owner = ic.index_owner
                      AND i.index_name = ic.index_name
                      AND i.table_owner = UPPER(v_userid)
                      AND i.table_name  = UPPER(v_tabname);
--
	CURSOR obj_priv_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS 
	   SELECT grantee, privilege FROM sys.dba_tab_privs 
	   WHERE owner      = v_userid 
	     AND table_name = v_tabname 
	   ORDER BY grantee; 
--
	CURSOR triggers_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS 
	   SELECT * FROM sys.dba_triggers 
	   WHERE table_owner= v_userid 
	     AND table_name = v_tabname 
	   ORDER BY TRIGGER_NAME; 
 
 
--
	col_str	VARCHAR2(200); 
	v_user		VARCHAR2(30) := UPPER(p_schema); 
        v_tabname       VARCHAR2(30) := UPPER(p_table);
        v_colname       VARCHAR2(130);  
	v_output	VARCHAR2(480);    -- max of 16 cols at 30 chars each 
        v_dummy         NUMBER := 0; 
	v_delrule	VARCHAR2(4); 
	v_status	VARCHAR2(4); 
        v_excp          NUMBER(1) := 0; 
	v_excptab       VARCHAR2(60) := NULL;  
	srch_cond	VARCHAR2(1000); 
        v_table_type    VARCHAR2(11);
        v_table_comments VARCHAR2(4000);
        buf             VARCHAR2(250);
        cnt             NUMBER;
        v_big           varchar2(32000);
BEGIN 
   DBMS_OUTPUT.ENABLE(1000000);         -- Prevents buffer exceeded error 
   DBMS_OUTPUT.PUT_LINE(CHR(10)); 
   DBMS_OUTPUT.PUT_LINE( 'ANALYZING THE TABLE  '||p_schema||'.'||p_table); 
   DBMS_OUTPUT.PUT_LINE(CHR(10)); 
 
   ------ TABLE COLUMNS ----
 
   DBMS_OUTPUT.PUT_LINE('------TABLE COLUMNS:------'); 
   DBMS_OUTPUT.PUT_LINE(CHR(10));
   FOR c1 IN table_columns (v_user, v_tabname) LOOP 
       begin 
            v_colname := RPAD(c1.column_name,30,' ');
            v_big := v_colname || c1.TYPE;
            put_buffer(v_big); 
 
       end;
   END LOOP;
 
   ------ END TABLE COLUMNS ----
 
   ------ TABLE COMMENTS ----
 
   SELECT  table_type, comments  INTO v_table_type, v_table_comments
   FROM  dba_tab_comments
   WHERE owner = UPPER(p_schema) AND table_name = UPPER(p_table);
   DBMS_OUTPUT.PUT_LINE(CHR(10));
 
   DBMS_OUTPUT.PUT_LINE(v_table_type ||' '||p_schema ||'.'||p_table );
 
            put_buffer(v_table_comments);  
 
 
   ------ END TABLE COMMENTS ----
 
   ------ TABLE LENGTHS ----
   FOR i in 1..3 LOOP
       DBMS_OUTPUT.PUT_LINE(CHR(10)); 
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('------CHAR/VARCHAR2 COLUMNS LENGTHS:------'); 
   DBMS_OUTPUT.PUT_LINE(CHR(10));
   FOR c1 IN column_details (v_user, v_tabname) LOOP 
       begin 
            v_colname := RPAD(c1.column_name,30,' ');
            DBMS_OUTPUT.PUT_LINE( v_colname || c1.TYPE); 
 
       end;
   END LOOP;
 
   ------ END TABLE LENGTHS ----
 
   FOR i in 1..3 LOOP
       DBMS_OUTPUT.PUT_LINE(CHR(10)); 
   END LOOP;
 
 
 
 
   ------CONSTRAINTS:------ 
 
   DBMS_OUTPUT.PUT_LINE('------CONSTRAINTS:------'); 
   DBMS_OUTPUT.PUT_LINE(CHR(10)); 
   FOR c1 IN cons_cur(v_user, v_tabname) LOOP 
     begin 
       srch_cond := substr(c1.search_condition,1,length(c1.search_condition)); 
       -- Dont remove table constraint NOT NULL 
       IF (instr(srch_cond,'NOT NULL') < 1) or 
       (instr(srch_cond,'NOT NULL') IS NULL) THEN 
       BEGIN 
          DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME); 
          DBMS_OUTPUT.PUT_LINE('  ADD (CONSTRAINT '||C1.CONSTRAINT_NAME); 
 
          IF c1.constraint_type = 'P' THEN v_output := '    PRIMARY KEY ('; 
          ELSIF c1.constraint_type = 'R' THEN v_output := '    FOREIGN KEY ('; 
          ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE ('; 
          ELSIF c1.constraint_type = 'C' THEN 
	      v_output := '    CHECK ('||c1.search_condition||') '||v_excptab; 
          END IF; 
 
          FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP 
	     IF c2.position = 1 THEN 
	        v_output := v_output||c2.column_name; 
	     ELSIF c2.position > 1 THEN 
	        v_output := v_output||', '||c2.column_name; 
	     END IF; 
          END LOOP; 
          v_output := v_output ||')';  
          put_buffer (v_output); 
          IF c1.constraint_type = 'R' THEN 
	      v_output := NULL; 
	     FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP 
	        IF c3.position = 1 THEN 
	           v_output := '    REFERENCES '||c3.owner||'.'||c3.table_name||'('; 
	           v_output := v_output||c3.column_name; 
	        ELSIF c3.position > 1 THEN 
	           v_output := v_output||', '||c3.column_name; 
	        END IF; 
	     END LOOP; 
	     v_output := v_output||') '; 
             put_buffer (v_output); 
	     v_delrule := substr(c1.delete_rule,1,2); 
	     IF v_delrule IS NULL THEN v_output :=  v_excptab ||' )'; 
	     ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )'; 
	     ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE'||v_excptab || ')'; 
	     END IF; 
             --DBMS_OUTPUT.PUT_LINE(v_output); 
             put_buffer (v_output);
          END IF; 
 
         FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP 
	    IF c1.constraint_type in ('P','U') THEN 
                DBMS_OUTPUT.PUT_LINE(' USING INDEX ');    
	        DBMS_OUTPUT.PUT_LINE('   pctfree  '||c4.pct_free); 
	        DBMS_OUTPUT.PUT_LINE('   initrans      '||c4.ini_trans); 
	        DBMS_OUTPUT.PUT_LINE('   maxtrans      '||c4.max_trans); 
	        DBMS_OUTPUT.PUT_LINE('   tablespace    '||c4.tablespace_name); 
	        DBMS_OUTPUT.PUT_LINE(' Storage ('); 
	        DBMS_OUTPUT.PUT_LINE('   initial        '||c4.initial_extent); 
	        DBMS_OUTPUT.PUT_LINE('   next           '||c4.next_extent); 
	        DBMS_OUTPUT.PUT_LINE(' minextents     '||c4.min_extents); 
	        DBMS_OUTPUT.PUT_LINE('   maxextents     '||c4.max_extents); 
	        DBMS_OUTPUT.PUT_LINE('   pctincrease    '||c4.pct_increase||') '|| 
                                v_excptab ||')'); 
	    END IF; 
        END LOOP; 
 
        v_output := NULL; 
        v_status := substr(c1.status,1,1); 
        IF v_status = 'E' THEN  
            v_output := ' Status: This constraint '||c1.constraint_name||' was ENABLED'; 
        ELSIF v_status = 'D' THEN  
            v_output :=' Status: This constraint '||c1.constraint_name ||' was DISABLED';  
        END IF; 
   
        -- DBMS_OUTPUT.PUT_LINE(v_output);
        put_buffer (v_output);
        DBMS_OUTPUT.PUT_LINE(CHR(10)); 
 
     END; 
   END IF; 
   EXCEPTION 
     WHEN no_data_found THEN 
       DBMS_OUTPUT.PUT_LINE('No Data Found'); 
     WHEN others THEN 
       DBMS_OUTPUT.PUT_LINE('Other: '||substr(sqlerrm,1,60)); 
       DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type); 
       DBMS_OUTPUT.PUT_LINE(c1.search_condition); 
   END; 
  END LOOP;
 
        ------ END   CONSTRAINTS   ------ 
 
 
        --------  begin WHO REFERENCES THIS TABLE ---------
        DBMS_OUTPUT.PUT_LINE(CHR(10)); 
        DBMS_OUTPUT.PUT('-------WHO REFERENCES THE TABLE     ');
        DBMS_OUTPUT.PUT(v_user||'.'||v_tabname);
        DBMS_OUTPUT.PUT_LINE('--------');
        DBMS_OUTPUT.PUT_LINE('LIST OF THE TABLES THAT REFERENCE THIS TABLE');
            DBMS_OUTPUT.PUT_LINE(CHR(10));
        FOR c5 IN ref_cur(v_user, v_tabname) LOOP 
	    DBMS_OUTPUT.PUT_LINE(c5.fk_def);
            DBMS_OUTPUT.PUT_LINE(CHR(10));    
        END LOOP;  
 
        --------- end   WHO REFERENCES THIS TABLE --------
 
 
        -------   INDEXES   --------
 
        DBMS_OUTPUT.PUT_LINE('-------INDEXES ON THIS TABLE------');
        DBMS_OUTPUT.PUT_LINE(CHR(10));
        FOR c6 IN indx_info(v_user, v_tabname) LOOP 
	    DBMS_OUTPUT.PUT_LINE(c6.indx_info);     
        END LOOP;  
 
 
 
        ------- END   INDEXES  --------
 
        --------  begin WHO HAS ACCESS TO THIS TABLE ---------
        DBMS_OUTPUT.PUT_LINE(CHR(10)); 
        DBMS_OUTPUT.PUT('-------WHO HAS ACCESS TO THIS TABLE   ');
        DBMS_OUTPUT.PUT(v_user||'.'||v_tabname);
        DBMS_OUTPUT.PUT_LINE('--------');
        DBMS_OUTPUT.PUT_LINE('-------Except the owner of the table '|| v_user||'.'||v_tabname || ' THESE USERS HAVE ACCESS RIGHTS:');
        FOR c5 IN obj_priv_cur(v_user, v_tabname) LOOP 
	    DBMS_OUTPUT.PUT(c5.grantee);
            DBMS_OUTPUT.PUT('    ');
            DBMS_OUTPUT.PUT_LINE(c5.privilege);    
        END LOOP;  
 
        --------- end   WHO HAS ACCESS TO THIS TABLE -------- 
 
 
        --------  begin TRIGGERS                    ---------
        DBMS_OUTPUT.PUT_LINE(CHR(10)); 
        DBMS_OUTPUT.PUT('-------TRIGGERS ON THIS TABLE   ');
        DBMS_OUTPUT.PUT(v_user||'.'||v_tabname);
        DBMS_OUTPUT.PUT_LINE('--------');
        FOR c5 IN triggers_cur(v_user, v_tabname) LOOP 
	    DBMS_OUTPUT.PUT_LINE('Name: ' ||c5.TRIGGER_NAME);
	    DBMS_OUTPUT.PUT_LINE('The trigger is ' ||c5.STATUS);
	    DBMS_OUTPUT.PUT_LINE(c5.TRIGGER_TYPE);
	    DBMS_OUTPUT.PUT_LINE(c5.TRIGGERING_EVENT);
	    DBMS_OUTPUT.PUT_LINE(c5.COLUMN_NAME);
            DBMS_OUTPUT.PUT_LINE(c5.WHEN_CLAUSE);  
            put_buffer(c5.TRIGGER_BODY);   
        END LOOP;
 
        --------- end   TRIGGERS                        -------- 
  EXCEPTION 
  WHEN no_data_found THEN 
  DBMS_OUTPUT.PUT_LINE('This table: '||v_tabname||', Does not exist or has no constraints!'); 
END an_tab;
/

Open in new window

0
 
samir25Author Commented:
actually i created a table a as below
create table a from (select * from table b)

now in my package and views i am only referring a not b. but when i delete b then m y package doesnt run. i checked in the code and no where i have a...
but when i recreate 'a' then my package runs. when i check in depencies..for 'a' i get no rows selected.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sujith80Commented:
>> m y package doesnt run
Means it throws an error? what is the error?

>>but when i delete b then m y package doesnt run
you mean delete the data in b, or drop table b?

Alternatively you can see all the dependents of your package by this query.

select * from user_dependencies where name = upper('<your package name>');
0
 
samir25Author Commented:
i run the package thru apache server. where it gives standard error which is not helpful.
this package doesnt exist...
as i mentioned doing this
select * from user_dependencies where name = upper('<your package name>');
gives me 0
i meant when i drop table b then package doesnt run. when i re-create this b from a then i can run the package.
0
 
sujith80Commented:
>> as i mentioned doing this
>> select * from user_dependencies where name = upper('<your package name>');
>> gives me 0

can you pls take a look at the queries????
The first query I asked to run was
select * from user_dependencies where referenced_name = upper('<your table name>');
and the second one is
select * from user_dependencies where name = upper('<your package name>');

If both of them are returning no rows, then there is something missing in your description.

Why dont you run the package from sqlplus and see if it runs.
Have you read my post above? Answer to the questions.......
0
 
sujith80Commented:
here is an example

select * from user_dependencies where name = upper('test_pkg');
0
 
samir25Author Commented:
ok.. i missed on package... i will do that.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now