Overview

Becoming Familiar with the New Multi-Table Insert

Using the New Upsert Functionality

Table Function Overview

Using Synchronous CDC to Capture and Consume Incremental Source Changes

Propagation from a Data Warehouse to a Data Mart Overview

Clean Up

Application of complex filters

Data has to be validated against information already existing in target database tables

Data extracted without the knowledge of new versus changed information has to be checked against the target objects to determine whether it must be updated or inserted

The same data has to be inserted several times as detail level and aggregated information

Completed the Installing Oracle Database 10g on Windows Using Real Application Clusters (RAC) and Automated Storage Management (ASM) tutorial

OR

Completed the Installing Oracle Database 10g on Linux tutorial

Use the Multi-Table Insert for Pivoting

Use the Multi-Table Insert for Conditional Insertion

1.

Show the execution plan for the new multi-table insert. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@explain_mti_new.sql

DELETE
FROM PLAN_TABLE;

EXPLAIN PLAN FOR
INSERT ALL
INTO sales
VALUES(product_id, customer_id,weekly_start_date,2,9999, q_sun,sales_sun)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+1,2,9999, q_mon,sales_mon)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+2,2,9999, q_tue,sales_tue)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+3,2,9999, q_wed,sales_wed)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+4,2,9999, q_thu,sales_thu)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+5,2,9999, q_fri,sales_fri)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+6,2,9999, q_sat,sales_sat)
SELECT * FROM sales_input_table;

SET linesize 140
SELECT * from table(dbms_xplan.display);

Note: The input source table is scanned only once! The complexity of the denormalization is handled within the several INSERT INTO branches, thus avoiding multiple scans.

2.

Now show the execution plan for the multi-table insert based on a UNION ALL set operation. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@explain_mti_old.sql

DELETE FROM PLAN_TABLE;
COMMIT;

EXPLAIN PLAN FOR
INSERT INTO sales
(prod_id, cust_id, time_id, channel_id,promo_id,amount_sold,quantity_sold)
SELECT product_id, customer_id,weekly_start_date,2,9999,sales_sun,q_sun
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+1,2,9999,sales_mon,q_mon
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+2,2,9999,sales_tue,q_tue
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+3,2,9999,sales_wed,q_wed
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+4,2,9999,sales_thu,q_thu
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+5,2,9999,sales_fri,q_fri
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+6,2,9999,sales_sat,q_sat
FROM sales_input_table;

SET linesize 140
SELECT * from table(dbms_xplan.display);

COMMIT;

Note: The input source table is scanned seven times! The complexity of the denormalization is handled within the several SELECT operations.

With an increasing number of input records, the superiority and the performance improvement of the new multi-table insert statement—by reducing the statement to only one SCAN—will become more and more obvious.

3.

Now, you are ready to execute the following SQL script to perform the new multi-table insert:

@do_mti.sql

set timing on
alter system flush buffer_cache;

INSERT ALL
INTO sales
VALUES(product_id, customer_id,weekly_start_date,2,9999,q_sun,sales_sun)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+1,2,9999, q_mon,sales_mon)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+2,2,9999, q_tue,sales_tue)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+3,2,9999, q_wed,sales_wed)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+4,2,9999, q_thu,sales_thu)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+5,2,9999, q_fri,sales_fri)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+6,2,9999, q_sat,sales_sat)
SELECT * FROM sales_input_table;
Rollback;

4.

You can now compare the run-time statistics to the old way, using a UNION ALL pivoting approach:

@do_mti1_old.sql

set timing on
alter system flush buffer_cache;

INSERT INTO sales
(prod_id, cust_id, time_id, channel_id,promo_id,amount_sold,quantity_sold)
SELECT product_id, customer_id,weekly_start_date,2,9999,sales_sun,q_sun
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+1,2,9999,sales_mon,q_mon
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+2,2,9999,sales_tue,q_tue
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+3,2,9999,sales_wed,q_wed
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+4,2,9999,sales_thu,q_thu
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+5,2,9999,sales_fri,q_fri
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+6,2,9999,sales_sat,q_sat
FROM sales_input_table;

ROLLBACK;

1.

Create an intermediate table consisting of new information. From a SQL*Plus session logged on to the SH schema, execute the following SQL script, which contains the SQL statements from steps 1-4:

@mti2_prepare.sql

Rem create intermediate table with some records

CREATE
TABLE customers_new AS

SELECT *
FROM customers
WHERE cust_id BETWEEN 2000 AND 5000;

2.

Disable constraints on the SALES table; necessary for step 3.

ALTER TABLE sales DISABLE CONSTRAINT sales_customer_fk;

3.

Delete some data from the PRODUCTS table.

DELETE FROM customers WHERE cust_id BETWEEN 2000 AND 5000;

4.

Create an empty table for our special promotion information.

CREATE TABLE customers_special AS
SELECT cust_id, cust_credit_limit
FROM customers
WHERE rownum > 1;

5.

Issue the multi-table insert into several tables with different table structures. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@do_mti2.sql

INSERT /*+ APPEND NOLOGGING */FIRST
WHEN cust_credit_limit >= 4500 THEN
INTO customers
INTO customers_special VALUES (cust_id, cust_credit_limit)
ELSE
INTO customers
SELECT * FROM customers_new;

6.

Now, you can see what was inserted. Execute the following SQL script:

@control_mti2.sql

SELECT
COUNT(*) FROM customers;
SELECT
COUNT(*) FROM customers_special;
SELECT
MIN(cust_credit_limit) FROM customers_special;

7.

Before continuing you will want to reset your environment by executing the following SQL script:

@reset_mti2.sql

ALTER
TABLE sales
MODIFY
CONSTRAINT sales_customer_fk
RELY
ENABLE NOVALIDATE;

DROP TABLE customers_special;
DROP TABLE customers_new;

COMMIT;

1.

Create an external table (and directories) for the external products information.

2.

Perform an Upsert using the new SQL MERGE command.

3.

Show the execution plan of the new MERGE command.

4.

Perform an Upsert using two separate SQL commands (prior to Oracle9i functionality).

1.

To create the directory, perform the following. If you have already completed High-Speed Data Load and Rolling Window Operations, the directories will already exist and you can skip this step. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@create_directory.sql

