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