Oracle Backup Features

1. USER_QUEUES: Information about queues in a user's schema
2. DBA_QUEUES: Information about all queues in the database
3. USER_QUEUE_SCHEDULES: Information about propagation of messages in queue
4. USER_QUEUE_PRIVILEGES: Information about queuing privileges for the current user
5. V$AQ: Dynamic information about queues, such as number of messages of various types in queue

• Explicitly Named Indexes On Keys
• Share Locks On Unindexed FKs
• PK Lookup During FK Insertion
• View Constraints
• Function Based Index Enhancements
• MERGE Statement
• Multitable Inserts
• External Tables
• SELECT .. FOR UPDATE Enhancements

CREATE TABLE employees
(
  empno NUMBER(6),
  name VARCHAR2(30),
  dept_no NUMBER(2),
  CONSTRAINT emp_pk primary key(empno)
    USING INDEX
    (CREATE INDEX emp_pk_idx ON employees(empno))
);

ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
ALTER TABLE employees DROP CONSTRAINT empno_pk;

CREATE VIEW Emp_view 
(id PRIMARY KEY DISABLE NOVALIDATE, firstname)
AS SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;
 
ALTER VIEW Emp_view
ADD CONSTRAINT emp_view_unq
UNIQUE (first_name) DISABLE NOVALIDATE;

MERGE INTO employees e
    USING hr_records h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

-- Unconditional insert into ALL tables
INSERT ALL
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sysdate) 
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, 
manager_id MGR FROM employees WHERE employee_id > 200;
 
-- Pivoting insert to split non-relational data
INSERT ALL
  INTO Sales_info VALUES (employee_id,week_id,sales_MON)
  INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
  INTO Sales_info VALUES (employee_id,week_id,sales_WED)
  INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
  INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
    sales_WED, sales_THUR,sales_FRI 
FROM Sales_source_data; 
 
-- Conditionally insert into ALL tables
INSERT ALL
  WHEN SAL>10000 THEN
    INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  WHEN MGR>200 THEN
    INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, 
manager_id MGR FROM employees WHERE employee_id > 200;
 
-- Insert into the FIRST table with a matching condition
INSERT FIRST
  WHEN SAL > 25000  THEN
    INTO special_sal VALUES(DEPTID,SAL)
  WHEN HIREDATE like ('%00%') THEN
    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
  WHEN HIREDATE like ('%99%') THEN 
    INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
  ELSE
    INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
    MAX(hire_date) HIREDATE
  FROM employees GROUP BY department_id; 
  

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.

-- Create directory object to data location
CREATE DIRECTORY EXT_TABLES AS 'C:\Oracle\External_Tables';
 
-- Create the external table
-- Files must exist in the specified location
CREATE TABLE employees_ext
  (empno NUMBER(8), first_name VARCHAR2(30), last_name VARCHAR2(30))
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tables
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
      )
      LOCATION ('employees1.txt','employees2.txt')
    )
  PARALLEL 5
  REJECT LIMIT 200;
 
-- Query the table
SELECT * FROM employees_ext;

SELECT *
FROM   employees
WHERE  empno = 20
FOR UPDATE WAIT 30;