DROP DIRECTORY data_dir;
DROP DIRECTORY log_dir;

Rem *****
Rem CREATE DIRECTORIES
Rem note that security is controlled
Rem (a) with privileges on directory (read/write)
Rem (b) with privileges on external table
Rem *****

CREATE
DIRECTORY data_dir AS '/home/oracle/wkdir/';
CREATE
DIRECTORY log_dir AS '/home/oracle/wkdir/';

2.

When creating an external table, you are defining two parts of information:

After creation of this meta information, the external data can be accessed from within the database, without the necessity of an initial load. To create the external table, from a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@create_external_table2.sql

Rem *****
Rem preparation for upsert
Rem file prodDelta.dat, 1.2 MB must
Rem reuse of directory STAGE_DIR
Rem access via external table
Rem *****

DROP TABLE products_delta;
CREATE TABLE products_delta
(
PROD_ID NUMBER(6),
PROD_NAME VARCHAR2(50),
PROD_DESC VARCHAR2(4000),
PROD_SUBCATEGORY VARCHAR2(50),
PROD_SUBCATEGORY_ID NUMBER,
PROD_SUBCATEGORY_DESC VARCHAR2(2000),
PROD_CATEGORY VARCHAR2(50),
PROD_CATEGORY_ID NUMBER,
PROD_CATEGORY_DESC VARCHAR2(2000),
PROD_WEIGHT_CLASS NUMBER(2),
PROD_UNIT_OF_MEASURE VARCHAR2(20),
PROD_PACK_SIZE VARCHAR2(30),
SUPPLIER_ID NUMBER(6),
PROD_STATUS VARCHAR2(20),
PROD_LIST_PRICE NUMBER(8,2),
PROD_MIN_PRICE NUMBER(8,2),
PROD_TOTAL VARCHAR2(13),
PROD_TOTAL_ID NUMBER,
PROD_SRC_ID NUMBER,
PROD_EFF_FROM DATE,
PROD_EFF_TO DATE,
PROD_VALID CHAR(1)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY "|" LDRTRIM
)
location
('prodDelta.dat')
REJECT LIMIT UNLIMITED NOPARALLEL;

1.

From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@do_merge_new.sql

Rem *****
Rem MERGE
Rem new and modified product information has arrived
Rem use of external table again
Rem - seemlessly within the MERGE command !!!
Rem *****

MERGE INTO products t
USING products_delta s
ON ( t.prod_id=s.prod_id )
WHEN MATCHED THEN
UPDATE SET t.prod_name=s.prod_name, t.prod_list_price=s.prod_list_price,
t.prod_min_price=s.prod_min_price
WHEN NOT MATCHED THEN
INSERT
(prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_id,
prod_subcategory_desc, prod_category, prod_category_id, prod_category_desc,
prod_status, prod_list_price, prod_min_price, prod_total, prod_total_id,
prod_weight_class, prod_pack_size, supplier_id)
VALUES
(s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory, s.prod_subcategory_id,
s.prod_subcategory_desc, s.prod_category, s.prod_category_id,
s.prod_category_desc,
s.prod_status, s.prod_list_price, s.prod_min_price, s.prod_total,
s.prod_total_id,
s.prod_weight_class, s.prod_pack_size, s.supplier_id)
;

ROLLBACK;

Note: You will ROLLBACK so that you can reissue the same upsert with two SQL statements in a subsequent operation.

1.

From a SQL*Plus session logged on to the SH schema, run explain_merge_new.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_merge_new.sql

DELETE FROM plan_table;

COMMIT;

EXPLAIN PLAN FOR
MERGE INTO products t
USING products_delta s
ON ( t.prod_id=s.prod_id )
WHEN MATCHED THEN
UPDATE SET t.prod_name=s.prod_name, t.prod_list_price=s.prod_list_price,
t.prod_min_price=s.prod_min_price
WHEN NOT MATCHED THEN
INSERT
(prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_id,
prod_subcategory_desc, prod_category, prod_category_id, prod_category_desc,
prod_status, prod_list_price, prod_min_price, prod_total, prod_total_id,
prod_weight_class, prod_pack_size, supplier_id)
VALUES
(s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory, s.prod_subcategory_id,
s.prod_subcategory_desc, s.prod_category, s.prod_category_id,
s.prod_category_desc,
s.prod_status, s.prod_list_price, s.prod_min_price, s.prod_total,
s.prod_total_id,
s.prod_weight_class, s.prod_pack_size, s.supplier_id)
;

set linesize 140

select * from table(dbms_xplan.display);

1.

To leverage an updatable join view, a primary or unique key must exist on the join column. Therefore, you cannot use the external table directly. As a result, you need to create an intermediate table in the database with the appropriate unique key constraint. Run the following script:

do_merge_816.sql

CREATE TABLE prod_delta NOLOGGING AS SELECT * FROM products_delta;

CREATE UNIQUE INDEX p_uk ON prod_delta(prod_id);

UPDATE
(SELECT s.prod_id, s.prod_name sprod_name, s.prod_desc sprod_desc,
s.prod_subcategory sprod_subcategory,
s.prod_subcategory_desc sprod_subcategory_desc, s.prod_category sprod_category,
s.prod_category_desc sprod_category_desc, s.prod_status sprod_status,
s.prod_list_price sprod_list_price, s.prod_min_price sprod_min_price,
t.prod_id, t.prod_name tprod_name, t.prod_desc tprod_desc, t.prod_subcategory
tprod_subcategory,
t.prod_subcategory_desc tprod_subcategory_desc, t.prod_category tprod_category,
t.prod_category_desc tprod_category_desc, t.prod_status tprod_status,
t.prod_list_price tprod_list_price, t.prod_min_price tprod_min_price
FROM products t, prod_delta s WHERE s.prod_id=t.prod_id) JV
SET
tprod_list_price =sprod_list_price,
tprod_min_price =sprod_min_price;

