No relevant resource is found in the selected language.

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Read our privacy policy>Search


To have a better experience, please upgrade your IE browser.


FusionCube DB 3.1 Database Best Practice 03 (Oracle RAC 11g R2)

Rate and give feedback:
Huawei uses machine translation combined with human proofreading to translate this document to different languages in order to help you better understand the content of this document. Note: Even the most advanced machine translation cannot match the quality of professional translators. Huawei shall not bear any responsibility for translation accuracy and it is recommended that you refer to the English document (a link for which has been provided).
Modifying the Temporary Tablespace Size

Modifying the Temporary Tablespace Size

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. If the temporary tablespace is insufficient, you can increase its size to improve I/O performance. It is recommended that you set the temporary tablespace size to 400 GB and enable AutoExtend and local management.

  1. Create a temporary tablespace user_temp.

    SQL> create bigfile temporary tablespace user_temp tempfile size 400g autoextend on 
    extent management local;     

  2. Change the default tablespace to user_temp.

    SQL> alter database default temporary tablespace USER_TEMP;     

  3. Delete the temporary tablespace temp.

    SQL> drop tablespace temp including contents and datafiles;     

    If the system does not respond for a long period of time, the temporary tablespace temp is occupied by tasks in running. If this occurs, wait until the tasks are completed and release the temporary tablespace or restart the database. Then delete the temporary tablespace again.

    To query the tasks in running, run the following command:

    SQL> SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS FROM v$session se, v$sort_usage su WHERE se.saddr = su.session_addr;

    Obtain the sid and serial# fields of the session in the preceding command output and run the following command to kill the corresponding session (replace the sid and serial# fields in the SQL statement):

    SQL> alter system kill session 'sid,serial#';

  4. Change the name of the newly created tablespace user_temp to temp.

    SQL> alter tablespace USER_TEMP rename to temp;

Updated: 2018-12-26

Document ID: EDOC1100035759

Views: 32007

Downloads: 42

Average rating:
This Document Applies to these Products
Related Documents
Related Version
Previous Next