Re: ORA-01652 on none temporary tablespace

No issues with creating the table.

Our current workaround was to extend the datafile.

Thanks

On Tue, Jun 23, 2015 at 6:43 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> It does not happen with regular DML as that inserts directly into the
> table segment. Parallel DML would behave differently.
>
> You can run a small test case:
>
> create tablespace myts datafile size 1m autoextend off;
> create table t tablespace myts as select * from all_users;
>
> Then run various statements and see what happens.
>
>
>
>
>
> On Tue, Jun 23, 2015 at 5:07 PM, seekuel <seekuel_at_gmail.com> wrote:
>
>> Hi Stefan,
>>
>> Thank you for detailed steps. In our scenario, does it also apply in
>> insert and update? When the error occurs its mostly when there is an update
>> or insert. We used the database for datawarehouse.
>>
>> Thank you
>>
>> On Tue, Jun 23, 2015 at 5:58 PM, Stefan Knecht <knecht.stefan_at_gmail.com>
>> wrote:
>>
>>> This is something that Oracle does if you're running DDL statements such
>>> as create table as select or create index. Oracle will allocate the segment
>>> as temporary, populate it with data and then flag it as a permanent segment
>>> before completing the operation (in very roughly outlined steps).
>>>
>>>
>>>
>>> On Tue, Jun 23, 2015 at 3:26 PM, seekuel <seekuel_at_gmail.com> wrote:
>>>
>>>> Hi All,
>>>>
>>>> We got an error ORA-01652 on tablespace LL_DATA. LL_DATA is not a
>>>> temporary tablespace. There is still a 14G free for the tablespace use.
>>>>
>>>> *error:*
>>>> ORA-01652: unable to extend temp segment by 32 in tablespace LL_DATA
>>>> our database version is 11.2.0.3.0
>>>>
>>>> This is a bit confusing and I am hoping that someone might came across
>>>> with this type of scenario and help with troubleshooting.
>>>>
>>>> Thank you
>>>>
>>>
>>>
>>
>

--
http://www.freelists.org/webpage/oracle-l