Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: alter database command

    Setup: 9i

    My assumption is that my server TZ is set wrong.

    FILLER SQL> select dbtimezone from dual;

    DBTIME
    ------
    -07:00

    I am the US/Central timezone so I would expect this value to be -06:00 or -05:00 (Daylight time).

    FILLER SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    ----------------------------------------------
    -05:00

    As you can see my session time zone is correct (pre-daylight it is -06:00)

    Anyway:
    I used Enterprise Manager to create this data base if that is helpful.

    I get an error when I issue the alter database command

    FILLER SQL> alter database set time_zone = 'US/CENTRAL';
    alter database set time_zone = 'US/CENTRAL'
    *
    ERROR at line 1:
    ORA-02231: missing or invalid option to ALTER DATABASE

    OR

    FILLER SQL> alter database set time_zone = '-05:00';
    alter database set time_zone = '-05:00'
    *
    ERROR at line 1:
    ORA-02231: missing or invalid option to ALTER DATABASE

    Can anyone tell might what is wrong?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    strange.
    Can you alter session set time_zone?
    Code:
    platform@kod1> alter session SET TIME_ZONE = 'US/CENTRAL';
    
    Session altered.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Apparently there is an issue with this command if there are any columns in the database which are of type TIMESTAMP WITH LOCAL TIME ZONE

    Check out this:

    http://www.orafaq.net/archive/comp.d...3/20/95216.htm

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I did not create any. All the date fields I created where just plain DATE. From my readings of the applicable doc I have gathered that the default for DATE columns is still old fashioned date with NO timezone.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    Did you run the query?
    I didn't think there were any in my database but I found one when I ran the query.


    select u.name || '.' || o.name || '.' || c.name TSWLTZ_column
    from sys.obj$ o, sys.col$ c, sys.user$ u
    where c.type# = 231
    and o.obj# = c.obj#
    and u.user# = o.owner#;

    Might be worth a try.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    see my response in your other thread
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Sorry hit the button before I was done. I will check into the example tablespace for the offending column(s).

    The offending schema.table.column is oe.orders.order_date in case you are interested.

    Thanks.

    BTW: Anacedent found a way to get the TZ abbreviation.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •