Know active timezone version

SQL> select * from v$timezone_file;

FILENAME        VERSION
------------ ----------
timezlrg.dat          4

SQL plus environment settings


To know all sql environment values


00:21:35 SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
trimmed.....


To know specific variables value
00:31:42 SQL> sho define
define "&" (hex 26)
00:31:50 SQL>
 


DEFINE: to suppress the definition

SQL> select *  from v$tablespace where name='&tbs_name';
Enter value for tbs_name: USERS
old   1: select *  from v$tablespace where name='&tbs_name'
new   1: select *  from v$tablespace where name='USERS'

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         4 USERS                          YES NO  YES

SQL> set define off
SQL> /

no rows selected

SQL> 1
  1* select *  from v$tablespace where name='&tbs_name'
SQL> /

no rows selected

SQL> set define on
SQL> /
Enter value for tbs_name: USERS
old   1: select *  from v$tablespace where name='&tbs_name'
new   1: select *  from v$tablespace where name='USERS'

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         4 USERS                          YES NO  YES

SQL>


SQLBL: we can enable/disable  blank lines in query

Eg:

SQL> select
  2  name
  3  from
  4  v$database;

NAME
---------
ORCL

SQL> select
  2
SQL> name
SP2-0042: unknown command "name" - rest of line ignored.
SQL>
SQL> from
SP2-0042: unknown command "from" - rest of line ignored.
SQL> v$database;
SP2-0042: unknown command "v$database" - rest of line ignored.
SQL> set sqlbl on
SQL> select
  2
  3  name
  4
  5  from
  6  v$database;

NAME
---------
ORCL

SQL>

• 1st query without blank lines run fines
• 2nd query with blank lines error ed
• 3rd with setting SQLBL on we did even with blank lines 

TERMOUT : to suppress output to screen.

when during a big select ,deployment just spool , set termout off and execute the output will not be shown in screen.

00:31:50 SQL> sho termout
termout ON
00:32:27 SQL>
 
00:20:51 SQL> @a

  COUNT(*)
----------
     72620

Elapsed: 00:00:00.08
00:21:03 SQL> set termout off
00:21:18 SQL> @a
00:21:21 SQL> set termout on
00:21:33 SQL> @a

  COUNT(*)
----------
     72620

Elapsed: 00:00:00.08

 

Know ORACLE_HOME from Sqlplus

exec this as sysdba..

9i and above:
SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries WHERE library_name='DBMS_SUMADV_LIB';

10g and above:
var OHM varchar2(100);
EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
PRINT OHM