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

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?
mogulwindowsAsked:
Who is Participating?
 
seazodiacConnect With a Mentor Commented:
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
 
mogulwindowsAuthor Commented:
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
 
seazodiacCommented:
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
 
DanielztConnect With a Mentor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.