Oracle's Data dictionary

1. A data dictionary is a set of read-only system tables created and maintained by an Oracle server that stores information about databases and database objects. Data dictionaries fall into two main categories: base table and data dictionary view

The Oracle server generates these base tables by running SQL.BSP when the database is created, generating the data dictionary through Catalog.sql.

[email protected] admin]# ll sql.bsq catalog.sql-rw-r--r--. 1 Oracle oinstall 59322 April  catalog.sql-rw-r--r--. 1 Oracle oinstall 53130 July  sql.bsq[[email Protec Ted] admin]# Pwd/opt/oracle/product/11.2.0/db_1/rdbms/admin

2. Classification of data dictionary views

user_*: User-created object information all_*: Object information that the  user can access dba_*  : Object information for the entire database *: can be tables, indexes,objects

View a user-created table:sql> Select table_name from user_tables;                                                                   TABLE_NAME------------------------------zhusql> Select table_name from user_tables;                                                                   TABLE_NAME------------------------------ZHU

#查看结果一眼

A simpler way to query: using the User_catalog data dictionary

Sql> select * from User_catalog;                                          table_name                     table_type-----------------------------------------ZHU                            tablesql> select * from Cat;                                          table_name                     table_type-----------------------------------------ZHU                            TABLE User_catalog is the name of the cat

3. View the database name and creation date, log mode, open status.

Sql> select Name,created,log_mode,open_mode from V$database;                      NAME      CREATED     log_mode     open_mode----------------------------------------------------ZABBIX    2014 /5/2 Noarchivelog READ WRITE

4. View host name, instance name, version number

Sql> select Host_name,instance_name,version from V$instance;                 HOST_NAME                                                        instance_name    VERSION----------------------------------------------------------------------- --------------------------Zhu                                                              Zabbix           11.2.0.1.0

5. View the name of the control file

Sql> select * from V$controlfile;             STATUS  NAME                                          is_recovery_dest_file block_size file_size_blks--------------------------------------------- ----------------------------------------------------        /opt/oracle/oradata/zabbix/control01.ctl      NO                         16384            594        /opt/oracle/oradata/zabbix/control02.ctl      NO                         16384            594

6. View the Redo log configuration information for the database

Sql> select group#,members,bytes,status,archived from V$log;          group#    members      BYTES STATUS           archived------------------------------------------------------         1          1   52428800 INACTIVE         no         2          1   52428800 current          no         3          1   52428800 INACTIVE         NO

7. View the file location of the redo log

Sql> select Group#,status,type,member from V$logfile;       group# STATUS  TYPE    MEMBER------------------------------------------------------------------------------ --------------------------         3         online  /opt/oracle/oradata/zabbix/redo03.log         2         online  / Opt/oracle/oradata/zabbix/redo02.log         1         ONLINE  /opt/oracle/oradata/zabbix/redo01.log

This article from "Wave Scouring sand" blog, declined reprint!