INSERT INTO products
(prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_id,
prod_subcategory_desc, prod_category, prod_category_id, prod_category_desc,
prod_status, prod_list_price, prod_min_price, prod_total, prod_total_id,
prod_weight_class, prod_pack_size, supplier_id)
SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_id,
prod_subcategory_desc, prod_category, prod_category_id, prod_category_desc,
prod_status, prod_list_price, prod_min_price, prod_total, prod_total_id,
prod_weight_class, prod_pack_size, supplier_id
FROM prod_delta s WHERE NOT EXISTS (SELECT 1 FROM products t WHERE t.prod_id =
s.prod_id);

ROLLBACK;

You have to issue two separate SQL statements to accomplish the same functionality than the new MERGE command. To leverage the updatable join view functionality of 8.1.6, we also had to copy the external table into a real database table and create a unique index on the join column. In short, more operations, more space requirements, and more processing time.

1.

Set up the basic objects for a table function.

2.

Perform a nonpipelined table function, returning an array of records.

3.

Perform a pipelined table function.

4.

Perform transparent parallel execution of table functions.

5.

Perform a table function with autonomous DML.

6.

Perform seamless streaming through several table functions.

1.

Define the object (record) type for the examples. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@setup_tf_record.sql

PROMPT object_types

CREATE TYPE product_t AS OBJECT (
prod_id NUMBER(6)
, prod_name VARCHAR2(50)
, prod_desc VARCHAR2(4000)
, prod_subcategory VARCHAR2(50)
, prod_subcategory_desc VARCHAR2(2000)
, prod_category VARCHAR2(50)
, prod_category_desc VARCHAR2(2000)
, prod_weight_class NUMBER(2)
, prod_unit_of_measure VARCHAR2(20)
, prod_pack_size VARCHAR2(30)
, supplier_id NUMBER(6)
, prod_status VARCHAR2(20)
, prod_list_price NUMBER(8,2)
, prod_min_price NUMBER(8,2)
);

This is the basic object record type you will use in the examples.

2.

Define the object (collection) type for the examples. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@setup_tf_collection.sql

CREATE TYPE product_t_table AS TABLE OF product_t;
/

This type represents the structure of the set of records, which will be delivered back by your table functions.

3.

Define a package for the REF CURSOR types. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@setup_tf_package.sql

Rem package of all cursor types
Rem handle the input cursor type and the output cursor collection type
CREATE OR REPLACE PACKAGE cursor_PKG as
TYPE product_t_rec IS RECORD (
prod_id NUMBER(6)
, prod_name VARCHAR2(50)
, prod_desc VARCHAR2(4000)
, prod_subcategory VARCHAR2(50)
, prod_subcategory_desc VARCHAR2(2000)
, prod_category VARCHAR2(50)
, prod_category_desc VARCHAR2(2000)
, prod_weight_class NUMBER(2)
, prod_unit_of_measure VARCHAR2(20)
, prod_pack_size VARCHAR2(30)
, supplier_id NUMBER(6)
, prod_status VARCHAR2(20)
, prod_list_price NUMBER(8,2)
, prod_min_price NUMBER(8,2));
TYPE product_t_rectab IS TABLE OF product_t_rec;
TYPE strong_refcur_t IS REF CURSOR RETURN product_t_rec;
TYPE refcur_t IS REF CURSOR;
END;

4.

Create a log table for the table function example with autonomous DML. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

CREATE TABLE obsolete_products_errors
(prod_id NUMBER, msg VARCHAR2(2000));

This table will be used for the table function example that fans out some data as part of its execution.

1.

From a SQL*Plus session logged on to the SH schema, run create_tf_1.sql, or copy the following SQL statements into your SQL*Plus session:

@create_tf_1.sql

PROMPT SIMPLE PASS-THROUGH, FILTERING OUT obsolete products without
product_category 'Electronics'

Rem uses weakly typed cursor as input

CREATE OR REPLACE FUNCTION obsolete_products(cur cursor_pkg.refcur_t) RETURN
product_t_table
IS
prod_id NUMBER(6);
prod_name VARCHAR2(50);
prod_desc VARCHAR2(4000);
prod_subcategory VARCHAR2(50);
prod_subcategory_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_category_desc VARCHAR2(2000);
prod_weight_class NUMBER(2);
prod_unit_of_measure VARCHAR2(20);
prod_pack_size VARCHAR2(30);
supplier_id NUMBER(6);
prod_status VARCHAR2(20);
prod_list_price NUMBER(8,2);
prod_min_price NUMBER(8,2);
sales NUMBER:=0;
objset product_t_table := product_t_table();
i NUMBER := 0;
BEGIN
LOOP

-- Fetch from cursor variable
FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched

-- Category Electronics is not meant to be obsolete and will be suppressed
IF prod_status='obsolete' AND prod_category != 'Electronics' THEN
-- append to collection
i:=i+1;
objset.extend;
objset(i):=product_t( prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price);
END IF;
END LOOP;
CLOSE cur;
RETURN objset;
END;
/

2.

You cannot transparently SELECT from the defined table function obsolete_products. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@use_tf_1.sql

SELECT DISTINCT UPPER(prod_category), prod_status
FROM TABLE(obsolete_products(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price
FROM products)));

1.

From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@create_tf_2.sql

Rem same example, pipelined implementation
Rem strong ref cursor (input type is defined)
Rem a table without a strong typed input ref cursor
Rem cannot be parallelized

CREATE OR REPLACE FUNCTION obsolete_products_pipe(cur cursor_pkg.strong_refcur_t)
RETURN product_t_table
PIPELINED
PARALLEL_ENABLE (PARTITION cur BY ANY) IS
prod_id NUMBER(6);
prod_name VARCHAR2(50);
prod_desc VARCHAR2(4000);
prod_subcategory VARCHAR2(50);
prod_subcategory_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_category_desc VARCHAR2(2000);
prod_weight_class NUMBER(2);
prod_unit_of_measure VARCHAR2(20);
prod_pack_size VARCHAR2(30);
supplier_id NUMBER(6);
prod_status VARCHAR2(20);
prod_list_price NUMBER(8,2);
prod_min_price NUMBER(8,2);
sales NUMBER:=0;
BEGIN
LOOP

-- Fetch from cursor variable
FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
IF prod_status='obsolete' AND prod_category !='Electronics' THEN
PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price));
END IF;
END LOOP;
CLOSE cur;
RETURN;
END;
/

