裸设备上创建数据文件与删除表空间的某一个数据文件测试

一。 裸设备上创建数据文件测试

   今天做一个在裸设备上创建数据文件的测试，裸设备大小为200m，尝试添加这个裸设备为数据文件：

SQL> alter tablespace users add datafile '/dev/raw/raw1' size 200m;
alter tablespace users add datafile '/dev/raw/raw1' size 200m
*
ERROR at line 1:
ORA-01119: error in creating database file '/dev/raw/raw1'
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 1

SQL> alter tablespace users add datafile '/dev/raw/raw1' size 199m;
      
Tablespace altered.

   显然，不能把整个裸设备的空间作为数据文件，必须要预留一部分空间。这个预留的空间到底是多少呢？做如下测试：

   添加一个逻辑卷并绑定到/dev/raw/raw7上，查看此逻辑卷大小

  [root@ora-as4 root]# lvdisplay /dev/vg00/test07.dbf
--- Logical volume ---
LV Name                /dev/vg00/test07.dbf
VG Name                vg00
LV Write Access        read/write
LV Status              available
LV #                   7
# open                 3
LV Size                4 MB
Current LE             1
Allocated LE           1
Allocation             next free
Read ahead sectors     1024
Block device           58:6

可见此逻辑卷为4M，即4096k。将该逻辑卷作为数据文件添加到数据库中：

SQL> alter tablespace test add datafile '/dev/raw/raw7' size 4096k;
alter tablespace test add datafile '/dev/raw/raw7' size 4096k
*
ERROR at line 1:
ORA-01119: error in creating database file '/dev/raw/raw7'
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 1
 
 
SQL> alter tablespace test add datafile '/dev/raw/raw7' size 4095k;
alter tablespace test add datafile '/dev/raw/raw7' size 4095k
*
ERROR at line 1:
ORA-01119: error in creating database file '/dev/raw/raw7'
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 1
 
 
SQL>  alter tablespace test add datafile '/dev/raw/raw7' size 4089k;
 alter tablespace test add datafile '/dev/raw/raw7' size 4089k
*
ERROR at line 1:
ORA-01119: error in creating database file '/dev/raw/raw7'
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 1
 
 
SQL> alter tablespace test add datafile '/dev/raw/raw7' size 4088k;
 
Tablespace altered.

    由此可见，当预留空间为8k时，才能添加成功，也就是说必须要预留出比oracle的一个block大的空间时，才能添加成功。

二。删除数据文件测试

   试图删除表空间中某一个数据文件，失败。

SQL> alter database datafile '/dev/raw/raw1' drop;
alter database datafile '/dev/raw/raw1' drop
                                        *
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

   查看了一下oracle文档，发现如下解释：

There is no SQL statement that specifically drops a datafile. The only means of dropping a datafile is to drop the tablespace that contains the datafile. For example, if you want to remove a datafile from a tablespace, you could do the following:

1. Create a new tablespace
2. Move the data from the old tablespace to the new one
3. Drop the old tablespace

You can, however, drop a tempfile using the ALTER DATABASE statement. For example:

    由此可见，不能删除表空间中单个的数据文件。但对于temp表空间的数据文件可以单个删除。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-206125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-206125/