Working with TIME ZONE information

To view the time zone settings for the database and your session:

SELECT DBTIMEZONE, SESSIONTIMEZONE
FROM DUAL;

DBTIMEZONE	SESSIONTIMEZONE
+01:00	Europe/Zurich

Some other useful functions:

SYSTIMESTAMP --> Returns system date including fractional seconds, datatype TIMESTAMP WITH TIME ZONE.

LOCALTIMESTAMP --> Returns the user session's local timestamp, datatype TIMESTAMP

CURRENT_TIMESTAMP --> Returns datatype TIMESTAMP WITH TIME ZONE, in the local time zone set for the session

CURRENT_DATE --> Returns datatype DATE, in the local time zone set for the session

SELECT  SYSTIMESTAMP,
        LOCALTIMESTAMP,
        CURRENT_TIMESTAMP,
        CURRENT_DATE
FROM DUAL;

SYSTIMESTAMP	LOCALTIMESTAMP	CURRENT_TIMESTAMP	CURRENT_DATE
10.02.2014 09:17:00.301998 +01:00	10.02.2014 09:17:00.302006	10.02.2014 09:17:00.302006 +01:00	10.02.2014 09:17:00

Some conversion functions:

FROM_TZ

-- converts a TIMESTAMP to TIMESTAMP WITH TIMEZONE

SELECT LOCALTIMESTAMP, FROM_TZ(LOCALTIMESTAMP, '+01:00' ) FROM DUAL

LOCALTIMESTAMP                 FROM_TZ(LOCALTIMESTAMP,'+01:00')
------------------------------ ---------------------------------------------------------------------------
18.03.14 09:23:40.032126       18.03.14 09:23:40.032126 +01:00

-- or alternatively, look up the timezone used in your session
-- and use this in a subquery 
SELECT LOCALTIMESTAMP, 
       FROM_TZ(LOCALTIMESTAMP, SELECT SESSIONTIMEZONE FROM DUAL) ) "Converted to TSTZ"
FROM DUAL;

LOCALTIMESTAMP                 Converted to TSTZ
------------------------------ ----------------------------------------
18.03.14 09:28:39.470322       18.03.14 09:28:39.470322 +01:00

TO_TIMESTAMP_TZ

--converts a string to a TIMESTAMP WITH TIMEZONE datatype
SELECT TO_TIMESTAMP_TZ('03.02.1973 16:00:00', 'DD.MM.YYYY HH24:MI:SS') 
FROM DUAL;

TO_TIMESTAMP_TZ('03.02.197316:00:00','DD.MM.YYYYHH24:MI:SS')
---------------------------------------------------------------------------
03.02.73 16:00:00.000000000 +01:00

TO_TIMESTAMP

--converts a string to a TIMESTAMP datatype
SELECT TO_TIMESTAMP('03.02.1973 16:00:00:123456','DD.MM.YYYY HH24:MI:SS:FF') 
FROM DUAL;

TO_TIMESTAMP('03.02.197316:00:00:123456','DD.MM.YYYYHH24:MI:SS:FF')
---------------------------------------------------------------------------
03.02.73 16:00:00.123456000

CAST
Cast can be used for a variety of conversions. It takes an expression as its first parameter, and returns an instance of the datatype you are converting to.
It is useful to convert strings representing dates or times, into proper datetime formats.

-- to convert a string into a TIMESTAMP WITH LOCAL TIME ZONE, use CAST
SELECT CAST('03-FEB-11'
       AS TIMESTAMP WITH LOCAL TIME ZONE) "String to TWLTZ"
FROM DUAL;

String to TWLTZ
----------------------------
03.02.11 00:00:00.000000

For a string with more precision, use TO_TIMESTAMP to convert to a timestamp first:

SELECT CAST(
            TO_TIMESTAMP('03.02.1973 16:05:00','DD.MM.YYYY HH24:MI:SS')
            AS TIMESTAMP WITH LOCAL TIME ZONE
           ) "TS to TSWLTZ"
FROM DUAL;

TS to TSWLTZ
-----------------------------
03.02.73 16:05:00.000000

EXTRACT
Extracts the value indicated by the constant immediately following the opening parenthesis, from a valid timestamp expression.
Note that EXTRACT requires a timestamp expression as an argument.
Its return type is character if you extract TIMEZONE_REGION or TIMEZONE_ABBR, but numeric for all other.

SELECT  EXTRACT(MINUTE FROM LOCALTIMESTAMP),
        EXTRACT(YEAR   FROM LOCALTIMESTAMP),
        EXTRACT(MONTH  FROM LOCALTIMESTAMP),
        EXTRACT(DAY    FROM LOCALTIMESTAMP),
        EXTRACT(HOUR   FROM LOCALTIMESTAMP),
        EXTRACT(SECOND FROM LOCALTIMESTAMP)
