Where is the executing user's temporary tablespace assigned? You may want
to issue 'alter user OPS$XYZ temporary tablespace sort_tablespace_name;'
and retry this as OPS$XYZ.
Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in article >...
> Whenever an index is created by any Oracle user in a particular
tablespace,
> it seems to me that SYS creates the index first in its default
> tablespace and then moves it to the designated tablespace.
> Here is what happened:
> I was creating an 150M index with the following statement in SQL*Plus
under
> OPS$XYZ,
> which has also the DBA privileges --
> create index
> OPS$pXYZ.hist_alarm_ndx1
> on OPS$XYZ.hist_alarm ( meter_id, create_date desc, alarm_date desc
)
> tablespace EFM_HIST_IDX
> storage( initial 70M next 20M pctincrease 0 )
> pctfree 5
> /
> SYS's default tablespace is SCRATCH. Entire SCRATCH tablespace is 125M.
> EFM_HIST_IDX tablespace had about 190M freespace,
> with 2 big chunks of 83M contiguous freespace.
> Index creation took about 10 minutes.
> I monitored what was being generated in EFM_HIST_IDX and SCRATCH
> tablespaces using
> dba_free_space and dba_extents views/tables.
> Here is what I noticed:
> EFM_HIST_IDX had reserved 70M under the segment_name 10.3 owned by
OPS$XYZ.
> SCRATCH had several blocks under the same segment_name 10.3 owned by SYS.
> Then I got the message " unable to extend ..... in table space SCRATCH."
> Of course I resized the SCRATCH to 200M and it worked.
> The Question :
> Is this the way index creation supposed to work or is there something
> hidden that I am missing.
> Thanks in advance for your input and time>
Received on Fri May 29 1998 - 10:20:44 CDT
