Oracle enhanced date functions

Oracle enhanced date functions

-- Change the database time zone to UTC + 8

Alter database set TIME_ZONE = '+ 08:00 ';

/*

An error may occur during the modification: ORA-30079: the database time zone cannot be changed when the database has a timestamp with local timezone Column

This is because columns exist in existing tables in the current database. The column type is defined as timestamp with local time zone.

Therefore, the database cannot modify the database time zone.

Next, you need to use the statement to find out which table columns cause this problem. The query statement is as follows:

**/

-- Check whether any field that affects the time zone modification exists

SELECT u. name | '.' | o. name | '.' | c. name TSLTZcolumn

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

WHERE c. type #= 231

AND o. obj # = c. obj #

AND u. user # = o. owner #;

-- Delete the table where the affected field is located

Drop table oe. ORDERS;

-- If a foreign key reference is prompted, you can change the data type to TIMESTAMP (6) after the data in the column is clear)

Alter table oe. orders MODIFY oe. orders. order_date (TIMESTAMP (6 ));

-- Clear the recycle bin.

Purge recyclebin;

-- Run the SQL plus command window to restart the database.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

-- Reconnect to the session to check whether the database time zone can be changed

Alter databasese TTIME_ZONE = '+ 08:00 ';

-- After the modification is successful, check whether it is in the GMT +

Select dbtimezone from dual;

-- Modify the time zone of this session in four ways

SQL> ALTER SESSION SET TIME_ZONE = local;

SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;

SQL> ALTER SESSION SET TIME_ZONE = '+ 08:00 ';

SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London ';

-- Query the session time zone

Select session timezone from dual;

-- Query the system time of the Current Database Server

SELECT SYSDATE FROM DUAL

/*

Three different types of timestamps

Common TIMESTAMP: TIMESTAMP. The default longitude value is 6 digits after the decimal point, and the maximum longitude value is 9 digits.

TIMESTAMP set in the database: TIMESTAMP WITH TIME ZONE

TIMESTAMP of the time zone of the current operating system: TIMESTAMP WITH LOCAL TIME ZONE

**/

-- Create a temporary table and define three columns of time. The types are the preceding three types.

Create table word_time

(Currtime TIMESTAMP,

Dbtime timestamp with time zone,

Local_time timestamp with local time zone );

-- Insert sysdate into the three columns in the word_time table to view the difference

Insert into word_time VALUES (SYSDATE, SYSDATE, SYSDATE );

-- Query the word_time table and observe the differences between the three columns.

SELECT * FROM word_time;

/*

Note:

1. By searching the word_time table, we can see that the DBTIME column is special, with an extra value of +. This indicates the time zone we have set beforehand-East 8;

2. The difference between LOCAL_TIME and DBTIME is that the time zone of the operating system where the database server is located is consistent with the operating system time zone,

When the operating system time zone changes, the database must be restarted to take effect.

**/

-- The TZ_OFFSET function is used to calculate the hour and minute intervals between the 0 time zone and the specified time zone.

SELECT TZ_OFFSET ('Asia/Chongqing ') from dual;

-- Query time zone information in the Dynamic View

SELECT * FROM v $ timezone_names stWHERE st. tzname like 'Asia % ';

-- Query the time zone of the current session. It is of the DATE type and accurate to seconds.

SELECT CURRENT_DATE from dual;

-- The display result is of the TIMESTAMP type, including the time zone. By default, the precision is 6 bits and a maximum of 9 bits are reserved.

SELECT CURRENT_TIMESTAMP from dual;

-- The display result is of the TIMESTAMP type. The format is similar to time stamp with local time zone.

Select localtimestamp from dual;

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian