Dropping and creating tables in read only tablespaces?!

You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;

Tablespace created.

SQL> create table test tablespace ronly as select * from all_users;

Table created.

SQL> alter tablespace ronly READ ONLY;

Tablespace altered.

SQL> drop table test;

Table dropped.

I just dropped a table from a read only tablespace! Well, perhaps it’s because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let’s check which segments remain in the RONLY tablespace:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER   SEGMENT_NAME                    SEGMENT_TYPE
------- ------------------------------- ------------------
TANEL   BIN$ix7rAUXZfB3gQKjAgS4LXg==$0  TABLE

Indeed, it seems that the table segment wasn’t actually dropped. Well, let’s purge the recycle bin to try to actually drop the table segment:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER    SEGMENT_NAME                   SEGMENT_TYPE
-------- ------------------------------ ------------------
TANEL    9.130                          TEMPORARY

Wow, Oracle has converted the table segment into a temporary segment instead (see segment_type)! Bur our tablespace is read only, how can it do that?! The answer is that neither the regular DROP nor DROP PURGE need to write anything into the tablespace where the segment resides! The initial DROP operation just updated data dictionary, like renaming the table to BIN$… in OBJ$ and so on. The second DROP PURGE operation just ran a bunch of deletes against data dictionary to indicate that the table object is gone. But why is the TEMPORARY segment left behind? This has to do with locally managed tablespaces. Before LMT days, when you dropped a segment, then the segment space was released and acquired back to tablespace through inserts/updates to UET$/FET$ (used/free extents) base tables, which resided in system tablespace like all other data dictionary base tables. But with LMTs, the free space information is kept in bitmaps in the tablespace files themselves! Thus, if you drop a table in a read only LMT tablespace, the table will be gone, but the space will not be physically released (as you can’t update the LMT bitmaps in read only tablespace files). However, Oracle doesn’t want to lose that space should someone make the tablespace read write later on, so the table segment is updated to be a TEMPORARY segment instead of completely deleting it from data dictionary. That’s how the SMON can clean it up later on should that tablespace become read-write again. The 9.130 in SEGMENT_NAME column means relative file# 9 and starting block# 130, that’s a segment’s unique identifier in a tablespace. Let’s move on. This example is executed on Oracle 11.2, while logged on to a non-SYS/SYSTEM user:

SQL> select status from dba_tablespaces where tablespace_name = 'RONLY';

STATUS
---------
READ ONLY

The tablespace is still in read only status. Let’s try to CREATE a table into that tablespace:

SQL> create table test(a int) tablespace ronly;

Table created.

What? I can also CREATE a table into a read only tablespace?! Well, this is the behavior you get starting from 11gR2 onwards, it’s called deferred segment creation, Oracle doesn’t need to create any segments for a table until you actually insert rows into it! So, Oracle creates all needed metadata about the new table in data dictionary, but doesn’t actually allocate any space from the tablespace. This applies to other segment types like index and table/index partitions as well. There’s new syntax which controls the deferred segment creation:

SQL> drop table test purge;

Table dropped.

SQL> create table test (a int) segment creation IMMEDIATE tablespace ronly;
create table test (a int) segment creation IMMEDIATE tablespace ronly
*
ERROR at line 1:
ORA-01647: tablespace 'RONLY' is read-only, cannot allocate space in it

In the above case I disabled the deferred segment creation and got an error immediately as Oracle tried to allocate space from the read only tablespace. Let’s try the other way:

SQL> create table test (a int) segment creation DEFERRED tablespace ronly;

Table created.

Now, with deferred segment creation enabled, Oracle didn’t try to allocate space from the read only tablespace. Let’s look into the segments in that tablespace again:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER        SEGMENT_NAME    SEGMENT_TYPE
------------ --------------- ---------------
TANEL        9.130           TEMPORARY

We still have that old segment from an earlier DROP TABLE operation waiting to be cleaned up (when the tablespace goes into read-write mode again), but no segment for our latest test table created. Let’s make the tablespace read write and check again:

SQL> alter tablespace ronly read write;

Tablespace altered.

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

no rows selected

Apparently SMON has kicked in already and cleaned up the table segment which had been marked temporary earlier when I dropped the table in a read only tablespace.

Note that tables in SYS and SYSTEM schema can not use deferred segment creation:

SQL> create table sys.test (a int) segment creation deferred;
create table sys.test (a int) segment creation deferred
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

SQL> create table system.test (a int) segment creation deferred;
create table system.test (a int) segment creation deferred
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

SQL> create table tanel.test (a int) segment creation deferred;

Table created.

There’s also a parameter, deferred_segment_creation which controls the default behavior:

SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE

SQL>
This entry was posted in Oracle and tagged , , . Bookmark the permalink.

13 Responses to Dropping and creating tables in read only tablespaces?!

  1. senj says:

    good to know.
    deferred segment creation also makes old exp “miss” empty tables ( it queries DBA_SEGMENTS ). Very annoying, so I immediately turn it off.

  2. Anand says:

    Great blog entry :). Learned something new again from you :)

    Regards,
    Anand

  3. Amit Garg says:

    Nice article!!!
    Got to learn something interesting.

  4. Interesting, thanks for sharing.

  5. Vishal Gupta says:

    Holy cow. I would never thought that Oracle would allow something like this !!! This confirms my beleif, can’t trust Oracle on anything until you try it for yourself.

  6. Tanel Poder says:

    @Vishal Gupta

    Exactly, if Oracle documentation says one thing and expert X says another thing, but your testing shows a third thing, then you’re right! Of course you need to validate whether your test has been correct and valid, that’s where forums and peer review come into play :)

    Btw, you can also drop read only tables without a problem:

    
    SQL> create table test as select * from all_users;
    
    Table created.
    
    
    SQL> alter table test read only;
    
    Table altered.
    
    SQL> delete test;
    delete test
           *
    ERROR at line 1:
    ORA-12081: update operation not allowed on table "SCOTT"."TEST"
    
    
    SQL> drop table test purge;
    
    Table dropped.
    
    
  7. Uwe Hesse says:

    Starting with 11gR2, there is also another behaviour if a user has CREATE TABLE privilege but no quota on the tablespace where the table is (intended to be) created: The CREATE TABLE succeeds! It is the same reason why you can create tables now in READ ONLY tablespaces – the DEFERRED SEGMENT CREATION. CREATE TABLE is then just modifiying the Data Dictionary, so no quota is needed as long as no insert tries to create the initial extend. Then an error is raised :-)

  8. guojianjun says:

    wonderful.

  9. Noons says:

    What happens if one drops the table, the temp segment is created, and then we drop the tablespace “including contents and datafiles”? Does it lose the temp segment?
    I’m asking because I don’t have access to 11g to test.

  10. basil says:

    Hi Tanel,

    interesting as always. I wonder if is possible to recover a table partition dropped by accident which resides into a RO tablespace. The dba_segments still show it !

  11. John says:

    Intersting behaviour.

    Would make a good interview question: “When is read-only tablespace not read only?”

    ans: When you use defered segment creation

  12. Praveen N says:

    Thanks for the post .
    I have a question about sharing tablespaces using transportable TS :-
    I share same read-only tablespace (same data files) between two different databases . When I drop a table from one database I can see the temporary segments like you said , but the same object in the other database has no impact even though it shares the same datafiles and tablespace . how?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>