Re: Getting ORA-14452 when trying to gather stats

Ian was correct, I lost the tempfiles for my temporary tablespace. After readding them, everything worked as expected. Thank you.

Sandy

On 11/17/06, Ian Cary (C) <Ian.Cary_at_ordnancesurvey.co.uk> wrote:
>
> It looks to me as though you may have lost the tempfiles from your
> temporary tablespace.
>
>
>
> You can try adding one by using ALTER TABLESPACE <*your temp tablespace
> name*> ADD TEMPFILE '<*temp file name*>' size *<required size>*M;
>
>
>
> Cheers,
>
>
>
> Ian
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sandra Becker
> *Sent:* 16 November 2006 21:57
> *To:* oracle-l
> *Subject:* Getting ORA-14452 when trying to gather stats
>
>
>
> I recently upgrade to 9.2.0.8 and moved from 32-bit to 64-bit on Linux
> RHEL4. I was testing all my scripts to make sure everything still worked as
> expected. Everything seemed to be working well until I tried to gather
> stats on my primary application schema. Following is my code and errors. I
> would appreciate any insight as to where my problem might be. I have opened
> an SR with Oracle, but they haven't responded yet.
>
>
>
> execute dbms_stats.gather_schema_stats(ownname=>'MONITOR',-
> BLOCK_SAMPLE=>false,-
> METHOD_OPT=>'FOR ALL INDEXED COLUMNS',-
> CASCADE=>TRUE,-
> ESTIMATE_PERCENT=>20) ;
>
>
> BEGIN dbms_stats.gather_schema_stats(ownname=>'MONITOR',
> BLOCK_SAMPLE=>false, METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
> CASCADE=>TRUE, ESTIMATE_PERCENT=>20) ; END;
>
> *
> ERROR at line 1:
> ORA-14452: attempt to create, alter or drop an index on temporary table
> already
> in use
> ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 284
> ORA-06512: at "SYS.DBMS_STATS", line 10265
> ORA-14452: attempt to create, alter or drop an index on temporary table
> already
> in use
> ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 284
> ORA-06512: at "SYS.DBMS_STATS", line 6670
> ORA-06512: at " SYS.DBMS_STATS", line 6696
> ORA-25153: Temporary Tablespace is Empty
> ORA-06512: at "SYS.DBMS_STATS", line 10502
> ORA-06512: at "SYS.DBMS_STATS", line 10996
> ORA-06512: at "SYS.DBMS_STATS ", line 11183
> ORA-06512: at "SYS.DBMS_STATS", line 11237
> ORA-06512: at "SYS.DBMS_STATS", line 11214
> ORA-06512: at line 1
>
>
> Elapsed: 00:00:00.16
>
> Sandy
>
> .
>
>
> This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person.
>
> Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.
>
> Thank you for your cooperation.
>
> Ordnance Survey
> Romsey Road
> Southampton SO16 4GU
> Tel: 08456 050505http://www.ordnancesurvey.co.uk
>
>

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