How To: Create Staging table space

---

---

---

“Staging” schema is the one where all of your staging tables and interim tables are created. It is required to create on the same database where you have your source database.

ILM Data Archive (ILM DA) creates intermediate tables with the same structure as the source tables (it adds few columns for the tool to process) in this staging schema. These tables starts with “AA_” and appends with a number. Data is first copied into these staging tables and then loaded into the target. When you create a source connection you should enable the option “Use Staging”, only then, the tool will create the tables in this schema. Providing a staging schema is mandatory, because, even if you don’t want these staging tables to be created, the tool needs this schema to create “Interim Table” that the entity has.

You can find the scripts to create staging schema in the folder “sql_scripts” located in the ILM DA installation folder. Below is the script to create a staging user in the Oracle database –

— Example create tablespace command. Datafile and size need to be adjusted.

— CREATE TABLESPACE AM_STAGE LOGGING

— DATAFILE <data_file_spec> SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M

— EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER AM_STAGE IDENTIFIED BY AM_STAGE

DEFAULT TABLESPACE &&STAGING_TABLESPACE

TEMPORARY TABLESPACE TEMP;

ALTER USER AM_STAGE QUOTA UNLIMITED ON &&STAGING_TABLESPACE;

GRANT ALTER SESSION TO AM_STAGE;

GRANT CREATE DATABASE LINK TO AM_STAGE;

GRANT CREATE PROCEDURE TO AM_STAGE;

GRANT CREATE SEQUENCE TO AM_STAGE;

GRANT CREATE SESSION TO AM_STAGE;

GRANT CREATE SYNONYM TO AM_STAGE;

GRANT CREATE TABLE TO AM_STAGE;

GRANT CREATE TRIGGER TO AM_STAGE;

GRANT CREATE TYPE TO AM_STAGE;

GRANT CREATE VIEW TO AM_STAGE;

GRANT DELETE ANY TABLE TO AM_STAGE;

— Required to access the staging tables if you use the staging user for the delete from source step.

— If you cannot grant this privilege, you can do a direct delete grant on all application tables that are referenced in the metadata.

— Or, you can use the application user for the delete from source step.

— If you use the application user, the application user needs access to the staging tables.

GRANT SELECT ANY TABLE TO AM_STAGE;

— Not required if the staging user has access to the application user tables.

— If you cannot grant this privilege, you can do direct select grants on all application tables that are referenced in the metadata.

GRANT UPDATE ANY TABLE TO AM_STAGE;

— If you cannot grant this privilege, you can do direct update grants on all application tables that are referenced in the metadata.

GRANT EXECUTE ANY PROCEDURE TO AM_STAGE;

— Required if the archive entities execute any procedures in the application user schema.

GRANT ALTER ANY TRIGGER TO AM_STAGE;

— Required if you use the staging user for the delete from source step.

GRANT CREATE ANY TRIGGER TO AM_STAGE;

— Required if you use the staging user for the delete from source step. If you cannot grant this privilege,

— then the staging user needs direct select privileges on the application tables instead. For

— Example, grant select on ORDERS to amstage.

GRANT EXECUTE ANY TYPE TO AM_STAGE;

— Required to create staging tables with user defined types that are owned by a different user.

— The following user privileges are required for Oracle Applications:

— GRANT INSERT ON AP.AP_HISTORY_CHECKS_ALL TO AM_STAGE;

— GRANT INSERT ON AP.AP_HISTORY_INV_PAYMENTS_ALL TO AM_STAGE;

— GRANT INSERT ON AP.AP_HISTORY_INVOICES_ALL TO AM_STAGE;

— GRANT INSERT ON PO.PO_HISTORY_POS_ALL TO AM_STAGE;

— GRANT INSERT ON PO.PO_HISTORY_RECEIPTS TO AM_STAGE;

— GRANT INSERT ON PO.PO_HISTORY_REQUISITIONS_ALL TO AM_STAGE;

— Example create database link command to create the link specified in the “Database Link to ILM Repository” source repository attribute.

— Such a link will be automatically created in case it is not specified if an entity contains RunProcedure steps.

— CREATE PUBLIC DATABASE LINK ILM_HOME_LINK CONNECT TO AMHOME IDENTIFIED BY AMHOME USING ‘ILM_HOME_CONNECT_STRING’;

Usually the staging user is named as “AM_STAGE” and the ILM metadata respository as “AMHOME”, but, you are free to use any naming conventions and change the script mentioned above accordingly.

---