编程知识 cdmana.com

Oracle modify time zone

Oracle Change the time zone

 

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

SQL> select sessiontimezone from dual;

 

SESSIONTIMEZONE

---------------------------------------------------------------------------

+08:00

 

The conversation time zone is the 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

 

 

2. 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 time zone dependent tables

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 the time zone dependency 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,2)

 SALES_REP_ID                                       NUMBER(6)

 PROMOTION_ID                                       NUMBER(6)

 

4. View time zone dependency 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. Processing time zone dependency 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) Process the original table oe.orders Column in order_date

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 there are still columns of the above types

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. Change the time zone , But the query is still not effective

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

 

Database altered.

 

SQL> select dbtimezone from dual;

 

DBTIME

------

+00:00

 

7. Restart the database , Time zone effective

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 global business , Business must operate properly between multiple time zones . from 9i Version start ,Oracle The environment can know the time zone used . In order to achieve this function , You need to specify the time zone in which the database is running and the usage TIMESTAMP WITH TIME ZONE And TIMESTAMP WITH LOCAL TIME ZONE data type . The former has a time zone indicator , This indicator indicates the time zone it refers to . The latter data type is normalized to the database time zone when stored , But then it will be converted to the time zone of the client when retrieving . ordinary DATE and TIMESTAMP The data type is always normalized to the database time zone when stored , And it will be displayed as it is in the query process .

  About timestamp Several functions of :

 sysdate      Database server operating system time , Display without time zone ( In fact, it implies the time zone ).

 systimestamp Database server operating system time and time zone

  Be careful : The return values of the above two functions are not affected by the client .

 

 localtimestamp      Convert to the current time of the client according to the time zone of the client , But the display does not contain the time zone

 current_timestamp    Convert to the current time of the client according to the time zone of the client , Contains the client time zone

  Be careful : The return values of the above two functions are related to the time zone setting of the client , Will be converted to the time in the client's time zone .

版权声明
本文为[wx5caecf2ed0645]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/08/20210809181600667e.html

Scroll to Top