Task 1: Create Oracle Users and Tables

It is important to run procedures to create users and target tables on the Oracle database to use GoldenGate for caching.

Before you create users and tables, you need to configure the Oracle database for cache operations. You have to connect to the Oracle database as a database administrator. For details, see Configuring the Oracle Database to Cache Data.
Create the Oracle cache administration user, cacheadmin (who also owns the cache group), with the password ttpwd. This user also serves as a GoldenGate admin user. Create the Oracle application schema user oratt with the same password ttpwd. The following example creates the Oracle users using the SQL*Plus utility:
```
SQL> create user cacheadmin identified by ttpwd; 
User created.
SQL> create user oratt identified by ttpwd; 
User created.
```

To grant privileges to the Oracle users:

SQL> GRANT CREATE SESSION, RESOURCE, ALTER SYSTEM to oratt;
Grant succeeded.
SQL> GRANT SELECT ANY TRANSACTION TO oratt;
Grant succeeded.
SQL> GRANT SELECT ANY DICTIONARY TO oratt;
Grant succeeded.
SQL> GRANT FLASHBACK ANY TABLE TO oratt;
Grant succeeded.
SQL> GRANT UNLIMITED TABLESPACE TO oratt;
Grant succeeded.
SQL> GRANT SELECT ANY TABLE TO cacheadmin;
Grant succeeded.
SQL> GRANT INSERT ANY TABLE TO cacheadmin;
Grant succeeded.
SQL> GRANT UPDATE ANY TABLE TO cacheadmin;
Grant succeeded.
SQL> GRANT DELETE ANY TABLE TO cacheadmin;
Grant succeeded.

Now, run the grantCacheAdminPrivileges.sql script to grant privileges to the cache administration user cacheadmin that are required to create Oracle database objects. See The grantCacheAdminPrivileges.sql Script.

Create tables and insert rows into them. The oratt user owns three tables. The two tables customer and orders are cached in TimesTen.

SQL> CREATE TABLE customer
(
    custid     VARCHAR2(10) NOT NULL,
    firstname  VARCHAR2(20) NOT NULL,
    lastname   VARCHAR2(20) NOT NULL,
    address    VARCHAR2(128) NOT NULL,
    phone      VARCHAR2(16) NOT NULL,
    PRIMARY KEY (custid)
);
Table created.

SQL> CREATE TABLE orders
(
    orderid    NUMBER(10,0) NOT NULL,
    custid     VARCHAR2(10) NOT NULL,
    orderdate  DATE NOT NULL,
    priority   CHAR(1),
    amount     NUMBER(12,2) NOT NULL,
    PRIMARY KEY (orderid),
    FOREIGN KEY (custid) REFERENCES customer(custid)
);
Table created.

SQL> CREATE TABLE item
(
    itemno     NUMBER(4,0) NOT NULL,
    orderid    NUMBER(10,0) NOT NULL,
    itemcode   VARCHAR2(10) NOT NULL,
    quantity   NUMBER(4,0) NOT NULL,
    price      NUMBER(6,2) NOT NULL,
    totalvalue NUMBER(10,2) NOT NULL,
    PRIMARY KEY (orderid,itemno),
    FOREIGN KEY (orderid) REFERENCES order(orderid)
);
Table created.

SQL> INSERT INTO customer VALUES('C000000001', 'Fred', 'Bloggs', 'Nice Villas, 
  Pleasant Town', '+16072321234');
1 row inserted.
SQL> INSERT INTO orders VALUES(123456, 'C000000001', '21/10/2021', 'N', 430.46);
1 row inserted.
SQL> INSERT INTO item VALUES(1, 123456, 'I000001725', 2, 15.25, 30.50);
1 row inserted.
SQL> INSERT INTO item VALUES(2, 123456, 'I000207351', 4, 99.99, 399.96);
1 row inserted.
COMMIT;