2.

You can now transparently SELECT from the defined table function obsolete_products. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@use_tf_2.sql

SELECT DISTINCT prod_category, DECODE(prod_status,'obsolete','NO LONGER
AVAILABLE','N/A')
FROM TABLE(obsolete_products_pipe(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price
FROM products)));

1.

From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@pq_session.sql

SELECT *
FROM v$pq_sesstat
WHERE statistic in ('Queries Parallelized','Allocation Height');

2.

Now you can use one of the table functions in parallel. You enforce the parallel execution with a PARALLEL hint on the REF CURSOR. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@parallel_tf.sql

SELECT DISTINCT prod_category,
DECODE(prod_status,'obsolete','NO LONGER AVAILABLE','N/A')
FROM TABLE(obsolete_products_pipe(
CURSOR(SELECT /*+ PARALLEL(a,4)*/
prod_id, prod_name, prod_desc, prod_subcategory, prod_subcat_desc,
prod_category, prod_cat_desc, prod_weight_class, prod_unit_of_measure,
prod_pack_size, supplier_id,prod_status, prod_list_price,prod_min_price
FROM products a)));

3.

Rerun the SQL script to check the status of the parallelized queries:

@pq_session.sql

SELECT *
FROM v$pq_sesstat
WHERE statistic in ('Queries Parallelized','Allocation Height');

1.

From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@create_tf_3.sql

CREATE OR REPLACE FUNCTION obsolete_products_dml(cur cursor_pkg.strong_refcur_t,
prod_cat varchar2 DEFAULT 'Electronics') RETURN product_t_table
PIPELINED
PARALLEL_ENABLE (PARTITION cur BY ANY) IS
PRAGMA AUTONOMOUS_TRANSACTION;
prod_id NUMBER(6);
prod_name VARCHAR2(50);
prod_desc VARCHAR2(4000);
prod_subcategory VARCHAR2(50);
prod_subcategory_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_category_desc VARCHAR2(2000);
prod_weight_class NUMBER(2);
prod_unit_of_measure VARCHAR2(20);
prod_pack_size VARCHAR2(30);
supplier_id NUMBER(6);
prod_status VARCHAR2(20);
prod_list_price NUMBER(8,2);
prod_min_price NUMBER(8,2);
sales NUMBER:=0;
BEGIN
LOOP
-- Fetch from cursor variable
FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
IF prod_status='obsolete' THEN
IF prod_category=prod_cat THEN
INSERT INTO obsolete_products_errors VALUES
(prod_id, 'correction: category '||UPPER(prod_cat)||' still available');
COMMIT;
ELSE
PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price));
END IF;
END IF;
END LOOP;
CLOSE cur;
RETURN;
END;
/

2.

Now you can select from this table function and check the log table. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@use_tf_3a.sql

TRUNCATE
TABLE obsolete_products_errors;

SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price
FROM products)));

SELECT DISTINCT msg FROM obsolete_products_errors;

3.

Select from this table function again with a different input argument and check the log table.

@use_tf_3b.sql

TRUNCATE TABLE obsolete_products_errors;

SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price
FROM products),'Photo'));

SELECT DISTINCT msg FROM obsolete_products_errors;

You see that, dependent on the input variable, the table function processes the input rows differently, thus delivering a different result set and inserting different information into obsolete_products_error.

1.

This SQL statement uses two table functions, nested in each other. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@use_tf_stream.sql

SELECT DISTINCT prod_category, prod_status
FROM TABLE(obsolete_products_dml(CURSOR(SELECT *
FROM TABLE(obsolete_products_pipe(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price
FROM products))))));

SELECT COUNT(*) FROM obsolete_products_errors;

SELECT DISTINCT msg FROM obsolete_products_errors;
set echo off

2.

Now you can use this table function as INPUT for a CREATE TABLE AS SELECT command. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@CTAS_tf.sql

CREATE TABLE PIPE_THROUGH AS
SELECT DISTINCT prod_category, prod_status
FROM TABLE(obsolete_products_dml(CURSOR(SELECT *
FROM TABLE(obsolete_products_pipe(
CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class,
prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price,
prod_min_price
FROM products))))));

3.

Before you move to the next section of this tutorial, you need to clean up your environment by executing the following script:

@cleanup_tf.sql

DROP TABLE obsolete_products_errors;
DROP TABLE pipe_through;
DROP FUNCTION obsolete_products;
DROP FUNCTION obsolete_products_pipe;
DROP FUNCTION obsolete_products_dml;

Determines the relational tables (called source tables) from which the data warehouse application is interested in capturing change data.

Uses the Oracle supplied package, DBMS_LOGMNR_CDC_PUBLISH, to set up the system to capture data from one or more source tables.

Publishes the change data in the form of change tables.

Allows controlled access to subscribers by using the SQL GRANT and REVOKE statements to grant and revoke the SELECT privilege on change tables for users and roles.

Use the Oracle supplied package, DBMS_LOGMNR_CDC_SUBSCRIBE, to subscribe to source tables for controlled access to the published change data for analysis.

Extend the subscription window and create a new subscriber view when the subscriber is ready to receive a set of change data.

Use SELECT statements to retrieve change data from the subscriber views.

Drop the subscriber view and purge the subscription window when finished processing a block of changes.

Drop the subscription when the subscriber no longer needs its change data.

1.

Use synchronous CDC to track all the incremental changes.

2.

Create a change table.

3.

Subscribe to a change set and to all source table columns of interest.

4.

Activate a subscription and extend the subscription window.

5.

Investigate how to handle the new environment over time.

6.

Run the publisher.

7.

Drop the used change view and purge the subscription window.

8.

Clean up the CDC environment.

1.

First create a new intermediate table where you apply all changes. From a SQL*Plus session logged on to the SH schema, run cr_cdc_target.sql, or copy the following SQL statements into your SQL*Plus session:

@cr_cdc_target.sql

CREATE TABLE my_price_change_Electronics
(prod_id number, prod_min_price number, prod_list_price number, when date);

2.

