Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to use data dictionary view management objects in the database, I believe most people still do not know how, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

1. Classification of data dictionary

1. Static data dictionary table

2. Static data dictionary view

3. Dynamic data dictionary table

4. Dynamic Data Dictionary View

II. Use of Data Dictionary

1. Static data dictionary table

Static data dictionary tables can only be maintained by ORACLE.

2. Static data dictionary view

Usually users can obtain all the database information they need by querying the static data dictionary view.

a) Oracle static data dictionary classification

i. user_: Contains information about all schema objects owned by the current database user

ii. all_: Contains information about all schema objects accessible to the current database user

iii. dba_: Contains information about all database objects, and only users with DBA roles can access these views

b) Common static view families have a DBA_ ALL_ USER_view

i. col_privs Contains column permission information of the table, including information such as the grantor, the grantee, and the permission name.

ii. extents Store allocation information, including segment names, table space names and partition numbers, partition sizes

iii. indexes Index information contains table information such as index type, uniqueness, index function, etc.

iv. ind_columns index column information includes information such as how the columns on the index are sorted

v. Object information, including information such as object name type creation time, etc.

vi. segments Data segment information of table and index, including table space, storage settings, etc.

vii. sequences sequence information includes sequence name, cyclicity, maximum value and other information

viii. source Source code information for all stored procedures, functions, packages except triggers

ix. synonyms Synonym information includes information such as referenced objects

x. sys_privs System permission information includes system permission name granter

xi. tab_columns Column information for tables and views, including column data types and other information

xii. tab_privs table permission information

xiii. tables table information includes the table space to which the table belongs, storage parameters, number of data rows and other information.

xiv. triggers Trigger information includes the type of trigger, event. Trigger body and other information

xv. users information. Includes user temporary and default types of tablespaces

xvi. views View information

c) Permission View

i. role_sys_privs View of system permissions owned by the role

ii. role_tab_privs Object permissions owned by the role

iii. user_role_ privs user-owned role

iv. user_sys_privs Role of permissions the user has

v. user_tab_privs_mads user-assigned permissions on table objects

vi. user_tab_privs_recd User permissions on table objects

vii. user_col_privs_mads user-assigned object permissions on columns

viii. user_col_privs_recd Object permissions the user has on columns

3. Dynamic Performance View

Only sys users and users with DBA roles can access it.

a) Access is available when the database is booted to NOMOUNT state

i. v$parameter

ii. v$sga

iii. v$session

iv. v$process

v. v$instance

vi. v$version

vii. v$option

b) When the database is started to mount state, we can also access

i. v$log

ii. v$logfile

iii. v$datafile

iv. v$controlfile

v. v$database

vi. v$thread

vii. v$datafile_header

c) When the database is fully started. v_$fixed_table

i. v$access contains database objects currently locked and sessions accessing them

ii. v$archive contains information from redo log files required for archiving

iii. v$archived_log Contains archived log information obtained from the control file.

iv. v$archive_processes Contains state information about arch processes associated with an instance

v. v$backup Contains backup status information for online data files

vi. v$backup_async_io includes information about backup sets obtained from control files

vii. v$backup_corruption Contains information obtained from the control file about corruption in the data file backup.

viii. v$backup_datafile contains information about the backed-up datafile and backed-up control file retrieved from the control file

viii. v$backup_device contains information about supported backup devices

ix. v$backup_piece Contains information about backup blocks retrieved from the control file

x. v$backup_redolog contains information about the archive log for the backup set obtained from the control file

xi. v$backup_set contains information about backup sets obtained from control files

xii. v$bgprocess contains database daemon information

xiii. v$buffer_pool Contains information about all available buffer pools in the current instance

xiv. v$buffer_pool_statistics Contains statistics for all available buffer pools for the current instance

xv. v$cache Contains header information for each block in the SGA of the current instance

xvi. v$context Contains information about the properties of the current conversation.

xvii. $controlfile contains control file information

xviii. v$controlfile_record_section Contains information about the record section of the control file

xix. v$copy_corruption Contains information about corrupt copies of data files obtained from control queries

xx. v$database contains database information obtained from the control file

xxi. v$datafile contains the datafile information obtained from the control file

xxii. v$datafile_copy Contains information about the copy of the datafile obtained from the control file

xxiii. v$datafile_header Contains header information for the datafile

xxiv. v$db_object_cache contains database object information cached in the library cache

xxv. v$db_pipes contains information about pipes in the current database

xxvi. v$deleted_object contains information about deleted archive logs, data file copies, and backup blocks retrieved from the control file

xxvii. v$dispatcher_rate contains information about the scheduling process rate statistics

xxviii. v$dispatche contains information about the scheduling process

xxix. v$DLM_ALL_LOCKS Contains all current locks

xxx. v$DLM_CONVERT_LOCAL Contains information about the time consumed by the local lock conversion operation

xxxi. v$EVENT_NAME contains information about wait times

xxxii. v$fixed_table contains information about all available dynamic performance views and dynamic performance tables

xxxiii. v$sysstat Contains performance statistics for the current instance

xxxiv. v$instance contains details of the current instance

xxxv. v$sga Contains information about the main components of the SGA area

xxxvi. v$sgainfo contains detailed messages for SGA area

xxxvii. v$parameter Contains initialization parameter information

xxxviii. v$sversion contains Oracle version information

xxxix. v$option Contains option information for installed Oraclette components

xl. v$session contains all current session information

xli. v$process contains information about all processes in the current system

xlii. v$bgprocess contains information about all daemons in the database

xliii. v$database contains information about the current database

xliv. v$controlfile contains information about all control files in the current database

xlv. v$datafile contains information about all the data files in the current database

xlvi. v$dbfile contains the numbering information for all data files

xlvii. v$logfile contains all redo log file information for the current database

xlviii. v$log contains the current database redo log file information

xlix. v$log_history contains historical information about redo log file switching

l. v$thread contains information about the current database thread

li. v$lock contains information about the lock

lii. v$locked_object Contains information about database objects that are locked

liii. v$rollname contains fallback information for the currently online state

liv. v$rollstat contains statistics for all current fallback segments

lv. v$tablespace contains all tablespace information for the current database

lvi. v$tempfile contains information about temporary data files that are common to the current database

Above is "how to use data dictionary view to manage objects in database" all contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.