1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Date and Time Queries

Discussion in 'SQL PL/SQL' started by Arju, Oct 11, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Some Time Queries on Oracle

    To get timing according with seconds and minutes I set nls_date_format.
    Code (Text):
    SQL> alter session set nls_date_format='DD MONTH YYYY HH24:MI:SS';

    Session altered.
     
    1)In order to know when database was created issue,

    Code (Text):
    SQL> select created from v$database;

    CREATED
    --------------------------
    14 AUGUST    2008 00:04:50
    2)In order to know when the instance was started issue,
    Code (Text):
    SQL> select startup_time from v$instance;

    STARTUP_TIME
    --------------------------
    11 OCTOBER   2008 11:41:02
    3)In order to a particular user named ARJU's account creation time and password change time issue,
    Code (Text):
    SQL>  select ctime, ptime from sys.user$ where name='ARJU';

    CTIME                             PTIME
    -------------------------- --------------------------
    22 AUGUST    2008 19:02:55 11 OCTOBER   2008 18:58:51