ORACLE Data Dictionary usage instance

ORACLE Data dictionary is an important component. 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. Di

ORACLE Data dictionary is an important component. It is generated with the database and changes with the database changes,

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> set 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