Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016