Enterprise Data Manager Guide

6.4.3 HotFix 1
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3

Back Next

Select Clause for Partitioned Source Tables

If all tables in the interim table are partitioned in the source, the archive job uses the ALL_TAB_PARTITIONS Oracle view to populate the interim table values.

The select clause you configure depends on if the tables have the same table owner or multiple table owners. You can add multiple tables for each table owner if the tables have the same partition name. The procedure can process one partition for each table owner. If the table includes multiple partitions, then you must list the table owner, table name, and each partition name.

Single Table Owner Select Clause

Use the following syntax to form a select clause for the partition name when the interim tables have the same table owner:

SELECT A.table_owner, A.table_name, A.partition_name 
FROM all_tab_partitions A 
WHERE table_owner='<table owner>' AND table_name IN ('<table name>') AND partition_name='<partition name>'

For example, you want to add the following values in the interim table:

Table Owner	Table Name	Table Partition
XLA	XLA_AE_HEADERS	CST

Use the following syntax:

SELECT A.table_owner, A.table_name, A.partition_name 
FROM all_tab_partitions A 
WHERE table_owner='XLA' AND table_name IN ('XLA_AE_HEADERS') AND partition_name='CST'

Single Table Owner with Multiple Partitions within a Table Select Clause

Use the following syntax to form a select clause for the partition name when the interim tables have the same table owner and include multiple partitions for a table:

SELECT A.table_owner, A.table_name, A.partition_name
FROM all_tab_partitions A
WHERE table_owner='<table owner>' AND table_name IN ('<table name>') AND partition_name IN ('<partition name>','<partition name>')

For example, you want to add the following values in the interim table:

Table Owner	Table Name	Table Partition
XLA	XLA_AE_HEADERS	CST
XLA	XLA_AE_HEADERS	AR

Use the following syntax:

SELECT A.table_owner, A.table_name, A.partition_name
FROM all_tab_partitions A
WHERE table_owner='XLA' AND table_name IN ('XLA_AE_HEADERS') AND partition_name IN ('CST','AR')

Multiple Table Owners Select Clause

Use one select statement for each table owner. Use a UNION statement to join multiple select statements.

Use the following syntax to form multiple select statements when the interim tables have different table owners:

SELECT A.table_owner, A.table_name, A.partition_name 
FROM all_tab_partitions A 
WHERE A.table_owner='<table owner 1>' AND A.table_name IN ('<table name>') AND A.partition_name='<partition name>' 
UNION 
SELECT B.table_owner, B.table_name, B.partition_name, 'Y', null, null 
FROM all_tab_partitions B
WHERE B.table_owner='<table owner 2>' AND B.table_name IN ('<table name>') AND B.partition_name='<partition name>' 
UNION 
SELECT C.table_owner, C.table_name, C.partition_name, 'Y', null, null 
FROM all_tab_partitions C
WHERE C.table_owner='<table owner 3>' AND C.table_name IN ('<table name>') and C.partition_name='<partition name>'

For example, you want to add the following values in the interim table:

Table Owner	Table Name	Table Partition
XLA	XLA_AE_LINES	CST
APPLSYS	WF_ITEM_ACTIVITY_STATUSES	WF_ITEM45
PA	PA_SUMM_BALANCES	PA_SUMM_BALANCES_PAR11

Use the following syntax:

SELECT A.table_owner, A.table_name, A.partition_name 
FROM all_tab_partitions A
WHERE A.table_owner='XLA' AND A.table_name IN ('XLA_AE_LINES') AND A.partition_name='CST' 
UNION 
SELECT B.table_owner, B.table_name, B.partition_name, 'Y', null, null 
FROM all_tab_partitions B
WHERE B.table_owner='APPLSYS' AND B.table_name IN ('WF_ITEM_ACTIVITY_STATUSES') AND B.partition_name='WF_ITEM45' 
UNION 
SELECT C.table_owner, C.table_name, C.partition_name, 'Y', null, null 
FROM all_tab_partitions C
WHERE C.table_owner='PA' AND C.table_name IN ('PA_SUMM_BALANCES') AND C.partition_name='PA_SUMM_BALANCES_PAR11'

Rename Saved Search

Table of Contents

Enterprise Data Manager Guide

Enterprise Data Manager Guide

Select Clause for Partitioned Source Tables

Select Clause for Partitioned Source Tables

Single Table Owner Select Clause

Single Table Owner with Multiple Partitions within a Table Select Clause

Multiple Table Owners Select Clause