{"id":21,"date":"2011-09-26T05:51:00","date_gmt":"2011-09-26T05:51:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=21"},"modified":"2026-02-07T14:43:41","modified_gmt":"2026-02-07T20:43:41","slug":"read-only-tables-orig-published-apr-2000","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/21\/","title":{"rendered":"Read Only Tables [Orig Published Apr 2000]"},"content":{"rendered":"<div dir=\"ltr\" trbidi=\"on\"><span><span><\/span><\/span><br \/><span>In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only or not. For large or critical tables, you can have the tables that need to be made read only grouped together in tablespaces. This is especially important when you&#8217;re are partitioning large tables. For example, if you partition a table that loads historical data, you can make the older partitions read only by making their corresponding tablespace read only.<\/span><br \/><span>To make the tablespace read only, issue the command:<\/span><\/p>\n<blockquote><p><span><span><b>ALTER TABLESPACE <tablespace_name> READ ONLY;<\/tablespace_name><\/b><\/span><\/span><\/p><\/blockquote>\n<p><span>To make the tablespace back to read write mode, issue the command:<\/span><\/p>\n<blockquote><p><span><span><b>ALTER TABLESPACE <tablespace_name> READ WRITE;<\/tablespace_name><\/b><\/span><\/span><\/p><\/blockquote>\n<p><span>FYI, you cannot make the SYSTEM tablespace to READ ONLY. You can drop a table from the read only tablespace, but not truncate, update, delete or insert.<\/span><br \/><span>So, what do you do if you need to make just one table read only, not all the tables in the tablespace&#8230;. Well, you can use triggers. Create a trigger which fires for all insert, update and delete operations on the table&#8230; Here is an example:<\/span><\/p>\n<pre><span>SQL> create or replace trigger emp_read_only<br>2 before insert or update or delete<br>3 on emp<br>4 begin<br>5 raise_application_error (-20001, 'Table EMP is read only, you cannot modify data.');<br>6 end;<br>7 \/<\/span><\/pre>\n<p><span><\/span><\/p>\n<pre>Trigger created.<\/pre>\n<p><span><\/span><\/p>\n<pre>SQL> delete from emp;<br>delete from emp<br>*<br>ERROR at line 1:<br>ORA-20001: Table EMP is read only, you cannot modify data.<br>ORA-06512: at \"BIJU.EMP_READ_ONLY\", line 2<br>ORA-04088: error during execution of trigger 'BIJU.EMP_READ_ONLY'<br><\/pre>\n<div><\/div>\n<div><b><u><span>Update:<\/span><\/u><\/b><\/div>\n<div><\/div>\n<div><span>As of Oracle11g, a new clause is introduced in the ALTER TABLE statement, that can make a table to read-only.<\/span><\/div>\n<div><\/div>\n<div>\n<div><span><b>ALTER TABLE <table_name> READ ONLY;<\/table_name><\/b><\/span><\/div>\n<div><span><br \/><\/span><\/div>\n<div><span>If you try to perform insert or update or delete on read-only table, you get an ORA-12081 error. The nice part about this feature is you are not able to do TRUNCATE operation on a read-only table. Remember, even if the tablespace is read-only, you can truncate a table. You can perform operations on index associated with read-only tables. <\/span><\/div>\n<div><\/div>\n<div><span>To resume normal update activity on the table, perform\u00a0<\/span><\/div>\n<div><span><span><br \/><\/span><\/span><\/div>\n<div><span><span><b>ALTER TABLE <table_name> READ WRITE;<\/table_name><\/b><\/span><\/span><\/div>\n<div><\/div>\n<div><span>A new column READ_ONLY is added to DBA_TABLES. To list all the read-only tables in the database, you could do<\/span><\/div>\n<div><\/div>\n<div><span>select owner, table_name, tablespace_name <\/span><\/div>\n<div><span>from dba_tables<\/span><\/div>\n<div><span>where read_only = &#8216;YES&#8217;;\u00a0<\/span><\/div>\n<div><\/div>\n<\/div>\n<div><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only or not. For large or &#8230; <a title=\"Read Only Tables [Orig Published Apr 2000]\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2011\/21\/\" aria-label=\"Read more about Read Only Tables [Orig Published Apr 2000]\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[133],"tags":[134],"class_list":["post-21","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-oracle-11g"],"acf":[],"jetpack-related-posts":[{"id":1534,"url":"https:\/\/bijoos.com\/oraclenotes\/2015\/1534\/","url_meta":{"origin":21,"position":0},"title":"Oracle #DB12c New &#8211; TEMP_UNDO_ENABLED","author":"Biju","date":"June 24, 2015","format":false,"excerpt":"Global temporary table data is stored in the temporary tablespace (where no redo will be generated on the DML operations). When DML operations are performed on global temporary table, for read consistency undo must be generated. Such undo records are written to undo tablespace, and thus redo generated on the\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"ManyThingsOracle-pic1","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2015\/06\/ManyThingsOracle-pic1-300x191.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1720,"url":"https:\/\/bijoos.com\/oraclenotes\/2016\/1720\/","url_meta":{"origin":21,"position":1},"title":"Oracle Tidbits &#8211; May 2016","author":"Biju","date":"June 1, 2016","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in May\u00a02016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and use\u2026\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2032,"url":"https:\/\/bijoos.com\/oraclenotes\/2017\/2032\/","url_meta":{"origin":21,"position":2},"title":"Oracle Tidbits &#8211; January 2017 #oratidbit","author":"Biju","date":"February 1, 2017","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published on Facebook, Twitter and Google+\u00a0during weekdays\u00a0in January 2017. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2017\/02\/oratidbit.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":17,"url":"https:\/\/bijoos.com\/oraclenotes\/2011\/17\/","url_meta":{"origin":21,"position":3},"title":"Query &#8211; Tablespace Max Size","author":"Biju","date":"September 27, 2011","format":false,"excerpt":"I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user\/developer did...For\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":166,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/166\/","url_meta":{"origin":21,"position":4},"title":"OracleDB12c New Feature: Misc Security Changes","author":"Biju","date":"July 11, 2013","format":false,"excerpt":"Security in Oracle12c Database is enhanced, in this blog will try to identify few difference you would see compared with 11gR2. SELECT ANY DICTIONARY privilege in 12c excludes access to the following tables with authentication information: DEFAULT_PWD$ ENC$ LINK$ USER$ USER_HISTORY$ XS$VERIFIERS AUDIT_TRAIL initialization parameter is set to DB by\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":8,"url":"https:\/\/bijoos.com\/oraclenotes\/2012\/8\/","url_meta":{"origin":21,"position":5},"title":"SYSAUX Tablespace Growth &#8211; Review and Fix","author":"Biju","date":"November 7, 2012","format":false,"excerpt":"Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database... so need to make sure the contents are reviewed before adding more space... Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=21"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/21\/revisions"}],"predecessor-version":[{"id":2952,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/21\/revisions\/2952"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}