If you never used Change Data Capture before, you won't see any objects related to CDC other than the synchronous change set SYNC_SET and the synchronous change source SYNC_SOURCE. Both of these objects are created as part of the database creation.

@show_cdc_env1.sql

SELECT * FROM change_sources;

PROMPT see the change tables
Rem shouldn't show anything

SELECT * FROM change_tables;

PROMPT see the change sets SYNC_SET

SELECT decode(to_char(end_date,'dd-mon-yyyy hh24:mi:ss'),null,'No end date set.')
end_date,
decode(to_char(freshness_date,'dd-mon-yyyy hh24:mi:ss'),null,'No freshness
date set.') freshness_date
FROM change_sets WHERE set_name='SYNC_SET';

1.

You create a change table by using the provided package DBMS_CDC_PUBLISH.

Note that the old package name DBMS_CDC_LOGMNR_PUBLISH is a synonym pointing to DBMS_CDC_PUBLISH and is maintainted as-is for backward compatibility.

@cr_cdc_ct.sql

rem create a change table within the change set SYNC_SET.
Rem details on parameters see doc

PROMPT *** 10g ***
PROMPT NOTE THAT DBMS_LOGMNR_CDC_* are synonyms for the DBMS_CDC_* packages and are only around
PROMPT for backwards compatibility

Begin
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE (OWNER => 'SH', -
CHANGE_TABLE_NAME => 'PROD_price_CT',
CHANGE_SET_NAME => 'SYNC_SET',
SOURCE_SCHEMA => 'SH',
SOURCE_TABLE => 'PRODUCTS',
COLUMN_TYPE_LIST => 'prod_id number(6), prod_min_price number(8,2),
prod_list_price number(8,2)',
CAPTURE_VALUES => 'both',
RS_ID => 'y',
ROW_ID => 'n',
USER_ID => 'n',
TIMESTAMP => 'n',
OBJECT_ID => 'n',
SOURCE_COLMAP => 'y',
TARGET_COLMAP => 'y',
OPTIONS_STRING => null);
End;
/

This script creates a change table called PROD_PRICE_CT and the necessary trigger framework to track all subsequent changes on PRODUCTS. Note that the tracking is done as part of the atomic DML operation against PRODUCTS.

2.

Describe the change table PROD_PRICE_CT:

Desc prod_price_ct

See the static metadata columns as well as the columns PROD_ID, PROD_MIN_PRICE, and PROD_LIST_PRICE (equivalent to source table PRODUCTS).

3.

To see the metadata of the published source table and the change tables in the system, issue the following SQL commands:

@show_cdc_env2.sql

prompt see the published source tables
SELECT * FROM dba_source_tables;

prompt see published source columns
SELECT source_schema_name,source_table_name, column_name
FROM dba_source_tab_columns;

prompt see the change tables
SELECT * FROM change_tables;

1.

You must get a unique subscription handle that is used throughout the session and tell the system what columns you are interested in. You are using the existing change set SYNC_SET. This functionality is provided by the package DBMS_CDC_SUBSCRIBE.

@subs_cdc_ct.sql

variable subname varchar2(30)
begin
:subname := 'my_subscription_no_1';
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION (
CHANGE_SET_NAME => 'SYNC_SET',
DESCRIPTION => 'Change data PRODUCTS for ELECTRONICS',
SUBSCRIPTION_name => :subname);
END;
/

PRINT subname

2.

You can query the meta information about your subscription.

@show_cdc_env3.sql

SELECT handle, description,
decode(status,'N','Not activated.','Activated.') status,
earliest_scn, latest_scn, decode(last_purged,null,'Never purged.',
to_char(last_purged,'dd-mon-yyyy hh24:mi:ss')) last_purged,
decode(last_extended, null,'Never extended.',
to_char(last_extended,'dd-mon-yyyy hh24:mi:ss')) last_extended
FROM
user_subscriptions;

SELECT * FROM user_subscribed_tables;
SELECT source_table_name, column_name
FROM dba_subscribed_columns;

3.

After creating a subscription, you set up a change view for this subscription. The complete handling of change views was enhanced with Oracle Database 10g: you can name a change table or you do not have to drop change views to extend and/or purge subscriber windows.

@cr_cdc_rv.sql

rem now subscribe to a single source table and columns of interest.
rem A subscription can contain one or more tables from the same change set.

PROMPT ***10g***
Rem use new interface to name subscriber view explictly !!
variable view_name varchar2(30);

BEGIN
:view_name := 'my_prod_price_change_view';
DBMS_CDC_SUBSCRIBE.SUBSCRIBE (
SUBSCRIPTION_NAME => :subname,
SOURCE_SCHEMA => 'sh',
SOURCE_TABLE => 'products',
COLUMN_LIST => 'prod_id, prod_min_price, prod_list_price',
subscriber_view => :view_name );
END;
/

PROMPT ***10g***
Rem The view is created automatically
desc MY_PROD_PRICE_CHANGE_VIEW

On the publisher site you prepared a source system for CDC.

On the subscriber site, you identified and subscribed to all source tables (and columns) of interest and defined/created our Change View.

1.

Activate and extend a subscription window. You will also have a look into the metadata.

@sub_cdc1.sql

rem now activate the subscription since we are ready to receive
rem change data the ACTIVATE_SUBSCRIPTION procedure sets
rem subscription window to empty initially
rem At this point, no additional source tables can be added to the
rem subscription

EXEC
DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION -
(SUBSCRIPTION_name => 'my_subscription_no_1');

rem now recheck the subscriptions and see that it is active
rem the view is still not created ...

SELECT handle, description,
decode(status,'N','Not activated.','Activated.') status, earliest_scn, latest_scn,
decode(last_purged,null,'Never purged.',
to_char(last_purged,'dd-mon-yyyy hh24:mi:ss')) last_purged,
decode(last_extended, null,'Never extended.',
to_char(last_extended,'dd-mon-yyyy hh24:mi:ss')) last_extended
from user_subscriptions;

2.

Any changes you apply now to the source table PRODUCTS is reflected in the change table. The changes are transparently maintained with triggers on the source table.

@dml_cdc1.sql

Rem now do some changes

UPDATE products
SET prod_list_price=prod_list_price*1.1
WHERE prod_min_price > 100;
COMMIT;

Rem you will see entries in the change table
Rem note that we have entries for the old and the new values

SELECT Count(*) FROM prod_price_ct;

Note that you have two records for each source row, one representing the old, and the other the new value set for the subscribed columns.

Again, please never use the change table itself for identifying the changes on a source table. Use the subscriber view that will be created for you. This is the only supported way to guarantee that every change is delivered only once to a subscribing application.

3.

Unlike the change table, the change view does not show any records yet. This is because you applied the DML operation AFTER you extended the subscription window. Consequently you must not see anything yet.

Rem the view does not show anything
select count(*) from MY_PROD_PRICE_CHANGE_VIEW;

You are now preparing for consuming the changes by extending the subscription window.

@ext_cdc_sub1.sql

rem now set upper boundary (high-water mark) for the subscription window
rem At this point, the subscriber has created a new window that begins
rem where the previous window ends.
rem The new window contains any data that was added to the change table.

EXEC DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW -
(SUBSCRIPTION_NAME => 'my_subscription_no_1');

rem now recheck the subscriptions and see that it is active

SELECT handle, description,
decode(status,'N','Not activated.','Activated.') status,
earliest_scn, latest_scn,
decode(last_purged,null,'Never purged.',
to_char(last_purged,'dd-mon-yyyy hh24:mi:ss')) last_purged,
decode(last_extended, null,'Never extended.',
to_char(last_extended,'dd-mon-yyyy hh24:mi:ss')) last_extended
from user_subscriptions;

Rem ... and you will see data
SELECT count(*) FROM my_prod_price_change_view;

4.

You can now select from this system-generated change view, and you will see only the changes that have happened for your time window.

@sel_cdc_cv1.sql

Rem changes classified for specific product groups

SELECT p1.prod_id, p2.prod_category, p1.prod_min_price,
p1.prod_list_price, commit_timestamp$
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id
AND operation$='UN';

PROMPT and especially the Electronics' ones - 3 records only

SELECT p1.prod_id, p2.prod_category, p1.prod_min_price,
p1.prod_list_price, commit_timestamp$
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id
AND operation$='UN'
AND p2.prod_category='Electronics';

In the example shown above, you are joining back to the source table PRODUCTS. When you set up a CDC environment, please ensure that such an operation should not be necessary; if possible, a change table should be usable as stand-alone source for synchronizing your target environment. Joining back to the source table may reduce the data stored on disk, but it creates additional workload on the source site. Join-back cannot be accomplished on a different system than the source system.

5.

You can now consume the changes on your target system.

@consume_cdc1.sql

Prompt ... you can now consume the changes
rem arbitrary example, where we only track ELECTRONICS changes. This shall demonstrate
rem the flexibility as well as the responsibility of the client site (consumer) to deal
rem appropriately with the changes

INSERT into my_price_change_electronics
SELECT p1.prod_id, p1.prod_min_price, p1.prod_list_price, commit_timestamp$
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id
AND p2.prod_category='Electronics' AND operation$='UN';

COMMIT;

SELECT prod_id, prod_min_price, prod_list_price,
to_char(when,'dd-mon-yyyy hh24:mi:ss')
FROM my_price_change_electronics;

1.

Other DML operations take place on the source table PRODUCTS.

@dml_cdc2.sql

PROMPT other changes will happen
UPDATE products
SET prod_min_price=prod_min_price*1.1
WHERE prod_min_price < 10;

COMMIT;

2.

The synchronous CDC framework tracks these DML operations transparently in the change table.

@show_cdc_ct2.sql

SELECT count(*) FROM prod_price_ct ;

PROMPT and especially the ELECTRONICS' ones

SELECT COUNT(*) FROM prod_price_ct p1, products p2
WHERE p1.prod_id=p2.prod_id AND p2.prod_category='Electronics';

3.

@sel_cdc_cv1.sql

Rem changes classified for specific product groups

SELECT p1.prod_id, p2.prod_category, p1.prod_min_price,
p1.prod_list_price, commit_timestamp$
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id
AND operation$='UN';

PROMPT and especially the Electronics' ones - 3 records only

SELECT p1.prod_id, p2.prod_category, p1.prod_min_price,
p1.prod_list_price, commit_timestamp$
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id
AND operation$='UN'
AND p2.prod_category='Electronics';

You haven't extended the time window for the change view yet.

4.

To tell the CDC framework that you are done with a set of changes, you simply have to purge and extend the subscrtiption.

@purge_cdc_sub_window1.sql

PROMPT purge old data from the subscription window

Rem this will NOT delete any records from the change table. It just tells the CDC system
Rem that the changed data used so fasr is no longer needed

EXEC DBMS_CDC_SUBSCRIBE.PURGE_WINDOW -
(SUBSCRIPTION_name => 'my_subscription_no_1');
PROMPT still the same number of records in the change table
PROMPT REMEMBER THE NUMBER OF ROWS !!!
SELECT COUNT(*) FROM prod_price_ct;
PROMPT ... change view is empty

SELECT COUNT(*) FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id AND p2.prod_category='Electronics';

5.

To get the new changes reflected in the change view, you have to extend the time window for the change view.

@ext_cdc_sub_window1.sql

PROMPT let's get the new change
Rem 'do it again Sam'
Rem first extend the window you want to see
EXEC DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW -
(SUBSCRIPTION_name => 'my_subscription_no_1');
Rem ... now you will see exactly the new changed data since the last consumption
SELECT COUNT(*) FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id AND p2.prod_category='Electronics';

The new change view shows exactly the changes that were not consumed yet. This is different than the content in the change table.

6.

Now consume the new changes. Because the incremental changes you are interested in are stored in a change table (a "normal" Oracle table), you can use any language or SQL construct the database supports.

@consume_cdc2.sql

PROMPT the new changes will be used

MERGE INTO my_price_change_Electronics t
USING (
SELECT p1.prod_id, p1.prod_min_price, p1.prod_list_price, commit_timestamp$
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id AND p2.prod_category='Electronics' AND
operation$='UN') cv
ON (cv.prod_id = t.prod_id)
WHEN MATCHED THEN
UPDATE SET t.prod_min_price=cv.prod_min_price,
t.prod_list_price=cv.prod_list_price
WHEN NOT MATCHED THEN
INSERT VALUES (cv.prod_id, cv.prod_min_price, cv.prod_list_price,
commit_timestamp$);

COMMIT;

rem look at them

SELECT prod_id, prod_min_price, prod_list_price,
to_char(when,'dd-mon-yyyy hh24:mi:ss')
FROM my_price_change_electronics;

1.

From a SQL*Plus session logged on to the SH schema, run purge_ct.sql, or copy the following SQL statements into your SQL*Plus session:

@purge_cdc_ct.sql

PROMPT what to do to avoid overflow of change table ? PURGE IT !

Rem only the rows where we have no potential subscribers will be purged !!!

Rem we guarantee to keep all rows as long as we have subscribers which haven't
rem purged their window ...

exec DBMS_CDC_PUBLISH.purge_change_table('sh','prod_price_ct')
PROMPT REMEMBER THE NUMBER OF ROWS !!!

Rem you will have 18 entries for the last updated 9 records

SELECT COUNT(*) FROM prod_price_ct;

PROMPT this is exactly twice the number of changes we made with the second DML operation

SELECT COUNT(*) FROM prod_price_ct p1, products p2
WHERE p1.prod_id=p2.prod_id AND p2.prod_category='Electronics';

Although we haven’t specified any specific time window for the purge operation of the change table, the CDC framework ensures that only those changes will be purged that are no longer needed by a subscriber.

1.

Because you consumed the second set of change already, you can drop your used change view and purge your subscription window. You will also purge the change table.

@purge_cdc_sub_window2.sql

PROMPT ... purge the newly consumed data from the subscription window (2nd DML operation))

