{"id":954,"date":"2024-12-27T20:03:46","date_gmt":"2024-12-27T20:03:46","guid":{"rendered":"https:\/\/w3buddy.com\/?p=954"},"modified":"2026-01-15T13:15:49","modified_gmt":"2026-01-15T07:45:49","slug":"oracle-tablespace-quota-management-script","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-tablespace-quota-management-script\/","title":{"rendered":"Oracle Tablespace Quota Management Script"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In Oracle databases, it&#8217;s important to manage user quotas on tablespaces effectively to prevent users from consuming excessive disk space. The following scripts provide tools to report, view, and manage tablespace quotas allocated to users.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. <strong>Tablespace Quota Details for All Users<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This script reports the quota allocated for each database user and the amount of tablespace they have consumed.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">set pagesize 200\nset lines 200\ncol ownr format a20         justify c heading 'Owner' \ncol name format a20         justify c heading 'Tablespace' trunc \ncol qota format a12         justify c heading 'Quota (KB)' \ncol used format 999,999,990 justify c heading 'Used (KB)' \nset colsep '|'\nselect \n  username          ownr, \n  tablespace_name   name, \n  decode(greatest(max_bytes, -1), \n    -1, 'UNLIMITED', \n    to_char(max_bytes\/1024, '999,999,990') \n  )                 qota, \n  bytes\/1024        used \nfrom \n  dba_ts_quotas \nwhere \n  max_bytes != 0 \n  or bytes != 0 \norder by \n  1, 2;\n\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation<\/strong>: This query retrieves the tablespace quotas and usage for all users, showing the tablespace name, the allocated quota (in KB), and the amount of space currently used (in KB).<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Sample Output<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">       Owner        |     Tablespace     | Quota (KB) | Used (KB)\n--------------------|--------------------|------------|------------\nAPPQOSSYS           |SYSAUX              |UNLIMITED   |           0\nAUDSYS              |SYSAUX              |UNLIMITED   |       2,560\nDBSFWUSER           |SYSAUX              |UNLIMITED   |           0\nGGSYS               |SYSAUX              |UNLIMITED   |           0\nGSMADMIN_INTERNAL   |SYSAUX              |UNLIMITED   |         896\nHR                  |SYSAUX              |UNLIMITED   |       1,600\nLBACSYS             |SYSTEM              |UNLIMITED   |         320\nMDSYS               |SYSAUX              |UNLIMITED   |     172,992\nMDSYS               |SYSTEM              |UNLIMITED   |           0\nOLAPSYS             |SYSAUX              |UNLIMITED   |           0\nOUTLN               |SYSTEM              |UNLIMITED   |         576\n\n11 rows selected.<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2. <strong>Tablespace Quota Details for a Specific User<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If you need to check the tablespace quota details for a specific user, use the following script:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">set pagesize 200\nset lines 200\ncol ownr format a20         justify c heading 'Owner' \ncol name format a20         justify c heading 'Tablespace' trunc \ncol qota format a12         justify c heading 'Quota (KB)' \ncol used format 999,999,990 justify c heading 'Used (KB)' \nset colsep '|'\nselect \n  username          ownr, \n  tablespace_name   name, \n  decode(greatest(max_bytes, -1), \n    -1, 'UNLIMITED', \n    to_char(max_bytes\/1024, '999,999,990') \n  )                 qota, \n  bytes\/1024        used \nfrom \n  dba_ts_quotas \nwhere \n  (max_bytes != 0 \n   or bytes != 0) \n  and username = '&amp;USERNAME'\norder by \n  1, 2;\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Sample Output<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">\n       Owner        |     Tablespace     | Quota (KB) | Used (KB)\n--------------------|--------------------|------------|------------\n        HR          |SYSAUX              |UNLIMITED   |       1,600<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">3. <strong>Assign Unlimited Quota to a User<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To assign an unlimited tablespace quota to a user for a specific tablespace, you can use the following commands:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-\">alter user HR quota unlimited on HR_TS; \nalter user HR quota unlimited on HR_IDX;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation: <\/strong>This command will assign unlimited quota on the specified tablespaces (HR_TS and HR_IDX) to the user HR.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Also read:<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"http:\/\/Also read:  How to Resolve ORA-01536: Space Quota Exceeded for Tablespace\">How to Resolve ORA-01536: Space Quota Exceeded for Tablespace<\/a><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">These scripts can help database administrators to monitor and manage tablespace allocations effectively. Ensure that users do not exceed their allocated quotas, preventing disk space issues and system slowdowns.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle databases, it&#8217;s important to manage user quotas on tablespaces effectively to prevent users from consuming excessive disk space. The following scripts provide tools to report, view, and manage tablespace quotas allocated to users. 1. Tablespace Quota Details for All Users This script reports the quota allocated for each database user and the amount [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-954","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/954","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=954"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/954\/revisions"}],"predecessor-version":[{"id":955,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/954\/revisions\/955"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}