| Table Access script [message #372296] |
Wed, 31 January 2001 14:32  |
Regina
Messages: 9
Registered: January 2001
|
Junior Member |
|
|
Can anyone tell me how I can determine what is accessing a particular table? And When? I want to drop some tables, but want to make sure nothing is or has accessed in awhile.
Any help would be greatly appreciated.
|
|
|
|
| Re: Table Access script [message #372299 is a reply to message #372296] |
Wed, 31 January 2001 19:38  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
This is not conclusive, as it only shows tables still cached somewhere, but it's a start:
select owner, table_name
from all_tables
minus
select owner, name
from v$db_object_cache
where type = 'TABLE'
also try something like:
select * from v$sqltext
where InStr(Upper(sql_text), 'MY_TABLENAME')>0
|
|
|
|
| Re: Table Access script - dependancies... [message #372300 is a reply to message #372296] |
Wed, 31 January 2001 19:47 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
You can also look for dependencies with something like this:
Select distinct
owner uses_owner,
name object_used,
type
from ALL_DEPENDENCIES
where
referenced_owner='THE_OWNER' and
referenced_name like '%' and
( referenced_type<>'NON-EXISTENT') and
( referenced_type='TABLE'
)
and owner <> 'SYS'
and name <> 'DUAL'
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