EXEC DBMS_CDC_SUBSCRIBE.PURGE_WINDOW -
(SUBSCRIPTION_name =>'my_subscription_no_1');

PROMPT purge all change tables again

EXEC DBMS_CDC_PUBLISH.purge_change_table('sh','prod_price_ct')

PROMPT ... and you will see an empty change

SELECT * FROM prod_price_ct;

The change table is now empty. All changes are consumed and can be purged.

1.

From a SQL*Plus session logged on to the SH schema, run cleanup_cdc.sql, or copy the following SQL statements into your SQL*Plus session:

@cleanup_cdc.sql

PROMPT CLEAN UP

exec DBMS_CDC_SUBSCRIBE.drop_subscription -
(subscription_name=> 'my_subscription_no_1');

exec DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE (OWNER => 'sh', -
CHANGE_TABLE_NAME => 'prod_price_CT', -
FORCE_FLAG => 'Y');

DROP TABLE my_price_change_electronics;

UPDATE products SET prod_list_price=prod_list_price/1.1
WHERE prod_min_price > 100;

UPDATE products SET prod_list_price=prod_list_price/1.1
WHERE prod_min_price < 10;

COMMIT;

1.

Enable a RESUMABLE session.

2.

Create a new tablespace as a potential transportable tablespace.

