RE: ORA-1652: unable to extend temp segment by 8091 in tablespace

2003-08-18 Thread Roger Xu 




Thanks all. The replies have been really helpful.

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Friday, August 15, 2003 6:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  ORA-1652: unable to extend temp segment by 8091 in 
  tablespaceRaju is right, 
  it's late on a Friday, I had a brain fart. This will tell you if you have other objects in your 
  system tablespace.select owner, 
  'TABLE' object_type, table_name from 
  dba_tables where owner not in 
  ('SYS','MDSYS','OUTLN','WMSYS','SYSTEM','ORDSYS') and tablespace_name = 'SYSTEM' union select 
  owner, 'INDEX' object_type, index_name from dba_indexes 
  where owner not in 
  ('SYS','MDSYS','OUTLN','WMSYS','SYSTEM','ORDSYS') and tablespace_name = 'SYSTEM'; 
  If you find other owners objects in 
  SYSTEM, particularly SAPR3 objects, they should be moved to the proper tablespace. HTH jared 
  


  
  raju pa 
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 
 08/15/2003 04:34 PM 
 Please respond to ORACLE-L 
                      To:     
       Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>         cc:       
        
  Subject:        RE: ORA-1652: unable to 
extend temp segment by 8091 in tablespace         
  SYSTEMEven if the default tablespace for user is other 
  than 'SYSTEM' question is :   Where does the 
  data from anlyze go? To some catalog table? What you can do is : 
    1) Check if 'SYSTEM' has any other user 
  non-system/sys user objects. Move them away. I do not know if moving system 
  objects out of 'system' tablespace is advisable. Maybe not. 2) Check and add space for  'SYSTEM' 
  tablespace to be safe.Roger Xu 
  <[EMAIL PROTECTED]> wrote: USERNAME       
   DEFAULT_TABLESP TEMPORARY_TABLE--- --- 
  ---SAPR3           PSAPUSER1D   
     PSAPTEMPOPS$ORAQA2      SYSTEM     
       PSAPTEMPOPS$QA2ADM      SYSTEM   
         PSAPTEMP   -Original 
  Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Friday, August 15, 2003 4:28 
  PMTo: [EMAIL PROTECTED]Cc: Roger 
  XuSubject: Re: ORA-1652: unable to extend temp segment by 8091 in 
  tablespace SYSTEMImportance: HighWhat does this query return? select   username, default_tablespace, 
  temporary_tablespace from dba_users where username like 'OPS$%' or 
  username = 'SAPR3' 
  Jared 
  


  
  "Roger Xu" 
<[EMAIL PROTECTED]> Sent by: 
[EMAIL PROTECTED] 
 08/15/2003 12:34 PM Please respond to 
ORACLE-L 
          
       To: 
       Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
       cc: 
        
      
 Subject:        ORA-1652: unable to extend 
temp segment by 8091 in tablespace           
SYSTEMHello,While 
  user OPS$ORAQA2 running:ANALYZE TABLE "SAPR3"."BSIS" COMPUTE STATISTICS 
  FOR TABLE FOR ALL INDEXED COLUMNS SIZE 1 FOR ALL INDEXESI had the 
  following error:SQL error -1652 at location 
  stats_tab_collect-4ORA-1652: unable to extend temp segment by 8091 in 
  tablespace           SYSTEMA default 
  temporary tablespace w! as assigned to the user 
  OPS$ORAQA2.Thanks,Roger XuDatabase AdministratorDr 
  Pepper Bottling Company of Texas(972)721-8337-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net-- Author: Roger 
  XuINET: [EMAIL PROTECTED]Fat City Network Services   
   -- 858-538-5051 http://www.fatcity.comSan Diego, California   
       -- Mailing list and web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).For technical support please email 
  [EMAIL PROTECTED] or you cancall (972)721-8257. This email has 
  been scanned for all viruses by the MessageLabs Email Security System. 
  
  
  Do you Yahoo!?SBC Yahoo! DSL - Now only $29.95 per month! 
  
  For technical support please email [EMAIL PROTECTED] or you 
  cancall (972)721-8257. This email has been scanned for all viruses by 
  the MessageLabs Email Security System.

RE: ORA-1652: unable to extend temp segment by 8091 in tablespace

2003-08-15 Thread Jared . Still 

Raju is right, it's late on a Friday, I had a brain fart.

This will tell you if you have other objects in your system tablespace.

select owner, 'TABLE' object_type, table_name
from 
dba_tables
where owner not in ('SYS','MDSYS','OUTLN','WMSYS','SYSTEM','ORDSYS')
and tablespace_name = 'SYSTEM'
union
select owner, 'INDEX' object_type, index_name
from 
dba_indexes
where owner not in ('SYS','MDSYS','OUTLN','WMSYS','SYSTEM','ORDSYS')
and tablespace_name = 'SYSTEM';


