Describes the usage of data dictionaries and related SQL queries in Oracle databases.

Describes the usage of data dictionaries and related SQL queries in Oracle databases.

Oracle Data Dictionary Overview
 
A database is a collection of data. The database maintains and manages the user data, so where are these user data tables, what is the user information, and where is the path to store the user data, this information does not belong to users, but is the core of Database Maintenance and Management of user data. This information is maintained by the database data dictionary, the Data Dictionary of the database collects the basic information required for running these databases. Each database provides its own data dictionary solution, which has different forms but has the same purpose and function. For example, in mysql, the data dictionary is represented in information_schema, sqlserver is displayed in the sys system schema.
Oracle Data dictionary is a series of database objects automatically created after the Oracle database is installed. A data dictionary is the metadata of an Oracle database object structure. Familiar with and in-depth research on data dictionary objects can help us understand the internal mechanism of Oracle to a great extent.
The Oracle dictionary view consists of four layers: X $ internal table, basic data dictionary table, data dictionary view, and dynamic performance view.
The data dictionary table is no different from the table created by the user. However, the data in the data dictionary table is the system data stored by the Oracle system, while the common table stores the user data, to facilitate the differences between these tables, the names of these tables end with "$". In the SQL statement we see, we can see the tables ending with "$, you can think of this as a data dictionary table. In this case, we should not end our user table with "$" to avoid misunderstanding, the data dictionary table is stored by the System user, so its owner is sys. When you manually use the create database Command, $ ORACLE_HOME/rdbms/admin/SQL is called. bsq file. This will generate the data dictionary tables. Open SQL. bsq and you will find that many data dictionaries end with almost $, such as col $ and tab $.
The data in the data dictionary table is maintained by the database system itself. Therefore, although the data content can be modified using DML statements like normal tables, however, it is recommended that you do not do this by yourself, because these tables apply to the internal database, we sometimes see 604 of the recursive SQL here, the SQL here is actually probably the execution content of our related data dictionary table. So remember not to modify the contents of these tables.
I said just now. The user of the data dictionary table here is sys, which exists in the system tablespace and all table names end with "$". To facilitate the user's query of the data dictionary table, such names are not conducive to our memory. Therefore, Oracle has created user views for these data dictionaries separately, not only with more acceptable names, it also hides the relationship between data dictionary tables and allows bytes to be queried through views. It is simple and visual. Oracle names the views DBA_XXXX, ALL_XXXX, and USER_XXXX respectively for the range of these objects.
User _ OBJECT view: Describes the objects in the current user schema;
All _ OBJECT view: describes information about all objects that the current user has access;
Dba _ OBJECT view: contains information about all database objects;
Note: two scripts are run when you create a database. Run catalog. SQL to create the internal dictionary table of the database. Then run catrpoc. SQL, which is used to create pl \ SQL objects such as stored procedures and packages in the database. If we use dbca to create a database, dbca will automatically call these two scripts. Otherwise, you need to manually run the create database Command to create the database.
 
Data Dictionary SQL query
The following describes how to use common data dictionaries for ORACLE users by category.
 
1. User
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;

 
2. Tables
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_objectswhere 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_segmentswhere 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;

 
3. 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_segmentswhere segment_name=upper('&index_name');

 
4. Serial number
View the serial number. last_number is the current value.

SQL>select * from user_sequences;

 
5. 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

SQL>select * from user_synonyms;

 
7. Constraints
View the constraints of a table

SQL>select constraint_name, constraint_type,search_condition, r_constraint_namefrom user_constraints where table_name = upper('&table_name'); SQL>select c.constraint_name,c.constraint_type,cc.column_namefrom user_constraints c,user_cons_columns ccwhere c.owner = upper('&table_owner') and c.table_name = upper('&table_name')and c.owner = cc.owner and c.constraint_name = cc.constraint_nameorder 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');

Articles you may be interested in:

• Analysis of Oracle Data Dictionary Technology
• Brief description of the deadlock query and solution in Oracle Database
• Sort out the key points of Data Query Optimization in Oracle databases
• Basic query optimization and subquery Optimization in Oracle databases
• Comprehensive query optimization suggestions for some Oracle databases
• How to query locked objects and end their sessions in ORACLE
• Oracle 9i uses flashback query to Restore database accidental deletion
• Detailed description of SQL Server and Oracle paging Query
• How to configure MySQL or Oracle database for Node. js programs in Linux