How to determine Oracle Database And OS version

The following query determine you the database version

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production

Alternatively, you can query from product_component_version.

SQL> col product for a40
SQL> col version for a11
SQL> col status for a15
SQL> select * from product_component_version;
PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- ---------------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
TNS for 32-bit Windows:                  10.2.0.1.0  Production

By using export import data pump command you can determine client version

C:\>expdp help=y
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:33:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.

C:\>impdp help=y
Import: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:32:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Both in case of Data Pump Export and Import first few digits show it version.

How to determine database compatibility level

COMPATIBILITY allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.
In order to determine your current database compatibility level you can query from view database_compatible_level.

SQL> col value for a11
SQL> col description for a50
SQL> select * from database_compatible_level;
VALUE          DESCRIPTION
-----------    -----------------------------------------------------------------
10.2.0.1.0     Database will be completely compatible with this software version

Alternatively you can check your initialization parameter COMPATIBLE setting.

SQL> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
compatible                           string      10.2.0.1.0

You can also query from v$parameter.

SQL> select value from v$parameter where name='compatible';

VALUE
-----------
10.2.0.1.0

Listener Version

$ lsnrctl version

Version of OPATCH

$ perl $ORACLE_HOME/OPatch/opatch.pl version

Sun Solaris Version

$ cat /etc/release

RedHat Linux Version

vi /etc/redhat-release

Perl Version

$ perl -v
Or
$ perl -version

Java Version

$ java -version

Version of Installed packages on Solaris

$ pkginfo -igrep perl

Version of Installed packages on Linux

$ rpm -qagrep

Kernel Version of Unix

$ uname -a

Bit of Operating System

$ isainfo -b

Bit of your Oracle Software

To check if your Oracle Binary is 32 bit or 64 bit you can use the file command on any of the oracle executable like

$ file $ORACLE_HOME/bin/oracle

Finding Oracle data block size

SQL> SHOW PARAMETER DB_BLOCK_SIZE

Finding O/S block size

df -g grep "block size" (in Solrieas)

Check my Operating System 64-bit?

Solaris

$/usr/bin/isainfo –kv

Linux

$uname -m