This content has been marked as final. Show 3 replies
Part of the oracle Document about large index..
In the above case you dont have permision to create a temp tablespace then you should ask your DBA to do so thats the only option.
When you create an index on a table that already has data, Oracle Database must use sort space to create the index. The database uses the sort space in memory allocated for the creator of the index (the amount for each user is determined by the initialization parameter SORT_AREA_SIZE), but the database must also swap sort information to and from temporary segments allocated on behalf of the index creation. If the index is extremely large, it can be beneficial to complete the following steps: Create a new temporary tablespace using the CREATE TABLESPACE command. Use the TEMPORARY TABLESPACE option of the ALTER USER command to make this your new temporary tablespace. Create the index using the CREATE INDEX command. Drop this tablespace using the DROP TABLESPACE command. Then use the ALTER USER command to reset your temporary tablespace to your original temporary tablespace.
Depending on version, EXPLAIN PLAN FOR CREATE INDEX ... + output from DBMS_XPLAN.DISPLAY should give you an estimate for the index size and also the temp space usage.
You could alter your session settings to use a manual WORKAREA_SIZE_POLICY and try to increase the SORT_AREA_SIZE manually and reduce the temp space usage.
If your DBA is worth his/her pay, then he/she should have created (or can create) a temporary tablespace "group" which enables
users to consume temporary space from multiple tablespaces.
Check the fine Oracle® Database Administrator's Guide on this issue: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces002.htm#sthref1546