#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    11
    Rep Power
    0

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


    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
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    11
    Rep Power
    0
    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?

IMN logo majestic logo threadwatch logo seochat tools logo