-
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.
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|