Gary via Linkedin and Glen via Twitter pointed out that the issue was my primary key index which did not have a tablespace specified and as such when the sequence was used to insert an ID into a table the primary key index was updated and because that was the first use - the tables were just created then an extent was needed and the ORA-10950 error was of course thrown.
The issue was not the sequence storage but the index storage for the primary key. The fix still worked of course by changing the CRM default tablespace to CRM from USERS. But, a better fix is to actually change the code to assign a tablespace to the primary key index. As i created by primary key index indirectly by specifying a primary key constraint. I needed to add the tablespace clause to the ALTER TABLE. To test this i first put the CRM users default tablespace back to CRM:
pxf$ sqlplus system/oracle1@//192.168.56.78:1523/xepdb1
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 1 10:10:38 2019
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> sho user
USER is "SYSTEM"
SQL> alter user crm default tablespace users;
User altered.
SQL>
Now connect as CRM and remove the code and re-install it to prove that the ORA-01950 error still occurs:
pxf$ sqlplus crm/crm@//192.168.56.78:1523/xepdb1
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 1 10:13:09 2019
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> @lic_delete
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table dropped.
... {output removed to save space}
Now install the code again and see the error:
SQL> @license
Table created.
... {output removed to save space}
Table altered.
Sequence created.
insert into crm_lic_products (id,product_name) values (crm_lic_products_seq.nextval,'PFCLScan')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
... {output removed to save space}
So we have the error. Now change the license.sql script and add the tablespace clause for all primary keys - here is an example for the CRM_LIC_PRODUCTS table:
-- ------------------------------------------------------------------
-- products table - product names
-- ------------------------------------------------------------------
create table crm_lic_products (
id number not null,
product_name varchar2(100) not null
)
tablespace crm
/
-- primary key
alter table crm_lic_products add constraint crm_lic_products_pk primary key (id) using index tablespace crm;
create sequence crm_lic_products_seq
start with 1
increment by 1
nocache
nocycle
/
The tablespace is now set to CRM for this constraint which will create a primary key index in the CRM tablespace. So now install that and see if to works:
pxf$ sqlplus crm/crm@//192.168.56.78:1523/xepdb1
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 1 11:09:21 2019
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> @license
Table created.
Table altered.
Sequence created.
c
1 row created.
1 row created.
... {output removed to save space}
So it clearly works now and the issue is not the storage of the sequence but the storage of the result of the sequence i.e. in the primary key index.
The main point for me of the post yesterday and this post today is that we from an Oracle Security point of view can further control data by specifying quotas on tablespaces. This implies - and the performance guys will probably disagree from a monitoring and a lack of performance advantage - that we could create separate tablespaces for some sections of data - business domains - security domains - whatever is needed and then we can also control the access to add or change data through these tablespace and quota controls as well.
