26 December, 2011

DROP TABLESPACE INCLUDING CONTENTS drops segments

As reported in a few posts earlier,  DDLs always commit when beginning execution, even if they subsequently fail execution.
Here is a demonstration of a DROP TABLESPACE INCLUDING CONTENTS  causing segments in the tablespace to be dropped even if the DROP TABLESPACE fails.  Furthermore, segments dropped by a DROP TABLESPACE are no longer in the recyclebin !


SQL> create tablespace TBS_2_DROP datafile '/tmp/tbs_2_drop.dbf' size 100M ;

Tablespace created.

SQL> 
SQL> connect hemant/hemant
Connected.
SQL> -- create table and populate it
SQL> create table TABLE_IN_TBS_2_DROP  (col_1 number, col_2 char(50))
  2  tablespace TBS_2_DROP
  3  /

Table created.

SQL> 
SQL> insert into TABLE_IN_TBS_2_DROP
  2  select rownum, to_char(rownum)
  3  from dual
  4  connect by level < 501
  5  /

500 rows created.

SQL> 
SQL> create index NDX_T_I_T_2_D on TABLE_IN_TBS_2_DROP (col_1) tablespace TBS_2_DROP;

Index created.

SQL> 
SQL> -- now connect AS SYSDBA
SQL> connect / as sysdba
Connected.
SQL> 
SQL> select segment_name, segment_type, bytes/1048576
  2  from dba_segments
  3  where tablespace_name = 'TBS_2_DROP'
  4  /

SEGMENT_NAME         SEGMENT_ BYTES/1048576
-------------------- -------- -------------
TABLE_IN_TBS_2_DROP  TABLE            .0625
NDX_T_I_T_2_D        INDEX            .0625

SQL> 
SQL> alter tablespace TBS_2_DROP begin backup;

Tablespace altered.

SQL> 
SQL> drop tablespace TBS_2_DROP including contents;
drop tablespace TBS_2_DROP including contents
*
ERROR at line 1:
ORA-01150: cannot prevent writes - file 14 has online backup set
ORA-01110: data file 14: '/tmp/tbs_2_drop.dbf'


SQL> 
SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'TBS_2_DROP'
  4  /

no rows selected

SQL> 
SQL> select object_name, original_name, type
  2  from dba_recyclebin
  3  where owner = 'HEMANT'
  4  /

no rows selected

SQL>
Although the DROP TABLESPACE failed with an ORA-1150 error, it did execute the recursive sqls drop segments present in the Tablespace. And these segments are not recoverable now !
.
.
.

No comments: