Re: analyze a table ,but get a ORA-00959 & ORA-06512

On Dec 12, 7:06 pm, gazzag <gar..._at_jamms.org> wrote:
> On 12 Dec, 10:55, one..._at_gmail.com wrote:
>
>
>
> > that's the problem:
> > SQL> select temporary_tablespace from dba_users where
> > username='SYSTEM';
>
> > TEMPORARY_TABLESPACE
> > ------------------------------
> > TEMP
>
> > so i change it :
> > SQL> show user
> > USER is "SYS"
> > SQL> alter user system temporary tablespace tbs_temp;
>
> > User altered.
>
> > SQL> select temporary_tablespace from dba_users where
> > username='SYSTEM';
>
> > TEMPORARY_TABLESPACE
> > ------------------------------
> > TBS_TEMP
>
> > but still got the error:
> > SQL> exec dbms_stats.gather_table_stats(ownname => 'TAOBAO', tabname
> > => 'BMW_GUEST', estimate_percent => 100, method_opt=> 'FOR ALL INDEXED
> > COLUMNS');
> > BEGIN dbms_stats.gather_table_stats(ownname => 'TAOBAO', tabname =>
> > 'BMW_GUEST', estimate_percent => 100, method_opt=> 'FOR ALL INDEXED
> > COLUMNS'); END;
>
> > *
> > ERROR at line 1:
> > ORA-00959: tablespace 'TEMP' does not exist
> > ORA-06512: at "SYS.DBMS_STATS", line 10301
> > ORA-06512: at "SYS.DBMS_STATS", line 10315
> > ORA-06512: at line 1
>
> > did i miss some step ?- Hide quoted text -
>
> Sorry, my fault. I meant, what does this query return:
>
> select temporary_tablespace
> from dba_users
> where username = 'SYS';
>
> HTH
>
> -g

thank you and i am so stupid。

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                            TEMP
SYSTEM                         TBS_TEMP
DBSNMP                         TEMP
TAOBAO                         TBS_TEMP
JIVE                           TBS_TEMP
PERFSTAT                       TBS_TEMP
TBUSER                         TBS_TEMP
TEST                           TBS_TEMP
APAY                           TBS_TEMP
DBFLASH                        TBS_TEMP
OUTLN                          TEMP
WMSYS                          TEMP

then, i changed them all, and it's ok.

But I have another question, if a user's default temporary tablespace were dropped , I think Oracle should use the db's default temporary tablespace as the user's , but actually it doesn't.

excuse my poor English !

thank you ! Received on Wed Dec 12 2007 - 05:21:56 CST