3.

Create a LIST partitioned table in the new tablespace.

4.

Leverage the new RESUMABLE statement capabilities for efficient error detection and handling.

5.

Create a new RANGE_LIST partitioned table (this is Oracle9i Release 2 functionality).

6.

Prepare the metadata export for a transportable tablespace.

7.

Import the tablespace into another database.

1.

This SQL statement uses two table functions, nested in each other. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@set_resumable.sql

ALTER SESSION ENABLE RESUMABLE TIMEOUT 1200 NAME
'create list partitioning';

This brings your session in the so-called resumable mode, names it, and enables a maximum suspension time of 1200 seconds.

1.

First, make sure that the potential tablespace TRANSFER does not exist. From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@drop_TS.sql

DROP TABLESPACE my_obe_transfer INCLUDING CONTENTS AND DATAFILES;

The tablespace is dropped. If you get an ORA-959 message, you can ignore it; this means that the tablespace does not exist.

2.

From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

NOTE: If you are using a different drive or path for your data files, the script will need to be edited accordingly.

@create_ts.sql

CREATE TABLESPACE my_obe_transfer DATAFILE '/tmp/tt' SIZE 2M REUSE autoextend off;

The tablespace is intentionally defined too small for the target table to enforce a space error during table creation.

1.

From a SQL*Plus session logged on to the SH schema, run create_list_part_table.sql.

NOTE: This statement will hang due to space problems in the TRANSFER tablespace and show up in the alert log. When it hangs move to the next step.

@create_list_part_table.sql

DROP TABLE sales_prod_dept;

PROMPT create table in new TS that is too small

CREATE TABLE sales_prod_dept
(prod_category, prod_subcategory,cust_id,
time_id,channel_id,promo_id, quantity_sold, amount_sold
) NOLOGGING TABLESPACE my_obe_transfer
PARTITION BY LIST (prod_category)
(PARTITION electronic_sales values ('Electronics'),
PARTITION hardware_sales values ('Hardware'),
PARTITION sw_other_sales values ('Software/Other'),
PARTITION p_and_a values ('Peripherals and Accessories'),
PARTITION photo_sales values ('Photo')
)

AS
SELECT p.prod_category, p.prod_subcategory, s.cust_id, s.time_id,s.channel_id,
s.promo_id, SUM(s.amount_sold) amount_sold, SUM(s.quantity_sold) quantity_sold
FROM sales s, products p, times t
WHERE p.prod_id=s.prod_id
AND s.time_id = t.time_id
AND t.fiscal_year=2000
GROUP BY prod_category, prod_subcategory,cust_id, s.time_id,channel_id, promo_id
;

1.

From a SQL*Plus session logged on to the SH schema, execute the following SQL statement:

SELECT NAME, STATUS, ERROR_MSG FROM dba_resumable;

As long as no error occurs, the session stays in a normal status. As soon as an error occurs, your session status changes, and the ORA-error causing this problem is displayed. The current statement in the RESUMABLE session will be suspended until either (a) the problem is fixed, or (b) the timeout limit is reached. The error will also show up in the alert.log file of the running instance.

2.

