分类目录归档：数据库

1、dba_registry视图

SQL> set line 200
SQL> col comp_name for a35 
SQL> col version for a12
SQL> col status for a6
SQL> select comp_name, version, status from dba_registry;

COMP_NAME                           VERSION      STATUS
----------------------------------- ------------ ------
Spatial                             10.2.0.5.0   VALID
Oracle interMedia                   10.2.0.5.0   VALID
OLAP Catalog                        10.2.0.5.0   VALID
Oracle Enterprise Manager           10.2.0.5.0   VALID
Oracle XML Database                 10.2.0.5.0   VALID
Oracle Text                         10.2.0.5.0   VALID
Oracle Expression Filter            10.2.0.5.0   VALID
Oracle Rule Manager                 10.2.0.5.0   VALID
Oracle Workspace Manager            10.2.0.5.0   VALID
Oracle Data Mining                  10.2.0.5.0   VALID
Oracle Database Catalog Views       10.2.0.5.0   VALID
Oracle Database Packages and Types  10.2.0.5.0   VALID
JServer JAVA Virtual Machine        10.2.0.5.0   VALID
Oracle XDK                          10.2.0.5.0   VALID
Oracle Database Java Packages       10.2.0.5.0   VALID
OLAP Analytic Workspace             10.2.0.5.0   VALID
Oracle OLAP API                     10.2.0.5.0   VALID

17 rows selected.

