DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: select tables used in packages but not used in sql queries during last two weeks, my

  1. #1
    Join Date
    Dec 2014
    Posts
    2

    Question select tables used in packages but not used in sql queries during last two weeks, my

    Hi all,

    My need is to

    1) select tables mentioned in packages with 'ABS%' name
    and then
    2) select those tables from the first step that were not used in sql queries during last two weeks.
    How do I perform it?

    Thanks ahead.

  2. #2
    Join Date
    Dec 2014
    Posts
    2
    I think about something like that
    SELECT t.owner, t.table_name, u.name as package_name, s.LAST_LOAD_TIME
    FROM ALL_TABLES T,
    dba_source U,
    v$sql s
    where instr(upper(u.text),t.table_name) > 0
    and instr(s.SQL_FULLTEXT,t.table_name) > 0
    and u.type = 'PACKAGE'
    and u.name like 'ABC%'
    and then result should be filtered somehow by LAST_LOAD_TIME column.
    But it seems to be very heave request.
    Maybe someone can suggest me how to improve?

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    If you have set the "MONITORING" option in your tables, then do this:
    Code:
    SELECT D.*, M.*
      FROM Dba_Dependencies D, Sys.Dba_Tab_Modifications M
     WHERE D.Owner = 'SCOTT'
       AND D.TYPE LIKE 'PACK%'
       AND D.Name LIKE 'ABC%'
       AND D.Referenced_Type = 'TABLE'
       AND M.Table_Owner = D.Owner
       AND M.Table_Name = D.Referenced_Name;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width