Create, Modify and Migrate Tables in 12c

Creating a table :-

Below script will create a table in XX schema.

create table XX.XX_ORACLE_DEMO
(
TRX_ID NUMBER(15) not null,
TRX_NUMBER VARCHAR2(30),
TRX_TYPE VARCHAR2(30) not null,
AMOUNT   NUMBER
);


This will create one object in DB.

Use below query to verify :-

select * from dba_objects where object_name like 'XX_ORACLE_DEMO%';

This will give you one record i.e. only table info.

-- Upgrade the Table .  ( to create Editioning View)

begin
ad_zd_table.upgrade('XX', 'XX_ORACLE_DEMO');     --- Object owner  , table name are paremeters.
end;
/

select * from dba_objects where object_name like 'XXG_AR_DEMO%'

Now it wil give you 3 records. Once you run upgrade script it will create Editioned view and synonym in apps for the table.


Altering  a table :- 


 alter table  XX.XX_ORACLE_DEMO add BU_CODE VARCHAR2(30);

Table Shows New Column

select * from XX.XX_ORACLE_DEMO

But Editioning view is not showing new column

select * from XX.XX_ORACLE_DEMO#


Run the below script to Refresh the Editioning View

begin
ad_zd_table.patch('XX', 'XX_ORACLE_DEMO');     --- Object owner  , table name are paremeters.
end;
/

Migrating Tables to Other instances :-  In 12.2 oracle provides new feature to migrate tables using XDF file which can be downloaded from one instance and uploaded to another instance. Below are the commands which can be used to download and upload XDF files.


XDF Generation:- 

Download :-
$FND_TOP/bin/xdfgen.pl apps/apps_pwd XX_ORACLE_DEMO owner_app_shortname='XX'

Upload :-
$FND_TOP/bin/xdfcmp.pl xx/&xx_pwd XX_ORACLE_DEMO.xdf apps/&apps_pwd