If you find other owners objects in SYSTEM, particularly SAPR3 objects, they 
should be moved to the proper tablespace.

HTH

jared









raju pa <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 08/15/2003 04:34 PM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM


Even if the default tablespace for user is other than 'SYSTEM' question is :
 
Where does the data from anlyze go? To some catalog table? What you can do is :
 
1) Check if 'SYSTEM' has any other user non-system/sys user objects. Move them away. I do not know if moving system objects out of 'system' tablespace is advisable. Maybe not. 
2) Check and add space for  'SYSTEM' tablespace to be safe.

Roger Xu <[EMAIL PROTECTED]> wrote:
USERNAME        DEFAULT_TABLESP TEMPORARY_TABLE
--- --- ---
SAPR3           PSAPUSER1D      PSAPTEMP
OPS$ORAQA2      SYSTEM          PSAPTEMP
OPS$QA2ADM      SYSTEM          PSAPTEMP
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, August 15, 2003 4:28 PM
To: [EMAIL PROTECTED]
Cc: Roger Xu
Subject: Re: ORA-1652: unable to extend temp segment by 8091 in tablespace SYSTEM
Importance: High


What does this query return? 

select   username, default_tablespace, temporary_tablespace 
from dba_users 
where username like 'OPS$%' or username = 'SAPR3' 


Jared 






"Roger Xu" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 08/15/2003 12:34 PM 
 Please respond to ORACLE-L 
        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
        cc:         
        Subject:        ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM



Hello,

While user OPS$ORAQA2 running:
ANALYZE TABLE "SAPR3"."BSIS" COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS SIZE 1 FOR ALL INDEXES

I had the following error:
SQL error -1652 at location stats_tab_collect-4
ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM

A default temporary tablespace w! as assigned to the user OPS$ORAQA2.

Thanks,

Roger Xu
Database Administrator
Dr Pepper Bottling Company of Texas
(972)721-8337

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Roger Xu
 INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).



For technical support please email [EMAIL PROTECTED] or you can
call (972)721-8257. 
This email has been scanned for all viruses by the MessageLabs Email Security System.

Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!

RE: ORA-1652: unable to extend temp segment by 8091 in tablespace SYSTEM

2003-08-15 Thread raju pa 
Even if the default tablespace for user is other than 'SYSTEM' question is :
 
Where does the data from anlyze go? To some catalog table? What you can do is :
 
1) Check if 'SYSTEM' has any other user non-system/sys user objects. Move them away. I do not know if moving system objects out of 'system' tablespace is advisable. Maybe not. 
2) Check and add space for  'SYSTEM' tablespace to be safe.Roger Xu <[EMAIL PROTECTED]> wrote:



USERNAME    DEFAULT_TABLESP TEMPORARY_TABLE--- --- ---SAPR3   PSAPUSER1D  PSAPTEMPOPS$ORAQA2  SYSTEM  PSAPTEMPOPS$QA2ADM  SYSTEM  PSAPTEMP
 

-Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Friday, August 15, 2003 4:28 PMTo: [EMAIL PROTECTED]Cc: Roger XuSubject: Re: ORA-1652: unable to extend temp segment by 8091 in tablespace SYSTEMImportance: HighWhat does this query return? select   username, default_tablespace, temporary_tablespace from dba_users where username like 'OPS$%' or username = 'SAPR3' Jared 




"Roger Xu" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 
 08/15/2003 12:34 PM  Please respond to ORACLE-L 
                To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>         cc:                 Subject:        ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEMHello,While user OPS$ORAQA2 running:ANALYZE TABLE "SAPR3"."BSIS" COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS SIZE 1 FOR ALL INDEXESI had the following error:SQL error -1652 at location stats_tab_collect-4ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEMA default temporary tablespace w!
