Oracle Data Dictionary Summary

The following describes how to use common data dictionaries for oracle users by category.

I. Users

 

View the default tablespace of the current user
SQL> select username, default_tablespace from user_users;

View the role of the current user
SQL> select * From user_role_privs;

View the system and table-level permissions of the current user
SQL> select * From user_sys_privs;
SQL> select * From user_tab_privs;

 

Ii. Table

View All tables under a user
SQL> select * From user_tables;

View tables whose names contain log characters
SQL> select object_name, object_id from user_objects where instr (object_name, 'log')> 0;

View the creation time of a table
SQL> select object_name, created from user_objects where object_name = upper ('& table_name ');

View the size of a table
SQL> select sum (bytes)/(1024*1024) as size (m) from user_segments where segment_name = upper ('& table_name ');

View the table in the Oracle memory Partition
SQL> select table_name, cache from user_tables where instr (cache, 'y')> 0;

Iii. Index

View the number and category of Indexes
SQL> select index_name, index_type, table_name from user_indexes order by table_name;
View indexed fields
SQL> select * From user_ind_columns where index_name = upper ('& index_name ');

View index size
SQL> select sum (bytes)/(1024*1024) as size (m) from user_segments where segment_name = upper ('& index_name ');

Iv. Serial number

View the serial number. last_number is the current value.
SQL> select * From user_sequences;

V. View

View view name
SQL> select view_name from user_views;

View the SELECT statement for creating a view
SQL> select view_name, text_length from user_views;
SQL & gt; set long 2000;

// Description: You can set the size of set long based on the text_length value of the view.
SQL> select text from user_views where view_name = upper ('& view_name ');

6. Synonyms

View synonym name
SQL> select * From user_synonyms;

VII. Constraints

View the constraints of a table
SQL> select constraint_name, constraint_type, search_condition, r_constraint_name

2 from user_constraints where table_name = upper ('& table_name ');

// Note: The table name must be in uppercase.

SQL> select C. constraint_name, C. constraint_type, CC. column_name

2 from user_constraints C, user_cons_columns CC

3 Where C. Owner = upper ('& table_owner ')

4 and C. table_name = upper ('& table_name ')
5 and C. Owner = cc. Owner and C. constraint_name = cc. constraint_name
6 order by CC. position;

8. storage functions and processes

View the status of functions and processes
SQL> select object_name, status from user_objects where object_type = 'function ';
SQL> select object_name, status from user_objects where object_type = 'Procedure ';

View the source code of functions and processes
SQL> select text from all_source where owner = user and name = upper ('& plsql_name ');

IX. triggers

View triggers

Set long 50000;
Set heading off;
Set pagesize 2000;

 

Select

'Create or replace trigger' |
Trigger_name | ''| CHR (10) |
Decode (substr (trigger_type, 1, 1 ),
'A', 'after', 'B', 'before', 'I', 'instead of ') |
CHR (10) |
Triggering_event | CHR (10) |
'On' | table_owner | '.' |
Table_name | ''| CHR (10) |
Decode (instr (trigger_type, 'ach row'), 0, null,
'For each row') | CHR (10 ),
Trigger_body
From user_triggers;