Explain the data dictionary in Oracle database and related SQL query usage _oracle

Oracle Data Dictionary Overview

Database is a collection of data, database maintenance and management of this user's data, then where the user data tables, the user's information, where the data storage of these users where the path, this information is not part of the user's information, but is the database maintenance and management of user data core, This information is maintained by the data dictionary of the database, and the database data dictionary brings together the basic information needed for the operation of these databases. Each database provides a solution of its own data dictionary, although the form is different, but the purpose and function is the same, such as in MySQL, the data dictionary is in the INFORMATION_SCHEMA, SQL Server is in the SYS system schema to show.
Oracle's data dictionary is a series of database objects that are automatically created after an Oracle database is installed. A data dictionary is a metadata information about the structure of an Oracle database object. Familiarity with and in-depth study of data dictionary objects can help us understand the Oracle internal mechanism to a large extent.
The Oracle dictionary view includes four levels, namely, the x$ internal table, the underlying data dictionary table, the data dictionary view, and the dynamic performance view.
There is no difference between a data dictionary table and a table created by a user. The data in the data dictionary, however, is the system data that is stored in the Oracle system, and the normal table holds the data of the user, and for the convenience of distinguishing the tables, the names of the tables are terminated with "$", and "$" is seen in the SQL statement we see. The end of these tables, you can think of this is probably a data dictionary table, again, so, we create our own user table when we do not use the "$" end, so as not to let others misunderstand, the data dictionary table is the system to store users of the system, so his owner is sys, in the manual create Database command, the $ORACLE_HOME/RDBMS/ADMIN/SQL.BSQ file will be invoked, and this will be done by generating our data dictionary tables. Open SQL.BSQ will find that a lot of data dictionaries end up with almost $, such as col$,tab$.
For a data dictionary table, the data inside is maintained by the database system itself, so although it is possible to use DML statements to modify the content of the data, it is best not to do it yourself, because these tables are used in the database, we sometimes see 604 of the recursive SQL here SQL is actually very likely to be the execution of our relevant data dictionary table. So here we remember not to modify the contents of these table.
I just said it. The user of the data dictionary table here is sys, exists in the system This table space, the table name ends with "$", in order to facilitate the user to query the data dictionary table, such names are not good for our memory, so Oracle for these data dictionaries have established a user view view, Not only is there a more acceptable name, but also hides the relationship between the data dictionary table, let us byte through the view to query, simple and image, Oracle for the scope of these objects, respectively, the view named Dba_xxxx, All_xxxx and User_xxxx
User_ Object View: Describes the object under the current user schema;
All_ Object View: Describes information about all objects that the current user has access to;
Dba_ Object View: Includes information for all database objects;
Note: Two scripts are run when you create a database. Run Catalog.sql first, which is used to create the internal dictionary table for the database. Then run Catrpoc.sql, which is used to create Pl\sql objects such as stored procedures, packages, etc. built into the database. If we use DBCA to create a database, then DBCA will call both scripts automatically. Otherwise, when the CREATE DATABASE command is executed, you need to run both scripts manually.

Data Dictionary SQL query
The following categories list the query usage methods for some Oracle users ' common data dictionaries.

1, users
View the default table space for 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 system and table-level permissions for the current user

Sql>select * from User_sys_privs;
Sql>select * from User_tab_privs;

2, table
View all tables under the user

Sql>select * from User_tables;

View a table with a name that contains log characters

Sql>select object_name,object_id from User_objects
where InStr (object_name, ' LOG ') >0;

View when a table was created

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 tables placed in Oracle's memory area

Sql>select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;

3, Index
View the number and category of indexes

Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;

To view the fields indexed by an index

Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');

Viewing the size of an index

Sql>select sum (bytes)/(1024*1024) as "size (M)" from User_segments
where Segment_name=upper (' &index_name ') ;

4, serial number
View serial number, Last_number is the current value

Sql>select * from User_sequences;

5, view
View the name of the view

Sql>select view_name from User_views;

View a SELECT statement that creates a view

Sql>set view_name,text_length from User_views;
Sql>set long;        Note: You can set the size of the set long
sql>select text from user_views where View_name=upper (' &view_name ') based on the view's text_length value;

6. Synonyms
View the name of a synonym

Sql>select * from user_synonyms;

7. Constraint conditions
view constraints on 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 procedures
View the status of functions and procedures

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 source code for functions and procedures

Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');