as
 assigned to the user OPS$ORAQA2.Thanks,Roger XuDatabase AdministratorDr Pepper Bottling Company of Texas(972)721-8337-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Roger Xu INET: [EMAIL PROTECTED]Fat City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, California        -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).For technical support please email [EMAIL PROTECTED] or you cancall
 (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System.
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!

RE: ORA-1652: unable to extend temp segment by 8091 in tablespace

2003-08-15 Thread Jared . Still 

You need to change the default tablespace for those OPS$ users

alter user OPS$ORAQA2 default tablespace PSAPUSER1D;

and

alter user OPS$QA2ADM default tablespace PSAPUSER1D;

should do it.

The only user that should have the SYSTEM tablespace as a
default is the SYS user.  All others should be somewhere else.

use this this query to check for others:

select username 
from dba_users
where default_tablespace = 'SYSTEM'
and username not in ('SYS');

Jared









"Roger Xu" <[EMAIL PROTECTED]>
 08/15/2003 02:46 PM

        
        To:        <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM


USERNAME        DEFAULT_TABLESP TEMPORARY_TABLE
--- --- ---
SAPR3           PSAPUSER1D      PSAPTEMP
OPS$ORAQA2      SYSTEM          PSAPTEMP
OPS$QA2ADM      SYSTEM          PSAPTEMP
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, August 15, 2003 4:28 PM
To: [EMAIL PROTECTED]
Cc: Roger Xu
Subject: Re: ORA-1652: unable to extend temp segment by 8091 in tablespace SYSTEM
Importance: High


What does this query return? 

select   username, default_tablespace, temporary_tablespace 
from dba_users 
where username like 'OPS$%' or username = 'SAPR3' 


Jared 






"Roger Xu" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 08/15/2003 12:34 PM 
 Please respond to ORACLE-L 
        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
        cc:         
        Subject:        ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM



Hello,

While user OPS$ORAQA2 running:
ANALYZE TABLE "SAPR3"."BSIS" COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS SIZE 1 FOR ALL INDEXES

I had the following error:
SQL error -1652 at location stats_tab_collect-4
ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM

A default temporary tablespace was assigned to the user OPS$ORAQA2.

Thanks,

Roger Xu
Database Administrator
Dr Pepper Bottling Company of Texas
(972)721-8337

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Roger Xu
 INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).



For technical support please email [EMAIL PROTECTED] or you can
call (972)721-8257. 
This email has been scanned for all viruses by the MessageLabs Email Security System.

RE: ORA-1652: unable to extend temp segment by 8091 in tablespace SYSTEM

2003-08-15 Thread Roger Xu 



USERNAME    DEFAULT_TABLESP 
TEMPORARY_TABLE--- --- 
---SAPR3   
PSAPUSER1D  
PSAPTEMPOPS$ORAQA2  
SYSTEM  
PSAPTEMPOPS$QA2ADM  
SYSTEM  
PSAPTEMP
 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Friday, August 15, 2003 4:28 
  PMTo: [EMAIL PROTECTED]Cc: Roger 
  XuSubject: Re: ORA-1652: unable to extend temp segment by 8091 in 
  tablespace SYSTEMImportance: HighWhat does this query return? select   username, default_tablespace, 
  temporary_tablespace from 
  dba_users where username like 'OPS$%' 
  or username = 'SAPR3' Jared 
  


  
  "Roger Xu" 
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 
 08/15/2003 12:34 PM 
 Please respond to ORACLE-L 
                  To:     
   Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>         cc:       
        
  Subject:        ORA-1652: unable to extend 
temp segment by 8091 in tablespace           
SYSTEMHello,While user OPS$ORAQA2 running:ANALYZE TABLE 
  "SAPR3"."BSIS" COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS SIZE 1 FOR 
  ALL INDEXESI had the following error:SQL error -1652 at location 
  stats_tab_collect-4ORA-1652: unable to extend temp segment by 8091 in 
  tablespace           SYSTEMA default 
  temporary tablespace was assigned to the user 
  OPS$ORAQA2.Thanks,Roger XuDatabase AdministratorDr 
  Pepper Bottling Company of Texas(972)721-8337-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net-- Author: Roger 
  Xu INET: [EMAIL PROTECTED]Fat City Network Services   
   -- 858-538-5051 http://www.fatcity.comSan Diego, California   
       -- Mailing list and web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).For technical 
  support please email [EMAIL PROTECTED] or you cancall 
  (972)721-8257. This email has been scanned for all viruses by the 
  MessageLabs Email Security System.

Re: ORA-1652: unable to extend temp segment by 8091 in tablespace

2003-08-15 Thread Jared . Still 

What does this query return?

select   username, default_tablespace, temporary_tablespace
from dba_users
where username like 'OPS$%' or username = 'SAPR3'


Jared







"Roger Xu" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 08/15/2003 12:34 PM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM


Hello,

While user OPS$ORAQA2 running:
ANALYZE TABLE "SAPR3"."BSIS" COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS SIZE 1 FOR ALL INDEXES

I had the following error:
SQL error -1652 at location stats_tab_collect-4
ORA-1652: unable to extend temp segment by 8091 in tablespace           SYSTEM

A default temporary tablespace was assigned to the user OPS$ORAQA2.

Thanks,

Roger Xu
Database Administrator
Dr Pepper Bottling Company of Texas
(972)721-8337

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Roger Xu
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).