{"id":27412,"date":"2024-03-16T08:26:13","date_gmt":"2024-03-16T08:26:13","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/"},"modified":"2024-03-22T10:58:32","modified_gmt":"2024-03-22T10:58:32","slug":"how-to-clean-up-temporary-tablespace-in-oracle","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/","title":{"rendered":"How to clean up temporary tablespace in Oracle?"},"content":{"rendered":"

To clean up Oracle temporary tablespace, you can follow these steps:<\/p>\n

    \n
  1. First, connect to the Oracle database instance.<\/li>\n
  2. Check the sessions currently using temporary tablespace.<\/li>\n
  3. Choose s.sid, s.serial#, s.username, t.tablespace, t.blocks
    \nFROM v$session s, v$sort_usage t
    \nWHERE s.saddr equals t.session_addr;<\/li>\n
  4. This will display the SID, serial number, username, tablespace, and block count of sessions using temporary tablespace.<\/li>\n
  5. The sessions that are using temporary tablespace can be terminated.<\/li>\n
  6. Terminate the session with a specified SID and serial number in the system.<\/li>\n
  7. The ‘sid’ and ‘serial#’ are the SID and serial number of the session retrieved in step 2.<\/li>\n
  8. Search for and remove temporary segments of temporary table spaces that are no longer in use.<\/li>\n
  9. Retrieve the owner, name, and type of segments from the database view dba_segments where the tablespace is named ‘TEMP’.<\/li>\n
  10. This will display the owner, name, and type of temporary segments belonging to the temporary tablespace.<\/li>\n
  11. Remove the temp file ‘tempfile_path’ from the temporary tablespace.<\/li>\n
  12. ‘tempfile_path’ is the path of the temporary file to be deleted.<\/li>\n
  13. Set the size of the temporary tablespace to the desired size.<\/li>\n
  14. Reduce the size of the temporary tablespace to 100M.<\/li>\n
  15. This will resize the temporary tablespace to 100MB and ensure it stays that size.<\/li>\n
  16. Finally, rebuild the temporary tablespace.<\/li>\n
  17. Add a new temporary file of size 1GB to the TEMP tablespace.<\/li>\n
  18. The ‘tempfile_path’ is the path of the temporary file to be added, while ‘1G’ is the initial size of the file.<\/li>\n<\/ol>\n

    This completes the process of cleaning up the temporary tablespace in Oracle. Please make sure to backup the database before proceeding and ensure that no other processes are affected while cleaning the temporary tablespace.<\/p>\n","protected":false},"excerpt":{"rendered":"

    To clean up Oracle temporary tablespace, you can follow these steps: First, connect to the Oracle database instance. Check the sessions currently using temporary tablespace. Choose s.sid, s.serial#, s.username, t.tablespace, t.blocks FROM v$session s, v$sort_usage t WHERE s.saddr equals t.session_addr; This will display the SID, serial number, username, tablespace, and block count of sessions using […]<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-27412","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nHow to clean up temporary tablespace in Oracle? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to clean up temporary tablespace in Oracle?\" \/>\n<meta property=\"og:description\" content=\"To clean up Oracle temporary tablespace, you can follow these steps: First, connect to the Oracle database instance. Check the sessions currently using temporary tablespace. Choose s.sid, s.serial#, s.username, t.tablespace, t.blocks FROM v$session s, v$sort_usage t WHERE s.saddr equals t.session_addr; This will display the SID, serial number, username, tablespace, and block count of sessions using […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T08:26:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T10:58:32+00:00\" \/>\n<meta name=\"author\" content=\"Olivia Parker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Olivia Parker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\"},\"author\":{\"name\":\"Olivia Parker\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\"},\"headline\":\"How to clean up temporary tablespace in Oracle?\",\"datePublished\":\"2024-03-16T08:26:13+00:00\",\"dateModified\":\"2024-03-22T10:58:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\"},\"wordCount\":296,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\",\"name\":\"How to clean up temporary tablespace in Oracle? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T08:26:13+00:00\",\"dateModified\":\"2024-03-22T10:58:32+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to clean up temporary tablespace in Oracle?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\",\"name\":\"Olivia Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"caption\":\"Olivia Parker\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to clean up temporary tablespace in Oracle? - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How to clean up temporary tablespace in Oracle?","og_description":"To clean up Oracle temporary tablespace, you can follow these steps: First, connect to the Oracle database instance. Check the sessions currently using temporary tablespace. Choose s.sid, s.serial#, s.username, t.tablespace, t.blocks FROM v$session s, v$sort_usage t WHERE s.saddr equals t.session_addr; This will display the SID, serial number, username, tablespace, and block count of sessions using […]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T08:26:13+00:00","article_modified_time":"2024-03-22T10:58:32+00:00","author":"Olivia Parker","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Olivia Parker","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/"},"author":{"name":"Olivia Parker","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9"},"headline":"How to clean up temporary tablespace in Oracle?","datePublished":"2024-03-16T08:26:13+00:00","dateModified":"2024-03-22T10:58:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/"},"wordCount":296,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/","name":"How to clean up temporary tablespace in Oracle? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T08:26:13+00:00","dateModified":"2024-03-22T10:58:32+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-clean-up-temporary-tablespace-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to clean up temporary tablespace in Oracle?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9","name":"Olivia Parker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","caption":"Olivia Parker"},"url":"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=27412"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27412\/revisions"}],"predecessor-version":[{"id":61642,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/27412\/revisions\/61642"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=27412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=27412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=27412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}