Oracle Date and Time Functions

Date and Time functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.

The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.

The datetime functions are:

• ADD_MONTHS
• CURRENT_DATE
• CURRENT_TIMESTAMP
• DBTIMEZONE
• EXTRACT (datetime)
• FROM_TZ
• LAST_DAY
• LOCALTIMESTAMP
• MONTHS_BETWEEN
• NEW_TIME
• NEXT_DAY
• ROUND (date)
• SESSIONTIMEZONE
• SYSDATE
• SYSTIMESTAMP
• TO_CHAR (datetime)
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_DSINTERVAL
• TO_YMINTERVAL
• TRUNC (date)

ADD_MONTHS

ADD_MONTHS returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

Examples

SELECT TO_CHAR(ADD_MONTHS(hire_date,1),'DD-MON-YYYY') "Next month"
     FROM employees 
     WHERE last_name = 'Baer';

Next Month
-----------
07-JUL-2012

add_months('01-Aug-12', 3) would return '01-Nov-12'
add_months('01-Aug-12', -3) would return '01-May-12'

CURRENT_DATE

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

Examples

SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
+04:00          15/04/2013 09:13:26

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.

Examples

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------
+04:00          15/04/2013 09:20:22.333000 AM +04:00

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

Examples

SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
------
+00:00

EXTRACT (datetime)

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.

Examples

SELECT EXTRACT(month FROM order_date) "Month",
  COUNT(order_date) "No. of Orders"
  FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC;

     Month No. of Orders
---------- -------------
        11            15
         7            14
         6            14
         3            11
         5            10
         9             9
         2             9
         8             7
        10             6
         1             5
        12             4
         4             1
 
12 rows selected.

