In regard to Intermedia text indexing, you know how when you create the index and you get the $R, $I tables etc. And by default the index places the LOB_SEGMENT INDEXES and TABLES in the same tablespace as the $ tables. Well, that's really annoyed me i.e with fragmentation, 1300 extents of 128K, so after I created my indexes, I dropped the corresponding $ tables and re-created them as follows.
CREATE TABLE DR$TEST_XML_IND$R (
ROW_NO NUMBER (3),
DATA BLOB)
LOB(DATA)
STORE AS TEST_$R_LOB_SEGMENT
(TABLESPACE LOB_DATA_INDX STORAGE (INITIAL 100M NEXT 100m)
INDEX TEST_$R_INDEX
(TABLESPACE LOB_DATA_INDX STORAGE (INITIAL 1M NEXT 1m)))
TABLESPACE RECORD_DATA NOLOGGING
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 4096
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
CREATE TABLE DR$TEST_XML_IND$K (
DOCID NUMBER (38),
TEXTKEY ROWID NOT NULL,
CONSTRAINT TEST_$K_PK
PRIMARY KEY ( TEXTKEY ))
ORGANIZATION INDEX NOCOMPRESS
TABLESPACE INDX PCTFREE 10
STORAGE ( INITIAL 50M NEXT 50M PCTINCREASE 0 ) ;
(Also for the other two $I and $
After I finish getting the $ table recreated and their indexes re-created, I manually inserted a record into on of the referencing tables and syncronized.
But I got the following error. Any ideas why, surely I can specify where I want the LOB_SEGMENT to go to?
Tue Jun 5 18:10:45 2001
Errors in file /opt/oracle/admin/CFA/bdump/cfa_snp2_9473.trc:
ORA-12012: error on auto execute of job 1
ORA-20000: interMedia Text error:
DRG-50857: oracle error in drekmap (select rowid row locator)
DRG-50858: OCI error: OCI_NO_DATA
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.CTX_DDL", line 1298
ORA-06512: at "CTXSYS.SYNC_CERS_INDEX", line 7
ORA-06512: at line 1
The trace file has the same information in it also.
Then I did the following
alter index test_xml_ind rebuild;
To my horror, it recreated the $ tables and stuck them all back in the origional tablespace. AHHHH!. Then I inserted another row into the referencing table and re-ran the SYNCRONZISE and guess what...it worked.
What I want to know is.
a). Why can't I create the $'s lob segments in a separate tablespace from the $ tables, therefore minimuzing fragmentation.
b). Why can I specify my OWN storage parameters for these LOBSEGMENTS. I mean it allowed me to do it, but wouldn't process the records.
Don't get ths confused with the the following :-
ctx_ddl.create_preference('storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('storage', 'I_TABLE_CLAUSE',
'tablespace RECORD_DATA storage (initial 500M next 100M)');
ctx_ddl.set_attribute('storage', 'K_TABLE_CLAUSE',
'tablespace RECORD_DATA storage (initial 1M next 1M)');
ctx_ddl.set_attribute('storage', 'R_TABLE_CLAUSE',
'tablespace RECORD_DATA storage (initial 1M next 1M)');
ctx_ddl.set_attribute('storage', 'N_TABLE_CLAUSE',
'tablespace RECORD_DATA storage (initial 1M next 1M)');
ctx_ddl.set_attribute('cfastorage', 'I_INDEX_CLAUSE',
'tablespace RECORD_INDX storage (initial 500M next 100M)');
ctx_ddl.set_attribute('storage', 'P_TABLE_CLAUSE',
'tablespace RECORD_DATA storage (initial 1M next 1M);
As yes, I know this is the tables storage paramters, but I want to modify the LOB_SEGMENTS and LOB_INDEXES, which at the moment have the really easy names to read i.e. SYS_LOB0000026885C00002$$ with the great storage paramters as INITIAL 128K and NEXT 128 K. Some of my index lob_segments end up with 1,300 extents, what a pain in the butt.
Please and ideas into this matter would be appreciated.
Cheers,
