This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Sep 25, 2012 3:43 PM by mdrake RSS

Change default XML Storage

963618 Newbie
Currently Being Moderated
Hello,

As per the bellow the default XML storage has been changed in 11gr2.

http://docs.oracle.com/cd/E11882_01/appdev.112/e10492/whatsnew.htm

>
Oracle Database 11g Release 2 (11.2.0.2) New Features in Oracle XML DB

The following Oracle XML DB features are new in Oracle Database 11g Release 2 (11.2.0.2).

Default Storage Model for XMLType

The default XMLType storage model is used if you do not specify a storage model when you create an XMLType table or column. Prior to Oracle Database 11g Release 2 (11.2.0.2), unstructured (CLOB) storage was used by default. The default storage model is now binary XML storage.




We have a application which works fine on r1 but not on r2 due to this change, we are going to investigate resolving the issue on the application in the future, in the mean time we need to be able to use the CLOB storage.

Does any one know where we can change this functionality back to the pre 11gR2 change?

thanks
Luke
  • 1. Re: Change default XML Storage
    Jason_(A_Non) Expert
    Currently Being Moderated
    When you create a table, the DDL simply needs to include
    STORE AS BASICFILE CLOB
    instead of
    STORE AS SECUREFILE BINARY XML

    Example:
    CREATE TABLE HOLDS_XML  -- old table structure
        (n_col   NUMBER(5) NOT NULL PRIMARY KEY,
         xml_col XMLTYPE)
      XMLTYPE xml_col STORE AS BASICFILE CLOB;
    If you don't include the 4th line, the Oracle uses the default, based on DB version. As you read, they changed the default from STORE AS BASICFILE CLOB for 11.1.0.6 thru 11.2.0.1 to STORE AS SECUREFILE BINARY XML for 11.2.0.2 onwards.

    I'm sure this forum and/or Oracle would be interested to know what this error is and how it relates to BINARY storage of the XML.
  • 2. Re: Change default XML Storage
    963618 Newbie
    Currently Being Moderated
    Thank you but we can not do this as it is the application which is performing CREATE TABLE, not a DBA.

    Thus for us to change the CREATE TABLE's would be a major application change and require hundreds of hours of development.

    The issue is when storing data as a Binary type escaped carriage returns are returned a literal carriage returns and are lost, however using CLOB the escaped CRs are returned correctly.

    This may seem like a small issue but it is causing us errors.

    Ideally we would like to return the originally storage of XMLTYPE schema or system wide.

    thanks
  • 3. Re: Change default XML Storage
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    You can enforce it via a alter session/system or database parameter setting or reducing the compatible parameter to a lesser value (if I remember correctly)

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm#BABJFEBB

    Edited by: Marco Gralike on Sep 22, 2012 3:30 AM
  • 4. Re: Change default XML Storage
    964325 Newbie
    Currently Being Moderated
    Hello Marco,

    Thanks for the pointer. Do you know if that would cause the same CREATE TABLE to use different storage for XMLTYPE?

    We (I'm working with Luke) have two Oracle instances and when the same SQL is run on both they end up having different storage types. Executing "SHOW PARAMETER DB_SECURE;" returns a value of "PERMITTED" on both instances, so maybe it is a user setting (I'm not a DBA)?

    The CREATE TABLE SQL:

    CREATE TABLE "MY_XML_TEST"
    (
    "ID" NUMBER(10, 0) NOT NULL,
    "XML" "SYS"."XMLTYPE" NOT NULL
    );

    Instance #1, an AWS RDS instance. Version banner reports "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production":

    CREATE TABLE "AWS"."MY_XML_TEST"
    (
    "ID" NUMBER(10,0) NOT NULL ENABLE,
    "XML" "SYS"."XMLTYPE" NOT NULL ENABLE
    )
    SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
    )
    TABLESPACE "AWS" XMLTYPE COLUMN "XML" STORE AS BASICFILE CLOB
    (
    TABLESPACE "AWS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    ) ;

    Instance #2, an Oracle RAC install. Version banner reports "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production"

    CREATE TABLE "MY_ORACLE"."MY_XML_TEST"
    (
    "ID" NUMBER(10,0) NOT NULL ENABLE,
    "XML" "SYS"."XMLTYPE" NOT NULL ENABLE
    )
    SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
    )
    TABLESPACE "MY_ORACLE" XMLTYPE COLUMN "XML" STORE AS SECUREFILE BINARY XML
    (
    TABLESPACE "MY_ORACLE" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    )
    ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;

    Any further pointers would be appreciated.

    Thanks.

    -Eric
  • 5. Re: Change default XML Storage
    Jason_(A_Non) Expert
    Currently Being Moderated
    Since Marco mentioned the compatible parameter, what does
    show parameter compatible
    return on the two different systems?
  • 6. Re: Change default XML Storage
    mdrake Expert
    Currently Being Moderated
    Going back to the Escaped Carriage returns.. Are they in an attribute
     <FOO junk="Goodbye &#10; Cruel World"/>
    In which case there is a a known nug for the fact that when re-serialize this document we generate
     <FOO junk="Goodbye
     Cruel World"/>
    And then we parse that we get
     <FOO junk="Goodbye Cruel World"/>
    The patch ensures that the CR remains escaped when the XML is seriailized
  • 7. Re: Change default XML Storage
    964325 Newbie
    Currently Being Moderated
    Hello A Non,

    Thanks for the input.

    Instance #1 (AWS) returns "11.2.0". Instance #2 (RAC) returns "11.2.0.0.0". Are these essentially equivalent?

    Thanks.

    -Eric
  • 8. Re: Change default XML Storage
    964325 Newbie
    Currently Being Moderated
    Hello mdrake,

    Yes, we are losing the escaped CRs in an attribute. To which bug/patch are you referring? Our assertion was that switching back to CLOB would prevent any processing of the XML and preserve our inserted XML.

    Thanks.

    -Eric
  • 9. Re: Change default XML Storage
    MGralike Newbie
    Currently Being Moderated
    From 11.2.0.2.0 and up, if allowed and applicable (tablespace has to be defined with ASSM otherwise --> error) every XMLTYPE will default to SECUREFILE. I can't remember the "workarounds" anymore exactly, because I use by default SECUREFILE anyway, but I think you can

    - You could force default behavior via "DB_SECUREFILE"
    - You could force default behavior by setting the "COMPATIBLE" database parameter to 11.2.0.1.0 or lower

    I thought there was also a third option, but can't remember currently.

    As is said by the info URL is pinpointing to:
    Initialization Parameter for SecureFiles
    
    The db_securefile parameter is set in the file init.ora:
    db_securefile
    
    Parameter Name: db_securefile
    
    Parameter Type: text
    
    Allowable Values: { ALWAYS | FORCE | PERMITTED | NEVER | IGNORE }
    
    Default Value: PERMITTED
    
    Description: This parameter enables the database administrator to either allow SECUREFILE LOBs to be created (PERMITTED), disallow SECUREFILE LOBs from being created 
    going forward (NEVER), force all LOBs created going forward to be SECUREFILE LOBs (FORCE), attempt to create SECUREFILE LOBs but fall back to BASICFILE LOBs (ALWAYS), 
    or disallow SECUREFILE LOBs and ignore any errors that would otherwise be caused by forcing BASICFILE LOBs with SECUREFILE options (IGNORE).
    
    If NEVER is specified, any LOBs that are specified as SECUREFILE LOBs are created as BASICFILE LOBs. All SECUREFILE specific storage options and features (for example, 
    compress, encrypt, deduplicate) will throw an exception. The BASICFILE LOB defaults are used for storage options not specified.
    
    ALWAYS attempts to create all LOBs as SECUREFILE LOBs but creates any LOBs not in ASSM tablespaces as BASICFILE LOBs, unless SECUREFILE is explicitly specified.
    Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified.
    
    If FORCE is specified, all LOBs created in the system will be created as SECUREFILE LOBs. If the LOB is being created in an MSSM tablespace, an error will be thrown. 
    Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified.
    
    If IGNORE is specified, the SECUREFILE keyword and all SECUREFILE options are ignored.
    
    Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.
    
    Example: ALTER SYSTEM SET db_securefile = 'ALWAYS';
    I once commented the reason for Oracle 's behavior change as (- why not to choose for "CLOB" aka "BASICFILE")

    - Binary XML CLOB will be depricated
    - Binary XML CLOB is slower then Securefile on all fronts
    - Binary XML CLOB is less efficient in storage
    - Binary XML Securefile is Oracle’s default from 11.2.0.2 and onwards
    - Binary XML Securefile is way faster regarding content driven XML selects / xpath / xquery than Binary XML CLOB/Basicfile storage by default.

    (apparently in 2010: http://www.liberidu.com/blog/2010/09/10/oracle-11-2-0-2-small-but-important-changes)
  • 10. Re: Change default XML Storage
    MGralike Newbie
    Currently Being Moderated
    We have a application which works fine on r1 but not on r2 due to this change, we are going to investigate resolving the issue on the application in the future, in the mean time we need to be able to use the CLOB storage.
    Can you give a hint of what the original issue is ("resolving the issue") ?
  • 11. Re: Change default XML Storage
    mdrake Expert
    Currently Being Moderated
    Much better to the get the issue fixed. We are going to depricate the CLOB storage option for XMLType in the near future...

    The switch to Binary XML was 11.2.0.2.0, so I suspect that is the difference, assuming both tablespaces are ASSM
  • 12. Re: Change default XML Storage
    MGralike Newbie
    Currently Being Moderated
    As Mark mentioned, its better to get this issue fixed instead of changing the behavior of the database for all sessions and processes that connect to this database. I never like to fix something on "database level" if it is not absolutely needed. Solving stuff on database level will also force a lot of (downgrade) behavior changes in the context of "doing stuff smarter", "bug fixes", "patching security holes", etc... Fix the problem where there is a problem. Do you guys also have a saying that mentions a "fly" and "canon" (approach) ?
  • 13. Re: Change default XML Storage
    mdrake Expert
    Currently Being Moderated
    Just to be sure, this is the issue
    SQL> drop table T1
      2  /
    
    Table dropped.
    
    SQL> create table T1 of XMLType
      2  /
    
    Table created.
    
    SQL> drop table T2
      2  /
    
    Table dropped.
    
    SQL> create Table T2 of XMLType
      2  XMLTYPE store as CLOB
      3  /
    
    Table created.
    
    SQL> VAR TEXT VARCHAR2(32)
    SQL> --
    SQL> begin
      2    :TEXT := '<FOO BAA="Hello &#0xA; World"/>';
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select XMLTYPE(:TEXT)
      2    from DUAL
      3  /
    
    XMLTYPE(:TEXT)
    --------------------------------------------------------------------------------
    <FOO BAA="Hello
     World"/>
    
    
    SQL> insert into T1 values (XMLTYPE (:TEXT))
      2  /
    
    1 row created.
    
    SQL> select *
      2    from T1
      3  /
    
    SYS_NC_ROWINFO$
    --------------------------------------------------------------------------------
    <FOO BAA="Hello
     World"/>
    
    
    SQL> select XMLPARSE(CONTENT XMLSERIALIZE(DOCUMENT OBJECT_VALUE AS CLOB)WELLFORMED)
      2    from T1
      3  /
    
    XMLPARSE(CONTENTXMLSERIALIZE(DOCUMENTOBJECT_VALUEASCLOB)WELLFORMED)
    --------------------------------------------------------------------------------
    <FOO BAA="Hello
     World"/>
    
    
    SQL> insert into T2 values (XMLTYPE (:TEXT))
      2  /
    
    1 row created.
    
    SQL> select *
      2    from T2
      3  /
    
    SYS_NC_ROWINFO$
    --------------------------------------------------------------------------------
    <FOO BAA="Hello
     World"/>
    
    
    SQL> select XMLPARSE(CONTENT XMLSERIALIZE(DOCUMENT OBJECT_VALUE AS CLOB) WELLFORMED)
      2    from T2
      3  /
    
    XMLPARSE(CONTENTXMLSERIALIZE(DOCUMENTOBJECT_VALUEASCLOB)WELLFORMED)
    --------------------------------------------------------------------------------
    <FOO BAA="Hello &#0xA; World"/>
    
    SQL>
  • 14. Re: Change default XML Storage
    mdrake Expert
    Currently Being Moderated
    Tracked by bug 14673958. Please contact support..
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points