Shulou(Shulou.com)05/31 Report--

This article mainly shows you "Oracle how to modify the time zone", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to modify the time zone Oracle" this article.

Oracle modifies time zone

1. First, confirm whether the session time zone is correct. The session time zone may not be consistent with the database time zone.

SQL > select sessiontimezone from dual

SESSIONTIMEZONE

+ 08:00

Conversation time zone is Beijing time zone

SQL > select dbtimezone from dual

DBTIME

-

+ 00:00

DB is the world time zone

SQL > select tz_offset (sessiontimezone), tz_offset (dbtimezone) from dual

TZ_OFFS TZ_OFFS

--

+ 08:00 + 00:00

two。 Check whether the database has such a field type TIMESTAMP WITH LOCAL TIME ZONE

SQL > select c.owner | |'. | | c.table_name |'('| | c.column_name | |') -'

| | c.data_type | |''col |

From dba_tab_cols c, dba_objects o

Where c.data_type like'% WITH LOCAL TIME ZONE'

And c.owner=o.owner

And c.table_name = o.object_name

And o.object_type = 'TABLE'

Order by col

/

COL

OE.ORDERS (ORDER_DATE)-TIMESTAMP (6) WITH LOCAL TIME ZONE

-- View the tables on which the time zone depends

SQL > select u.name | |'. | | o.name | |'. | | c.name TSLTZcolumn

From sys.obj$ o, sys.col$ c, sys.user$ u

Where c.type# = 231

And o.obj# = c.obj#

And u.user# = o.owner#

TSLTZCOLUMN

OE.ORDERS.ORDER_DATE

3. View time zone dependent table structure

SQL > desc oe.orders

Name Null? Type

-

ORDER_ID NOT NULL NUMBER (12)

ORDER_DATE NOT NULL TIMESTAMP (6) WITH LOCAL TIME

ZONE

ORDER_MODE VARCHAR2 (8)

CUSTOMER_ID NOT NULL NUMBER (6)

ORDER_STATUS NUMBER (2)

ORDER_TOTAL NUMBER (8 dint 2)

SALES_REP_ID NUMBER (6)

PROMOTION_ID NUMBER (6)

4. View time zone dependent table data

SQL > select ORDER_DATE from oe.orders

ORDER_DATE

21-MAR-04 08.18.21.862632 AM

09-JAN-06 12.19.44.123456 PM

09-JAN-06 01.34.13.112233 PM

27-JAN-06 01.22.51.962632 AM

02-FEB-06 05.34.56.345678 PM

03-FEB-06 12.19.11.227550 PM

28-FEB-06 09.03.03.828330 AM

30-MAR-06 02.22.09.509801 AM

30-MAR-06 05.34.50.545196 AM

28-JUL-06 02.22.59.662632 AM

28-JUL-06 03.34.16.562632 AM

..

28-JUN-08 11.53.32.335522 AM

15-JUL-08 08.18.23.234567 AM

27-JUL-08 10.59.10.223344 PM

02-AUG-08 01.22.48.734526 AM

105 rows selected.

5. Working with time zone dependent tables

(1) create temporary tables for backup

SQL > create table oe.test1 (order_id number,order_date date)

Table created.

SQL > insert into oe.test1 (order_id,order_date) select order_id,order_date from oe.orders

105 rows created.

SQL > commit

Commit complete.

(2) deal with the column order_date in the original table oe.orders

SQL > alter table oe.orders drop column order_date

Table altered.

SQL > alter table oe.orders add order_date date

Table altered.

SQL > update oe.orders a set order_date= (select order_date from oe.test1 b where a.order_id=b.order_id)

105 rows updated.

SQL > commit

Commit complete.

(3) query again whether the columns of the above types still exist

SQL > select c.owner | |'. | | c.table_name |'('| | c.column_name | |') -'

| | c.data_type | |''col |

From dba_tab_cols c, dba_objects o

Where c.data_type like'% WITH LOCAL TIME ZONE'

And c.owner=o.owner

And c.table_name = o.object_name

And o.object_type = 'TABLE'

Order by col

/

No rows selected

SQL > select u.name | |'. | | o.name | |'. | | c.name TSLTZcolumn

From sys.obj$ o, sys.col$ c, sys.user$ u

Where c.type# = 231

And o.obj# = c.obj#

And u.user# = o.owner#

No rows selected

6. Modify the time zone, but the query still does not take effect

SQL > alter database set time_zone='+8:00'

Database altered.

SQL > select dbtimezone from dual

DBTIME

-

+ 00:00

7. Restart the database and the time zone takes effect

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

SQL >

SQL > select dbtimezone from dual

DBTIME

-

+ 08:00

SQL > select tz_offset (sessiontimezone), tz_offset (dbtimezone) from dual

TZ_OFFS TZ_OFFS

--

+ 08:00 + 08:00

8. Delete temporary table

SQL > drop table oe.test1 purge

Table dropped.

In addition:

For a globalized business, the business must function properly between multiple time zones. Starting with version 9i, the Oracle environment is able to know the time zone used. To achieve this, you need to specify the time zone in which the database is running and use the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types. The former has a time zone indicator that indicates the time zone it refers to. The latter data type is normalized to the database time zone when stored, but then converted to the client time zone when retrieved. Normal DATE and TIMESTAMP data types are always normalized to the database time zone when stored and are displayed as is during the query process.

Several functions about timestamp:

Sysdate database server operating system time, showing no time zone (actually implied time zone).

Systimestamp database server operating system time and time zone

Note: the return values of the above two functions are not affected by the client.

Localtimestamp converts to client current time based on client time zone, but the display does not include time zone

Current_timestamp converts to client current time based on client time zone, including client time zone

Note: the return values of the above two functions are related to the client time zone setting and will be converted to the client time zone.

The above is all the content of the article "how to modify the time zone in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.