Follow by Email

Monday, September 8, 2014

Usefull Oracle Database Queries


Get the details of important oracle parameters such as "PASSWORD_LOCK_TIME" and "SESSIONS_PER_USER" etc

select * from dba_profiles order by profiles

Get the Name of the oracle instance

SELECT sys_context('USERENV','DB_NAME') AS Instance FROM dual;
Get the name of the current oracle instance and user id

 select name,user from v$database


Query to see who has access to your package


select grantee, table_name, privilege
     from dba_tab_privs
     where
       table_name = 'YOUR_PACKAGE_NAME'


Query to see what tables are being used in your package
SELECT a.owner schema_name,a.name package_name,a.referenced_name table_name,b.COMMENTSFROM dba_dependencies a,dba_tab_comments bWHERE TYPE IN ('TABLE', 'PACKAGE BODY')

and referenced_type ='TABLE'

and name like ('%CSAS%')

and a.REFERENCED_NAME=b.TABLE_NAME







/*select * from dba_tab_comments where comments is not null*/












I will keep adding other usefull queries here....

No comments:

Post a Comment