Oracle Data Dictionary

Oracle Data Dictionary

The Oracle data dictionary is where the data is stored, and its purpose is to describe the data. Data dictionaries can be divided into static and dynamic.

static Data dictionary

A static data dictionary: the User Access data dictionary does not change, mainly by the table and the view, the data dictionary table is not directly accessible, but we can access the data dictionary view. The static data dictionaries are divided into three categories, their prefixes are user_*, all_*, dba_*, respectively.

• User_*: This view stores information about all objects under the current user
• All_*: This view stores information about the objects that the current user can access. (all_* does not need to have the object as compared to user_*, only the right to access the object )
• Dba_*: This view stores information about all objects in the database. (provided that the current user has permission to access these databases, you must generally have Administrator privileges

The above three classes deal with different access ranges (permissions differ), others are consistent, and only SYS and users with DBA system privileges can see all views. Users who do not have DBA authority can only see the user_* and all_* views. If the relevant SELECT permission is not granted, they cannot see the dba_* view.

select * from dictionary;

Dba_data_file: Records information about a physical data file, including information about the name, size, table space of the data file, and basic information about the data file

Dba_tab_grant: Permissions granted to objects in the database

Dba_tab_privs: Viewing permissions for users in a database

Dba_sys_privs: View the privileges of the users and roles used

Dba_tab_quotas: View all user table space limits

Dba_ts_quotas: All user table space limits

Dba_uers: View all user information

Dba_view: View All View information

Dba_roles: Show All role information

Dba_tablespaces: Querying some basic data dictionaries about tablespaces

Dba_free_tablespaces: Show free space for table space

Dba_temp_files: Basic information for temporary files

Dba_tables; DBA_TAB_COLUMNS:DBA basic information for all tables of the user

DBA_SYNONYMS:DBA basic information for all synonyms of the user

DBA_SEQUENCES:DBA information for all the sequences of users

DBA_CONSTRAINTS:DBA information about all the constraints of the user

DBA_INDEXES:DBA basic information for all indexes of the user

Dba_ind_columns: Columns with compressed indexes on all tables and clusters

DBA_TRIGGERS:DBA basic information for all triggers of the user

DBA_SOURCE:DBA basic information for all stored procedures of the user

DBA_SEGMENTS:DBA basic information for all segments of the user

DBA_EXTENTS:DBA basic information for all areas of the user

DBA_OBJECTS:DBA basic information for all objects of the user

Cat: All base tables that the current user can access

System.tab: All base tables, views, synonyms that the current user can access

Dict: All table information that constitutes a data dictionary

Role_tab_privs: Privileges for table objects that are granted to all roles

User_sys_privs: View your permissions to view system and table-level permissions for the current user

User_role_privs: Show user roles to view the roles of the current user

User_tab_privs: This view is primarily about storing permission information for all tables under the current user.

User_users: Mainly describes the current user's information, view the current user's default tablespace, including the current user name, account ID, account status, table space name, creation time, etc., all user-related data

User_tables: Query which tables the user owns, mainly describes all the tables that the current user owns, including the table name, table space name, cluster name, and so on. This view provides a clear understanding of what tables the current user can manipulate.

User_objects: Primarily describes information about all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, sequences, and so on. This view is more comprehensive than the User_tables view

User_indexes: Query which indexes the user owns

User_views: Querying which views the user has

User_objects: Query which database objects the user owns, including tables, views, stored procedures, triggers, packages, indexes, sequences, Java files, and so on

Dynamic Data dictionary

II. Dynamic Data Dictionary Oracle contains a number of potential tables and views that are maintained by system administrators such as SYS, which are called Dynamic Data dictionaries (or dynamic performance views) because they are constantly updated when the database is running. These views provide information about memory and disk operations, so we can only read access to them and not modify them. These dynamic performance views in Oracle are views that start with v$

V$_: Dynamic view of the current instance, including views used by system management and system optimization

GV_: Dynamic view of all instances in a distributed environment, including views for system management and system optimization use

V$access: This view displays the database objects that are locked in the database and the session objects that access them (the sessions object).

V$session: This view lists the details of the current session.

V$active_instance: This view primarily describes the information for an instance of an activity under the current database.

V$context: This view lists the properties information for the current session. such as namespaces, property values, and so on.

V$instance: Basic information for a DB instance.

V$datafile;: basic information for data files

V$controlfile; Basic information for control files

V$logfile: Basic information about the log file, information about the instance resetting the log group file name and its location

V$log extracting information about redo log groups from a control file

V$archived_dest record the path information for the archived log file

V$database: Basic information about the database

Show parameter log_archive_dest: log file parameter information

V$parameter: Records the values of all items in the initialization parameter file

V$system_parameter display the current valid parameter information for the instance

V$process information about the current process

V$bgprocess: Background Process information

V$archived_log: Some basic information about the archive status log file

V$SGA: Shows the size of the SGA area of the instance

V$db_object_cache; V$sql: Some information about the memory structure

V$sgastat: Statistical information about SGA usage

V$lock: Set all information for an object lock by accessing a database session

V$sql: Logging details of SQL statements

V$sqltext: Record statement information for SQL statements

Data Dictionary usage examples:

2. Display user information (owning table space): Select Default_tablespace,temporary_tablespace from dba_users where username= ' GAME ';
4. View the default tablespace for the current user select Username,default_tablespace from user_users;
6. View the role of the current user select * from User_role_privs;
8. View system and table-level permissions for the current user select * from User_sys_privs; SELECT * from User_tab_privs;
10. Displays the permissions that the current session has. SELECT * from Session_privs;
12. Displays the system permissions that the specified user has, select * from Dba_sys_privs where grantee= ' GAME ';
14. Show privileged User select * from v$pwfile_users;
16. Display user information (owning table space) Select Default_tablespace,temporary_tablespace from dba_users where username= ' GAME ';
18. Displays the user's profile select profiles from dba_users where username= ' GAME ';
20. View all tables under User select * from User_tables;
22. View a table with a name that contains the LOG character select object_name,object_id from User_objects where InStr (object_name, ' LOG ') >0;
24. View the creation time of a table select object_name,created from user_objects where Object_name=upper (' &table_name ');
26. View the size of a table select sum (bytes)/(1024*1024) as "size (M)" from User_segments where Segment_name=upper (' &table_name ');
28. View tables placed in the memory area of Oracle Select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;
30. View index number and category select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;
32. View index indexed fields select * from User_ind_columns where Index_name=upper (' &index_name ');
34. View the size of the index select SUM (bytes)/(1024*1024) as "size (M)" from User_segments where Segment_name=upper (' &index_name ');
36. To view the serial number, Last_number is the current value of select * from User_sequences;
38. View the name of the view select View_name from User_views;
40. View the SELECT statement that created the view set view_name,text_length from User_views; Set Long 2000; Description: The size of the set long can be set according to the Text_length value of the view select text from User_views where View_name=upper (' &view_name ');
42. View the name of the synonym select * from user_synonyms;
44. View constraints on a table select Constraint_name, Constraint_type,search_condition, r_constraint_name from user_constraints where tabl E_name = Upper (' &table_name ');

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;

1. View the status of functions and procedures select Object_name,status from user_objects where object_type= ' function '; Select Object_name,status from user_objects where object_type= ' PROCEDURE ';
2. View the source code of the function and procedure select text from All_source where Owner=user and Name=upper (' &plsql_name ');

Oracle Data Dictionary