Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create an index for a relatively large table (80M rows).

First I got an ORA-01652 error, then I searched in SO and found a related question.

However, trying to implement the steps mentioned there,I got an ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace error.

If index is a permanent object and cannot be created in a temporary tablespace, how should I understand this part of Oracle manual?

Thanks.

share|improve this question
add comment

migrated from stackoverflow.com Nov 17 '11 at 16:39

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

Tables and indexes are created in users tablespace, which is USERS by default. A temporary tablespace, TEMP by default, is used by oracle for sorting large amount of data on disk. Temporary tablespace is typically used for processing joins, order by or creating indexes.

Since the default tablespace TEMP is not large enough for creating a large index, your example creates a large temporary tablespace ts_tmp for the index creation purpose only and drops it afterwards.

When creating the index with CREATE UNIQUE INDEX ... TABLESPACE ... command, don't use ts_tmp in the TABLESPACE clause. This will give you Attempt to create PERMANENT object in a TEMPORARY tablespace error.

Either don't use the TABLESPACE clause, then the defualt users tablespace will be used. Or give a tablespace name that was created with a CREATE TABLESPACE command.

share|improve this answer
    
So I won't create a temporary tablespace but a permanent tablespace with CREATE TABLESPACE command. I wasn't using TABLESPACE clause while creating index anyway. But still I wonder why Oracle (and the answer to the question linked above) tells to use temporary tablespace and then drop it after creating index. –  bonsvr Nov 16 '11 at 18:23
    
You will create the temporay tablespace, but you won't use it to create the index. It will be used by oracle while creating the index. If you were not using the TABLESPACE clause with the CREATE INDEX command, did you by any chance use ALTER USER me DEFAULT TABLESPACE ts_tmp command by mistake intead of ALTER USER me TEMPORARY TABLESPACE ts_tmp? –  Raihan Nov 16 '11 at 19:06
    
I used ALTER USER MY_USERNAME TEMPORARY TABLESPACE ts_tmp . Thanks for answering by the way. –  bonsvr Nov 16 '11 at 19:18
    
Did you replace me with your username in the commands? –  Raihan Nov 17 '11 at 1:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.