datefile(2)

今天试试drop datafile
SQL> select * from v$version where rownum <2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL>

创建test1表空间和sa用户
SQL> create tablespace test1 datafile '/oracle/app/oracle/oradata/orcl/test' size 10m;
Tablespace created.
（可以看到test没有.dbf后缀，但这个表空间仍然可以使用）
SQL> create user sa identified by oracle default tablespace test1;
User created.
SQL> select username,default_tablespace from dba_users where username='SA';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SA                             TEST1
SQL> grant connect,resource to sa;
Grant succeeded.
看看数据文件
SQL> select name,status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/oracle/app/oracle/oradata/orcl/system01.dbf       SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf        ONLINE
/oracle/app/oracle/oradata/orcl/test               ONLINE

非归档模式
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     4
Current log sequence           6

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/test' offline drop;
Database altered
这样只能offlin，没有删除数据文件

SQL> select name,status from v$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
/oracle/app/oracle/oradata/orcl/system01.dbf       SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf        ONLINE
/oracle/app/oracle/oradata/orcl/test               RECOVER

alter tablespace test drop datafile '...'是不能删除offline的数据文件的。
SQL> alter tablespace test1 drop datafile '/oracle/app/oracle/oradata/orcl/test';
alter tablespace test1 drop datafile '/oracle/app/oracle/oradata/orcl/test'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST1
SQL>
（好吧，这里报错是不能删除表空间的第一个数据文件）


那先online然后添加文件看看
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/test' online;
Database altered.
SQL>

SQL> alter tablespace test1 add datafile '/oracle/app/oracle/oradata/orcl/test1.dbf' size 10m;
Tablespace altered.

SQL> select tablespace_name,file_name,status from dba_data_files;
TABLESPACE_NAME                FILE_NAME                                          STATUS
------------------------------ -------------------------------------------------- ---------
USERS                          /oracle/app/oracle/oradata/orcl/users01.dbf        AVAILABLE
UNDOTBS1                       /oracle/app/oracle/oradata/orcl/undotbs01.dbf      AVAILABLE
SYSAUX                         /oracle/app/oracle/oradata/orcl/sysaux01.dbf       AVAILABLE
SYSTEM                         /oracle/app/oracle/oradata/orcl/system01.dbf       AVAILABLE
TEST1                          /oracle/app/oracle/oradata/orcl/test               AVAILABLE
TEST1                          /oracle/app/oracle/oradata/orcl/test1.dbf          AVAILABLE

SQL> alter tablespace test1 drop datafile '/oracle/app/oracle/oradata/orcl/test1.dbf';
Tablespace altered.

SQL> select tablespace_name,file_name,status from dba_data_files;
TABLESPACE_NAME                FILE_NAME                                          STATUS
------------------------------ -------------------------------------------------- ---------
USERS                          /oracle/app/oracle/oradata/orcl/users01.dbf        AVAILABLE
UNDOTBS1                       /oracle/app/oracle/oradata/orcl/undotbs01.dbf      AVAILABLE
SYSAUX                         /oracle/app/oracle/oradata/orcl/sysaux01.dbf       AVAILABLE
SYSTEM                         /oracle/app/oracle/oradata/orcl/system01.dbf       AVAILABLE
TEST1                          /oracle/app/oracle/oradata/orcl/test               AVAILABLE
SQL>

创建同样的看看。
SQL> alter tablespace test1 add datafile '/oracle/app/oracle/oradata/orcl/test1.dbf' size 10m;
Tablespace altered.
SQL> select tablespace_name,file_name,status from dba_data_files;
TABLESPACE_NAME                FILE_NAME                                          STATUS
------------------------------ -------------------------------------------------- ---------
USERS                          /oracle/app/oracle/oradata/orcl/users01.dbf        AVAILABLE
UNDOTBS1                       /oracle/app/oracle/oradata/orcl/undotbs01.dbf      AVAILABLE
SYSAUX                         /oracle/app/oracle/oradata/orcl/sysaux01.dbf       AVAILABLE
SYSTEM                         /oracle/app/oracle/oradata/orcl/system01.dbf       AVAILABLE
TEST1                          /oracle/app/oracle/oradata/orcl/test               AVAILABLE
TEST1                          /oracle/app/oracle/oradata/orcl/test1.dbf          AVAILABLE
6 rows selected.
SQL>
还是原来的配方，还是原来的味道，可以看到这样删除重建同样的数据文件也是ok的。

再来看看归档模式下的offline
先开归档
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  300630016 bytes
Fixed Size                  2252704 bytes
Variable Size             167772256 bytes
Database Buffers          125829120 bytes
Redo Buffers                4775936 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL>

SQL> alter database open;
Database altered.


SQL> select name,status from V$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
/oracle/app/oracle/oradata/orcl/system01.dbf       SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf        ONLINE
/oracle/app/oracle/oradata/orcl/test               ONLINE
/oracle/app/oracle/oradata/orcl/test1.dbf          ONLINE
6 rows selected.
SQL>

offline看看
SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/test1.dbf' offline;
Database altered.

SQL> select name,status from V$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
/oracle/app/oracle/oradata/orcl/system01.dbf       SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf        ONLINE
/oracle/app/oracle/oradata/orcl/test               ONLINE
/oracle/app/oracle/oradata/orcl/test1.dbf          RECOVER
6 rows selected.
SQL>

SQL> recover datafile 6
Media recovery complete.

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/test1.dbf' online;
Database altered.

SQL> select name,status from V$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
/oracle/app/oracle/oradata/orcl/system01.dbf       SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf        ONLINE
/oracle/app/oracle/oradata/orcl/test               ONLINE
/oracle/app/oracle/oradata/orcl/test1.dbf          ONLINE
6 rows selected.

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/test1.dbf' offline drop;
Database altered.

SQL> select name,status from V$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
/oracle/app/oracle/oradata/orcl/system01.dbf       SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf        ONLINE
/oracle/app/oracle/oradata/orcl/test               ONLINE
/oracle/app/oracle/oradata/orcl/test1.dbf          RECOVER
6 rows selected.
SQL>
好像都一样啊
当然都是自己看了一点东西就上手练练，理论性的东西还欠缺很多啊。多看，多实践，加油！
先到这吧。
下次继续