5 Replies Latest reply: May 10, 2013 7:42 PM by Marco Gralike RSS

    ORA-29896: Length of PARAMETER string longer than 1000 characters

    Marco Gralike
      Let's describe it as - " I always want to much " (but I guess there is no solution / only a workaround...) ???
      SQL> drop  index WIKI_XMLINDEX01 force;
      
      Index dropped.
      
      CREATE INDEX WIKI_XMLINDEX01 ON "WIKI_STAGE"(object_value) 
        INDEXTYPE IS XDB.XMLINDEX
        PARAMETERS ('PATHS (INCLUDE (/page/revision/comment)
                            NAMESPACE MAPPING (xmlns="http://www.mediawiki.org/xml/export-0.3/"))
                            PATH TABLE      path_table_01
                                ( PCTFREE 5 PCTUSED 90 INITRANS 5
                                  STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
                                           NOLOGGING ENABLE ROW MOVEMENT PARALLEL 4
                                          )
                                  TABLESPACE mediawiki_xmlindex
                                )
                            PATH ID INDEX   pathid_xmlindex_01
                                (PCTFREE 5 PCTUSED 90 INITRANS 5
                                  STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
                                           NOLOGGING PARALLEL 4
                                          )
                                  TABLESPACE mediawiki_xmlindex
                                 )
                             ORDER KEY INDEX orderkey_xmlindex_01
                                (PCTFREE 5 PCTUSED 90 INITRANS 5
                                  STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
                                           NOLOGGING PARALLEL 4
                                          )
                                  TABLESPACE mediawiki_xmlindex
                                 )
                    '); 
      CREATE INDEX WIKI_XMLINDEX01 ON "WIKI_STAGE"(object_value)
      *
      ERROR at line 1:
      ORA-29896: Length of PARAMETER string longer than 1000 characters
      Solved it by wanting "less" (but I guess there is still an PARAMETER issue here).

      SQL> drop  index WIKI_XMLINDEX01 force;
      
      Index dropped.
      
      CREATE INDEX WIKI_XMLINDEX01 ON "WIKI_STAGE"(object_value) 
      INDEXTYPE IS XDB.XMLINDEX
      PARAMETERS ('PATHS (INCLUDE (/page/revision/comment)
                          NAMESPACE MAPPING (xmlns="http://www.mediawiki.org/xml/export-0.3/"))
                          PATH TABLE      path_table_01
                              (NOLOGGING ENABLE ROW MOVEMENT PARALLEL 4 TABLESPACE mediawiki_xmlindex )
                          PATH ID INDEX   pathid_xmlindex_01
                              (NOLOGGING PARALLEL 4 TABLESPACE mediawiki_xmlindex )
                          ORDER KEY INDEX orderkey_xmlindex_01
                              (NOLOGGING PARALLEL 4 TABLESPACE mediawiki_xmlindex )
                  '); 
      Message was edited by:
      Marco Gralike
        • 1. Re: ORA-29896: Length of PARAMETER string longer than 1000 characters
          594554
          Marco,

          This is indeed a limitation for the XMLIndex in 11gR1. Here are a couple of suggestions:

          1. Some secondary index storage options are defaulted from the overall XMLindex/PathTable and these might very well be sufficient for your use. Thus, you can avoid specifying these explicitly for the secondary indexes.
          2. Some options can be specified in a subsequent ALTER INDEX statement. But keep in mind that some clauses might trigger a full index rebuild so it's better to specify them upfront in the CREATE INDEX statement.
          3. In the next release, this length limitation would be removed.

          Hope this helps
          -Chandra.
          • 2. Re: ORA-29896: Length of PARAMETER string longer than 1000 characters
            Marco Gralike
            Thank you Chandra for your reply. This was only one of the minor TEST items I got and more or less easy to bring up a workaround.

            Yesterday was a typical Monday and I wasn't really able to make some progress. Because this fiddling around is only some personal testing I didn't want to create a SR/SR's, although I think it is time to do some mutual real testing. I have the test scripting still at hand.


            Does the ODCI make use off the standard memory structures while creating the domain index structures. I increased all memory SGA / PGA structures but only could get it to work after deleting 2/3th of my XML records.
            CREATE INDEX WIKI_XMLINDEX01 ON "WIKI_STAGE"(object_value)
            *
            ERROR at line 1:
            ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
            ORA-29400: data cartridge error
            ORA-04030: out of process memory when trying to allocate 36368 bytes (qmixmCrsrCtx:b,qmemNextBuf:Large Alloc)
            BTW the following is incorrect
            SQL> select dbms_metadata.get_ddl('TABLE','WIKI_STAGE') from dual;
            
            DBMS_METADATA.GET_DDL('TABLE','WIKI_STAGE')
            --------------------------------------------------------------------------------
              CREATE TABLE "WIKI"."WIKI_STAGE" OF "SYS"."XMLTYPE"
             OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS255
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
              TABLESPACE "MEDIAWIKI_STAGE" )
             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)
              TABLESPACE "MEDIAWIKI_STAGE"
             XMLTYPE COLUMN "SYS_NC_ROWINFO$" STORE AS SECUREFILE BINARY XML (
              TABLESPACE "MEDIAWIKI_STAGE" ENABLE STORAGE IN ROW CHUNK 8192
              NOCACHE LOGGING  DECRYPT  NOCOMPRESS  KEEP_DUPLICATES
              STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 BUFFER_POOL DEFAULT)) 
              ALLOWNONSCHEMA DISALLOW ANYSCHEMA
            Mind the "ALLOWNONSCHEMA"...

            Sorry I am bitching here, but as said it was one off those horrible Mondays...

            Message was edited by:
            Marco Gralike
            • 3. Re: ORA-29896: Length of PARAMETER string longer than 1000 characters
              985301
              This issue still persists in 11.2.0.3. Any idea when the restriction of 1000 characters will be removed?
              • 4. Re: ORA-29896: Length of PARAMETER string longer than 1000 characters
                Marco Gralike
                I think we still have to wait. In the greatest/latest release, the following happens. Thanks for the reminder - to actually check. DBMS_XMLINDEX is still your friend.
                SQL> create user otn identified by otn account unlock;
                
                User created.
                
                SQL> grant dba, xdbadmin to otn;
                
                Grant succeeded.
                
                SQL> conn otn/otn
                Connected.
                
                SQL> create table wiki_stage of xmltype;
                
                Table created.
                
                SQL> CREATE INDEX WIKI_XMLINDEX01 ON "WIKI_STAGE"(object_value)
                  2    INDEXTYPE IS XDB.XMLINDEX
                  3    PARAMETERS ('PATHS (INCLUDE (/page/revision/comment)
                  4                  NAMESPACE MAPPING (xmlns="http://www.mediawiki.org/xml/export-0.3/"))
                  5                  PATH TABLE        path_table_01
                  6                      ( PCTFREE 5 PCTUSED 90 INITRANS 5
                  7                     STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
                  8                           NOLOGGING ENABLE ROW MOVEMENT PARALLEL 4
                  9                          )
                 10                     TABLESPACE mediawiki_xmlindex
                 11                      )
                 12                  PATH ID INDEX   pathid_xmlindex_01
                 13                      (PCTFREE 5 PCTUSED 90 INITRANS 5
                 14                     STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
                 15                           NOLOGGING PARALLEL 4
                 16                          )
                 17                     TABLESPACE mediawiki_xmlindex
                 18                    )
                 19                   ORDER KEY INDEX orderkey_xmlindex_01
                 20                      (PCTFREE 5 PCTUSED 90 INITRANS 5
                 21                     STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
                 22                           NOLOGGING PARALLEL 4
                 23                          )
                 24                     TABLESPACE mediawiki_xmlindex
                 25                    )
                 26*             ')
                CREATE INDEX WIKI_XMLINDEX01 ON "WIKI_STAGE"(object_value)
                *
                ERROR at line 1:
                ORA-29896: Length of PARAMETER string longer than 1000 characters
                Edited by: Marco Gralike on May 11, 2013 2:40 AM
                • 5. Re: ORA-29896: Length of PARAMETER string longer than 1000 characters
                  Marco Gralike
                  I, or you, could request via a SR, for an enhancement request... Not sure though, if I haven't already done so a long long time ago, but a reminder can't hurt I guess...