How to effectively use oracle data dictionary

ORACLE Data dictionary is an important part of a database. It is generated with the database and changes with the database. It is reflected in some tables and Views under the sys user. The data dictionary name is an uppercase English character. The data dictionary contains user information, user permission information, all data object information, table constraints, and statistical analysis data.

ORACLE Data dictionary is an important part of a database. It is generated with the database and changes with the database. It is reflected in some tables and Views under the sys user. The data dictionary name is an uppercase English character. The data dictionary contains user information, user permission information, all data object information, table constraints, and statistical analysis data.

ORACLE Data dictionary is an important part of a database. It is generated with the database and changes with the database. It is reflected in some tables and Views under the sys user. The data dictionary name is an uppercase English character.

The data dictionary contains user information, user permission information, all data object information, table constraints, and views of the statistical analysis database. We cannot manually modify the information in the data dictionary. Generally, ORACLE users do not know how to use it effectively.

The name and interpretation of all data dictionary tables. It has a synonym, dict.
Dict_column field names and explanations in all data dictionary tables

To query index-related data dictionaries, use the following SQL statement:

SQL> select * from dictionary where instr (comments, 'index')> 0;

If you want to know the detailed meaning of each field name in the user_indexes table, you can use the following SQL statement:

SQL> select column_name, comments from dict_columns where
Table_name = 'user _ indexes ';

You can easily know the detailed name and explanation of the data dictionary without viewing other ORACLE documents.

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> 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 from
User_constraints where table_name = upper ('& table_name ');

SQL> select c. constraint_name, c. constraint_type, cc. column_name
From user_constraints c, user_cons_columns cc
Where c. owner = upper ('& table_owner') and c. table_name = upper ('& table_name ')
And c. owner = cc. owner and c. constraint_name = cc. constraint_name
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;