Hi,
My problem is similar to what Mike has experienced.
But it was more severe in my case as I was tight on
space and I had to wait till it releases the temporary
segments it had used up on my regular tablespace(Yes,
I have a separate and distinct temporary tablespace).
I also faced the same Ora-1630 error after which I
increased the extent size and finished the job.
I am aware that Oracle uses the system tablespace for
sorting if you dont assign a temporary tablespace,
which is not the case here.
Thanks,
Ravi
- "Malinowski, Mike (CAP, CARD)"
<Mike.Malinowski_at_gecapital.com> wrote:
> I'm interpreting the sentence "I do have a temporary
> tablespace assigned
> which has a 300 Meg available when I ran the script"
> to mean that Ravi has
> assigned separate and distinct "default" and
> "temporary" tablespaces. I
> have observed this same behavior and am curious too.
>
> Allow me to clarify that in my case, there is a user
> with default tablespace
> "USER_TBL" and temporary tablespace "TEMP". This
> user also has unlimited
> quota on "USER_TBL", "USER_IDX", and "TEMP". I will
> on occasion see an
> ORA-1630 error in my "alert_<OracleSID>.log" file
> indicating a temp segment
> in the "USER_IDX" tablespace ran out of extents. I
> might be able to explain
> this away if it happened while I was creating an
> index in the "USER_IDX"
> tablespace, but that's not happening when this error
> occurs.
>
> Ravi, does this also reflect what you are observing?
>
> Mike.
>
> -----Original Message-----
> [mailto:DBA.Gurgaon_at_geind.GE.com]
> Sent: Monday, 2000:May:29 06:55
> To: Multiple recipients of list ORACLE-L
>
>
> Ravi
>
> Default tablespace can be used for sorting only if
> it has been defined as
> temporary tablespace instead of your temp tablespace
> for that particular
> user in which you are working.
> Otherwise if you don't specify any temporary
> tablespace for that particular
> user,than it takes system tablespace as temporary
> tablespace.
>
> Vikas
>
> -----Original Message-----
> Sent: Monday, May 29, 2000 7:14 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi List,
>
> I am doing some DDL operations with some sorting
> (create as select * from...) in the database using
> tables which are large(200 Meg+).
> I am surprised to see Oracle is using the default
> tablespace(it already has some objects) to allocate
> the temporary segments for sorting and failed to
> execute the script as it ran out of extents. Later
> we
> increased the extent size on the default tablespace
> and finished it. I could see this by querying the
> dba_segments as these temporary segments were kept
> for
> a while before SMON cleaned them.
>
> It was doing fine before.
>
> I would like to know, under what circumstances
> oracle
> uses the default tablespace as the temporary
> tablespace? I do have a temporary tablespace
> assigned
> which has a 300 Meg available when I ran the script.
> I am running Orcl ver:7.3.2 on Sun solaris 2.5.
> Thanks In Advance
> Ravi
>
> __________________________________________________
> Do You Yahoo!?
> Kick off your party with Yahoo! Invites.
> http://invites.yahoo.com/
> --
> Author: ravi chandra
> INET: ravi4671_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (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).
> --
> Author: Gurgaon, DBA (CAP, GECSI, CONTRACTOR)
> INET: DBA.Gurgaon_at_geind.GE.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (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).
> --
> Author: Malinowski, Mike (CAP, CARD)
> INET: Mike.Malinowski_at_gecapital.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (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).
Received on Tue May 30 2000 - 12:21:21 CDT
