{"id":606,"date":"2011-09-22T07:52:52","date_gmt":"2011-09-22T05:52:52","guid":{"rendered":"http:\/\/www.oradba.ch\/?p=606"},"modified":"2011-09-22T07:57:53","modified_gmt":"2011-09-22T05:57:53","slug":"tablespace-quotas-are-forever","status":"publish","type":"post","link":"https:\/\/www.oradba.ch\/wordpress\/2011\/09\/tablespace-quotas-are-forever\/","title":{"rendered":"Tablespace quotas are forever"},"content":{"rendered":"

Today I came across a strange behavior of tablespace quotas. I’ve had to create new tablespaces in a test and a development database. So I’ve created a tablespace in each of the databases and informed the developer. His answer: “Great, in development I can create tables, but in test I do not have quota on the tablespace”<\/em> Ok, I did not set a quota on the newly create tablespaces nor does the user has the privilege UNLIMITED TABLESPACE. But what happened? It seems that tablespace quotas remain even if a tablespace has been deleted. Ok, lets have a closer look.<\/p>\n

The initial situation<\/h3>\n

We assume, that somebody has created a tablespace TEST and granted unlimited quota on this to user SCOTT.<\/p>\n

\r\ncreate tablespace TEST datafile '\/u01\/oradata\/PT1120\/test01PT1120.dbf' size 5M;\r\n\r\nalter user SCOTT quota unlimited on TEST;\r\n\r\nselect TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED \r\nfrom DBA_TS_QUOTAS where username='SCOTT';\r\n\r\nTABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED\r\n-------------------- ---------- ---------- ----------\r\nTEST                 SCOTT              -1 NO\r\n<\/pre>\n
Let’s drop the tablespace TEST and see what happens to the tablespace and the quota.<\/p>\n
\r\ndrop tablespace TEST including contents and datafiles;\r\n\r\nTablespace dropped.\r\n\r\nselect TS#,NAME,ONLINE$ from TS$;\r\n\r\n       TS# NAME                              ONLINE$\r\n---------- ------------------------------ ----------\r\n         0 SYSTEM                                  1\r\n         1 SYSAUX                                  1\r\n         2 UNDOTBS1                                1\r\n         3 TEMP                                    1\r\n         4 USERS                                   1\r\n         5 EXAMPLE                                 1\r\n         6 TEST                                    3\r\n\r\n7 rows selected.\r\n\r\nselect TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED\r\nfrom DBA_TS_QUOTAS where username='SCOTT';\r\n\r\nTABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED\r\n-------------------- ---------- ---------- ----------\r\nTEST                 SCOTT              -1 YES\r\n<\/pre>\n
As you can see the tablespace status in TS$ has chanted from 1 to 3 (dropped). Querying DBA_TS_QUOTAS is now showing in the column DROPPED that the tablespace for which this quota is valid has been dropped. The information in DBA_TS_QUOTA is taken from TS$ and TSQ$.<\/p>\n
Somewhen later<\/h3>\n
Lets create again a tablespace TEST and verify SCOTT’s quota on this new tablespace. In real world this could be after a going live. Or just after quite some time when nobody remember that there use to be a tablespace TEST.<\/p>\n
\r\ncreate tablespace test datafile '\/u01\/oradata\/PT1120\/test01PT1120.dbf' size 10M;\r\n\r\nTablespace created.\r\n\r\nselect TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED\r\nfrom dba_ts_quotas where username='SCOTT';\r\n\r\nTABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED\r\n-------------------- ---------- ---------- ----------\r\nTEST                 SCOTT              -1 NO\r\n<\/pre>\n
\u00c9 voila, SCOTT has again access to the newly created tablespace.<\/p>\n
Conclusion<\/h3>\n
From my understanding it looks like an expected behavior. But is it good, bad or ugly? As usual it depends. If a test somewhen becomes a productive database, it its worth to have a look if there are still some quotas hanging around. Just to make sure, that a low privilege user can not suddenly fill up a tablespace. On the other hand it may be handy when the quota remains and we do not have to grant again  quota to x users on a newly created tablespace. I tend more towards the safe side. When a quota is not needed or makes no sense, it should be deleted. What are your opinion and experience on this topic?<\/p>\n
By the way, the post has started with a movie title as well ended with one. Do you know which two?<\/p>\n","protected":false},"excerpt":{"rendered":"
It looks that not only diamonds are forever but also tablespace quotas. Due to the fact that Oracle has a history of tablespaces, it’s kind of obvious that it also keeps information quotas. Is this an expected behavior? What could be the impact ?<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[6,7,8,5,11],"tags":[38,18],"class_list":["post-606","post","type-post","status-publish","format-standard","hentry","category-10gr2","category-11gr1","category-11gr2","category-oracle-database","category-security","tag-tablespace","tag-trivadiscontent"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1aErb-9M","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":484,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/04\/find-user-with-unlimited-tablespace-quota\/","url_meta":{"origin":606,"position":0},"title":"Find User with unlimited Tablespace Quota","author":"Stefan","date":"29. April 2011","format":false,"excerpt":"When performing a database security audit various informations about users, roles and privileges have to be collected, including \"who has unlimited tablespace on SYSTEM\". It is quite easy to find user with UNLIMITED TABLESPACE or a UNLIMITED quota on SYSTEM. But what when the system privilege is assigned to a\u2026","rel":"","context":"In "10gR2"","block_context":{"text":"10gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/10gr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":600,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/09\/tablespace-point-in-time-recovery-and-oracle-11-2-0-2\/","url_meta":{"origin":606,"position":1},"title":"Tablespace point in time recovery and Oracle 11.2.0.2","author":"Stefan","date":"1. September 2011","format":false,"excerpt":"Tablespace point in time recovery (TSPITR) in particular, the fully automatic TSPITR is not a 11g new feature. It is likely that this can be used without any problem. But it looks like that a bug has been introduced with the latest patchset (11.2.0.2). What is the problem and how\u2026","rel":"","context":"In "11gR2"","block_context":{"text":"11gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/11gr2\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/TSPITR-300x278.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2202,"url":"https:\/\/www.oradba.ch\/wordpress\/2016\/11\/oracle-12-release-2-documentation-available\/","url_meta":{"origin":606,"position":2},"title":"Oracle 12 Release 2 Documentation available","author":"Stefan","date":"8. November 2016","format":false,"excerpt":"Oracle just released the documentation for Oracle 12c Release 2. It seems that most of the new security features are available as discussed in my presentation at DOAG SIG Security in D\u00fcsseldorf on the 18th of october. See docs.oracle.com for the documentation bookshelf. Yet a short summary of new security\u2026","rel":"","context":"In "12cR2"","block_context":{"text":"12cR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12cr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":7707,"url":"https:\/\/www.oradba.ch\/wordpress\/2020\/11\/oracle-security-eus-snippets-setup-proxy-user-privileges\/","url_meta":{"origin":606,"position":3},"title":"Oracle Security EUS Snippets – Setup Proxy User Privileges","author":"Stefan","date":"5. November 2020","format":false,"excerpt":"Since I'm always short of time for a longer blog post, I'll just try a short one. Intended as a mini-series, I will show different configuration examples for Oracle Enterprise User Security. Today I\u2019ll start with the configuration of EUS based proxy privileges. The environment I use is DOE, my\u2026","rel":"","context":"In "12R2"","block_context":{"text":"12R2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12r2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":527,"url":"https:\/\/www.oradba.ch\/wordpress\/2011\/05\/database-audit-and-audit-trail-purging\/","url_meta":{"origin":606,"position":4},"title":"Database Audit and Audit trail purging","author":"Stefan","date":"30. May 2011","format":false,"excerpt":"Setting up database audit is fairly easy. Since the availability of DBMS_AUDIT_MGMT the housekeeping of the audit trail is just a trifle. After the introduction of DBMS_AUDIT_MGMT in an post early this February, I would like to take a closer look at the housekeeping of the Audit Trail and provide\u2026","rel":"","context":"In "10gR2"","block_context":{"text":"10gR2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/10gr2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":13852,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/08\/sql-toolbox-for-simplified-oracle-unified-audit-data-analysis\/","url_meta":{"origin":606,"position":5},"title":"SQL Toolbox for simplified Oracle Unified Audit Data Analysis","author":"Stefan","date":"28. August 2023","format":false,"excerpt":"On my journey through the area of database security, Oracle Unified Audit has been a constant companion. I not only created audit concepts, but also often had the opportunity to implement them. Besides the configuration, the administration and evaluation of the audit data was always part of it. Occasionally I\u2026","rel":"","context":"In "19c"","block_context":{"text":"19c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/19c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/comments?post=606"}],"version-history":[{"count":3,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/606\/revisions"}],"predecessor-version":[{"id":608,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/606\/revisions\/608"}],"wp:attachment":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/media?parent=606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/categories?post=606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/tags?post=606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}