How to find database related version info

1 How to find the version of the Export and Import utilities.

The version of the Export and Import utilities can be obtained by invoking the utilities with the parameter HELP=Y. The banner of the help page shows the version of the utility. For example:

> exp HELP=Y
Export: Release 8.1.7.4.1 - ... (other output)

> imp HELP=Y
Import: Release 9.2.0.8.0 - ... (other output)

> expdp HELP=Y
Export: Release 10.1.0.5.0 - ... (other output)

> impdp HELP=Y
Import: Release 10.2.0.3.0 - ... (other output)

Alternatively you could invoke the utility in interactive mode (just enter: 'exp' or 'imp' or 'expdp' or 'impdp') in order to obtain the banner, and cancel the program with CTRL-C (on Windows) or CTRL-D (on Unix).


2 How to find the version of the database.

The easiest way to determine the database version (including the main patch-set release which has been installed on the database) is to perform the following query in SQL*Plus:

SELECT * FROM v$version;

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



The output shows the edition (Enterprise), the version of the database (Oracle10g), the release with the installed patch-set (10.2.0.3.0), and the 32-bit/64-bit version (64-bit).

In general, if Oracle is a 64bit version, the word '64bit' will be displayed in the connection information of the opening banners of the Oracle executables such as SQL*Plus. It will also be displayed in the headers of Oracle trace files. Otherwise if the word '64bit' is not displayed, it can be assumed that Oracle is a 32bit version.

Alternatively, when starting SQL*Plus and connecting to the database, the connect message also displays the version of the database:

> sqlplus system/manager

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 31 09:27:19 2007
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Subsequent cumulative patch-sets installations, such as the 10.2.0.3.8 patch-set bundle for the Windows platform, will not always be shown in the banner. Starting with Oracle10g Release 2 (10.2.0.x) the patchset and installed Critical Patch Update information can also be examined inside the database, e.g.:

SET lines 100 numwidth 12 pages 100
COL action_time FOR a30
COL action FOR a12
COL version LIKE action
COL comments FOR a30

SELECT action_time, action, version, id, comments
  FROM dba_registry_history
 ORDER BY action_time;

ACTION_TIME                   ACTION   VERSION           ID COMMENTS
----------------------------- -------- ----------- -------- --------------------------
15-MAR-06 03.01.00.987490 AM  UPGRADE  10.2.0.2.0           Upgraded from 10.2.0.1.0
18-JAN-07 02.10.05.942649 PM  UPGRADE  10.2.0.3.0           Upgraded from 10.2.0.2.0
18-MAR-07 04.11.23.119000 PM  CPU      10.2.0.3.0   5846376 Patch2

3 How to Find the Database Characterset
Check the view NLS_DATABASE_PARAMETERS.
You can find out the database characterset with the following query in SQL*Plus:

COL value FOR a20
SELECT * FROM nls_database_parameters
 WHERE parameter LIKE '%SET' 
 ORDER BY 1;  

4 How to find the version of the Operating System

4.1. Which Unix version is installed.
   --------------------------------
   The Unix command 'uname -a' (generally located in /usr/bin) is available on
   most Unix platforms and is usually sufficient to retrieve basic information
   about the Operating System version. Examples:

     HP-Unix / Sun            : /usr/bin/uname -a
     Linux                    : /bin/uname -a
     Silicon Graphics SGI     : /bin/uname -aR
     IBM-RS6000 AIX           : /usr/bin/oslevel
     Digtial Unix/Compaq Tru64: /usr/sbin/sizer -v

   To determine if your Operating System in 32-bit or 64-bit, check the 
   following:

     HP-Unix (11+)            : /usr/bin/getconf KERNEL_BITS
     Sun (7+)                 : /bin/isainfo -v
     Linux                    : more /proc/cpuinfo   See also:
                                Note:269976.1 "Linux: checking 64-bit Info"
     Silicon Graphics SGI     : only available in 64-bit
     IBM-RS6000 AIX           : /bin/lslpp -l bos.64bit
     Digtial Unix/Compaq Tru64: only available in 64-bit

5 Check the version and status of Data Dictionary Components

Enter in SQL*Plus:

SET lines 80 
COL version FOR A10 
COL comp_id FOR A8 
COL schema LIKE comp_id 
COL comp_name FOR A35 
COL status FOR A12 
SELECT comp_id, schema, status, version, comp_name 
  FROM dba_registry 
 ORDER BY 1; 




References:
1 Export-Import iSR - How to Find the Database and Export/Import Version [ID 175627.1]
2 Export-Import iSR - How to Find the Database Characterset [ID 175630.1]
3 Export-Import iSR - How to Find the Operating System Version and if 32 or 64 Bit [ID 175628.1]
4 Export-Import iSR - How to Find the Version of the Data Dictionary Components [ID 175632.1]