/*
|| Oracle 10g RMAN Listing 3
||
|| Contains examples of new Oracle 10g Logical Flashback features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| new Logical Flashback features and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/
 
-----
-- Listing 3.1: Simple Flashback Query Examples
-----

-- Simulate a user error
UPDATE hr.departments
   SET manager_id = NULL;

COMMIT;

-- View data as it existed before the transaction was committed. This 
-- example assumes that the approximate time of the damage is known
SELECT *
  FROM hr.departments
  AS OF TIMESTAMP 
TO_TIMESTAMP('12/05/2004 14:45:00', 'MM/DD/YYYY HH24:MI:SS');

-- Repair damaged data using the flashed-back data
UPDATE hr.departments D1
   SET D1.manager_id = (
       SELECT manager_id 
         FROM hr.departments
           AS OF TIMESTAMP 
           TO_TIMESTAMP('12/05/2004 11:55:00', 'MM/DD/YYYY HH24:MI:SS') D
        WHERE manager_id IS NOT NULL
          AND d1.department_id = D.department_id
       );

COMMIT;       
 
-----
-- Listing 3.2: Add new Jobs, Departments, and Employees
-----

INSERT INTO hr.departments (department_id, department_name, manager_id, location_id)
VALUES (280, 'Science Fiction Writers', 901, 1500);

INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary, job_type)
VALUES ('EDITOR', 'Science Fiction Editor', 100000, 199999, 'S');

INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary, job_type)
VALUES ('WRITER-1', 'Science Fiction Writer 1', 5000, 29999, 'S');

INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary, job_type)
VALUES ('WRITER-2', 'Science Fiction Writer 2', 25000, 64999, 'S');

INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary, job_type)
VALUES ('WRITER-3', 'Science Fiction Writer 3', 55000, 74999, 'S');

COMMIT;

INSERT INTO hr.employees (
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,   
    hire_date, 
    job_id, 
    salary, 
    commission_pct, 
    manager_id, 
    department_id
)
VALUES (
    901, 
    'John', 
    'Campbell', 
    'jcampbell@astounding.com',
    '212-555-1212',
    TO_DATE('02/08/1943', 'MM/DD/YYYY'),
    'EDITOR',
    110000,
    NULL,
    100,
    280
);

INSERT INTO hr.employees (
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,   
    hire_date, 
    job_id, 
    salary, 
    commission_pct, 
    manager_id, 
    department_id
)
VALUES (
    902, 
    'Isaac', 
    'Asimov', 
    'iasimov@astounding.com',
    '212-555-1313',
    TO_DATE('01/01/1949', 'MM/DD/YYYY'),
    'WRITER-1',
    5000,
    NULL,
    901,
    280
);

INSERT INTO hr.employees (
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,   
    hire_date, 
    job_id, 
    salary, 
    commission_pct, 
    manager_id, 
    department_id
)
VALUES (
    903, 
    'Robert', 
    'Heinlein', 
    'bheinlein@astounding.com',
    '212-555-1414',
    TO_DATE('09/03/1945', 'MM/DD/YYYY'),
    'WRITER-2',
    25000,
    NULL,
    901,
    280
);

INSERT INTO hr.employees (
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,   
    hire_date, 
    job_id, 
    salary, 
    commission_pct, 
    manager_id, 
    department_id
)
VALUES (
    904, 
    'Ray', 
    'Bradbury', 
    'rbradbury@astounding.com',
    '212-555-1515',
    TO_DATE('10/31/1946', 'MM/DD/YYYY'),
    'WRITER-1',
    48500,
    NULL,
    901,
    280
);

INSERT INTO hr.employees (
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,   
    hire_date, 
    job_id, 
    salary, 
    commission_pct, 
    manager_id, 
    department_id
)
VALUES (
    905, 
    'Harlan', 
    'Ellison', 
    'hellison@astounding.com',
    '212-555-1515',
    TO_DATE('10/31/1962', 'MM/DD/YYYY'),
    'WRITER-3',
    32500,
    NULL,
    901,
    280
);

COMMIT;
 
-----
-- Listing 3.3: Sample transactions:
-- 1.) Add a new employee
-- 2.) Update salaries and department IDs for selected employees
-- 3.) Delete the newly-added employee
-----

INSERT INTO hr.employees (
    employee_id, 
    first_name, 
    last_name, 
    email, 
    phone_number,   
    hire_date, 
    job_id, 
    salary, 
    commission_pct, 
    manager_id, 
    department_id
)
VALUES (
    906, 
    'David', 
    'Brin', 
    'dbrin@astounding.com',
    '212-555-1616',
    TO_DATE('10/31/1987', 'MM/DD/YYYY'),
    'WRITER-3',
    37500,
    NULL,
    901,
    280
);

COMMIT;

UPDATE hr.employees
   SET salary = salary * 1.05
 WHERE employee_id >= 902;

COMMIT;

UPDATE hr.employees
   SET department_id = 270
 WHERE employee_id = 905;

COMMIT;

DELETE FROM hr.employees
 WHERE employee_id = 906; 

COMMIT;

 
-----
-- Listing 3.4: Flashback Version Example
-----

-- Using the new ORA_ROWSCN pseudocolumn
SELECT 
    ORA_ROWSCN, 
    employee_id, 
    first_name, 
    last_name
  FROM hr.employees;

-- Show all changes to selected rows regardless of versions available. 
-- Note the use of MINVALUE AND MAXVALUE for the timestamp range so that
-- all possible versions are shown

-- What are results from prior RMAN commands and sessions?
SET LINESIZE 120
TTITLE 'Current FLASHBACK VERSION Results For Selected Employees'
COL versions_xid        FORMAT A16        HEADING 'XID'
COL versions_startscn   FORMAT 99999999   HEADING 'Vsn|Start|SCN'
COL versions_endscn     FORMAT 99999999   HEADING 'Vsn|End|SCN'
COL versions_operation  FORMAT A12        HEADING 'Operation'
COL last_name           FORMAT A12        HEADING 'Last Name'
COL department_id       FORMAT 9999       HEADING 'Dept'
COL salary              FORMAT 999999.99  HEADING 'Salary'

SELECT 
	versions_xid,
	versions_startscn,
	versions_endscn,
	DECODE(
	    versions_operation, 
	        'I', 'Insert', 
	        'U', 'Update', 
	        'D', 'Delete', 'Original') "Operation",
    last_name,
    department_id,
	salary
  FROM hr.employees
 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  WHERE employee_id >= 901
;  
   
-----
-- Listing 3.5: Flashback Transaction Example
-----

-- Show the contents of the FLASHBACK_TRANSACTION_QUERY view
SET PAGESIZE 120
SET LINESIZE 100
TTITLE 'Current FLASHBACK_TRANSACTION_QUERY Contents For Selected Employees'
COL xid                 FORMAT A16      HEADING 'XID#'
COL commit_scn          FORMAT 99999999 HEADING 'Commit|SCN'
COL operation           FORMAT A10      HEADING 'Operation'
COL logon_user          FORMAT A06      HEADING 'User|Logon'
COL table_owner         FORMAT A06      HEADING 'Table|Owner'
COL table_name          FORMAT A12      HEADING 'Table Name'
COL undo_sql            FORMAT A80     HEADING 'UNDO SQL'

SELECT 
	xid,
	operation,
	logon_user,
    table_owner,
    table_name,
	commit_scn,
	undo_sql
  FROM flashback_transaction_query
  WHERE xid IN (SELECT versions_xid
                  FROM hr.employees
              VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
                 WHERE employee_id >= 901
                   AND versions_xid IS NOT NULL);
----- 
-- Listing 3.6: Create a new table (HR.APPLICANTS)
-----
DROP TABLE hr.applicants CASCADE CONSTRAINTS;
create table HR.APPLICANTS
(
  applicant_id     NUMBER(5)    NOT NULL,
  last_name        VARCHAR2(24) NOT NULL,
  first_name       VARCHAR2(24) NOT NULL,
  middle_initial   VARCHAR2(1),
  gender           VARCHAR2(1),
  application_date DATE         NOT NULL,
  job_desired      VARCHAR2(10) NOT NULL,
  salary_desired   NUMBER(10,2) NOT NULL,
  added_on         DATE         DEFAULT SYSDATE NOT NULL,
  added_by         VARCHAR2(12) NOT NULL,
  changed_on       DATE DEFAULT SYSDATE NOT NULL,
  changed_by       VARCHAR2(12) NOT NULL
)
TABLESPACE EXAMPLE
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

-- Comments
COMMENT ON TABLE hr.applicants
    IS 'Controls domain of Applicants, i.e. persons who have applied for an employment opportunity';
COMMENT ON COLUMN hr.applicants.applicant_id
    IS 'Unique identifier for an Applicant';
COMMENT ON COLUMN hr.applicants.last_name
    IS 'Applicant Last Name';
COMMENT ON COLUMN hr.applicants.first_name
    IS 'Applicant First Name';
COMMENT ON COLUMN hr.applicants.middle_initial
    IS 'Applicant Middle Initial';
COMMENT ON COLUMN hr.applicants.gender
    IS 'Applicant Gender';
COMMENT ON COLUMN hr.applicants.application_date
    IS 'Application Date';
COMMENT ON COLUMN hr.applicants.job_desired
    IS 'Job Applied For';
COMMENT ON COLUMN hr.applicants.salary_desired
    IS 'Desired Salary';
COMMENT ON COLUMN hr.applicants.added_on
    IS 'Added On';
COMMENT ON COLUMN hr.applicants.added_by
    IS 'Added By';
COMMENT ON COLUMN hr.applicants.changed_on
    IS 'Last Updated On';
COMMENT ON COLUMN hr.applicants.changed_by
    IS 'Last Updated By';

-- Create indexes and constraints
CREATE UNIQUE INDEX hr.applicants_pk_idx
    ON hr.applicants(applicant_id)
    TABLESPACE EXAMPLE
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
    (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    );

ALTER TABLE hr.applicants
    ADD CONSTRAINT applicants_pk
    PRIMARY KEY (applicant_id);

CREATE INDEX hr.applicants_last_name_idx
    ON hr.applicants(last_name)
    TABLESPACE EXAMPLE
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
    (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    );

-- Create/Recreate check constraints
ALTER TABLE hr.applicants
    ADD CONSTRAINT applicant_gender_ck
    CHECK ((gender IN('M', 'F') or gender IS NULL));

-- Create sequence
DROP SEQUENCE hr.seq_applicants;
CREATE SEQUENCE hr.seq_applicants
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 3;

-- Create INSERT/UPDATE row-level trigger
CREATE OR REPLACE TRIGGER hr.tr_briu_applicants
    BEFORE INSERT OR UPDATE ON hr.applicants
    FOR EACH ROW
DECLARE
    entry_id NUMBER := 0;
BEGIN

    IF INSERTING THEN
        BEGIN

            SELECT
                hr.seq_applicants.NEXTVAL
                INTO entry_id
                FROM DUAL;

            :new.applicant_id := entry_id;
            :new.added_on := SYSDATE;
            :new.added_by := DBMS_STANDARD.LOGIN_USER;
            :new.changed_on := SYSDATE;
            :new.changed_by := DBMS_STANDARD.LOGIN_USER;

        END;

    ELSIF UPDATING THEN
        BEGIN
            :new.changed_on := SYSDATE;
            :new.changed_by := DBMS_STANDARD.LOGIN_USER;
        END;

    END IF;

END TR_BRIU_APPLICANTS;
/

-- Create a first set of applicants
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Aniston', 'Seth', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 88017.94);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Niven', 'Ray', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 82553.39);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Brown', 'Jackson', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 70113.04);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Murdock', 'Charlton', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 70389.16);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Bedelia', 'Colin', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 38720.86);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Chandler', 'Gino', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 55511.77);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Lerner', 'Hex', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 80587.46);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Robinson', 'Mekhi', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 49516.37);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Chestnut', 'Denis', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 73042.53);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Costa', 'Doug', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 65403.50);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Bello', 'Lucy', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 78432.05);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Playboys', 'Edward', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 54464.91);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Spader', 'Leonardo', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 49207.14);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Jovovich', 'Edwin', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 56825.48);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Graham', 'Ray', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 69169.14);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Barkin', 'Suzanne', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 49641.49);

COMMIT;
 
-----
-- Listing 3.7: Create a second set of applicants
-----

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Shandling', 'Mac', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 98871.03);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Sizemore', 'Casey', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 73455.15);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Pressly', 'Bob', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 63675.02);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Sandler', 'Joanna', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 56205.25);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Callow', 'Ramsey', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 90966.42);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Skerritt', 'Rade', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 44394.27);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('McBride', 'Earl', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 58023.76);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Shepherd', 'Charles', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 67411.52);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Nicholas', 'Mint', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 63045.15);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Bassett', 'Jennifer', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 86512.69);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Tobolowsky', 'Ronny', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 77830.91);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Finney', 'Marisa', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 92955.58);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Maxwell', 'Emily', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 72122.43);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('MacLachlan', 'Walter', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 97292.06);

COMMIT;
 
-----
-- Listing 3.8: Performing a FLASHBACK TABLE operation
-----

-- Enable row movement first, otherwise error ORA-08189 ("Cannot flashback
-- the table because row movement is not enabled") will result
ALTER TABLE hr.applicants ENABLE ROW MOVEMENT;

-- Issue the FLASHBACK TABLE command for the selected SCN
FLASHBACK TABLE hr.applicants TO SCN 2177093;
 
-----
-- Listing 3.9: The Recycle Bin
-----

-- Verify the contents of the Recycle Bin
SHOW RECYCLEBIN;

-- Show details of Recycle Bin contents
SET PAGESIZE 120
SET LINESIZE 100
TTITLE 'Current Recycle Bin Contents'
COL object_name         FORMAT A30      HEADING 'Object Name'
COL type                FORMAT A8       HEADING 'Object|Type'
COL original_name       FORMAT A20      HEADING 'Original Name'
COL droptime            FORMAT A20      HEADING 'Dropped On'
COL dropscn             FORMAT 9999999  HEADING 'Drop|SCN'
SELECT
     object_name
    ,type
    ,original_name
    ,droptime
  FROM dba_recyclebin
 WHERE owner = 'HR'
;

-- Query directly from a table in the Recycle Bin using its object identifier
SELECT *
  FROM "BIN$0M5fd0JLT2Gops3S5EDkNw==$0";
 
-----
-- Listing 3.10: Recycle Bin Housekeeping (in order of destructiveness)
-----

-- Purge an index from the Recycle Bin. Note that any index that's enforcing
-- a constraint can't be purged via this method
PURGE INDEX hr.applicant_last_name_idx;

-- Purge a table and its dependent objects from the Recycle Bin
PURGE TABLE hr.applicants;

-- Purge all dropped tables and their dependent objects for a specific
-- tablespace from the Recycle Bin
PURGE TABLESPACE example;

-- Purge all objects from the Recycle Bin for the current user account
PURGE RECYCLEBIN;

-- Purge all objects from the Recycle Bin for the entire database
PURGE DBA_RECYCLEBIN;
 
-----
-- Listing 3.11: Create a third set of applicants
-----

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Conners', 'Jose', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 49579.74);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('McFerrin', 'Jonatha', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 87397.11);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Webb', 'Night', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 85049.10);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Dzundza', 'Tramaine', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 35239.10);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('McCormack', 'Ethan', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 49729.40);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Withers', 'Andie', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 50804.93);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Goodman', 'Sonny', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 97469.88);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Goodman', 'Fiona', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 35213.20);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Watson', 'Melanie', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 76803.79);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Chaplin', 'Bridgette', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 67701.57);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Patton', 'Ted', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 43295.03);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Winwood', 'Chloe', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 57301.55);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('King', 'Clint', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 50291.11);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Carrington', 'Joan', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 91919.56);

insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED)
values ('Tyson', 'Hex', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 56582.30);

COMMIT;