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?


1 Answer 1


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.

  • 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. Commented Nov 16, 2011 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
    Commented Nov 16, 2011 at 19:06
  • I used ALTER USER MY_USERNAME TEMPORARY TABLESPACE ts_tmp . Thanks for answering by the way. Commented Nov 16, 2011 at 19:18
  • Did you replace me with your username in the commands?
    – Raihan
    Commented Nov 17, 2011 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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