Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as sys
// View the database Time Zone
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+ 00: 00
// View the current time and time zone
SQL> select distinct imestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
-11 02.39.49.421000 PM +
// I am clearly in the east 8 zone. How can I display the database time zone as 0?
SQL> alter database set time_zone = '+ 8:00 ';
ORA-30079: cannot alter database timezone when database has timestamp with local time zone columns
// From the error message, we can see that the data type of columns in some tables in the database is timestamp with local time zone.
// We have to delete these columns before we can change them. Next we will look for these classes.
SQL> select u. name | '.' | o. name | '.' | c. name tsltz_column
2 from sys. obj $ o, sys. col $ c, sys. user $ u
3 where c. type #= 231 and
4 o. obj # = c. obj # and
5 u. user # = o. owner #;
TSLTZ_COLUMN
--------------------------------------------------------------------------------
OE. ORDERS. ORDER_DATE
// We found the order_date column in the orders table under the oe user.
SQL> desc oe. orders;
Name Type Nullable Default Comments
-----------------------------------------------------------------------------------------------------------------------
ORDER_ID NUMBER (12) primary key column.
ORDER_DATE TIMESTAMP (6) with local time zone timestamp with local time zone column, not null constraint.
ORDER_MODE VARCHAR2 (8) y check constraint.
CUSTOMER_ID NUMBER (6)
ORDER_STATUS NUMBER (2) Y 0: Not fully entered, 1: Entered, 2: Canceled-bad credit ,-
3: Canceled-by customer, 4: Shipped-whole order ,-
5: Shipped-replacement items, 6: Shipped-backlog on items ,-
7: Shipped-special delivery, 8: Shipped-billed, 9: Shipped-payment plan ,-
10: Shipped-paid
ORDER_TOTAL NUMBER (8, 2) y check constraint.
SALES_REP_ID NUMBER (6) Y References hr. employees. employee_id.
PROMOTION_ID NUMBER (6) Y Sales promotion ID. Used in SH schema
// Delete it
SQL> alter table oe. orders drop column order_date;
Table altered
// So that we can modify the time zone
SQL> alter database set time_zone = '+ 8:00 ';
Database altered
// Close the database
// SHUTDOWN is not an SQL command, it is an SQL * Plus command.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
// Start the database
SQL> startup;
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 7877988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
// View the time zone
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+ 08:00