In Oracle : Create Oracle tables for the source schema

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle Data Warehouse & BI

In-Oracle.com  -> Oracle Data Warehouse & BI  -> Oracle Data Warehouse

-> Create Oracle tables for the source schema

 

 Create Oracle tables for the source schema

 

 

My examples are mainly created on the following tables (created in PAUL_DW schema, but we can use another schema as well):

 

SALES, PRODUCTS, CUSTOMERS, REGIONS, PRODUCT_TYPES, COUNTRIES

 

Here are their definition:

 

ALTER TABLE PAUL_DW.COUNTRIES
DROP PRIMARY KEY CASCADE;

DROP TABLE PAUL_DW.COUNTRIES CASCADE CONSTRAINTS;

CREATE TABLE PAUL_DW.COUNTRIES
(
COUNTRY_ID NUMBER(20),
COUNTRY_OFFICIAL_NAME VARCHAR2(100 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

 


CREATE UNIQUE INDEX PAUL_DW.COUNTRIES_PK ON PAUL_DW.COUNTRIES
(COUNTRY_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE PAUL_DW.COUNTRIES ADD (
CONSTRAINT COUNTRIES_PK
PRIMARY KEY
(COUNTRY_ID)
USING INDEX PAUL_DW.COUNTRIES_PK);
 

 

ALTER TABLE  PAUL_DW.PRODUCT_TYPES
DROP PRIMARY KEY CASCADE;

DROP TABLE PAUL_DW.PRODUCT_TYPES CASCADE CONSTRAINTS;

CREATE TABLE PAUL_DW.PRODUCT_TYPES
(
PRODUCT_TYPE_ID NUMBER(10),
PRODUCT_TYPE_NAME VARCHAR2(200 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PAUL_DW.PRODUCT_TYPES_PK ON PAUL_DW.PRODUCT_TYPES
(PRODUCT_TYPE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE PAUL_DW.PRODUCT_TYPES ADD (
CONSTRAINT PRODUCT_TYPES_PK
PRIMARY KEY
(PRODUCT_TYPE_ID)
USING INDEX PAUL_DW.PRODUCT_TYPES_PK);
 

 

ALTER TABLE PAUL_DW.REGIONS
DROP PRIMARY KEY CASCADE;

DROP TABLE PAUL_DW.REGIONS CASCADE CONSTRAINTS;

CREATE TABLE PAUL_DW.REGIONS
(
REGION_ID NUMBER(3),
REGION_NAME VARCHAR2(200 BYTE),
REGION_COUNTRY VARCHAR2(200 BYTE)

)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PAUL_DW.REGIONS_PK ON PAUL_DW.REGIONS
(REGION_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE PAUL_DW.REGIONS ADD (
CONSTRAINT REGIONS_PK
PRIMARY KEY
(REGION_ID)
USING INDEX PAUL_DW.REGIONS_PK);
 

 

ALTER TABLE PAUL_DW.CUSTOMERS
DROP PRIMARY KEY CASCADE;

DROP TABLE PAUL_DW.CUSTOMERS CASCADE CONSTRAINTS;

CREATE TABLE PAUL_DW.CUSTOMERS
(
CUSTOMER_ID NUMBER(20),
CUSTOMER_NAME VARCHAR2(50 BYTE),
CUSTOMER_REGION_ID NUMBER(3),
CUSTOMER_TEL NUMBER(15)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PAUL_DW.CUSTOMERS_PK ON PAUL_DW.CUSTOMERS
(CUSTOMER_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE PAUL_DW.CUSTOMERS ADD (
CONSTRAINT CUSTOMERS_PK
PRIMARY KEY
(CUSTOMER_ID)
USING INDEX PAUL_DW.CUSTOMERS_PK);

ALTER TABLE PAUL_DW.CUSTOMERS ADD (
CONSTRAINT CUSTOMERS_REGION_FK
FOREIGN KEY (CUSTOMER_REGION_ID)
REFERENCES PAUL_DW.REGIONS);
 

 

ALTER TABLE PAUL_DW.SALES
DROP PRIMARY KEY CASCADE;

DROP TABLE PAUL_DW.SALES CASCADE CONSTRAINTS;

CREATE TABLE PAUL_DW.SALES
(
SALE_ID NUMBER(20),
PRODUCT_ID NUMBER(22),
CUSTOMER_ID NUMBER(20),
QUANTITY NUMBER(20),
PRICE NUMBER(12,2)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PAUL_DW.SALES_PK ON PAUL_DW.SALES
(SALE_ID)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE PAUL_DW.SALES ADD (
CONSTRAINT SALES_PK
PRIMARY KEY
(SALE_ID)
USING INDEX PAUL_DW.SALES_PK);

ALTER TABLE PAUL_DW.SALES ADD (
CONSTRAINT SQLES_CUSTOMER_FK
FOREIGN KEY (CUSTOMER_ID)
REFERENCES PAUL_DW.CUSTOMERS,
CONSTRAINT SQLES_PRODUCT_FK
FOREIGN KEY (PRODUCT_ID)
REFERENCES PAUL_DW.PRODUCTS);
 

 

Here is the code to populate these tables:

 

Insert into COUNTRIES
(COUNTRY_ID, COUNTRY_OFFICIAL_NAME)
Values
(1, 'Country 1');
Insert into COUNTRIES
(COUNTRY_ID, COUNTRY_OFFICIAL_NAME)
Values
(2, 'Country 2');
Insert into COUNTRIES
(COUNTRY_ID, COUNTRY_OFFICIAL_NAME)
Values
(3, 'Country 3');
COMMIT;

 

Insert into PRODUCT_TYPES
(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)
Values
(1, 'fruits');
Insert into PRODUCT_TYPES
(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)
Values
(2, 'vegetables');
Insert into PRODUCT_TYPES
(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)
Values
(3, 'electronical equipments');
Insert into PRODUCT_TYPES
(PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME)
Values
(4, 'clothes');
COMMIT;

 

Insert into REGIONS
(REGION_ID, REGION_NAME, REGION_COUNTRY)
Values
(1, 'Region 1 C1', 'Country 1');
Insert into REGIONS
(REGION_ID, REGION_NAME, REGION_COUNTRY)
Values
(2, 'Region 2 C1', 'Country 1');
Insert into REGIONS
(REGION_ID, REGION_NAME, REGION_COUNTRY)
Values
(3, 'Region 3 C1', 'Country 1');
Insert into REGIONS
(REGION_ID, REGION_NAME, REGION_COUNTRY)
Values
(4, 'Region 1 C2', 'Country 2');
Insert into REGIONS
(REGION_ID, REGION_NAME, REGION_COUNTRY)
Values
(5, 'Region 1 C3', 'Country 3');
Insert into REGIONS
(REGION_ID, REGION_NAME, REGION_COUNTRY)
Values
(6, 'Region 2 C3', 'Country 3');
COMMIT;

 

 

Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(1, 'Mr. King', 1, 8003456744);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(2, 'John Smith', 1, 345345344);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(3, 'Adams', 2, 345634534);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(4, 'Brown', 3, 234234242);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(5, 'Allan', 4, 53534534);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(6, 'Elena', 5, 435345345);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(7, 'Steve', 6, 345345345);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(8, 'Maria', 6, 2345234224);
Insert into CUSTOMERS
(CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_REGION_ID, CUSTOMER_TEL)
Values
(9, 'John', 1, 34534534);
COMMIT;
 

Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(1, 'apples', 1, 1);
Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(2, 'apples', 1, 2);
Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(3, 'apples', 1, 3);
Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(4, 'grapes', 1, 1);
Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(5, 'cabbage', 2, 1);
Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(6, 'carrot', 2, 1);
Insert into PRODUCTS
(PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE_ID, PRODUCT_COUNTRY_ID)
Values
(7, 'HDD Seagate', 3, 2);
COMMIT;
 

Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(1, 1, 1, 1, 10);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(2, 1, 2, 2, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(3, 2, 4, 20, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(4, 3, 3, 1, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(5, 3, 5, 1, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(6, 4, 4, 2, 2);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(7, 5, 6, 11, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(8, 6, 1, 11, 2);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(9, 7, 7, 12, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(10, 7, 8, 1, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(11, 2, 9, 2, 2);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(12, 5, 4, 3, 1);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(13, 3, 6, 3, 2);
Insert into SALES
(SALE_ID, PRODUCT_ID, CUSTOMER_ID, QUANTITY, PRICE)
Values
(14, 1, 5, 2, 1);
COMMIT;
 

 

Note: This is done just for create a quick example. In real life, the logical model is more complex to serve a real situation.

 

 

In-Oracle.com  -> Oracle Data Warehouse & BI  -> Oracle Data Warehouse

-> Create Oracle tables for the source schema

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.