Shulou(Shulou.com)06/01 Report--

Summary of data Dictionary tables and views commonly used in Oracle

Classification of a data dictionary

1. Static data dictionary table 2. Static data dictionary view 3 dynamic data dictionary table 4 dynamic data dictionary view

2. Use of data dictionaries

1. The use of static data dictionary tables

Static data dictionary tables can only be maintained by ORACLE.

two。 Use of static data dictionary view *

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

Oracle static data dictionary classification

Name prefix meaning

User_ contains information about all the schema objects owned by the current database user

All_ contains information about all schema objects that can be accessed by the current database user

Dba_ contains all the database object information, and only users with the DBA role can access these views

For example:

Select * from dba_tables

Select * from all_tables

Select * from user_tables

Note: dba_ must start with sys users, while non-sys users need to be prefixed with sys.

Here are some common view families, all of which have a view of DBA_ ALL_ USER_

Col_privs contains the column permission information of the table, including the grantee, grantee, permission name and other information.

Extents stores allocation information, including data segment name, table space name and partition number, partition size

Indexes index information includes table information such as index type, uniqueness, index function, etc.

Ind_columns index column information includes information such as how the columns on the index are sorted

Object object information, including object name, type, creation time, etc.

Segment information for segments tables and indexes, including tablespaces, storage settings, etc.

Sequences sequence information includes sequence name, cyclicity, maximum value and other information.

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

Synonyms synonym information includes information such as referenced objects

Sys_privs system permission information includes the grantee of the system permission name

Column information for tab_columns tables and views, including data types of columns

Tab_privs permission information

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

Triggers trigger information includes trigger type, event. Information such as trigger body

Users user information. Includes user temporary and default table space types

Views View Information

Take a look at the permissions view

View of system permissions owned by the role_sys_privs role

Object permissions owned by the role_tab_privs role

Roles owned by user_role _ privs user

The role of the permissions owned by the user_sys_privs user

About table object permissions assigned by user_tab_privs_mads user

Permissions on table objects owned by user_tab_privs_recd users

Object permissions on columns assigned by the user_col_privs_mads user

Object permissions on columns that the user_col_privs_recd user has

3. Dynamic performance tables are tables that are created after the database instance is started. Dynamic performance tables are all data SYS users; used to store database performance-related information in the process of running. View through the following view

Select name from v_$fixed_table

4. Use of dynamic performance views

Only sys users and users with the DBA role can access it.

V$ parameter v$sga v$session v$process v$instance v$version v$option can be accessed when the database is started to the state of NOMOUNT

We can also access v$log v$logfiel v$datafile v$controlfile v$ database v$thread v$datafile_header when the database starts to the state of mount

When the database is fully started. Access to v_$fixed_table

V$access contains the database objects that are currently locked and the sessions that are accessing them

V$archive contains the information in the redo log file required for archiving

V $archived_log contains archive log information obtained from the control file.

V$archive_processes contains the status information of an instance-related arch process

V $backup contains backup status information for online data files

V$backup_async_io includes information about backup sets obtained from control files

V $backup_corruption contains information obtained from the control file about corruption in the backup of the data file.

V $backup_datafile contains information about backup data files and backup control files obtained from control files

V $backup_device contains information that supports backup devices

V$backup_piece contains information about backup blocks obtained from the control file

V$backup_redolog contains information about the archive log of the backup set obtained from the control file

V$backup_set contains information about backup sets obtained from control files

V$bgprocess contains database background process information

V $buffer_pool contains information about all available buffer pools in the current instance

V $buffer_pool_statistics contains statistics for all available buffer pools in the current instance

V $cache contains header information for each block in the SGA of the current instance

V$context contains property information for the current conversation.

V$controlfile contains control file information

V $controlfile_record_section contains information about the record section of the control file

V $copy_curruption contains information about corrupted copies of data files obtained from the control inquiry.

V$database contains database information obtained from the control file

V$datafile contains data file information obtained from the control file

