glufke.net

Como descobrir a versão do Oracle

22/08/2007

Existem várias formas de descobrir a versão do Oracle sem olhar a “telinha inicial” do SQL*Plus. Neste artigo, debateremos 4 maneiras de se descobrir isso. Algumas são bem conhecidas e outra bem “raras” de se ver.

Usar a V$Version

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
PL/SQL Release 9.0.1.4.0 - Production
CORE    9.0.1.2.0       Production
TNS for Linux: Version 9.0.1.4.0 - Production
NLSRTL Version 9.0.1.4.0 - Production

5 rows selected.

SQL que mostra versão

SQL> select trunc( (&_O_RELEASE/100000000)) || '.' ||
  2    trunc( mod((&_O_RELEASE/1000000),100)) || '.' ||
  3    trunc( mod((&_O_RELEASE/10000),100)) || '.' ||
  4    trunc( mod((&_O_RELEASE/100),100))
  5    from dual;

TRUNC((
-------
9.0.1.4

1 row selected.

SQL>

Mais um script interessante

rem -----------------------------------------------------------------------
rem Filename:   ver.sql
rem Purpose:    Show database version with options intalled
rem             (handy for your HELP/ABOUT menu)
rem Date:       12-Nov-1999
rem Author:     Frank Naude (frank@ibi.co.za)
rem -----------------------------------------------------------------------

set head off feed off pages 0 serveroutput on

col banner format a72 wrap

select banner
from   sys.v_$version;

select '   With the '||parameter||' option'
from   sys.v_$option
where  value = 'TRUE';

select '   The '||parameter||' option is not installed'
from   sys.v_$option
where  value <> 'TRUE';

begin
    dbms_output.put_line('Port String: '||dbms_utility.port_string);
end;
/

Este script gera uma saída como essa:

SQL> @ver
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
   With the Partitioning option
   With the Objects option
   With the Advanced replication option
   With the Bit-mapped indexes option
   With the Connection multiplexing option
   With the Connection pooling option
   With the Database queuing option
   With the Incremental backup and recovery option
   With the Instead-of triggers option
   With the Parallel backup and recovery option
   With the Parallel execution option
   With the Parallel load option
   With the Point-in-time tablespace recovery option
   With the Fine-grained access control option
   With the Proxy authentication/authorization option
   With the Change Data Capture option
   With the Plan Stability option
   With the Online Index Build option
   With the Coalesce Index option
   With the Managed Standby option
   With the Materialized view rewrite option
   With the Materialized view warehouse refresh option
   With the Database resource manager option
   With the Spatial option
   With the Visual Information Retrieval option
   With the Export transportable tablespaces option
   With the Transparent Application Failover option
   With the Fast-Start Fault Recovery option
   With the Sample Scan option
   With the Duplexed backups option
   With the Java option
   With the OLAP Window Functions option
   With the Block Media Recovery option
   With the Fine-grained Auditing option
   With the Application Role option
   With the Enterprise User Security option
   With the Oracle Data Guard option
   With the OLAP option
   With the Heap segment compression option
   With the Join index option
   With the Trial Recovery option
   With the Oracle Data Mining option
   With the Online Redefinition option
   With the Streams option
   With the File Mapping option
   The Real Application Clusters option is not installed
   The Oracle Label Security option is not installed
Port String: IBMPC/WIN_NT-8.1.0
SQL>

Usar a DBMS_UTILITY

SQL> DESC DBMS_OUTPUT

PROCEDURE DB_VERSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VERSION                        VARCHAR2                OUT
 COMPATIBILITY                  VARCHAR2                OUT

SQL>
SQL>
SQL> declare
  2  a1 varchar2(1000);
  3  a2 varchar2(1000);
  4  begin
  5    dbms_utility.db_version(a1, a2);
  6    dbms_output.put_line('VERSAO: '||A1||'   COMPATIBILTY: '||A2);
  7  end;
  8  /
VERSAO: 9.2.0.5.0   COMPATIBILTY: 9.2.0.0.0
SQL> 

Comente aqui

Tags: DBA, SQL, Utilidade