ORA-43853: SECUREFILE LOBs (Large Objects) cannot be used in non-automatic segment space management tablespace "SYSTEM"

[indiVar0508]

Hi,

i am working on latest image for oracle, where i am running a query with JSON type i think supported from v21, but i am getting this error

ORA-43853: SECUREFILE LOBs (Large Objects) cannot be used in non-automatic segment space management tablespace "SYSTEM"

I was trying to run this query for oracle DB

CREATE TABLE my_json_table (
        "JSON_columns" JSON
)

can you help me how to resolve this, i tried this with query

alter session set db_securefile='IGNORE';
ALTER SYSTEM SET db_securefile = 'IGNORE';

but these both are giving me this error ORA-00922: missing or invalid option

Thanks

[gvenzl]

Hi @indiVar0508,

Thanks a lot for using these images!

I think your issue is that you are trying to create a table under the SYS or SYSTEM user, which are both database administrative users. While you could make the CREATE TABLE work with these users, the best practice is to create a user for the app and use that user instead.

You could do so by using the createAppUser command, i.e.:

docker exec <container name> createAppUser <username> <password>

and then connect to that app user via docker exec -ti <container name> sqlplus <username>/<password>@xepdb1:

CREATE TABLE my_json_table (
        "JSON_columns" JSON
)

[indiVar0508]

Thanks @gvenzl ,

This worked for me :+1: ,

Closing this issue!

[gvenzl]

Glad to hear it!