How to find out the Oracle version

Scripts Diversos para o Oracle SQL*Plus. (Relacionado a ferramenta Oracle SQL*Plus, para questões de SQL, usar o forum SQL)
Post Reply
User avatar
tfg
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 83
Joined: Mon, 03 May 2004 3:38 pm
Location: Novo Hamburgo - RS

Hello everyone ...

Can you find the Oracle version without looking at the SQL * Plus home screen?
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Of course!
See this SELECT:

Select all

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.
You can also use the following SELECT:

Select all

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> 
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Another interesting script:

Select all

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; 
/ 
Example:

Select all

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> 
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Another way:: -The: -O: -O

Select all

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>  
Carlos Sérgio
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Tue, 27 Dec 2005 10:16 am
Location: SÃO PAULO

Thank you for strength!

Thanks!

ABR!
JOPA
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 58
Joined: Fri, 30 Oct 2009 9:52 am
Location: Salvador - BA
Contact:
Atenciosamente,

João Paulo A. C. do Bomfim

"Deu certo? Deixa! Funcionou? Não mexa!"

Useful.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests