删除数据文件

Drop Empty Datafiles

Imagine that you just added a datafile to the wrong directory or tablespace—a fairly common error. All is not lost; the datafile doesn't contain any data yet, so you can easily drop it, right?

Unfortunately, you can't. Prior to Oracle Database 10g Release 2, your only clean option for removing a datafile is to drop the entire tablespace and then rebuild it without that particular file. If the tablespace contains data, you have to go through the time-consuming and laborious process of storing the data on a separate location and reinstating it. In addition to its inconvenience, this process makes the tablespace unavailable.

Thankfully, in Oracle Database 10g Release 2 the process has been simplified: You can just drop the datafile. For example, the following command will remove the indicated datafile from the tablespace as well as from the server.

alter tablespace users drop datafile '/tmp/users01.dbf'
 /

There are a couple restrictions, however: The datafile must be empty to be dropped.

You can't drop the last datafile in a tablespace; the tablespace itself must be dropped. And the tablespace must be online and in read-write status.

---

ps: 早就听说10gr2可以单独drop datafile了，以前的话，可以将数据文件offline drop，但是在数据字典内还是会有数据文件的信息。

?

---

Redefine a Partition Online

When most DBAs don't have the downtime to make changes to a table—such as partitioning it—they resort to the online redefinition tool, DBMS_REDEFINITION. This tool allows you to change the definition of the objects while keeping them accessible.

However, DBMS_REDEFINITION has a limitation that can render it unhelpful in some cases. For instance, you may want to move the partitions of a table to different tablespaces. To do so, you have to move the entire table, even if it's partitioned. If the table is big, this approach will generate a lot of redo and undo and the presence of partitions cannot be exploited. If you could move one partition at a time, however, it would cut down the time, space, and redo/undo requirements significantly.

Oracle Database 10g Release 2 allows you to do exactly that: You can redefine a single partition of a table, either through Oracle Enterprise Manager or the command line.

Let's see an example using the command line. Here you have a table named ACCOUNTS with 11 partitions, all in the same tablespace USERS. You want to move them to a new tablespace ACCDATA, which you created specifically for this table. You'll move the table one partition at a time.

First, create an interim table with the same structure as the table ACCOUNTS but now with data on the ACCDATA tablespace.

SQL> create table accounts_int
  2  tablespace accdata
  3  as
  4  select * from accounts
  5  where 1=2
  6  /

Note where the partitions are located now:

SQL> select partition_name, tablespace_name, num_rows
  2  from user_tab_partitions
  3  /
PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             USERS                                1014
P2                             USERS                                1042
P3                             USERS                                1002
P4                             USERS                                 964
P5                             USERS                                 990
P6                             USERS                                1042
P7                             USERS                                 915
P8                             USERS                                 983
P9                             USERS                                1047
P10                            USERS                                1001
PMAX                           USERS                                   0
11 rows selected.

All partitions are in USERS tablespace. Now move the first partition P1 to the tablespace ACCDATA.

SQL> begin
  2     dbms_redefinition.start_redef_table (
  3        uname => 'ARUP',
  4        orig_table => 'ACCOUNTS',
  5        int_table  => 'ACCOUNTS_INT',
  6        part_name  => 'P1'
  7     );
  8  end;
  9  /
PL/SQL procedure successfully completed.

Note the line 6, where the part_name parameter specifies the partition to be reorganized. If this parameter is omitted, all the partitions will be redefined at the same time.

Now, synchronize the interim table with the original table. (You need to do this only if there are updates going to the table ACCOUNTS.)

SQL> begin
  2     dbms_redefinition.sync_interim_table (
  3        uname => 'ARUP',
  4        orig_table => 'ACCOUNTS',
  5        int_table  => 'ACCOUNTS_INT',
  6        part_name  => 'P1'
  7     );
  8  end;
  9  /
PL/SQL procedure successfully completed.

Finally, finish the redefinition process.

SQL> begin
  2     dbms_redefinition.finish_redef_table (
  3        uname => 'ARUP',
  4        orig_table => 'ACCOUNTS',
  5        int_table  => 'ACCOUNTS_INT',
  6        part_name  => 'P1'
  7     );
  8  end;
  9  /
PL/SQL procedure successfully completed.

Confirm the partition P1 was indeed moved to the tablespace ACCDATA.

SQL> select partition_name, tablespace_name, num_rows
  2  from user_tab_partitions
  3  /
PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             ACCDATA                              1014
P2                             USERS                                1042
P3                             USERS                                1002
P4                             USERS                                 964
P5                             USERS                                 990
P6                             USERS                                1042
P7                             USERS                                 915
P8                             USERS                                 983
P9                             USERS                                1047
P10                            USERS                                1001
PMAX                           USERS                                   0
11 rows selected.

That's it; repeat the same procedure for other partitions.

In contrast, if you had reorganized the table entirely, you would have (a) required a space equal to the size of the entire table and (b) generated undos for the entire table, which would have to be present—else you would received an error. By doing the process for a single partition, however, you reduced the space requirement to that of a single partition and reduced the undo generation for that partition only.

This powerful and useful feature enables you to reorganize very large objects—as most partitioned objects are—online. Furthermore, note how the statistics are copied to the redefined table as well, as shown in the values of NUM_RWS in the above query; you do not have to regenerate statistics for the newly created table or partitions.

---

Btw: 实际上，转换分区的存储表空间，可以通过move来完成，但是在move的过程当中会产生TM 锁，那么也就是说在move的过程当中所有的与该分区有关的DML都会被lock。