联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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