测试数据文件的删除

SQL> drop table tests;

Table dropped.

SQL> create table tests as select owner,tablespace_name,table_name from all_tables;

Table created.

SQL> insert into tests select * from tests;

1577 rows created.

SQL> /

3154 rows created.

SQL> /

6308 rows created.

SQL> /
insert into tests select * from tests
*
ERROR at line 1:
ORA-01653: unable to extend table DEMO.TESTS by 8 in tablespace TESTS

SQL> /
insert into tests select * from tests
*
ERROR at line 1:
ORA-01653: unable to extend table DEMO.TESTS by 8 in tablespace TESTS

SQL> alter tablespace tests add datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf' size 4m;

Tablespace altered.

SQL> insert into tests select * from tests;

12616 rows created.

SQL> commit;

Commit complete.

----1--------------------delete & move ---------------------------------------------------

SQL> delete tests t1 where t1.rowid not in(select min(rowid) from tests t group by owner,table_name,tablespace_name);

11039 rows deleted.

SQL> alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf';
alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

    SQL> alter table tests move;

Table altered.

SQL> alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf';

Tablespace altered.

SQL>

-------2-------------------------------truncate --------------------------------------------

SQL> truncate table tests;

Table truncated.

SQL> alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf';

Tablespace altered.

---------3----------------------------drop -----------------------------------------------------

SQL> drop table tests;

Table dropped.

SQL> alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf';
alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> purge recyclebin;

Recyclebin purged.

SQL> alter tablespace tests drop datafile '/u01/app/oracle/oradata/ORCL/datafile/test2.dbf';

Tablespace altered.

--------------------end---------------------------------------------------------------------------