FROM DUAL;

EXTRACT(MINUTEFROMLOCALTIMESTAMP)	EXTRACT(YEARFROMLOCALTIMESTAMP)	EXTRACT(MONTHFROMLOCALTIMESTAMP)	EXTRACT(DAYFROMLOCALTIMESTAMP)	EXTRACT(HOURFROMLOCALTIMESTAMP)	EXTRACT(SECONDFROMLOCALTIMESTAMP)
3	2014	2	10	10	24.256402

Using the SYSTIMESTAMP, which includes TIME ZONE, in the EXTRACT function:

SELECT  EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)   "TZ_HOUR",
        EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) "TZ_MINUTE"
FROM DUAL;

TZ_HOUR	TZ_MINUTE
1	0

SYS_EXTRACT_UTC
Extract the universal time (UTC) based on a timestamp that contains time zone information:

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)
FROM DUAL;

SYS_EXTRACT_UTC(SYSTIMESTAMP)
10.02.2014 09:49:37.390230

AT TIME
Converts a datetime value to another time zone. AT TIME can convert TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.
But, take care, NOT the DATE datatype!
Note that the parenthesis are not required, but it makes the function more readable:

SELECT (LOCALTIMESTAMP) AT TIME ZONE DBTIMEZONE "DB time"
FROM DUAL;

DB time
10.02.2014 10:59:45.901974 +01:00

Or, using a named time zone region:

SELECT (CURRENT_TIMESTAMP) AT TIME ZONE 'Europe/Moscow' "Moscow Time"
FROM DUAL;

Moscow Time
10.02.2014 13:14:06.632136 +03:00

Specifying an offset from UTC:

SELECT (CURRENT_TIMESTAMP) AT TIME ZONE '-5:00' "New York Time"
FROM DUAL;

New York Time
10.02.2014 05:15:51.388302 -05:00

Use a TIMESTAMP value instead of TIMESTAMP WITH TIME ZONE:

SELECT (LOCALTIMESTAMP) AT TIME ZONE 'America/Los_Angeles' "LA Time"
FROM DUAL;

LA Time
10.02.2014 02:19:39.958949 -08:00

AT LOCAL
Convert the source data into the local time equivalent:

SELECT 
        FROM_TZ(
                TO_TIMESTAMP('10.02.2014 02:21:06.938886', 'DD.MM.YYYY HH24:MI:SS:FF')
               ,'US/Hawaii') 
        AT LOCAL "Hawaiian to Local Time"
FROM DUAL;

Hawaiian to Local Time
10.02.2014 13:21:06.938886000 +01:00

Without the AT LOCAL function, you would of course get the timestamp in the time zone specified

SELECT  FROM_TZ(
                TO_TIMESTAMP('10.02.2014 02:21:06.938886', 'DD.MM.YYYY HH24:MI:SS:FF')
               ,'US/Hawaii') "Hawaiian time"
FROM DUAL;

Hawaiian time
10.02.2014 02:21:06.938886000 -10:00

Which privileges are needed to execute DBMS_REDEFINITION package?

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.

In addition to having execute privileges on this package, you must be granted the following privileges:

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

LOCK ANY TABLE

SELECT ANY TABLE

In addition, the following privileges are required to execute COPY_TABLE_DEPENDENTS procedure:

CREATE ANY TRIGGER

CREATE ANY INDEX

Source: Oracle Documentation

How to rename a partition

ALTER TABLE SCOTT.EMPLOYEES
RENAME PARTITION START_DATE_FEBRUARY_2014 
TO
START_DATE_FEB_2014;

How to group output using GROUPING SETS.

Following up with the table from my previous post "How to use the MERGE statement with a subquery", here is an example on how to group the information using the GROUPING SETS functionality in SQL:

The query

SELECT MAJOR,COURSE,TEACHER,SUM(CREDITS) "Total Credits"
FROM  COURSE_CATALOG
GROUP BY GROUPING SETS( (MAJOR,COURSE,TEACHER),(COURSE,TEACHER), (MAJOR,TEACHER),NULL );

The NULL directive tells SQL to generate a grand total at the end of the grouping.
Notice how the GROUPING SETS translates directly into the equivalent of grouping the rows three separate times

First by using

GROUP BY MAJOR, COURSE, TEACHER

then by using

GROUP BY COURSE,TEACHER

and finally by using

GROUP BY MAJOR, TEACHER

