Solved

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

Posted on 2004-04-02
4
7,778 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

739 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