Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now