SELECT EXTRACT(YEAR FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             2013


SELECT EXTRACT(MONTH FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(MONTHFROMDATE'1998-03-07')
---------------------------------
                               04

SELECT EXTRACT(DAY FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(DAYFROMDATE'1998-03-07')
--------------------------------
                              10

FROM_TZ

FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.

Examples

SELECT FROM_TZ(TIMESTAMP '2013-03-28 08:00:00', '4:00') FROM DUAL;

FROM_TZ(TIMESTAMP'2013-03-2808:00:00','4:00')
-------------------------------------------------------
28-MAR-13 08.00.00 AM +04:00

LAST_DAY

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE.

Examples

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
30-MAR-13 31-MAR-13          1

LOCALTIMESTAMP

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Examples

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                    LOCALTIMESTAMP
-------------------------------------------------------------------
15/04/2013 10:45:01.535000 AM +04:00  15/04/2013 10:45:01.535000 AM

MONTHS_BETWEEN

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

Examples

SELECT MONTHS_BETWEEN 
   (TO_DATE('03-03-2013','MM-DD-YYYY'),
    TO_DATE('01-01-2013','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
2.06451612

NEW_TIME

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. The return type is always DATE, regardless of the datatype of date.

Examples

SELECT 
new_time (to_date ('2013/02/15 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') "New Date and Time"
FROM DUAL

New Date and Time
--------------------
14/02/2013 10:45:00 AM

NEXT_DAY

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE.

Examples

SELECT NEXT_DAY('02-FEB-2013','TUESDAY') "NEXT DAY" FROM DUAL;

NEXT DAY
-----------
05-FEB-2013

ROUND (date)

ROUND returns date rounded to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.

Examples

SELECT ROUND (TO_DATE ('27-07-12','DD-MM-YY'),'YEAR')  "New Year" FROM DUAL;
 
New Year
---------
01-01-13

SELECT ROUND (TO_DATE ('20-07-12','DD-MM-YY'),'MONTH')  "New Month" FROM DUAL;
 
New Month
---------
01-08-12

ROUND Function Valid Format Parameters:

Unit	Valid Format Parameters	Rounding Rule
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	Rounds up on July 1st
Quarter	Q	Rounds up on the 16th day of the second month of the quarter
Month	MONTH, MON, MM, RM	Rounds up on the 16th day of the month
Week	WW	Same day of the week as the first day of the year
W	W	Same day of the week as the first day of the month
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session.

Examples

SELECT SESSIONTIMEZONE FROM DUAL;

SESSION
-------
+04:00

SYSDATE

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE.

Examples

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"  FROM DUAL;

NOW
-------------------
04-16-2013 12:31:27

SYSTIMESTAMP

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

Examples

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-----------------------------------------------
16/04/2013 12:33:54.235000 PM +04:00

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') "Fractional Seconds" FROM DUAL;

Fractional Seconds
---------------
45345.500000

TO_CHAR (datetime)

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.

Examples

to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'

List of Valid Parameters For the TO_CHAR (datetime) Function

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY, YY, Y	Last 3, 2, or 1 digit(s) of year.
IYY, IY, I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

TO_TIMESTAMP

TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples

SELECT TO_TIMESTAMP ('10-09-2012 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF')
   FROM DUAL;

TO_TIMESTAMP('10-09-0214:10:10.123000','DD-MM-RRHH24:MI:SS.FF')
-------------------------------------------------------------------
10/09/2012 02:10:10.123000000 PM

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.

SELECT TO_TIMESTAMP_TZ('2012-12-01 11:00:00 +4:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

TO_TIMESTAMP_TZ('2012-12-0111:00:00+04:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------
01/12/2012 11:00:00.000000000 AM +04:00

TO_DSINTERVAL

TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.

Examples

SELECT employee_id, last_name FROM employees
   WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
   <= DATE '2012-01-01';

EMPLOYEE_ID LAST_NAME
----------- ---------------
        100 King
        101 Kochhar
        200 Whalen

TO_YMINTERVAL

TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted.

Examples

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
   FROM employees;

HIRE_DATE       14 months
---------       ---------
11/01/2013 11/03/2014
03/01/2013 03/03/2014
10/01/2013 10/03/2014
11/01/2013 11/03/2014
20/12/2012 20/02/2014
20/12/2012 20/02/2014
02/01/2013 02/03/2014

TRUNC (date)

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE.

Examples

SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'YEAR') "Trunc Year" FROM DUAL;
 
Trunc Year
---------
01/01/2012

SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'MONTH') "Trunc Month" FROM DUAL;
 
Trunc Month
---------
01/10/2012

For Trunc (date) Function Valid Format Parameters

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

Oracle OPERATORS

Oracle provides operators to be used with data in order to perform some related action and return a result. An operator can act on a single operand (Unary Operators) or on two operands (Binary Operators).

The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.

Oracle provides following kinds of operators:

1. Arithmetic Operators
2. Concatenation Operators
3. Comparison Operators
4. Logical Operators
5. Hierarchical Operators
6. Set Operators

Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value.

The available Arithmetic Operators are: +, -, *, and /.

Examples

The following are some examples.

SELECT sal + comm FROM emp
  WHERE SYSDATE - hiredate
  > 365;

UPDATE emp
  SET sal = sal * 1.1;

c := a + b;

Concatenation Operator

Concatenation Operator operate on two string operands to join them into a single string.

The available Concatenation Operator is ||.

Examples

SELECT 'Name is ' || ename
   FROM emp;

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Comparison Operators

Comparison Operators are used to build a comparable condition between two terms, thereby returning a logical result as TRUE, FALSE, or NULL.

The available Comparison operators are: =, <, >, <=, >=, (NOT)IN, (NOT) LIKE, (NOT) BETWEEN, (NOT) EXISTS, and IS (NOT) NULL.

Examples

The following are some examples.

=	Equality test.	SELECT * FROM emp WHERE sal = 1500;
>	Greater than test	SELECT * FROM emp WHERE sal > 1500;
<=	Less than or equal to tests	SELECT * FROM emp WHERE sal <= 1500;
NOT IN	Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM emp WHERE job NOT IN ('CLERK', 'ANALYST');
EXISTS	TRUE if a subquery returns at least one row.	SELECT ename, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno);

Logical Operators

Logical Operators operate on two operands and return a BOOLEAN output.

The available logical operators are: AND, OR, and NOT.

AND Operator

Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  AND deptno = 10;

OR Operator

Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  OR deptno = 10;

NOT Operator

Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.

SELECT * FROM emp
  WHERE NOT (sal BETWEEN 1000 AND 2000);

Hierarchical Operators

Hierarchical Operators are used only in hierarchical queries.

The available Hierarchical Operators are: PRIOR and CONNECT_BY_ROOT.

Example

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

KING
----------JONES
--------------------SCOTT
------------------------------ADAMS
--------------------FORD
------------------------------SMITH
----------BLAKE
--------------------ALLEN
--------------------WARD
--------------------MARTIN
--------------------TURNER
--------------------JAMES
----------CLARK
--------------------MILLER

Set Operators

Set Operators are used to compound multiple queries and return the combined result set.

The available SET Operators are: UNION, UNION ALL, MINUS, and INTERSECT.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_DATE and TO_NUMBER functions) when columns do not exist in one or the other table:

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_number(null), date_in
    FROM orders_list2;

UNION ALL Example

The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:

SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT part FROM orders_list1 
MINUS 
SELECT part FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT part FROM orders_list1 
INTERSECT 
SELECT part FROM orders_list2;

PART 
---------- 
TAILPIPE 

Orace Materialized Views

Materialized views facilitate you to execute a SQl query and save its results either locally or in a remote database.

After the Materialized view is initially populated it can be refreshed later on to store fresh results into the underlying table.

Materialized Views are mainly used for two reasons:

1) Replication of data to separate remote databases.

2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

In situations where complex sql queries are performed mainly in reporting or datawarehouse environments Materialized Views are really helpful in improving performance.

Whenever a SQL query is executed oracle database has to lot of work in order to retrieve the data. For example it may have to do sorting (Memory or Disk Based), it has to decide the execution plan for the sql statement (Do a full tables scan or a indexed based scan) and lots of other stuff before retrieving the requested data.

These type of queries if performed repeatedly will affect the performance of the server in a negative way.

But with Materialized Views the performance can be improved significantly, because when a materialized view is created it stores all the data along with the execution plans. So even if the query is executed repeatedly it will not eat up all the resources as it did earlier.

The Materialized view can be created on the base of tables, views or other materialized views.

When a Materialized View is created, oracle also create a table with the same name as that of the materialized view and also creates a materialized view object.

Types of Materialized Views

There are following types of Materialized views.

• Read-Only Materialized Views.
• Updatable Materialized Views.

Read-Only Materialized Views

You can make a materialized view read-only during creation by omitting the FOR UPDATE clause or disabling the equivalent option in the Advanced Replication interface in Oracle Enterprise Manager. Read-only materialized views use many of the same mechanisms as updatable materialized views, except that they do not need to belong to a materialized view group.

In addition, using read-only materialized views eliminates the possibility of a materialized view introducing data conflicts at the master site or master materialized view site, although this convenience means that updates cannot be made at the remote materialized view site. The following is an example of a read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees AS
  SELECT * FROM hr.employees@orc1.example.com;

Updatable Materialized Views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Advanced Replication interface in Oracle Enterprise Manager. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.

Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an example of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
  SELECT * FROM hr.departments@orc1.example.com;

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

1. Complete - Refreshable Materialzed Views.
2. Fast-refresh Materialized Views.

COMPLETE REFERSH MATERIALIZED VIEWS

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command at periodic intervals.

CREATE MATERIALIZED VIEW hr.employees
  REFRESH COMPLETE
  NEXT SYSDATE + 1/1440 
  AS SELECT * FROM hr.employees@orc1.example.com;

So above we have created a materialized view based on the sales table, which will completely refresh itself after every one minute.

FAST REFRESH MATERIALIZED VIEW

Fast refreshable materialized views work a little bit differently. When a fast refresh materialized view is created it initially populates the materialized view table with data from the base or master table.

After the initial data is populated only modified data is applied to the materialized view table after each refresh, Instead of a complete refresh like that in Complete refresh materialized views.

Fast refresh using materialized view logs.

Fast refresh using ROWID range.

Materialized View Logs

Materialized view logs are required if you want to use fast refresh. Materialized view logs are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed.

Now lets create a materialized view log on the master table.

CREATE MATERIALIZED VIEW LOG ON hr.employees@orc1.example.com WITH 
PRIMARY KEY;

If your base table does not have a primary key then a following error will occur.

ORA-12014: table does not contain primary key constraint

In that case make materialized view log based on ROWID.

CREATE MATERIALIZED VIEW LOG ON hr.employees@orc1.example.com WITH 
ROWID;

We are creating a materialized view based on primary key refresh.

 CREATE MATERIALIZED VIEW hr.employees
  REFRESH WITH PRIMARY KEY
  NEXT SYSDATE + 3/1440 
  AS SELECT * FROM hr.employees@orc1.example.com;

Materialized Views Refresh Modes

Manual Refresh: Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)

Automatic Refresh: Can be performed in two ways:

ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.

ON DEMMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

Dropping Materialized Views

DROP MATERIALIZED VIEW hr.employees;

Oracle DATABASE LINK

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites

• To create a private database link, you must have the CREATE DATABASE LINK system privilege.
• To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege.
• You must have the CREATE SESSION system privilege on the remote Oracle database.
• You must be able to connect to the remote database.
• Oracle Net must be installed on both the local and remote Oracle databases.

Database Link Types

When you create a database link, you must decide who will have access to it. The following sections describe how to create the basic types of links.

Private Database Links

CREATE DATABASE LINK mylink
USING 'tns_service_name';

Above private link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE DATABASE LINK mylink
CONNECT TO remote_username IDENTIFIED BY mypassword 
USING 'tns_service_name';

Above private fixed user link to the database with service name 'tns_service_name'. The link connects to the remote database with the remote_username / mypassword regardless of the connected user.

Public Database Links

CREATE PUBLIC DATABASE LINK mylink
USING 'tns_service_name';

Above public link to the remote database. The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE PUBLIC DATABASE LINK mylink
CONNECT TO remote_username IDENTIFIED BY mypassword 
USING 'tns_service_name';

Above public fixed user link. The link connects to the remote database with the remote_username / mypassword .

How to Use

Once Database Link is created you can access remote database objects by using @dblink.

Examples

SELECT * FROM employees@mylink;

UPDATE employees@mylink
   SET salary=salary*1.1
   WHERE last_name = 'Baer';

You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:

CREATE SYNONYM emp_table 
   FOR oe.employees@mylink;

SELECT * FROM emp_table;

Drop Database Link

Use the DROP DATABASE LINK statement to remove a database link from the database.

A private database link must be in your own schema.

DROP DATABASE LINK mylink;

To drop a PUBLIC database link, you must have the DROP PUBLIC DATABASE LINK system privilege.

DROP PUBLIC DATABASE LINK mylink;