| |
Using the ETL Infrastructure of Oracle9i
Module Objectives
Purpose
In this module, you will learn how to use the Extraction, Transformation,
and Loading (ETL) infrastructure of Oracle9i.
Objectives
After completing this module, you should be able to:
 |
Perform a multi-table Insert |
|
Compare the new Upsert functionality, implemented
as SQL command MERGE, with its manual implementation prior to Oracle9i |
|
Create and use table functions |
|
Use synchronous Change Data Capture to track - and consume
- incremental data changes |
|
Combine components of Oracle9i’s ETL infrastructure
into an efficient, parallel, and seamless transformation process without
staging |
|
Propagate information for a data mart by
leveraging the new LIST partitioning from Oracle9i,
new RANGE_LIST partitioning from Oracle9i
Release 2, the transportable tablespace mechanism, and the new RESUMABLE
statement capabilities |
Prerequisites
Before starting this module, you should have:
Reference Material
The following is a list of useful reference materials if you want additional
information about the topics in this module:
|
Documentation: Data Warehousing Guide
|
Overview
What Happens During the ETL Process?
What happens during the ETL process? During Extraction, the desired
data has to be identified and extracted from many different sources, including
database systems and applications. Very often, it’s not possible to identify
the specific subset of interest, therefore more data than necessary has
to be extracted, and the identification of the relevant data will be done
at a later point in time. Depending on the source system’s capabilities
(e.g., OS resources), some transformations may take place during this
extraction process. The size of the extracted data varies from hundreds
of kilobytes to hundreds of gigabytes, depending on the source system
and the business situation. The same is true for the time delta between
two (logically) identical extractions: the time span may vary between
days/hours and minutes to near real-time. Web server log files for example
can easily become hundreds of megabytes in a very short period of time.
After extracting data, it has to be physically transported to the target
system or an intermediate system for further processing. Depending on
the chosen way of transportation, some transformations can be done during
this process, too. For example, a SQL statement, which directly accesses
a remote target through a gateway, can concatenate two columns as part
of the SELECT.
After extracting and transporting the data, the most challenging (and
time consuming) part of ETL follows: Transformation and Loading
into the target system.
This may include:
|
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. |
This should be done as quickly as possible in a scaleable manner and
must not affect the existing target with respect to concurrent access
for information retrieval.
Oracle9i offers
a wide variety of new capabilities to address all the issues and tasks
relevant in an ETL scenario. It is important to understand that the database
offers an infrastructure rather than trying to address a ‘one-size- fits-all’
solution. The underlying database has to enable the most appropriate ETL
process flow for a specific customer need and not to dictate or constrain
it from a technical perspective.
Become familiar with the new Multi-Table Insert
MyCompany gets some nonrelational data structures from one of its partner
companies, which sells our products for a special advertisement campaign.
The data structure is a denormalized, nonrelational record structure from
a proprietary mainframe systems consisting of one record per customer
and product per week.
Those data structures have to be inserted into our data warehouse. Since
we’re storing our sales record data per customer and product per day,
we have to transform the incoming data.
As part of the transformation, the nonrelational denormalized data structure
must be transformed from one record per week into seven records, each
consisting the information of a business day (first example business transformation).
In addition, our data warehouse keeps track of all new customers with
a credit_limit above a certain limit. Those customers should be tracked
in addition separately
We’re implementing those business transformations by leveraging Oracle
9i’s new multi-table
insert capabilities.
| 1. |
Ensure that the appropriate partition is existent |
| 2. |
Insert the appropriate Promotion record in the dimension
table PROMOTIONS to avoid constraint violations |
| 3. |
Use Multi-table insert for the first example business
transformation
- Pivoting of data and insertion into the same table |
| 4. |
Use Multi-table insert for the second example business
transformation
- Conditional insertion of the same data into multiple tables with
different structures |
1. ENSURE THAT THE APPROPRIATE PARTITION IS EXISTENT
Unless you finished the previous module, Using
High-Speed Data Load and Rolling Window Operations, you will need
to create a new, empty partition for your SALES
fact table. To do this, you perform the following steps:
| 1. |
Execute the following query to see if the partition sales_q1_2001
already exists:
@select_partition.sql
SET PAGESIZE 999
COLUMN partition_name FORMAT A15
COLUMN high_value FORMAT A60
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name= 'SALES'
ORDER BY partition_position;
|
| 2. |
If the partition sales_q1_2001
does not exist, execute the following
SQL, logged in as the database user SH:
@create_partition_for_sales2.sql
Rem *****
Rem create ADDITIONAL PARTITION on sales
Rem *****
ALTER TABLE sales ADD PARTITION sales_q1_2001
VALUES LESS THAN (TO_DATE('01-APR-2001','DD-MON-YYYY'));
Rem *****
Rem what is in the partition now?
Rem empty
Rem *****
SELECT COUNT(*)
FROM sales PARTITION (sales_q1_2001);
|
2. Insert the Appropriate Promotion Information
You have to insert a new record into the PROMOTIONS
table to avoid a constraint violation during the multi-table insert. To
do this, you perform the following steps:
| 1. |
From a SQL*Plus session logged on to the SH schema, issue the following
command:
@insert_into_promo.sql
INSERT INTO promotions VALUES
(501,'special partner ','ad magazine','magazine', 10243,
TO_DATE('01-JAN-2001','dd-mon-yyyy'),
TO_DATE('01-APR-2001','dd-mon-yyyy'),
'Promotion total');
COMMIT;
|
| 2. |
Control the validity of the PROMOTIONS
dimension by executing the following command:
@validate_dimension.sql
EXECUTE dbms_olap.validate_dimension('promotions_dim',
'sh',false,true)
SELECT COUNT(*)
FROM mview$_exceptions;
The database object dimension helps to organize and group dimensional
information into hierarchies. This represents natural 1:n relationships
between columns or column groups (the levels of a hierarchy) that
cannot be represented with constraint conditions. Going up a level
in the hierarchy is called rolling up the data and going down a
level in the hierarchy is called drilling down the data.
The information of a dimension object is declarative only and not
enforced by the database. If the relationships described by the
dimensions are incorrect, incorrect results can occur. Therefore,
you should verify the relationships specified by CREATE
DIMENSION using the
DBMS_OLAP.VALIDATE_DIMENSION procedure periodically.
|
Please see Using
Basic Database Functionality for Data Warehousing for more information
about Oracle’s database object DIMENSION and its usage for Query
Rewrite.
3. Use the Multi-Table Insert for Pivoting
Before issuing the multi-table insert, you want to investigate plans
for this multi-table insert and its implementation prior to Oracle9i.
To do this, you perform the following steps:
| 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,'P',
501,q_sun,sales_sun)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+1,'P',
501,q_mon,sales_mon)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+2,'P',
501,q_tue,sales_tue)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+3,'P',
501,q_wed,sales_wed)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+4,'P',
501,q_thu,sales_thu)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+5,'P',
501,q_fri,sales_fri)
INTO sales
VALUES(product_id, customer_id,weekly_start_date+6,'P',
501,q_sat,sales_sat)
SELECT *
FROM sales_input_table;
set linesize 120
set pagesize 999
@?/rdbms/admin/utlxpls
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
TRUNCATE TABLE PLAN_TABLE;
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,'P',
501,sales_sun,q_sun
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+1,'P',
501,sales_mon,q_mon
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+2,'P',
501,sales_tue,q_tue
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+3,'P',
501,sales_wed,q_wed
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+4,'P',
501,sales_thu,q_thu
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+5,'P',
501,sales_fri,q_fri
FROM sales_input_table
UNION ALL
SELECT product_id, customer_id,weekly_start_date+6,'P',
501,sales_sat,q_sat
FROM sales_input_table;
set linesize 120
set pagesize 999
@?/rdbms/admin/utlxpls
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
INSERT ALL
INTO sales VALUES
(product_id, customer_id,weekly_start_date,'P',
501,q_sun,sales_sun)
INTO sales VALUES
(product_id, customer_id,weekly_start_date+1,'P',
501, q_mon,sales_mon)
INTO sales VALUES
(product_id, customer_id,weekly_start_date+2,'P',
501, q_tue,sales_tue)
INTO sales VALUES
(product_id, customer_id,weekly_start_date+3,'P',
501, q_wed,sales_wed)
INTO sales VALUES
(product_id, customer_id,weekly_start_date+4,'P',
501, q_thu,sales_thu)
INTO sales VALUES
(product_id, customer_id,weekly_start_date+5,'P',
501, q_fri,sales_fri)
INTO sales VALUES
(product_id, customer_id,weekly_start_date+6,'P',
501, q_sat,sales_sat)
SELECT *
FROM sales_input_table;
COMMIT;
|
4. Using the Multi-Table Insert for Conditional Insertion
In this multi-table insert, you will insert data into several tables
with different table structures based on some conditions. To do this,
you perform the following steps:
| 1. |
Create an intermediate table consisting ‘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 20000;
|
| 2. |
Disable constraints on SALES table; necessary for 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 20000;
|
| 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 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;
As expected, the second table, CUSTOMERS_SPECIAL,
consists only of customer data with a cust_credit_limit
greater than or equal to 4500.
|
| 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;
|
Using the New Upsert Functionality, SQL MERGE
Keyword Overview
MyCompany has to update its product information in the data warehouse
periodically with data from its products database. Unfortunately, we’re
getting product delta information without the possibility to distinguish
between new and updated information, so we have to figure this out on
the data warehouse site.
To use the new SQL MERGE
command to either update or insert the data conditionally, you perform
the following steps:
| 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. Create an External Table (and Directories) for the New Product Information
Before you can create the external table, you will need to create a
directory object in the database that will point to the directory on the
file system where the data files will reside. Optionally, you can separate
the location for the log, bad and discard files from the location of the
data files, as shown in the following example.
| 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 'd:\wkdir';
CREATE DIRECTORY log_dir AS 'd:\wkdir';
|
| 2. |
When creating an external table, you are defining two parts of
information:
| 1. |
The metadata information for the table representation inside
the database |
| 2. |
The access parameter definition of how to extract the
data from the external file |
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 be available
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_SUBCAT_DESC VARCHAR2(2000),
PROD_CATEGORY VARCHAR2(50),
PROD_CAT_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)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'prod_delta.bad_xt'
LOGFILE log_dir:'prod_delta.log_xt'
FIELDS TERMINATED BY "|" LDRTRIM
)
location
(
'prodDelta.dat'
)
)REJECT LIMIT UNLIMITED NOPARALLEL;
|
2. Performing an Upsert by Using the New SQL MERGE
Command
To perform an upsert using the new SQL MERGE
command, you use the following steps:
| 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_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_subcat_desc, prod_category, prod_cat_desc,
prod_status, prod_list_price, prod_min_price)
VALUES
(s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory,
s.prod_subcat_desc, s.prod_category, s.prod_cat_desc,
s.prod_status, s.prod_list_price, s.prod_min_price);
ROLLBACK;
Note: You will ROLLBACK
so that you can reissue the same Upsert with two SQL statements
in a subsequent operation.
|
3. Show the Execution Plan of the New MERGE
Command
To look at the execution plan of the MERGE
statement, you perform the following steps:
| 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
TRUNCATE TABLE plan_table;
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_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_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)
VALUES (s.prod_id, s.prod_name, s.prod_desc,
s.prod_subcategory, s.prod_subcat_desc,
s.prod_category, s.prod_cat_desc,
s.prod_weight_class, s.prod_unit_of_measure,
s.prod_pack_size, s.supplier_id, s.prod_status,
s.prod_list_price, s.prod_min_price);
set linesize 120
select *
from table(dbms_xplan.display);
|
4. Perform an Upsert by Using Two Separate SQL Commands (Before Oracle9i)
Before Oracle9i,
such Upsert functionality could be implemented as a combination of two
SQL statements (insert plus update) or in a procedural manner. To demonstrate
how this was done, perform the following steps:
| 1. |
To leverage an updateable 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.
The script do_merge_816.sql
contains the SQL for the next three steps.
CREATE TABLE prod_delta NOLOGGING AS SELECT * FROM products_delta;
CREATE UNIQUE INDEX p_uk ON prod_delta(prod_id);
|
| 2. |
Issue the Update operation based on an updateable join view. This
functionality was introduced in Oracle 8.1.6. From a SQL*Plus session
logged on to the SH schema, execute the following SQL script:
UPDATE
( SELECT s.prod_id, s.prod_list_price sprod_list_price,
s.prod_min_price sprod_min_price, t.prod_id,
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 t.prod_list_price = s.prod_list_price,
t.prod_min_price = s.prod_min_price;
|
| 3. |
Issue the second part of the MERGE - the Insert statement:
INSERT INTO products
( prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcat_desc, prod_category, prod_cat_desc,
prod_status, prod_list_price, prod_min_price)
SELECT prod_id, prod_name, prod_desc, prod_subcategory,
prod_subcat_desc, prod_category, prod_cat_desc,
prod_status, prod_list_price, prod_min_price
FROM prod_delta s
WHERE NOT EXISTS
( SELECT 1
FROM products t
WHERE t.prod_id = s.prod_id);
ROLLBACK;
We have to issue two separate SQL statements to accomplish the
same functionality than the new MERGE command. To leverage the updateable
join view functionality of 8.1.6, we also had to copy the external
table into a real database table and to create a unique index on
the join column. In summary, more operations, more space requirements,
and more processing time.
|
Table Function Overview
In the ETL process, the data extracted from a source system passes through
a sequence of transformations before it is loaded into a data warehouse.
Complex transformations are implemented in a procedural manner, either
outside the database or inside the database in PL/SQL. When the results
of a transformation become too large to fit into memory, they must be
staged by materializing them either into database tables or flat files.
This data is then read and processed as input to the next transformation
in the sequence. Oracle9i's
table functions provide the support for pipelined and parallel execution
of such transformations implemented in PL/SQL, C or Java.
A table function is defined as a function, which can produce a set of
rows as output; additionally, Oracle9i's
table functions can take a set of rows as input. These sets of rows are
processed iteratively in subsets, thus enabling a pipelined mechanism
to stream these subset results from one transformation to the next before
the first operation has finished. Furthermore, table functions can be
processed transparently in parallel, which is equivalent to SQL operations
such as a table scan or a sort. Staging tables are no longer necessary.
Examining Basic Implementation Issues for Table Functions
Because of the powerful and multifaceted usage of table functions as
part of complex transformations, you will examine basic implementation
issues for table functions only. To do this, you perform the following
steps:
| 1. |
Set up the basic objects for the table function.
|
| 2. |
Perform nonpipelined table function, returning an array of records.
|
| 3. |
Perform pipelined table function.
|
| 4. |
Perform transparent parallel execution of table functions.
|
| 5. |
Perform table function with autonomous DML. |
| 6. |
Perform seamless streaming through several table functions. |
1. Setting Up the Basic Objects for the Table Function
A table function can produce a set of rows as output and can take a
set of rows as input. Therefore, it is imperative that you have to define
a record type and a collection of records to define the input and output;
those types can either be strongly typed, that is, well-defined prior
to their execution, or weakly typed, which means without a fixed record
format before run time. You will examine both variants.
To set up the table function objects, you perform the following steps:
| 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
Rem 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_subcat_desc VARCHAR2(2000)
, prod_category VARCHAR2(50)
, prod_cat_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 our 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
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_subcat_desc VARCHAR2(2000)
, prod_category VARCHAR2(50)
, prod_cat_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:
@setup_tf_logtab.sql
CREATE TABLE obsolete_products_errors
(prod_id NUMBER, msg VARCHAR2(2000));
This table will be used for the table function example which fans
out some data as part of its execution.
|
Table functions return a set of records to the subsequent operation.
The result set can be either delivered en-block, the default PL/SQL behavior,
or pipelined - increments of the result set will be streamed to the subsequent
operation as soon as they are produced.
For table function implementations in PL/SQL, the PL/SQL engine controls
the size of the incrementally returned array. For interface implementations
of table functions, in C or Java, it is up to the programmer to define
the incremental return set.
See the Data Cartridge Developer’s Guide for details on interface
implementations.
2. Performing Nonpipelined Table Function, Returning an Array of Records
The following example represents a simple table function, returning
its result set nonpipelined as complete array. The table function simply
filters out all records of product category 'Boys' and returns all other
records. Note that the input REF CURSOR is weakly typed, meaning it could
be any valid SQL statement. It is then up to the table function code to
handle its input, probably based on other input variables.
To perform the nonpipelined table function, you perform the following
steps:
| 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
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_subcat_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_cat_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 cur INTO 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;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
-- Category BOYS is not meant to be obsolete
-- and will be suppressed
IF prod_status='obsolete' AND prod_category != 'Boys' THEN
-- append to collection
i:=i+1;
objset.extend;
objset(i):=product_t(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);
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_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)));
|
Note that the input SELECT
list for the cursor variable must match the definition of the table function,
in our case pkg.refcur_t.
If you can ensure that the table definition, in our case products, match
this input cursor, you can simplify the statement by using the SELECT
* FROM TABLE notation.
3. Performing Pipelined Table Functions
The following example represents the same filtering-out than the previous
example. Unlike the first one, this table function returns its result
set pipelined, which means incrementally. Note that the input REF CURSOR
is here strongly typed, meaning it could be only a SQL statement returning
a record type of product_type_record.
To perform the pipelined table function, you perform the following steps:
| 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_subcat_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_cat_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_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;
EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
IF prod_status='obsolete' AND prod_category !='Boys' THEN
PIPE ROW (product_t(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));
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_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)));
|
4. Perform Transparent Parallel Execution of Table Functions
Table functions can be parallelized transparently without any special
intervention or setup. The only necessary declaration, which has to be
done as part of the table function creation, is to tell the SQL engine
if there are any restrictions or rules for parallelizing its execution.
Think of a table function that processes a complex aggregation operation
over some product_category attributes. If we want to process this operation
in parallel, we have to guarantee that all records with the same product
category attributes are processed from the same parallel execution slave,
so that our aggregation operation covers all records that belong to the
same grouping. This implies that, during parallel processing, a distribution
rule must be used for parallel processing, which guarantees the above
described correct distribution.
This parallel distribution rule is defined as part of the table function
header, for example
PARALLEL_ENABLE (PARTITION cur BY ANY) IS
That’s all a developer has to do to enable parallel execution of a table
function.
To perform one of the table functions you previously defined in parallel,
you perform the following steps:
| 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 will
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');
|
5. Perform Table Functions with Autonomous DML
Although a table function is part of a single atomic transaction, it
provides the additional capability to fan out data to other tables within
the scope of an autonomous transaction. This can be used in a variety
of ways, such as for exception or progress logging or to fan out subsets
of data to be used by other independent transformations.
The following example logs some "exception" information into
the OBSOLETE_PRODUCTS_ERRORS
table:
| 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 'Men')
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_subcat_desc VARCHAR2(2000);
prod_category VARCHAR2(50);
prod_cat_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);
sales NUMBER:=0;
BEGIN
LOOP
FETCH cur INTO 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, prod_total;
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');
ELSE
PIPE ROW (product_t( 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,
prod_total));
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_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
)
)
);
SELECT DISTINCT msg
FROM obsolete_products_errors;
|
| 3. |
You will want to 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_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
),'Boys')
);
SELECT DISTINCT msg
FROM obsolete_products_errors;
You will see that, dependent on input variable, the table function
processes the input rows differently, thus delivering a different
result set and insert different information into obsolete_products_error.
|
6. Perform Seamless Streaming Through Several Table Functions
Besides the transparent usage of table functions within SQL statements
and its capability of being processed in parallel, one of the biggest
advantages is that table functions can be called from within each other.
Furthermore, table functions can be used in any SQL statement and
become the input for all kinds of DML statements.
To see how this is done, perform the following steps:
| 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_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)))
)));
|
| 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_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)))
)));
|
| 3. |
Before you move to the next section of this module, you will want
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;
|
USE SYNCHRONOUS CHANGE DATA CAPTURE (CDC) TO CAPTURE AND CONSUME INCREMENTAL
SOURCE CHANGES
Oftentimes, data warehousing involves the extraction and transportation
of relational data from one or more source databases, into the data warehouse
for analysis. Change Data Capture quickly identifies and processes only
the data that has changed, not entire tables, and makes the change data
available for further use.
Without Change Data Capture, database extraction is a cumbersome process
in which you move the entire contents of tables into flat files, and then
load the files into the data warehouse. This ad hoc approach is expensive
in a number of ways.
Change Data Capture does not depend on intermediate flat files to stage
the data outside of the relational database. It captures the change data
resulting from INSERT, UPDATE, and DELETE operations made to user tables.
The change data is then stored in a database object called a change table,
and the change data is made available to applications in a controlled
way.
PUBLISH AND SUBSCRIBE MODEL
Most Change Data Capture systems have one publisher that captures and
publishes change data for any number of Oracle source tables. There can
be multiple subscribers accessing the change data. Change Data Capture
provides PL/SQL packages to accomplish the publish and subscribe tasks.
PUBLISHER
The publisher is usually a database administrator (DBA) who is in charge
of creating and maintaining schema objects that make up the Change Data
Capture system. The publisher performs these tasks:
|
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.
|
SUBSCRIBERS
The subscribers, usually applications, are consumers of the published
change data. Subscribers subscribe to one or more sets of columns in source
tables. Subscribers perform the following tasks:
|
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.
|
Please consult the ‘Oracle Data Warehousing Guide’ for a more detailed
discussion about the Change Data Capture Architecture.
We will now use synchronous CDC to track all the incremental changes
for the table PRODUCTS. For demonstration purposes the publisher and subscriber
will be the same database user.
| 1. |
We will first create a new intermediate table where we 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_prod_price_change_for_boys
(prod_id number(6), prod_min_price number(8,2),
prod_list_price number(8,2), 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;
Rem see the change tables
Rem shouldn't show anything
SELECT *
FROM change_tables;
Rem 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';
|
All changes are stored in Change Tables. Change Tables have a one-to-one
dependency to a source table; they consist of several fixed metadata columns
and a dynamic set of columns equivalent to the identified source columns
of interest.
| 1. |
You create a change table by using the provided package DBMS_LOGMNR_CDC_PUBLISH.
@cr_cdc_ct.sql
EXEC DBMS_LOGMNR_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
);
This 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. Please see the documentation for a discussion of the parameters.
|
| 2. |
Describe the change table PROD_PRICE_CT:
Desc prod_price_ct
See the static meta data columns as well as the columns PROD_ID,
PROD_MIN_PRICE, and PROD_LIST_PRICE (equivalent to source table
PRODUCTS).
|
| 3. |
To see the meta data 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;
|
Now anybody who’s interested in the change information can subscribe
to a change set for controlled access to the published change data for
analysis. Note that the logical entity for a subscription handle is a
change set and not a change table. A change set can consist of several
change tables and guarantees logical consistency for all of its change
tables. After subscribing to a change set, we also have to subscribe to
all source table columns of interest.
| 1. |
You must get a unique subscription handle that is used throughout
the session and to 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_LOGMNR_CDC_SUBSCRIBE.
@subs_cdc_ct.sql
variable subhandle number;
exec DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE ( -
CHANGE_SET => 'SYNC_SET', -
DESCRIPTION => 'Change data PRODUCTS for BOYS', -
SUBSCRIPTION_HANDLE => :subhandle);
PROMPT see the unique subhandle number
PRINT subhandle
EXEC DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE ( -
SUBSCRIPTION_HANDLE => :subhandle, -
SOURCE_SCHEMA => 'sh', -
SOURCE_TABLE => 'products', -
COLUMN_LIST => 'prod_id, prod_min_price, prod_list_price');
|
| 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;
|
So far you accomplished the necessary steps to set up a CDC environment:
|
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. Now you are ready to use CDC.
|
The first step after setting up the publish and subscribe framework is
to activate a subscription and to extend the subscription window to see
all changes that have taken place since either the last subscription window
extension or the activation of a subscription.
| 1. |
Activate and extend a subscription window. You will also have a
look into the meta data.
@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_HANDLE => :subhandle);
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 we are applying now to the source table PRODUCTS will
be 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_name='Kids Cargo Shorts' and prod_min_price > 11.20;
COMMIT;
Rem you will see entries in the change table
Rem note that we have entries for the old and the new values
SELECT *
FROM prod_price_ct;
If you haven’t modified the standard SALES HISTORY data set, you
will select 20 rows. Note that we 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 one and only supported way
to guarantee that every change is delivered only once to a subscribing
application.
|
| 3. |
You are now preparing for consuming the changes. Create a subscriber
view by using the DBMS_LOGMNR_CDC_SUBSCRIBE package.You will also
create a more meaningful name for it.
@sub_view1.sql
rem Set upper boundary (high-water mark) for the subscription
rem window. At this point, the subscriber has created a new
rem window that begins where the previous window ends.
rem The new window contains any data that was added to the change
rem table.
EXEC DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW -
(SUBSCRIPTION_HANDLE => :subhandle);
rem To access the new change data, the subscriber must call
rem the CREATE_SUBSCRIBER_VIEW procedure, and select from
rem the new subscriber view that is generated by Change
rem Data Capture.
variable viewname varchar2(4000);
exec DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW -
(SUBSCRIPTION_HANDLE => :subhandle, -
SOURCE_SCHEMA => 'SH', -
SOURCE_TABLE => 'products', -
VIEW_NAME => :viewname);
Rem you will see the viewname we saw before ...
PRINT viewname;
REM see what has been created
SELECT object_name,object_type
FROM user_objects
WHERE created > SYSDATE - 5/1440;
Rem let's get a more proper name for it
DECLARE
old_name varchar2(30);
BEGIN
SELECT view_name INTO old_name FROM user_subscribed_tables;
EXECUTE IMMEDIATE
'CREATE SYNONYM my_prod_price_change_view FOR ' || old_name;
END;
/
|
| 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_change_view1.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 BOYS' ones
SELECT p1.*
FROM prod_price_ct p1, products p2
WHERE p1.prod_id=p2.prod_id
AND p2.prod_category='Boys'
AND operation$='UN';
If you haven’t modified the standard SALES HISTORY data set, you
will select 10 rows, 9 rows for product category GIRLS, one for
BOYS.
In the example shown above, we’re 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 standalone source for synchronizing your
target environment. Joining back to the source table might reduce
the data stored on disk, but it creates additional workload on the
source site. Furthermore, when asynchronous CDC will be supported
in a future release, the change table will no longer be necessarily
stored in the source system itself but in an different system (potentially
an ODS environment). Join-back cannot be accomplished on a different
system than the source system.
|
| 5. |
We can now consume the changes on our target system.
@consume_cdc1.sql
Prompt ... you can now consume the changes
rem arbitrary example, where we only track changes for BOYS.
rem This shall demonstrate the flexibility as well as the
rem responsibility of the client site (consumer) to deal
rem appropriately with the changes
INSERT into my_prod_price_change_for_boys
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='Boys'
AND operation$='UN';
COMMIT;
SELECT prod_id, prod_min_price, prod_list_price,
to_char(when,'dd-mon-yyyy hh24:mi:ss')
FROM my_prod_price_change_for_boys;
|
So far you exercised your first consumption with the synchronous CDC
framework. Now investigate how to handle such an environment over time
when additional changes will happen and old changes are consumed by all
subscribers.
| 1. |
Other DML operations will take place on the source table PRODUCTS
@dml_cdc2.sql
PROMPT other changes will happen
UPDATE products
SET prod_list_price=prod_list_price*1.1
WHERE prod_name='Kids Cargo Shorts'
AND prod_min_price < 6.5;
COMMIT;
|
| 2. |
The synchronous CDC framework tracks these DML operations transparently
in the change table.
@show_cdc_ct2.sql
PROMPT ... which will be reflected in the change table
PROMPT REMEMBER THE NUMBER OF ROWS !!!
SELECT count(*)
FROM prod_price_ct ;
PROMPT and especially the BOYS' ones
SELECT COUNT(*)
FROM prod_price_ct p1, products p2
WHERE p1.prod_id=p2.prod_id
AND p2.prod_category='Boys';
If you haven’t modified the standard SALES HISTORY data set, you
will select 48 rows, and 18 rows for product category BOYS.
|
| 3. |
However, the change view still shows 10 records only, one record
for product category BOYS.
@sel_change_view1.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 BOYS' ones
SELECT p1.*
FROM prod_price_ct p1, products p2
WHERE p1.prod_id=p2.prod_id
AND p2.prod_category='Boys'
AND operation$='UN';
We haven’t extended the time window for the change view yet.
|
| 4. |
To tell the CDC framework that we are done with a set of changes,
we have to do the following:
1. Drop the subscriber view
2. Purge the subscription window. No data will be deleted
from the change table; it is only reflected in the meta data of
the user subscription.
@purge_sub_window1.sql
PROMPT drop the subscriber view as they are finished with it
exec DBMS_LOGMNR_CDC_SUBSCRIBE.drop_subscriber_view -
(subscription_handle => :subhandle, -
SOURCE_SCHEMA => 'SH', -
SOURCE_TABLE => 'PRODUCTS');
PROMPT ... and purge old data from the subscription window
Rem change view must be deleted before you can purge
Rem this will NOT delete any records from the change table.
Rem It just tells the CDC Rem system that the changed data
Rem used so far is no longer needed
EXEC DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW -
(SUBSCRIPTION_HANDLE => :subhandle);
PROMPT still the same number of records in the change table
PROMPT REMEMBER THE NUMBER OF ROWS !!!
SELECT COUNT(*)
FROM prod_price_ct;
PROMPT recreate an re-query the change view will show you
PROMPT ZERO records, although there are changes you haven't
PROMPT consumed - you need to extend your window
exec DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW -
(SUBSCRIPTION_HANDLE => :subhandle, -
SOURCE_SCHEMA => 'sh', -
SOURCE_TABLE => 'products', -
VIEW_NAME => :viewname);
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='Boys';
PROMPT drop the subscriber view as they are finished with it
exec DBMS_LOGMNR_CDC_SUBSCRIBE.drop_subscriber_view -
(subscription_handle => :subhandle, -
SOURCE_SCHEMA => 'SH', -
SOURCE_TABLE => 'PRODUCTS');
|
| 5. |
To get the new changes reflected in the change view, you have to
extend the time window for the change view.
@extend_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_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW -
(SUBSCRIPTION_HANDLE => :subhandle);
Rem note that the view name is fix for a subhandle
variable viewname varchar2(4000);
exec DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW -
(SUBSCRIPTION_HANDLE => :subhandle, -
SOURCE_SCHEMA => 'sh', -
SOURCE_TABLE => 'products', -
VIEW_NAME => :viewname);
Rem ... now you will see exactly the new changed data since the
Rem last consumption not more, not less - 16 entries for 8 changed
Rem records (old and new values)
SELECT COUNT(*)
FROM my_prod_price_change_view p1, products p2
WHERE p1.prod_id=p2.prod_id
AND p2.prod_category='Boys';
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. Since the incremental changes you
are interested in are stored in a change table (a ‘normal’ Oracle
table), youcan use any language or SQL construct the database supports.
@consume_cdc2.sql
PROMPT the new changes will be used
MERGE INTO my_prod_price_change_for_boys 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='Boys'
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_prod_price_change_for_boys;
|
The Publisher is responsible for maintaining the CDC framework and to
guarantee that the change tables are purged regularly as soon as all subscribers
have consumed a specific set of change information.
| 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_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
Rem be purged !!!
exec DBMS_LOGMNR_CDC_PUBLISH.purge
PROMPT REMEMBER THE NUMBER OF ROWS !!!
SELECT COUNT(*)
FROM prod_price_ct;
PROMPT and especially the BOYS' ones
Rem this is exactly twice the number of changes we made with the DML
Rem operation (one row for the old, one for the new values)
SELECT COUNT(*)
FROM prod_price_ct p1, products p2
WHERE p1.prod_id=p2.prod_id
AND p2.prod_category='Boys';
If you haven’t modified the standard SALES HISTORY data set, you
will select 36 rows, representing the second DML operation (one
row for the old, and one row for the new values). Although we haven’t
specified any specific time window for the purge operation of the
change table, the CDC framework ensures that only the changes will
be purged that are no longer needed by a subscriber.
|
| 1. |
Since you consumed the second set of change already, you can drop
our used change view and purge our subscription window. You will
also purge the change table.
@purge_sub_window2.sql
PROMPT now drop the view and purge the window again
rem (which means that we consumed all the accessible changes)
exec DBMS_LOGMNR_CDC_SUBSCRIBE.drop_subscriber_view -
(subscription_handle => :subhandle, -
SOURCE_SCHEMA => 'sh', -
SOURCE_TABLE => 'products');
PROMPT ... and purge old data from the subscription window
EXEC DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW -
(SUBSCRIPTION_HANDLE => :subhandle);
PROMPT purge all change tables again
exec DBMS_LOGMNR_CDC_PUBLISH.purge
PROMPT ... and you will see an empty change table emp_ct
SELECT *
FROM prod_price_ct;
The change table is now empty. All changes are consumed and could
be purged.
|
Let’s now clean up the CDC environment.
| 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
exec DBMS_LOGMNR_CDC_SUBSCRIBE.drop_subscription -
(subscription_handle => :subhandle);
exec DBMS_LOGMNR_CDC_PUBLISH.DROP_CHANGE_TABLE (OWNER => 'sh', -
CHANGE_TABLE_NAME => 'prod_price_CT', -
FORCE_FLAG => 'Y');
DROP SYNONYM my_prod_price_change_view;
DROP TABLE my_prod_price_change_for_boys;
UPDATE products SET prod_min_price=prod_min_price*0.909
WHERE prod_name='Kids Cargo Shorts'
AND prod_min_price < 7;
UPDATE products SET prod_list_price=prod_list_price*0.909
WHERE prod_name='Kids Cargo Shorts'
AND prod_min_price > 11.50;
COMMIT;
|
Propagation from a Data Warehouse to a Data Mart Overview
Besides its central data warehouse, MyCompany is running several small
divisional data marts. For example, the products department wants to get
all transactional SALES
data partitioned by its main product categories for marketing campaign
analysis; only the SALES
data of 2000 is relevant. You will address the business problem with Oracle’s
Transportable Tablespace capabilities and Oracle’s new LIST partitioning,
introduced in Oracle9i.
Furthermore, to guarantee a successful completion of the generation process
of this new table, you will run this statement in RESUMABLE
mode, thus ensuring that any space problem will not cause the creation
process to fail.
Note: To demonstrate the benefit of resumable statements, you
will need TWO sessions and therefore TWO windows. Please read the following
section CAREFULLY before exercising.
To propagate from a data warehouse to a data mart, you perform the following
steps:
| 1. |
Enable a RESUMABLE
session.
|
| 2. |
Create a new tablespace as 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. Enable a RESUMABLE
Session
With resumable statements, Oracle9i
provides the ability to suspend and resume execution of large database
operations in the event of repairable failure. Currently the types of
errors from which a statement can resume are space limit and out-of-space
errors. When an operation suspends, you have the opportunity to take the
corrective steps to resolve the error condition. Alternatively, a procedure
can be registered to automate the error correction. Once the error condition
is solved, the suspended statement will automatically resume and continues
operation. If the error is not corrected within an optionally specified
suspension time limit, the statement will finally fail. You will choose
a manual error correction for this exercise.
To see how this is done, perform the following:
| 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.
|
2. Create a New Tablespace as Potential Transportable Tablespace
Now create an additional tablespace for storing our new LIST partitioned
fact table.
| 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 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 transfer
DATAFILE 'D:\oracle\oradata\orcl\transfer.dbf' SIZE 2M REUSE;
The tablespace is intentionally defined too small for the target
table to enforce a space error during table creation.
|
3. Create a LIST Partitioned Table in the New Tablespace
In Window 1, you will create a LIST partitioned table with a
CREATE TABLE AS SELECT
command in the newly created tablespace transfer.
| 1. |
From a SQL*Plus session logged on to the SH schema, execute the
following SQL script:
NOTE: This statement will
hang due to space problems in the TRANSFER
tablespace and show up in the alert log. Once it hangs move to the
next step.
@create_list_part_table.sql
DROP TABLE sales_prod_dept;
CREATE TABLE sales_prod_dept
( prod_category, prod_subcategory, cust_id,
time_id,channel_id,promo_id, quantity_sold, amount_sold
)
NOLOGGING TABLESPACE transfer
PARTITION BY LIST (prod_category)
( PARTITION boys_sales values ('Boys'),
PARTITION girls_sales values ('Girls'),
PARTITION men_sales values ('Men'),
PARTITION women_sales values ('Women')
)
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
;
|
4. Leverage the New RESUMABLE
Statement Capabilities for Efficient Error Detection and Handling
While the CREATE TABLE AS SELECT
is running in Window 1, you can control the status of all sessions
running in RESUMABLE mode
in Window 2.
| 1. |
From a SQL*Plus session logged on to the SH schema, execute the
following SQL script:
@show_resumable_status.sql
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 will change, and
the ORA-error causing this problem will be 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 following commands for
the appropriate operating system and in the appropriate directory
where the external files reside.
From a SQL*Plus session logged on to the SYSTEM Schema,
execute the following SQL script:
@fix_ts.sql
ALTER DATABASE DATAFILE 'D:\oracle\oradata\orcl\transfer.dbf'
AUTOEXTEND ON;
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, execute the following SQL script:
@show_resumable_status.sql
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.
|
5. Create a range-list partitioned table in the transfer tablespace
Oracle9i Release
2 enhances Oracle’s mature Partitioning technology even more, by adding
new partitioning strategies to address more business scenarios and by
enhancing existing functionality. One of the enhancements, the transparent
FAST SPLIT operation is discussed in Module I.
With the additional partitioning strategy RANGE-LIST, Oracle offers a
second composite partitioning method to subdivide a partitioned table
based on two logical attributes. The main partitioning strategy is the
well-known and most used Range partitioning strategy. You’ll find this
implementation in nearly every data warehouse implementation dealing with
Rolling Window Operations (for details about Rolling Window Operations,
please refer to Module I), Furthermore, every Range partition is partitioned
based on a List Partitioning strategy, enabling a finer partition granule
for addressing your business needs.
A typical example leveraging Range-List Partitioning is a global Retail
environment, having time range partitions (Rolling Window) and a region-oriented
list partition underneath, so you can maintain every time window for a
specific region independent of each other.
List partitioned tables, introduced in Oracle9i
Release 1, enable you to group sets of distinct unrelated values together
in one partition. Consequently, all values not being covered with an existing
partition definition will raise an Oracle error message. Although this
enforces a business constraint (you should not see a non-existing region
in your data warehouse), you cannot always be sure to not having such
violations. Oracle9i Release 2 therefore offers the capability to create
a DEFAULT List Partition, a kind of ‘catch-all’ partition for all undefined
partition key values.
Oracle9i Release
2 also introduces the so-called subpartition template technology, a common
optional element of both range-hash and range-list composite partitioning.
The template provides an easy and convenient way to define default subpartitions
for each table partition. Oracle will create these default subpartitions
in any partition for which you do not explicitly define subpartitions.
This clause is useful for creating symmetric partitions.
Let’s now create a Range-List partitioned tables, leveraging all of the
above-mentioned new functionality:
| 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 transfer
PARTITION BY RANGE (time_id)
SUBPARTITION BY LIST (channel_id)
SUBPARTITION TEMPLATE
( SUBPARTITION direct values ('S'),
SUBPARTITION internet values ('I'),
SUBPARTITION partner values ('P'),
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
;
This example also shows how you can easily create compressed and
uncompressed partitions as part of an initial table creation. See
Module I for details about the new feature of table compression.
|
This example also shows how you can easily create compressed and uncompressed
partitions as part of an initial table creation. See Module I for details
about the new feature of table compression.
As you can see, the partition and template names are inherited to all
of the subpartitions, thus making the naming – and identification – of
subpartitions much easier and more convenient.
| 1. |
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
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 of 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 use a new Oracle9i
Release 2 capability and 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.
| 1. |
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
Rem it is added just like for a range partitioned table
ALTER TABLE sales_prod_dept ADD
PARTITION alien_sales VALUES ('Aliens');
PROMPT now add another one, covering the DEFAULT value
ALTER TABLE sales_prod_dept
ADD PARTITION 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, we cover
all possible values for the partitioning key – the defined ones and ‘all
others’ – as soon as we have created a default partition.
So, to ADD a new partition to this table, we 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 we did before will raise an ORA error message.
| 1. |
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
PROMPT partition exists
ALTER TABLE sales_prod_dept
ADD PARTITION undefined_sales VALUES ('Undefined');
This will raise an Oracle error.
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';
|
6. Prepare the Metadata Export for a Transportable Tablespace
To prepare a tablespace for transportation, you simply have to export
the metadata information of the tablespace. This export, together with
a physical copy of the tablespace can be used for importing the tablespace
into another database instance. Before 9i,
the target database must be running on the same operating system with
the same database block size. Oracle9i
lifts the restricition of the same block size.
To show this capability, you perform the following steps:
| 1. |
From a SQL*Plus session logged on to the SH schema, execute the
following SQL script:
@make_ts_ro.sql
ALTER TABLESPACE 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
transfer as follows:
@export_metadata.sql
host exp userid='sys/oracle@orcl.world as sysdba'
FILE=meta_transfer.dmp TRANSPORT_TABLESPACE=y TABLESPACES=transfer
LOG=meta_transfer.log
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 longer exist
SELECT count(*)
FROM sales_prod_dept;
|
7. Importing the Tablespace into Another Database
To import the tablespace into another database, you simply have to copy
the file to its target destination, and to import the dictionary metadata.
You can simulate this by importing the recently dropped tablespace into
the same instance again. Issue the following commands for the appropriate
operating system and in the appropriate directory where the external files
reside.
| 1. |
You can now import the metadata of tablespace as follows:
@import_metadata.sql
host imp userid='sys/change_on_install@orcl.world as sysdba'
FILE=meta_transfer.dmp transport_tablespace=y buffer=100000000
datafiles='D:\oracle\oradata\orcl\transfer.dbf' tablespaces=transfer
log=import_metadata.log
The same export syntax can be used at the DOS prompt by dropping
the SQL*Plus 'host' command.
|
| 2. |
After a successful import, you will see the SALES_PROD_DEPT
table again. From a SQL*Plus session logged on to the SH schema,
execute the following SELECT
statement:
SELECT count(*) FROM sales_prod_dept;
|
Module Summary
In this module, you should have learned how to:
|
Perform a multi-table Insert |
|
Compare the new Upsert functionality, implemented as
the SQL MERGE command,
with its manual implementation prior to Oracle9i |
|
Create and use a table function |
|
Use synchronous Change Data Capture to track - and consume
- incremental data changes |
|
Combine components of the ETL infrastructure from Oracle9i
into an efficient, parallel, and seamless transformation process without
staging |
|
Propagate information for a data mart by leveraging
new LIST partitioning from Oracle9i,
the transportable tablespace mechanism, and the new RESUMABLE
statement capabilities |
|
Use Oracle9i
capabilities of DEFAULT List partitions and understand its business
value |
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
