Oracle 11g: Survive Commands.

Check user status:
SELECT username,account_status from dba_users where username='STRMADMIN';


Check DB TimeZone:
select DBTIMEZONE from dual;

Check DB status:
select * from v$instance;

time zones without repeating the time zone name for each abbreviation

SELECT UNIQUE TZNAME
FROM V$TIMEZONE_NAMES;

Converting a Datetime Value to Another Time Zone

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'America/Los_Angeles' "West Coast Time" FROM DUAL;

 list of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view
 select TZNAME, TZABBREV from V$TIMEZONE_NAMES;

Determine DB Unique Name:
SQL> SHOW PARAMETER db_unique_name;

SQL > var ORACLEHOME varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :ORACLEHOME) ;
SQL > PRINT ORACLEHOME

List all tablespace names
SQL> select tablespace_name from dba_tablespaces;

 SQL> select username from dba_users;
Will give you list of all users but all users may not have created objects in it.So we can not call them as schema.
SQL> select distinct owner from dba_objects;
Will give you the list of schemas available.

 Show current database: SELECT * FROM global_name;

Determine default table space for database:
select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

Determine default table space for user:
 SQL> select default_tablespace from dba_users where username = 'STRMADMIN';

DEFAULT_TABLESPACE
------------------------------
USERSPACE

Reference:
 http://www.orafaq.com/wiki/SQL*Plus_FAQ#What_is_the_difference_between_.26_and_.26.26.3F

https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia
http://www.oracle-scripts.net/?p=281