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!

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

Discussion in 'SQL PL/SQL' started by ecivgamer, Dec 11, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    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. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    For #1 you can use DBA_DEPENDENCIES or search DBA_SOURCE (or the USER/ALL versions of these views). the *_DEPENDENCIES views are useful if the tables are *directly* referenced in the code, but can miss cases in which a table is referenced via dynamic SQL.

    For #2, there isn't really a long-term cache of SQL queries. the V$SQL* views will have some information, but not over a range of time like that.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    if it is necessary to check the fact use of tables in sql-query, then it is possible to use views of dba_hist. *

    dba_hist...

    for example...

    Code (SQL):


    WITH
    list_tab AS
    (SELECT
        ut.owner||'.'||ut.TABLE_NAME  TABLE_NAME
     FROM
         dba_tables ut
     WHERE
         ut.TABLE_NAME LIKE 'ABS%'
     )
    SELECT ah.snap_id,ah.sample_time,CAST(st.sql_text AS varchar2(4000)) sql_txt
    FROM
        dba_hist_active_sess_history ah
    JOIN
        dba_hist_sqltext st ON   st.dbid = ah.dbid
                                 AND
                                 st.sql_id = ah.sql_id
                                 --!!and st.command_type in (3,6,7,189)
                                 -- see to v$sqlcommand

    JOIN list_tab lt ON instr(st.sql_text,lt.TABLE_NAME) > 0
                                 
                                 
    WHERE ah.sample_time BETWEEN trunc(sysdate)- 14 AND trunc(sysdate);



     
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    From the documentation: "DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity."

    The OP isn't going to get anything approaching 2-weeks of history from that view. I would be surprised to get as much as 24 hours.