Hello All,
I am trying to perform some tests in LiveSQL related to changing the partitioning scheme of a table in 18c.
Based on an example from the 18c documentation, I successfully created a table and several indexes,
but when I attempted to execute the ALTER TABLE statement, I got the error below:
ORA-01950: no privileges on tablespace 'SYSTEM'The same error is raised regardless of whether the ALTER TABLE statement includes the ONLINE clause or not.
I guess that probably the ALTER TABLE statement creates some intermediary objects for supporting the change of the partitioning scheme, and some (or all) of those objects are created in the SYSTEM tablespace.
I wonder whether this error is related in anyway to the LiveSQL environment limitations,
but I think that even in a "normal" environment the SYSTEM tablespace is usually NOT accessible
to a "normal" database schema owner, who is defined with a default tablespace and maybe an additional list of accessible custom tablespaces.
If access to the SYSTEM tablespace is required for some recursive action(s) performed on behalf of
the ALTER TABLE statement, I think that these actions SHOULD NOT require granting of any access on the SYSTEM tablespace to the schema that owns the table.
Also, the documentation of the ALTER TABLE statement in 18c does not specify any such prerequisite.
I created a LiveSQL script for this sample scenario:
https://livesql.oracle.com/apex/livesql/s/hs1oo1lea9v97aa7ljt3a8ucv Thanks a lot in advance if you can investigate this issue.
Have a great new year 2019 :)
Best Regards,
Iudith Mentzel
This is a known bug. Temporary objects used for the operation are defaulting to the SYSTEM tablespace, and if you don't have a quota on SYSTEM then the operation will fail.
The workaround (for a local installation) is to temporarily grant a quota on SYSTEM (which I recognise is hardly ideal).