Login to Pluggable Database:
============================
SQL> connect user2/oracle@192.168.56.111:1521/cdb2_pdb1;
Connected.
SQL> CREATE TABLE MAIN_TABLE (
2 name VARCHAR2(30),
3 order_date DATE,
4 no_rows NUMBER)
5 PARTITION BY RANGE(no_rows)
6 (
7 PARTITION no_rows1 VALUES LESS THAN (100) TABLESPACE users,
8 PARTITION no_rows2 VALUES LESS THAN (1000) TABLESPACE users,
9 PARTITION no_rows3 VALUES LESS THAN (10000) TABLESPACE users,
10 PARTITION no_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE users)
11 ;
Table created.
SQL> ALTER TABLE MAIN_TABLE
ADD CONSTRAINT pk_ref_main PRIMARY KEY (name) USING INDEX;
Table altered.
SQL> CREATE TABLE SUB_TABLE (
name VARCHAR2(30) ,
index_col VARCHAR2(30) ,
CONSTRAINT fk_ref_sub_main
FOREIGN KEY(name) REFERENCES main_table(name))
PARTITION BY REFERENCE(fk_ref_sub_main);
2 3 4 5 6 PARTITION BY REFERENCE(fk_ref_sub_main)
*
ERROR at line 6:
ORA-14652: reference partitioning foreign key is not supported
Note: Foreign Key Column (table_name) has to be NOT NULL.
SQL> CREATE TABLE SUB_TABLE (
name VARCHAR2(30) NOT NULL,
index_col VARCHAR2(30),
CONSTRAINT fk_ref_sub_main
FOREIGN KEY (name) REFERENCES MAIN_TABLE(name))
PARTITION BY REFERENCE(fk_ref_sub_main);
Table created.
SQL> insert into main_table values ('ORACLE','12-MAR-2014',1);
insert into main_table values ('ORACLE','12-MAR-2014',1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> connect sys/oracle@cdb2 as sysdba
Connected.
SQL> show parameter _segment_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
rollback_segments string
SQL> alter session set container=CDB2_PDB1;
Session altered.
SQL> sho user
USER is "SYS"
SQL> alter user user2 quota unlimited on users;
User altered.
SQL> connect user2/oracle@192.168.56.111:1521/cdb2_pdb1;
Connected.
SQL> insert into main_table values ('ORACLE','12-MAR-2014',1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from main_table;
NAME ORDER_DAT NO_ROWS
------------------------------ --------- ----------
ORACLE 12-MAR-14 1
SQL> insert into sub_table values ('ORACLE',1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sub_table;
NAME INDEX_COL
------------------------------ ------------------------------
ORACLE 1