Now fix the problem manually. Issue the individual tablespace/datafile definitions of the following commands. From a SQL*Plus session logged on to the SYSTEM Schema, execute the following SQL script:

@fix_ts.sql

ALTER DATABASE DATAFILE '/tmp/tt' AUTOEXTEND ON NEXT 5M;

The tablespace can now be autoextended by the database itself without any further interaction.

3.

As soon as the error is fixed, the suspended session automatically resumes and continues processing. Check the data dictionary view for change of status. From a SQL*Plus session logged on to the SH schema, check the status again:

SELECT NAME, STATUS, ERROR_MSG FROM dba_resumable;

The resumption of the statement also shows up in the alert.log file of the running instance and in the previously hung window.

1.

From a SQL*Plus session logged on to the SH schema, run the following SQL statements into your SQL*Plus session:

@create_new_range_list.sql

CREATE TABLE sales_rlp
COMPRESS
TABLESPACE MY_OBE_TRANSFER
PARTITION BY RANGE (time_id)
SUBPARTITION BY LIST (channel_id)
SUBPARTITION TEMPLATE
( SUBPARTITION direct values (3),
SUBPARTITION internet values (4),
SUBPARTITION partner values (2),
SUBPARTITION other values (DEFAULT)
)
(PARTITION SALES_before_1999 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) NOCOMPRESS)
AS
SELECT * FROM sales sample(10);

PROMPT as you can see, the partition and template name is inherited to all the subpartitions,
PROMPT thus making the naming - and identification - of subpartitions much easier ...

SELECT partition_name, subpartition_name, high_value from user_tab_subpartitions
WHERE table_name='SALES_RLP';

This example also shows how you can easily create compressed and uncompressed partitions as part of an initial table creation. The partition and template names are inherited by all the subpartitions, thus making the naming and identification of subpartitions much easier and more convenient.

2.

From a SQL*Plus session logged on to the SH schema, run the following SQL statements into your SQL*Plus session:

@show_range_list_names.sql

PROMPT as you can see, the partition and template name is inherited to all the subpartitions,
PROMPT thus making the naming - and identification - of subpartitions much easier ...

select partition_name, subpartition_name, high_value from user_tab_subpartitions
where table_name='SALES_RLP';

A List and Range-List partitioned table group sets distinct unrelated values together in one partition. Consequently, all values not being covered with an existing partition definition will raise an Oracle error message. We will now create a DEFAULT List Partition for the previously created List partitioned table sales_prod_dept, a kind of catch-all partition for all undefined partition key values.

3.

From a SQL*Plus session logged on to the SH schema, run the following SQL statements into your SQL*Plus session:

@cr_default_list_part.sql

PROMPT add a new partition to sales_prod_dept that does NOT have a DEFAULT partition
Rem it is added just like for a range partitioned table
ALTER TABLE sales_prod_dept ADD PARTITION gameboy_sales VALUES ('Gameboy');
PROMPT now add another one, covering the DEFAULT value
ALTER TABLE sales_prod_dept ADD PARTITION all_other_sales VALUES (DEFAULT);
PROMPT control the data dictionary

select partition_name, high_value from user_tab_partitions where
table_name='SALES_PROD_DEPT';

All records having an undefined partitioning key will be stored in this new partition other_sales. However, having a DEFAULT partition changes the way you’re adding new partitions to this table. Conceptually, you cover all possible values for the partitioning key—the defined ones and "all others"—as soon as you have created a default partition.

So, to ADD a new partition to this table, you logically have to SPLIT the default partition into a new partition with a set of defined keys and a new default partition, still covering "all other values" (now reduced by the keys we have specified for the new partition). Any attempt to add a new partition like you did before will raise an ORA error message.

4.

From a SQL*Plus session logged on to the SH schema, run the following SQL statements into your SQL*Plus session:

@split_default_list_part.sql

PROMPT Unlike the first time, we cannot simply add a new partition
PROMPT raises ORA-14323: cannot add partition when DEFAULT partition exists
Rem we cannot be sure whether the new value already exists in the DEFAULT partition

ALTER TABLE sales_prod_dept ADD PARTITION undefined_sales VALUES ('Undefined');

This will raise an Oracle error. Run the following commmands or the script split_default_list_part_b.sql

PROMPT so we have to SPLIT the default partition to ensure that
PROMPT all potential values of 'Undefined' are in the new partition

ALTER TABLE sales_prod_dept SPLIT
PARTITION other_sales VALUES ('Undefined') INTO
(PARTITION undefined_sales, PARTITION other_sales);

PROMPT control the data dictionary
PROMPT Note that without specifying any tablespace, the default
PROMPT tablespace of the partitioned table is used

select partition_name, tablespace_name, high_value
from user_tab_partitions
where table_name='SALES_PROD_DEPT';

1.

From a SQL*Plus session logged on to the SH schema, execute the following SQL script:

@make_ts_ro.sql

ALTER TABLESPACE my_obe_transfer READ ONLY;

This guarantees that no further changes can happen on the data in the tablespace.

2.

You can now export the data dictionary information of tablespace my_obe_ transfer as follows:

@export_metadata.sql

Optionally, you can create a specific directory where you want to store the export dump file:

CREATE DIRECTORY my_obe_dump_dir as '/home/oracle/wkdir/';

Now you can export the metadata only. Please compare the file size with the tablespace size to get a feeling what it would have meant to extract all the data and not only the metadata.

expdp \'/ as sysdba\' DIRECTORY=my_obe_dump_dir DUMPFILE= meta_MY_OBE_TRANSFER.dmp

Note: The same export syntax can be used at the DOS prompt by dropping the SQL*Plus "host" command.

3.

Before you import the tablespace information into another database, you will want to clean up and reset the session status by executing the SQL script:

@cleanup_tts.sql

DROP TABLESPACE transfer INCLUDING CONTENTS AND DATAFILES;
ALTER SESSION DISABLE RESUMABLE;

4.

Trying to access table sales_prod_dept raises an error. The table does not exist any longer.

SELECT count(*)
from sales_prod_dept;

1.

From a SQL*Plus session logged on to the SH schema, execute the following commands:

SET SERVEROUTPUT ON

EXEC dw_handsOn.cleanup_modules