I'm new in Oracle, so maybe my question could be stupid. I use Oracle only for data storage. I have made some research but I'm blocked. I use Oracle 12c. I created a PDB with admin user PEEI_SYS like this:
create pluggable database PEEI admin user PEEI_SYS identified by PEEI
roles = (DBA);
-- open PDB PEEI
alter pluggable database PEEI open read write;`
I have created another user called PEEI which should only do select, update, insert on tables owned by PEEI_SYS. I have created the user PEEI like this:
CREATE USER "PEEI" IDENTIFIED BY "PEEI" DEFAULT TABLESPACE PDATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;`
Now I would like that the user PEEI could insert rows in the table PEEI_SYS.PEEI_P_TRACKING. This table is created like this:
`CREATE TABLE PEEI_SYS.PEEI_P_TRACKING
(
"CODE_WORKFLOW" VARCHAR2(30 BYTE),
"STATUS" VARCHAR2(15 BYTE),
"DATE_UPDATE" DATE,
"USER_UPDATE" VARCHAR2(20 BYTE),
"DEB_WORKFLOW" DATE,
"FIN_WORKFLOW" DATE,
"TIME_SECOND" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PDATA" ;
GRANT SELECT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_READ;
GRANT DELETE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
GRANT INSERT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
GRANT UPDATE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
When I got the error I granted unlimited privileges to PEEI user on PDATA tablespaces like this: ALTER USER PEEI QUOTA UNLIMITED ON PDATA;
I have still the error. Could you please help me ?
Thank you very much in advance.
Kind regards,
Best Answer
You did not grant PEEI_SYS any quota in the tablespace. PEEI_SYS owns the table, and therefore the data blocks that need to be created. PEEI does not need any quota if it is not going to own objects.