Hi:
The problem has been fixed. It turned out that we have very big storage
clause for "isistore". All these Intermedia tables will be created when
InterMedia index gets created. So it would try to allocate six 1000M initial
extents. And we don't have that many empty block in RESINDEX tablespace.
begin
ctx_ddl.create_preference('isistore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('isistore', 'I_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'K_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'R_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'N_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'I_INDEX_CLAUSE',
'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'P_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 1000M next 500M pctincrease 0)');
end;
So I run the following to reduce the initial setting to 100M. After that
everything is OK.
begin
-- ctx_ddl.create_preference('isistore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('isistore', 'I_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'K_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'R_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'N_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'I_INDEX_CLAUSE',
'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
ctx_ddl.set_attribute('isistore', 'P_TABLE_CLAUSE',
'tablespace RESINDEX storage (initial 100M next 100M pctincrease 0)');
end;
Guang
-----Original Message-----
[EMAIL PROTECTED]
Sent: Friday, March 28, 2003 8:24 AM
To: Multiple recipients of list ORACLE-L
Guang,
Is your RESINDEX tablespace a Locally-Managed Tablespace with Uniform
Extents of less than 3 database blocks? If so, that's your problem.
InterMedia indexes consist of some LOB segments and those require extents
of at least 3 database blocks - at least in 8i.
If not, I don't know.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Guang Mei
<[EMAIL PROTECTED]> To: Multiple recipients
of list ORACLE-L
Sent by: <[EMAIL PROTECTED]>
[EMAIL PROTECTED] cc:
Subject: create interMedia
index problem
03/27/2003 10:53
PM
Please respond to
ORACLE-L
Hi:
I have this problem on our production server and I don't know too much
about InterMedia stuff. We have oracle 8173 on Sun Solaris 2.8.
[EMAIL PROTECTED]> desc DRUGDATA;
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER
DRUGREPID NOT NULL NUMBER
FIELD NOT NULL NUMBER
TEXT VARCHAR2(4000)
DRUGTERMID NUMBER
DATATYPEID NOT NULL NUMBER
ADD_FILE NOT NULL VARCHAR2(32)
ADD_DATE NOT NULL DATE
DEL_FILE VARCHAR2(32)
DEL_DATE DATE
STATUS NOT NULL CHAR(1)
ORDERBY NOT NULL NUMBER
[EMAIL PROTECTED]> select count(*) from DRUGDATA;
COUNT(*)
----------
0
TABLESPACE_NAME USED-Kb ALLOC-Kb USED% SEGS >EXT >NEXTEXT
-------------------- ----------- ----------- ------ ----- ----- --------
DATA 3,000 1,048,576 .3 1 1 504
INDEXES 10,664,424 14,680,064 72.6 480 11 693,080
PERFSTAT 105,728 2,097,152 5.0 56 150 128
PROTEOME 164,872 1,048,576 15.7 52 19 25,600
RBS 5,131,360 8,388,608 61.2 6 800 2,048
RESCTX 43,832 6,803,456 .6 66 28 504
RESDATA 34,470,408 46,137,344 74.7 361 2319 ########
RESINDEX 41,046,376 69,206,016 59.3 300 1063 512,000
SYSTEM 74,320 153,600 48.4 401 115 1,120
TEMP 10,224,960 10,240,000 99.9 1 2045 5,000
YPD 12,769,224 16,777,216 76.1 249 ##### 292,976
TABLESPACE_NAME USED-Kb ALLOC-Kb USED% SEGS >EXT >NEXTEXT
-------------------- ----------- ----------- ------ ----- ----- --------
YPDCUST 0 1,048,576 .0 0 0 0
Then I ran
create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT)
indextype is ctxsys.context
parameters ('LEXER ctxsys.ISILEX WORDLIST ctxsys.ISIWORDLIST
STOPLIST ctxsys.ISISTOP storage isistore memory 50M');
I got
create index DRUGDATAINDEX_TEXT on DRUGDATA (TEXT)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
DRG-50857: oracle error in drixtab.create_index_tables
ORA-01658: unable to create INITIAL extent for segment in tablespace
RESINDEX
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1
and I got from alert_log file:
ORA-1652: unable to extend temp segment by 128000 in tablespace
RESINDEX
I did coalesce on all tablespace and added another 2G datafile on RESINDEX
tablespace, I still got the same error. BTW, I could run the same sql on
two other DEV instances without any problem. The RESINDEX ts on them are
much more filled (like 90% full).
Any idea what might be the problem? TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
