Re: ORA-01630

Celine John wrote:
>
> Hi DBAs,
> I get the following message:
> ORA-01630: max # extents () reached in temp segment in
> tablespace
>
> when I am trying to create a snapshot as follows...
> create snapshot SNAP_A
> storage (initial 30M next 15 pctincrease 0)
> tablespace TS_A
> refresh complete with primary key as
> SELECT *
> FROM abc.tbl_a_at_prod
>
> There are about 3 million rows in the table.
>
> Can somebody tell me what I need to look at and what
> should I do?
>
> Is it the users default tablespace which in my case is
> TEMP, that is running out of space?
>
> Thanks so much.
>

Refresh complete on 3 million rows! wow. First, check the current size of the table you refresh from, and set size around the same order of magnitude. I think that it's probably in TS_A that you have a problem. You should not have any if the size is properly set initially. Now, you can also add MAXEXTENTS UNLIMITED to your storage clause - or set it as the default storage (check ALTER TABLESPACE in TFM) level.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).