ORA-43853 when creating a xml column stored as binary within an Oracle table

Question

I am trying to create a table in Oracle through the command:

CREATE TABLE xml_binary (
   id    NUMBER,
   fv  XMLTYPE)
   XMLTYPE fv STORE AS BINARY XML
      XMLSCHEMA "http://www.example.com/fvInteger_binary.xsd"
      ELEMENT "ROOT";

However, I get the error:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace

I found in this forum a solution which would be to ignore the error through the command:

ALTER SYSTEM SET db_securefile = 'IGNORE;

I do not think it is a good solution, however.

What can I do in order to solve it properly?

Thanks in advance!

MacAnthony · Accepted Answer · 2019-11-05 16:37:44Z

2

Securefile can be created only on ASSM tablespace.

SELECT tablespace_name,  segment_space_management
      FROM dba_tablespaces;

segment_space_management = auto = ASSM-tablespace.

db_securefile = 'IGNORE' works in that way. After restart, the db will ignore information about SF and it will create basicfile.

Solution:

1) Store binaryxml in basicfile.

XMLTYPE fv STORE AS BASICFILE BINARY XML

2) Store binaryxml in securefile in assm tablespace.

 XMLTYPE fv STORE AS securefile BINARY XML(tablespace assm_tablespace)

edited Nov 5, 2019 at 16:37

MacAnthony

4,5212 gold badges25 silver badges27 bronze badges

answered Feb 21, 2018 at 7:55

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ORA-43853 when creating a xml column stored as binary within an Oracle table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related