SET ECHO ON TERMOUT ON SCAN ON

REM **************** AS DBA ****************

CONNECT &user/&password

REM drop users

DROP USER c2 CASCADE;
DROP USER c1 CASCADE;
DROP USER p CASCADE;

REM create users

CREATE USER p IDENTIFIED by p QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO p;
GRANT CREATE TABLE TO p;
GRANT SELECT ANY DICTIONARY TO p;

CREATE USER c1 IDENTIFIED by c1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, ALTER SESSION TO c1;
GRANT CREATE TABLE TO c1;
GRANT SELECT ANY DICTIONARY TO c1;

CREATE USER c2 IDENTIFIED by c2 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, ALTER SESSION TO c2;
GRANT CREATE TABLE TO c2;
GRANT SELECT ANY DICTIONARY TO c2;

REM **************** AS USER P ****************

CONNECT p/p

CREATE TABLE p.parent AS SELECT rownum AS id, cast(NULL AS VARCHAR2(100)) AS pad FROM dual CONNECT BY level <= 1E3;
ALTER TABLE p.parent ADD CONSTRAINT parent_pk PRIMARY KEY (id);

GRANT REFERENCES ON p.parent TO c1;
GRANT READ ON p.parent TO c1;

GRANT REFERENCES ON p.parent TO c2;
GRANT READ ON p.parent TO c2;

REM **************** AS USER C1 ****************

CONNECT c1/c1

CREATE TABLE c1.child1 AS SELECT rownum AS id, p1.id AS parent_id, cast(NULL AS VARCHAR2(100)) AS pad FROM p.parent p1, p.parent p2, p.parent p3 WHERE rownum <= 1E8;
ALTER TABLE c1.child1 ADD CONSTRAINT child1_pk PRIMARY KEY (id);
ALTER TABLE c1.child1 ADD CONSTRAINT child1_parent_fk FOREIGN KEY (parent_id) REFERENCES p.parent (id);

REM **************** AS USER C2 ****************

CONNECT c2/c2

CREATE TABLE c2.child2 AS SELECT rownum AS id, cast(NULL AS NUMBER) AS parent_id, cast(NULL AS VARCHAR2(100)) AS pad FROM dual;
ALTER TABLE c2.child2 ADD CONSTRAINT child2_pk PRIMARY KEY (id);
ALTER TABLE c2.child2 ADD CONSTRAINT child2_parent_fk FOREIGN KEY (parent_id) REFERENCES p.parent (id);
