oracle 数据字典应用

1、可以通过v$fixed_table 或dictionary来得到oracle中所有的数据字典

2、v$database

SQL> select name,created,log_mode,open_mode from v$database;

NAME                           CREATED   LOG_MODE     OPEN_MODE
------------------------------ --------- ------------ --------------------
ORCL                           29-NOV-23 ARCHIVELOG   READ WRITE

 3、v$instance

SQL> select host_name,version,status,blocked,instance_name from v$instance ;

HOST_NAME
----------------------------------------------------------------
VERSION           STATUS       BLO INSTANCE_NAME
----------------- ------------ --- ----------------
localhost.localdomain
11.2.0.1.0        OPEN         NO  orcl

 4、v$version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 5、V$controlfile;

SQL> select * from v$controlfile;

STATUS  NAME                           IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------ --- ---------- --------------
        /u01/app/oracle/oradata/orcl/c NO       16384            600
        ontrol01.ctl

        /u01/app/oracle/flash_recovery NO       16384            600
        _area/orcl/control02.ctl

 6、v$log

SQL> select  * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1        190   52428800        512          1 YES
INACTIVE               3469063 11-MAR-24      3514833 24-OCT-25

         2          1        189   52428800        512          1 YES
INACTIVE               3454354 11-MAR-24      3469063 11-MAR-24

         3          1        191   52428800        512          1 NO
CURRENT                3514833 24-OCT-25   2.8147E+14

 7,v$logfile

SQL> select group#,type,member from v$logfile;

GROUP# TYPE    MEMBER
------ ------- ------------------------------------------------------------
     1 ONLINE  /u01/app/oracle/oradata/orcl/redo01.log
     2 ONLINE  /u01/app/oracle/oradata/orcl/redo02.log
     3 ONLINE  /u01/app/oracle/oradata/orcl/redo03.log

 8、查看归档

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     190
Next log sequence to archive   192
Current log sequence           192

 9、查看表空间dba_tablespaces;

SQL> select tablespace_name,block_size,status,contents,logging from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE STATUS     CONTENTS  LOGGING
------------------------------ ---------- ---------- --------- ---------
SYSTEM                               8192 ONLINE     PERMANENT LOGGING
SYSAUX                               8192 ONLINE     PERMANENT LOGGING
UNDOTBS1                             8192 ONLINE     UNDO      LOGGING
TEMP                                 8192 ONLINE     TEMPORARY NOLOGGING
USERS                                8192 ONLINE     PERMANENT LOGGING
MYTEMP                               8192 ONLINE     TEMPORARY NOLOGGING

 10、查看数据文件

SQL> select file_id,file_name,tablespace_name,status,bytes from dba_data_files;

   FILE_ID FILE_NAME                      TABLESPACE_NAME STATUS          BYTES
---------- ------------------------------ --------------- ---------- ----------
         1 /u01/app/oracle/oradata/orcl/s SYSTEM          AVAILABLE   734003200
           ystem01.dbf

         2 /u01/app/oracle/oradata/orcl/s SYSAUX          AVAILABLE   639631360
           ysaux01.dbf

         3 /u01/app/oracle/oradata/orcl/u UNDOTBS1        AVAILABLE   781189120
           ndotbs01.dbf

         4 /u01/app/oracle/oradata/orcl/u USERS           AVAILABLE     5242880
           sers01.dbf

   FILE_ID FILE_NAME                      TABLESPACE_NAME STATUS          BYTES
---------- ------------------------------ --------------- ---------- ----------

 11、查看用户dba_users

SQL> select username,created from dba_users;

USERNAME                       CREATED
------------------------------ ---------
BI                             26-DEC-23
PM                             26-DEC-23
IX                             26-DEC-23
SH                             26-DEC-23
OE                             26-DEC-23
HR                             26-DEC-23
ORACLE_OCM                     29-NOV-23