Sunday 3 July 2016

Useful data dictionary views




NLS_DATABASE_PARAMETERS --> lists permanent NLS parameters of the database.NLS_INSTANCE_PARAMETERS lists NLS parameters of the instance.
There is one row for each component.You can query any of DBA_*, ALL_*,USER_*.In the below table I am listing only USER_


Dictionary Name
Details
USER_OBJECTS
Stores details about all the objects in a schema –
Object Types – Table, View, Synonym, Trigger, Sequence, Procedure, Function, Package, Package Body, Trigger, Index, Database Link, Type, polcies, Rules (VPD policy rules)
USER_TABLES
Table – Table Space, Disk block allocation details
USER_TAB_COLS
Column of table
USER_VIEWS
Views data dictionary – You can get the view text 
USER_SEQUENCES
Sequence information – Seq min, max, increment, etc.
user_synonyms
Synonyms – Reference object details
User_triggers
Stores the all the trigger in the schema. – You can get the trigger text and trigger table etc.
User_trigger_cols
Trigger column details
User_source
Stores all the source code – You can get code for trigger, procedure, package, package body, type body, type, function, procedure.
User_procedures
Lists all the procedure – You can get a list of procedure for a given package.
User_arguments
List of all the parameters for a given procedure. It includes package procedures as well.
User_indexes
List of Indexes
ex: select * from user_indexes where table_name=''
user_ind_columns
Index column names

user_dependencies

Dependencies 

select * from user_dependencies 
                where referenced_name='GL_LEDGER_NORM_SEG_VALS' 
                and name not like 'XX%'

                and referenced_type='SYNONYM' and referenced_owner='APPS';

No comments:

Post a Comment