oracle数据字典应用实例

         1、获得数据库的名字、创建日期等；

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

                        NAME      CREATED     LOG_MODE       OPEN_MODE
                        ---------      -----------               ------------             ----------
                         DOG       2011/11/15  NOARCHIVELOG READ WRITE

       2、获得计算机的主机名，Oracle数据库的实例名以及数据库管理系统的版本；

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

                                HOST_NAME                                                        INSTANCE_NAME    VERSION
                                ---------------------------------------------------------------- ---------------- -----------------
                                JSB-WANGQIANG-G                                                  dog              9.2.0.1.0
       3、获取Oracle数据库管理系统的版本   

               SQL> select * from v$version;

                          BANNER
                          ----------------------------------------------------------------
                          Oracle9i Release 9.2.0.1.0 - Production
                          PL/SQL Release 9.2.0.1.0 - Production
                          CORE 9.2.0.1.0 Production

                          TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
                          NLSRTL Version 9.2.0.1.0 - Production
       4、获取控制文件名             

              SQL> select * from v$controlfile;

             STATUS  NAME
              ------- --------------------------------------------------------------------------------
                                  D:\ORACLE\ORADATA\DOG\CONTROL01.CTL
                                  D:\ORACLE\ORADATA\DOG\CONTROL02.CTL
                                  D:\ORACLE\ORADATA\DOG\CONTROL03.CTL

        5、获取重做日志配置信息；  

          SQL> select group#,members,bytes,status,archived from v$log;

                   GROUP#    MEMBERS      BYTES        STATUS           ARCHIVED
                          ---------- ----------            ----------        ----------------     --------
                              1          1                    104857600   INACTIVE             NO
                              2          1                     104857600  CURRENT          NO
                              3          1                     104857600  INACTIVE             NO

 

      6、获取重做日志文件所存放的位置；

      SQL> select *   from v$logfile;

      GROUP# STATUS  TYPE    MEMBER
      -----------        ----- ------ -       -------------------------------------------------------------------------------
         3                        ONLINE            D:\ORACLE\ORADATA\DOG\REDO03.LOG
         2                        ONLINE            D:\ORACLE\ORADATA\DOG\REDO02.LOG
         1      STALE      ONLINE            D:\ORACLE\ORADATA\DOG\REDO01.LOG

       7、获取Oracle数据库中的表空间以及每个表空间的状态 

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

TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS  LOGGING
------------------------------ ---------- --------- --------- ---------
SYSTEM                               4096 ONLINE    PERMANENT LOGGING
UNDOTBS1                             4096 ONLINE    UNDO      LOGGING
TEMP                                 4096 ONLINE    TEMPORARY NOLOGGING
DRSYS                                4096 ONLINE    PERMANENT LOGGING
EXAMPLE                              4096 ONLINE    PERMANENT LOGGING
INDX                                 4096 ONLINE    PERMANENT LOGGING
ODM                                  4096 ONLINE    PERMANENT LOGGING
TOOLS                                4096 ONLINE    PERMANENT LOGGING
USERS                                4096 ONLINE    PERMANENT LOGGING
XDB                                  4096 ONLINE    PERMANENT LOGGING
TEST02                               4096 ONLINE    PERMANENT LOGGING
UNDOTEST                             4096 ONLINE    UNDO      LOGGING
TEMP_TEST                            4096 ONLINE    TEMPORARY NOLOGGING
DATA01                               4096 ONLINE    PERMANENT LOGGING
TEST01                               4096 ONLINE    PERMANENT LOGGING

8、获取表空间存在的位置以及文件名；

 

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

   FILE_ID FILE_NAME                                                                        TABLESPACE_NAME                STATUS         BYTES
---------- -------------------------------------------------------------------------------- ------------------------------ --------- ----------
         1 D:\ORACLE\ORADATA\DOG\SYSTEM01.DBF                                               SYSTEM                         AVAILABLE  346030080
         2 D:\ORACLE\ORADATA\DOG\UNDOTBS01.DBF                                              UNDOTBS1                       AVAILABLE  209715200
         3 D:\ORACLE\ORADATA\DOG\DRSYS01.DBF                                                DRSYS                          AVAILABLE   20971520
         4 D:\ORACLE\ORADATA\DOG\EXAMPLE01.DBF                                              EXAMPLE                        AVAILABLE  152698880
         5 D:\ORACLE\ORADATA\DOG\INDX01.DBF                                                 INDX                           AVAILABLE   26214400
         6 D:\ORACLE\ORADATA\DOG\ODM01.DBF                                                  ODM                            AVAILABLE   20971520
         7 D:\ORACLE\ORADATA\DOG\TOOLS01.DBF                                                TOOLS                          AVAILABLE  104857600
         8 D:\ORACLE\ORADATA\DOG\USERS01.DBF                                                USERS                          AVAILABLE   26214400
         9 D:\ORACLE\ORADATA\DOG\XDB01.DBF                                                  XDB                            AVAILABLE   39976960
        10 D:\ORACLE\ORADATA\DOG\DATA01.DBF                                                 DATA01                         AVAILABLE   10485760
        11 D:\ORACLE\ORADATA\DOG\TEST01.DBF                                                 TEST01                         AVAILABLE   10485760
        12 D:\ORACLE\ORADATA\DOG\UNDOTEST.DBF                                               UNDOTEST                       AVAILABLE   10485760
        13 D:\ORACLE\ORADATA\DOG\TEST02.DBF                                                 TEST02                         AVAILABLE   10485760

  9、获取Oracle数据库系统的用户和创建数据库的日期；

     

SQL> select username,created from dba_users;

USERNAME                       CREATED
------------------------------ -----------
SYS                            2002/05/12
SYSTEM                         2002/05/12
SCOTT                          2002/05/12
YYY                            2011/11/28
ZHANGSAN                       2011/11/25
LMJ                            2011/11/25
DBSNMP                         2002/05/12
OUTLN                          2002/05/12
WMSYS                          2002/05/12
WKSYS                          2002/05/12
ORDSYS                         2002/05/12
ORDPLUGINS                     2002/05/12
MDSYS                          2002/05/12
CTXSYS                         2002/05/12
XDB                            2002/05/12
ANONYMOUS                      2002/05/12
ODM                            2002/05/12
ODM_MTR                        2002/05/12
WKPROXY                        2002/05/12
QS_ADM                         2002/05/12