3 Replies Latest reply on Dec 3, 2012 9:13 PM by L-MachineGun

    Large Index

      Need to create a large index, but returns TEMP tablespace error. I am aware of the issue here. I need a large TEMP tablespace.
      I do not have permissions to create large TEMP tablespace. Is there any other workaround to create large index without TEMP tablespaces?
        • 1. Re: Large Index
          Chanchal Wankhade

          Part of the oracle Document about large index..
          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.
          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.
          • 2. Re: Large Index
            Dom Brooks
            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.
            • 3. Re: Large Index
              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