A reader, August 09, 2006 - 8:12 am UTC
GRPAAA> SELECT TRIM ( office )
2 || ','
3 || TRIM ( farm )
4 || ','
5 || brn
6 || ','
7 || TRIM ( op_description )
8 || ','
9 || ALLOW
10 || ','
11 || frv
12 || ','
13 || insp
14 || ','
15 || desk
16 || ','
17 || admin_check
18 || ','
19 || claim_exists
20 || ','
21 || claim_authorised
22 || ','
23 || prev_prog
24 FROM erfc_10148
25 ORDER BY office, farm, op_description
26 /
SELECT TRIM ( office )
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TEMPGRP
Elapsed: 00:00:00.16
GRPAAA> select count(*) from erfc_10148;
COUNT(*)
----------
27925
1 row selected.
Elapsed: 00:00:00.03
GRPAAA>
Tom,
I get the above error when I run the query with those fields, but when I do a count(*) or a "select * from erfc_10148" the results are displayed.
Why would a query use a tablespace, is it to do the trim?
If queries use tablespaces, how could we predict which tablespace will be used by a query?
More info on that table:
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLUSTER_NAME IOT_NAME PCT_FREE
------------------------------ ------------------------------ ----------
PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
---------- ---------- ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS
----------- ------------ ---------- --------------- --- - ---------- ----------
EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANALYZ
------------------- ---------- ---------- ----- -------- ----------- -----------
PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON
--- ------------ - - --- ------- -------- --- --- --------------- -------- ---
CLUSTER_OWNER DEPENDEN COMPRESS
------------------------------ -------- --------
ERFC_10148 GRPAAADATA
10
40 1 255 40960 204800 1
505 1 1 1 YES N 27071 304
0 0 0 100 0
0 1 1 N ENABLED 27071 20-JUL-2006
NO N N NO DEFAULT DISABLED YES NO DISABLED YES
DISABLED DISABLED
1 row selected.
Elapsed: 00:00:00.03
GRPAAA>
August 09, 2006 - 10:51 am UTC
that is saying your temporary tablespace (which some queries will want to use and others won't) isn't quite working.
ask your dba about tempgrp - why is it "failing" - that would appear to be your temporary tablespace.
A reader, August 09, 2006 - 8:45 am UTC
Tom,
That is sorted,it appeared the temporary tablespace for the Sorting was filling up, was cleared by DBAs.
Thanks
Ravi