Solved

Please, help: "ORA-01652 unable to extend temp segment by 8192 in tablespace"?

Posted on 2004-04-02
4
7,793 Views
Last Modified: 2007-12-19
Here is my problem with Oracle 9.2:
I need to create a large index for a table in a specific tablespace. Problem is I don't know too much about Oracle and neither how the tablespace is configured.

The name of the table is PADRON and schema is PADRON.
Using Oracle Enterprise Manager Console and enter as System user, I see:
+Data Bases
   + OEA1 - SYSTEM
     + Schema
       + PADRON
         + Tables
           + PADRON
         + Indexes

I have two disks C: and F: in the same PC. Oracle was installed in C:, but PADRON was created in F:
In C: I have:

c:\oracle\oradata\oea1\CONTROL01.CTL
c:\oracle\oradata\oea1\CONTROL02.CTL
c:\oracle\oradata\oea1\CONTROL03.CTL
c:\oracle\oradata\oea1\CWMLITE01.DBF
c:\oracle\oradata\oea1\DRSYS01.DBF
c:\oracle\oradata\oea1\EXAMPLE01.DBF
c:\oracle\oradata\oea1\INDX01.DBF
c:\oracle\oradata\oea1\ODM01.DBF
c:\oracle\oradata\oea1\SYSTEM01.DBF
c:\oracle\oradata\oea1\TEMP01.DBF
c:\oracle\oradata\oea1\TOOLS01.DBF
c:\oracle\oradata\oea1\UNDOTBS01.DBF
c:\oracle\oradata\oea1\USERS01.DBF
c:\oracle\oradata\oea1\XDB01.DBF
c:\oracle\oradata\oea1\REDO01.LOG
c:\oracle\oradata\oea1\REDO02.LOG
c:\oracle\oradata\oea1\REDO03.LOG

In F: I have:
F:\datosora\PADRON.ORA    

Problem is that I don't have enough space in C: but plenty of it in F:

I enter to Oracle Sql*Plus:
   user=PADRON
   password=PADRON
   database= OEA1

I'm trying to do:

CREATE INDEX "PADRON"."INDICE01" ON "PADRON"."PADRON"  ("APEPAT", "APEMAT")

but I get this error:
"ORA-01652 unable to extend temp segment by 8192 in tablespace PADRON"

I think the tablespace of PADRON is in F:\datosora\PADRON.ORA, where I have plenty space. How can I be sure? How can I create the index? How can I change tablespace size?
0
Comment
Question by:mogulwindows
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 100 total points
ID: 10745499
can you first take a look at what is the default temporary tablespace of user PADRON?

log in as sysdba in sqlplus


SQL>select username, default_tablespace, temporary_tablespace from dba_users where username='PADRON';


post your results here,

but as a quick and dirty solution:

login as sysdba:

SQL> alter database tempfile 'c:\oracle\oradata\oea1\TEMP01.DBF' autoextend on;
SQL> alter database datafile 'F:\datosora\PADRON.ORA' autoextend on;

then try to create index again...

0
 

Author Comment

by:mogulwindows
ID: 10745569
Hi, seazodiac:
SQL> select username, default_tablespace, temporary_tablespace from dba_users where username='PADRON';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
PADRON                         PADRON
TEMP

My problem is that I don't have enough space in C:
If I do:
SQL> alter database tempfile 'c:\oracle\oradata\oea1\TEMP01.DBF' autoextend on;
wouldn't I have problems for lacking of space?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10745664
ok, you can create a new temporary tablespace in F: drive like this:

log in as sysdba:

SQL>create temporary tablespace temp02  tempfile 'F:\datosora\temp02.dbf' size 500M autoextend on;

then

SQL>alter user PADRON temporary tablespace temp02;

then you create index

SQL>CREATE INDEX "PADRON"."INDICE01" ON "PADRON"."PADRON"  ("APEPAT", "APEMAT");

and at last, you have the option of either dropping this temp tablespace or keep it...
0
 
LVL 8

Assisted Solution

by:Danielzt
Danielzt earned 150 total points
ID: 10745717

because you have less space on C drive, you you need to move some datafiles to F drive.
You can base on your case , select 1 or more datafiles and follow the following procedure to move it from C drive to F drive.

I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN
 ----------------------------------------------------  
Datafiles can be renamed or moved while the database is open.  However, the  tablespace must be made READ-ONLY.  This will allow users to select from the  tablespace, but prevents them from doing inserts, updates, and deletes.  The  amount of time the tablespace is required to be read only will depend on how  large the datafile(s) are and how long it takes to copy the datafile(s) to the  new location.    Making the tablespace read only freezes the file header, preventing updates  from being made to the file header.  Since this datafile is then at a read  only state, it is possible to copy the file while the database is open.     To do this you must follow these steps:    

1. Determine how many datafiles are associated with the tablespace.          
SVRMGR> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES                  
WHERE TABLESPACE_NAME = '<YOUR_TABLESPACE_NAME>';    

2. Make sure that all datafiles returned have the status AVAILABLE.    

3. Make the tablespace is read only.          
SVRMGR> ALTER TABLESPACE <YOUR_TABLESPACE_NAME> READ ONLY;    

4. Make sure that the tablespace is defined as read only in the data dictionary.           SVRMGR> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES                   WHERE TABLESPACE_NAME = '<YOUR_TABLESPACE_NAME>';           TABLESPACE_NAME                STATUS        
------------------------------ ---------        
<YOUR_TABLESPACE_NAME>         READ ONLY      

5. Copy the datafile(s) to the new location using the operating system copy     command.  
Once the datafile(s) have been copied to the new location compare     the sizes of the datafiles.  Make sure that the sizes match.    

6. Once the datafiles have been copied to the new location alter the tablespace offline.          SVRMGR> ALTER TABLESPACE <YOUR_TABLESPACE_NAME> OFFLINE;      

* At this point the tablespace is not accessible to users.    

7. Once the tablespace is offline you will need to rename the datafile(s) to     the new location.  This updates the entry for the datafile(s) in the     controlfile.          

SVRMGR> ALTER DATABASE RENAME FILE                   '/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'                   TO                   '/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';      

* You will need to do this for all datafiles associated with  this tablespace.    

8. Once the alter database statement has been processed for the datafile(s)  you can bring the tablespace online.          
SVRMGR> ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;    

9. After you bring the tablespace back online you can make the tablespace read/write again.          

SVRMGR> ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE;    

10. You can check the status of the tablespace to make sure it is read/write. You can also verify that the controlfile has been updated by doing the      following:            
SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;        

This will produce a readable copy of the contents of your controlfile  which will be placed in your user_dump_dest directory.    

11. Remove the datafile(s) from the old location at the O/S level.  

when you have free space on your C drive, your DB should be ok.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question