SQL> select dbms_metadata.get_ddl('VIEW','DBA_REGISTRY','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY" ("COMP_ID", "COMP_NAME", "VERSION", "STATUS", "MODIFIED", "NAMESPAC
E", "CONTROL", "SCHEMA", "PROCEDURE", "STARTUP", "PARENT_ID", "OTHER_SCHEMAS") A
S
  SELECT r.cid, r.cname, r.version,
       SUBSTR(dbms_registry.status_name(r.status),1,11),
       TO_CHAR(r.modified,'DD-MON-YYYY HH24:MI:SS'),
       r.namespace, i.name, s.name, r.vproc,
       DECODE(bitand(r.flags,1),1,'REQUIRED',NULL), r.pid,
       dbms_registry.schema_list_string(r.cid)
FROM registry$ r, user$ s, user$ i
WHERE r.schema# = s.user# AND r.invoker#=i.user#

SQL> DESC registry$
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CID                                                   NOT NULL VARCHAR2(30)
 CNAME                                                          VARCHAR2(255)
 SCHEMA#                                               NOT NULL NUMBER
 INVOKER#                                              NOT NULL NUMBER
 VERSION                                                        VARCHAR2(30)
 STATUS                                                NOT NULL NUMBER
 FLAGS                                                 NOT NULL NUMBER
 MODIFIED                                                       DATE
 PID                                                            VARCHAR2(30)
 BANNER                                                         VARCHAR2(80)
 VPROC                                                          VARCHAR2(61)
 DATE_INVALID                                                   DATE
 DATE_VALID                                                     DATE
 DATE_LOADING                                                   DATE
 DATE_LOADED                                                    DATE
 DATE_UPGRADING                                                 DATE
 DATE_UPGRADED                                                  DATE
 DATE_DOWNGRADING                                               DATE
 DATE_DOWNGRADED                                                DATE
 DATE_REMOVING                                                  DATE
 DATE_REMOVED                                                   DATE
 NAMESPACE                                             NOT NULL VARCHAR2(30)
 ORG_VERSION                                                    VARCHAR2(30)
 PRV_VERSION                                                    VARCHAR2(30)

SQL> SELECT BANNER,VERSION,modified,prv_version FROM SYS.registry$;

BANNER                                                                           VERSION      MODIFIED            PRV_VERSION
-------------------------------------------------------------------------------- ------------ ------------------- ----------------
Oracle Database Catalog Views Release 10.2.0.5.0 - 64bi                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Packages and Types Release 10.2.0.5.0 - Production               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Workspace Manager Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.3
JServer JAVA Virtual Machine Release 10.2.0.5.0 - Production                     10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XDK Release 10.2.0.5.0 - Production                                       10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Java Packages Release 10.2.0.5.0 - Production                    10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Expression Filter Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Data Mining Release 10.2.0.5.0 - Production                               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Text Release 10.2.0.5.0 - Production                                      10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XML Database Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Rule Manager Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle interMedia Release 10.2.0.5.0 - Production                                10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
OLAP Analytic Workspace Release 10.2.0.5.0 - Production                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle OLAP API Release 10.2.0.5.0 - Production                                  10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
OLAP Catalog Release 10.2.0.5.0 - Production                                     10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Spatial Release 10.2.0.5.0 - Production                                          10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Oracle Enterprise Manager Release 10.2.0.5.0 - Production                        10.2.0.5.0   2011-11-02 17:23:47 10.2.0.4.0

17 rows selected.

2、v$version或者PRODUCT_COMPONENT_VERSION视图

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> col product for a35
SQL> col product for a40
SQL> select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION      STATUS
---------------------------------------- ------------ --------------
NLSRTL                                   10.2.0.5.0   Production
Oracle Database 10g Enterprise Edition   10.2.0.5.0   64bi
PL/SQL                                   10.2.0.5.0   Production
TNS for Linux:                           10.2.0.5.0   Production

SQL> set long 1000
SQL> set pages 0
SQL> select dbms_metadata.get_ddl('VIEW','PRODUCT_COMPONENT_VERSION','SYS') FROM DUAL;

CREATE OR REPLACE FORCE VIEW "SYS"."PRODUCT_COMPONENT_VERSION" ("PRODUCT", "VERSION", "STATUS") AS
(select
substr(banner,1, instr(banner,'Version')-1),
substr(banner, instr(banner,'Version')+8,
instr(banner,' - ')-(instr(banner,'Version')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Version') > 0
and
((instr(banner,'Version') <   instr(banner,'Release')) or
instr(banner,'Release') = 0))
union
(select
substr(banner,1, instr(banner,'Release')-1),
substr(banner, instr(banner,'Release')+8,
instr(banner,' - ')-(instr(banner,'Release')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Release') > 0
and
instr(banner,'Release') <   instr(banner,' - '))


SQL> COL object_name for a20
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V$VERSION';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
PUBLIC                         V$VERSION            SYNONYM

SQL> SELECT TABLE_OWNER,TABLE_NAME FROM dba_synonyms a WHERE a.synonym_name='V$VERSION';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$VERSION

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V_$VERSION';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            V_$VERSION           VIEW

SQL> select dbms_metadata.get_ddl('VIEW','V_$VERSION','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','V_$VERSION','SYS')
-----------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$VERSION" ("BANNER") AS
  select "BANNER" from v$version

SQL> select * from v$fixed_table where name LIKE '%V%VERSION%';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
GV$VERSION                     4294951314 VIEW       65537
V$VERSION                      4294951045 VIEW       65537

SQL>  COL VIEW_DEFINITION FOR A80
SQL> select * from v$fixed_view_definition where view_name='V$VERSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ ------------------------------------------------------------------
V$VERSION                      select  BANNER from GV$VERSION where inst_id = USERENV('Instance')

SQL> select * from v$fixed_view_definition where view_name='GV$VERSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ -------------------------------------------------------
GV$VERSION                     select inst_id, banner from x$version

SQL> DESC x$version
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 BANNER                                                         VARCHAR2(64)

SQL> SET LINE 200
SQL> SELECT * FROM x$version;

ADDR                   INDX    INST_ID BANNER
---------------- ---------- ---------- ----------------------------------------------------------------
00002AB64240D028          0          1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
00002AB64240D028          1          1 PL/SQL Release 10.2.0.5.0 - Production
00002AB64240D028          2          1 CORE     10.2.0.5.0      Production
00002AB64240D028          3          1 TNS for Linux: Version 10.2.0.5.0 - Production
00002AB64240D028          4          1 NLSRTL Version 10.2.0.5.0 - Production

3、查看v$instance视图

SQL> select version from v$instance;

VERSION
------------
10.2.0.5.0

--通过同v$version同样操作，得出如下语句
 SELECT KS.INST_ID,
        KSUXSINS,
        KSUXSSID,
        KSUXSHST,
        KSUXSVER,
        KSUXSTIM,
        DECODE(KSUXSSTS,
               0,
               'STARTED',
               1,
               'MOUNTED',
               2,
               'OPEN',
               3,
               'OPEN MIGRATE',
               'UNKNOWN'),
        DECODE(KSUXSSHR, 0, 'NO', 1, 'YES', 2, NULL),
        KSUXSTHR,
        DECODE(KSUXSARC, 0, 'STOPPED', 1, 'STARTED', 'FAILED'),
        DECODE(KSUXSLSW,
               0,
               NULL,
               2,
               'ARCHIVE LOG',
               3,
               'CLEAR LOG',
               4,
               'CHECKPOINT',
               5,
               'REDO GENERATION'),
        DECODE(KSUXSDBA, 0, 'ALLOWED', 'RESTRICTED'),
        DECODE(KSUXSSHP, 0, 'NO', 'YES'),
        DECODE(KVITVAL,
               0,
               'ACTIVE',
               2147483647,
               'SUSPENDED',
               'INSTANCE RECOVERY'),
        DECODE(KSUXSROL,
               1,
               'PRIMARY_INSTANCE',
               2,
               'SECONDARY_INSTANCE',
               'UNKNOWN'),
        DECODE(QUI_STATE,
               0,
               'NORMAL',
               1,
               'QUIESCING',
               2,
               'QUIESCED',
               'UNKNOWN'),
        DECODE(BITAND(KSUXSDST, 1), 0, 'NO', 1, 'YES', 'NO')
   FROM X$KSUXSINST KS, X$KVIT KV, X$QUIESCE QU
  WHERE KVITTAG = 'kcbwst';

SQL> set line 90
SQL> desc X$KSUXSINST;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 KSUXSINS                                                       NUMBER
 KSUXSSID                                                       VARCHAR2(16)
 KSUXSHST                                                       VARCHAR2(64)
 KSUXSVER                                                       VARCHAR2(17)
 KSUXSTIM                                                       DATE
 KSUXSSTS                                                       NUMBER
 KSUXSSHR                                                       NUMBER
 KSUXSTHR                                                       NUMBER
 KSUXSARC                                                       NUMBER
 KSUXSLSW                                                       NUMBER
 KSUXSDBA                                                       NUMBER
 KSUXSSHP                                                       NUMBER
 KSUXSSCN                                                       VARCHAR2(16)
 KSUXSROL                                                       NUMBER
 KSUXSDST                                                       NUMBER

SQL> SELECT  KSUXSVER FROM SYS.X$KSUXSINST;

KSUXSVER
-----------------
10.2.0.5.0