We help IT Professionals succeed at work.

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

mogulwindows
mogulwindows asked
on
8,286 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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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?
CERTIFIED EXPERT
Top Expert 2004

Commented:
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...
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.