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 only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
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?
ALTER USER MY_USERNAME TEMPORARY TABLESPACE ts_tmp . Thanks for answering by the way.