V$datafile_copy includes information about copies of data files obtained from control files

V$datafile_header contains data file header information

V $db_object_cache contains database object information cached in the library cache

V$db_pipes contains pipe information in the current database

V$deleted_object contains information about deleted archive logs, copies of data files, and backup blocks obtained from control files

V $dispatcher_rate contains information about scheduling process rate statistics

V $dispatche contains information about the scheduling process

V $DLM_ALL_LOCKS contains all current locks

V $DLM_CONVERT_LOCAL contains information about the time consumed by the local lock conversion operation

V $EVENT_NAME contains information about waiting time

V $fixed_table contains information about all available dynamic performance views and dynamic performance tables

V $sysstat contains performance statistics for the current instance

V$ instance contains the details of the current instance

V$sga contains information about the main components of the SGA area

V$ sgainfo contains detailed messages for the SGA zone

V $parameter contains initialization parameter information

V$ sversion contains Oracle version information

V $option contains option information for installed Oraclette components

V$session contains all current session information

V$process contains all the process information of the current system

V$ bgprocess contains information about all background processes in the database

V $database contains the current database information

V $controlfile contains all the control file information of the current database

V $datafile contains information about all the data files in the current database

V $dbfile contains numbering information for all data files

V $logfile contains all the redo log file information of the current database

V $log contains the current database redo log file information

V$ log_history contains historical information about the switching of redo log files

V $thread contains information about the current database thread

V $lock contains lock information

V $locked_object contains information about locked database objects

V $rollname contains fallback information that is currently online

V $rollstat contains statistics for all current fallback segments

V $tablespace contains all the tablespace information of the current database

V $tempfile contains information about temporary data files in the current database.

View the current user's default tablespace

SQL > select username,default_tablespace from user_users

View the role of the current user

SQL > select * from user_role_privs

View the system permissions and table-level permissions of the current user

SQL > select * from user_sys_privs

SQL > select * from user_tab_privs

View all the tables under the user

SQL > select * from user_tables

View the column properties of all tables under the user

SQL > select * from USER_TAB_COLUMNS where table_name=:table_Name

Display user information (tablespace)

Select default_tablespace,temporary_tablespace

From dba_users where username='GAME'

1. Users

View the current user's default tablespace

SQL > select username,default_tablespace from user_users

View the role of the current user

SQL > select * from user_role_privs

View the system permissions and table-level permissions of the current user

SQL > select * from user_sys_privs

SQL > select * from user_tab_privs

Displays the permissions for the current session

SQL > select * from session_privs

Displays the system permissions of the specified user

SQL > select * from dba_sys_privs where grantee='GAME'

Show privileged users

Select * from v$pwfile_users

Display user information (tablespace)

Select default_tablespace,temporary_tablespace

From dba_users where username='GAME'

Display the user's PROFILE

Select profile from dba_users where username='GAME'

2. Table

View all the tables under the user

SQL > select * from user_tables

View a table whose name contains 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) / (1024024) as "size (M)" from user_segments

Where segment_name=upper ('& table_name')

View the table in the memory area of Oracle

SQL > select table_name,cache from user_tables where instr (cache,'Y') > 0

3. Index

View the number of indexes and categories

SQL > select index_name,index_type,table_name from user_indexes order by table_name

View the fields indexed by the index

SQL > select * from user_ind_columns where index_name=upper ('& index_name')

View the size of the index

SQL > select sum (bytes) / (1024024) as "size (M)" from user_segments

Where segment_name=upper ('& index_name')

4. Serial number

Check the 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 the select statement that creates the view

SQL > set view_name,text_length from user_views

SQL > set long 2000; description: you can set the size of the set long according to the text_ value of the view

SQL > select text from user_views where view_name=upper ('& view_name')

6. Synonyms

View the name of the synonym

SQL > select * from user_synonyms

7. Constraint conditions

View the constraints of 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. Column intact 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. Store 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 the source code of functions and procedures

SQL > select text from all_source where owner=user and name=upper ('& plsql_name')

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.