MAJOR	COURSE	TEACHER	Total Credits
English Literature	Ex. Phil	A.Peterson	8
Nuclear Science	Physics A	Goodman	8
Nuclear Science	Physics A	A. Brown	8
Nuclear Science	Physics A	R. Franklin	8
Nuclear Science	Mathematics A	M. Stevens	8
English Literature	Greek Mythology	D.Simmons	10
Nuclear Science	Advanced Algebra	K.Wise	10
English Literature	Early British Lit	A.Peterson	8
	Ex. Phil	A.Peterson	8
	Physics A	Goodman	8
	Physics A	A. Brown	8
	Physics A	R. Franklin	8
	Mathematics A	M. Stevens	8
	Greek Mythology	D.Simmons	10
	Advanced Algebra	K.Wise	10
	Early British Lit	A.Peterson	8
Nuclear Science		M. Stevens	8
Nuclear Science		A. Brown	8
English Literature		D.Simmons	10
Nuclear Science		Goodman	8
Nuclear Science		R. Franklin	8
Nuclear Science		K.Wise	10
English Literature		A.Peterson	16
			68

How to use the MERGE statement with a subquery

Here is an example on how to use the MERGE statement to update a table, using a self-referencing subquery. One of many ways to clean up unstructured data

Tables look as follows:

MAJOR	COURSE	CLASS	CREDITS	ROOM_NO	TEACHER
Nuclear Science	Physics A	A. Brown, Room 208	8
Nuclear Science	Mathematics A	M. Stevens, Room 56	8
Nuclear Science	Advanced Algebra	K.Wise, Audit. 20	10
English Literature	Ex. Phil	A.Peterson, Room 208	8
English Literature	Early British Lit	A.Peterson, Room 208	8
English Literature	Greek Mythology	D.Simmons, Room 206	10
Nuclear Science	Physics A	Goodman, Room 208	8

To populate the ROOM_NO and TEACHER columns, I used the following query:

MERGE INTO COURSE_CATALOG A
USING 
(SELECT CLASS FROM COURSE_CATALOG) B
ON (A.ROWID = B.ROWID)
WHEN MATCHED THEN
    UPDATE SET A.TEACHER = REGEXP_REPLACE(REGEXP_SUBSTR(CLASS, '[[:alpha:]]+.{1,}[^[:digit:]]',1,1,'i'), ', Room |, Audit.', ''),
               A.ROOM_NO = REGEXP_SUBSTR(B.CLASS,'[[:digit:]]+'),
               A.CLASS = NULL
               
;
COMMIT;

Result:

MAJOR	COURSE	CLASS	CREDITS	ROOM_NO	TEACHER
Nuclear Science	Physics A		8	208	A. Brown
Nuclear Science	Mathematics A		8	56	M. Stevens
Nuclear Science	Advanced Algebra		10	20	K.Wise
English Literature	Ex. Phil		8	208	A.Peterson
English Literature	Early British Lit		8	208	A.Peterson
English Literature	Greek Mythology		10	206	D.Simmons
Nuclear Science	Physics A		8	208	Goodman

How to deregister a database from a Recovery Catalog

Log on to the target database:

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:00:50 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
 305453026 PRODDB01

connect to the recovery catalog through sqlplus:

sqlplus uid/pwd@RMANCAT

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:01:33 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> select db_key,dbid,name from rc_database
  2  where dbid=305453026;

    DB_KEY       DBID NAME
---------- ---------- --------
         1  305453026 PRODDB01

Finally, execute the procedure dbms_rcvcat.unregisterdatabase, passing in the parameters db_key and dbid:

SQL> execute dbms_rcvcat.unregisterdatabase(1,305453026);

PL/SQL procedure successfully completed.

How to set a column to UNUSED

Setting a column to UNUSED can be a solution when you cannot or should not attempt to drop a column due to performance reasons.

Syntax:

ALTER TABLE [table name] SET UNUSED (col1, col2, ... col n);

or

ALTER TABLE [table name] SET UNUSED COLUMN (col1);

Example:

ALTER TABLE MYTABLE
SET UNUSED COLUMN MODIFIED_DATE;

Verify that the columns were set to UNUSED:

SELECT * 
FROM USER_UNUSED_COL_TABS;

Result:

TABLE_NAME	COUNT
MYTABLE	1
INVOICES_TMP	1

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS;

The column is now gone:

SELECT * 
FROM USER_UNUSED_COL_TABS;

TABLE_NAME	COUNT
INVOICES_TMP	1

You can also use the keyword CHECKPOINT when dropping the unused columns:

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS CHECKPOINT 1000;

Oracle explains about the CHECKPOINT clause:

"This clause causes a checkpoint to be applied after processing the specified number of rows"
and
"Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo Space."

Note that:
Setting a column to UNUSED will drop constraints and indexes on the column
An UNUSED column cannot be accessed, nor recovered.
You can however, add a new column with the same name as any UNUSED column for the table.

Sources:
Oracle Documentation: Marking columns unused
Oracle Documentation: ALTER TABLE