RO tablespaces, LMT and drop table.

From: Tanel Poder <tanel_at_@peldik.com>
Date: Sat, 22 Mar 2003 19:32:19 +0200
Message-ID: <3e7c9d66$1_2@news.estpak.ee>

Hi!

When dropping a table from a read only LMT tablespace, dictionary is updated, but the extent bitmap in datafile isn't, for obvious reasons. Now, if I put my tablespace back to read write, how do I get the extent, originally belonging to dropped table, freed?

I've tried just creating additional table in "full" tablespace or extending the datafile to create a second table and allocating extent over datafile boundary (to invoke smon as was done with temp segments), I tried even coalesce command on LMT (sic) tablespace etc, but so far I haven't got my extent back. I have tested this on Linux and Solaris.

Any ideas?

See my experiment below.

Thanks,
Tanel.

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Solaris: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production

SQL> create tablespace t datafile '/u21/oradata/ora92/t01.dbf' size 1048k autoextend off extent management local uniform size 1m;

Tablespace created.

SQL> create table t (a number) tablespace t;

Table created.

SQL> select count(*) from dba_free_space where tablespace_name = 'T';

  COUNT(*)

---

         0

SQL> alter tablespace t read only;

Tablespace altered.

SQL> drop table t;

Table dropped.

SQL> select count(*) from dba_free_space where tablespace_name = 'T';

  COUNT(*)

---

         0

SQL> alter tablespace t read write;

Tablespace altered.

SQL> select count(*) from dba_free_space where tablespace_name = 'T';

  COUNT(*)

---

         0 Received on Sat Mar 22 2003 - 11:32:19 CST