这篇文章主要介绍“datafile的create/offline/drop/rename方法怎么使用”，在日常操作中，相信很多人在datafile的create/offline/drop/rename方法怎么使用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”datafile的create/offline/drop/rename方法怎么使用”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
一、创建表空间：
create tablespace test1
datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' size 10M
autoextend on next 1M maxsize 2G
extent management local uniform size 1M
segment space management auto;
Tablespace created.
SQL>
二、更改表空间状态：
SYS> alter tablespace test1 read only;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='TEST1';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
TEST1 1 LOCAL UNIFORM AUTO READ ONLY
SYS> alter tablespace test1 read write;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='TEST1';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
TEST1 1 LOCAL UNIFORM AUTO ONLINE
SQL>
三、表空间重命名：（在线修改表空间名）
SYS> alter tablespace test1 rename to sales;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='TEST1';
no rows selected
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='SALES';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
SALES 1 LOCAL UNIFORM AUTO ONLINE
四、查询表空间信息：
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME;
FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- --------- ---------
/u01/app/oracle/oradata/DBdb/users01.dbf USERS 3466.25 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/undotbs01.dbf UNDOTBS1 3330 ONLINE UNDO
/u01/app/oracle/oradata/DBdb/sysaux01.dbf SYSAUX 740 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/system01.dbf SYSTEM 2800 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/example01.dbf EXAMPLE 338.75 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test101.dbf SALES 10 ONLINE PERMANENT
6 rows selected.
SQL>
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='SALES';
FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf SALES 10 ONLINE PERMANENT
五、表空间的大小更改三种方式：
1、alter tablespace sales add datafile '/u01/app/oracle/oradata/DBdb/sales02.dbf' size 10M;
2、alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' autoextend on maxsize 2G;
3 alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' resize 50M;
六、脱机
SYS> alter tablespace sales offline;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='SALES';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
SALES 1 LOCAL UNIFORM AUTO OFFLINE
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='SALES';
FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf SALES OFFLINE PERMANENT
SQL>
七、删除表空间
SYS> create table sales_1 (id number)
2 tablespace sales;
Table created.
SYS> select table_name,tablespace_name from dba_tables where tablespace_name='SALES';
TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------
SALES_1 SALES
SYS> drop tablespace sales;
drop tablespace sales
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
有对象的时候删除要用如下语句：
drop tablespace sales INCLUDING CONTENTS;
八：OMF
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> !mkdir -p /u01/app/oracle/oradata/omf
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/omf';
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/omf
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL> create tablespace test2;
Tablespace created.
SQL> col file_name for a804
SQL> col file_name for a80
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='TEST2';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST2 /u01/app/oracle/oradata/omf/DBDB/datafile/o1_mf_test2_f2x469nq_.dbf
SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafile
o1_mf_test2_f2x469nq_.dbf
SQL>
SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafile
o1_mf_test2_f2x469nq_.dbf
SQL>
SQL> drop tablespace test2;
Tablespace dropped.
SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafile
SQL>
删除表空间之后再查看操作系统物理路径下没有表空间对应的数据文件了。
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/omf
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/DBdb/test2.dbf' size 5m;
Tablespace created.
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='TEST2';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST2 /u01/app/oracle/oradata/DBdb/test2.dbf
SQL> !ls -lrt /u01/app/oracle/oradata/omf/DBDB/datafile/
total 0
SQL> !ls -lrt /u01/app/oracle/oradata/DBdb/test2.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 11 22:12 /u01/app/oracle/oradata/DBdb/test2.dbf
SQL>
--关闭OMF:
SQL> alter system set db_create_file_dest='';
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
九、修改数据文件可用性
可以通过执行数据文件的在线和离线操作修改数据文件的可用性，离线的数据文件不能被数据库所访问，直到它恢复在线状态之前。只读表空间中的数据文件也可以被离线或在线，只读表空间内的数据文件的在线或离线不影响表空间自身的状态，不管怎么样，在表空间未处于读写状态之前，这些文件都是不可写的。
9.1查看之前改名为sales的表空间状态：（执行的是0ffline）
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='SALES';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf SALES OFFLINE OFFLINE PERMANENT
SQL>
--修改表空间名：
SQL> alter tablespace SALES rename to test1;
alter tablespace SALES rename to test1
*
ERROR at line 1:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test101.dbf'
SQL> alter tablespace sales online;
Tablespace altered.
SQL> alter tablespace SALES rename to test1;
Tablespace altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
9.2 归档模式下的数据文件离线
SQL> alter tablespace TEST1 online;
Tablespace altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 488
Next log sequence to archive 490
Current log sequence 490
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' offline;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' online;
alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test101.dbf'
验证，当offline 数据文件时再次online需要recover数据文件，而offline表空间则可以执行执行online，如下进行recover及online数据文件；
SQL> recover datafile 6;
Media recovery complete.
SQL>
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 OFFLINE ONLINE PERMANENT
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' online;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
9.2、非归档模式下的数据文件离线
在非归档模式下使用alter database ... offline for drop语句离线数据文件。offline关键字标记该数据文件离线，不论其是否损坏，所以可以打开数据库；for drop关键字标记该数据文件随后被删除（只是标记，物理文件还在），该数据文件不能再次恢复到在线状态。（实际上，在在线日志组还未发生切换之前，还是可以恢复到在线状态的）
9.2.1先模拟在线日志还没有切换时的offline for drop;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 488
Current log sequence 490
SQL>
--查询当前log日志组：
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 YES INACTIVE
3 50 1 YES INACTIVE
--执行offline for drop操作：
SQL> alter database datafile 6 offline for drop;
Database altered.
SQL> !ls /u01/app/oracle/oradata/DBdb/test101.dbf
/u01/app/oracle/oradata/DBdb/test101.dbf
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 YES INACTIVE
3 50 1 YES INACTIVE
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
--查询在线日志还未切换，可以进行recover；
SQL> recover datafile 6;
Media recovery complete.
SQL>
SQL> alter database datafile 6 online;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 YES INACTIVE
3 50 1 YES INACTIVE
9.2.2模拟在线日志组已发生切换且日志已被覆盖后执行offline for drop;
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 NO INACTIVE
3 50 1 NO INACTIVE
SQL> alter database datafile 6 offline for drop;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO ACTIVE
2 50 1 NO ACTIVE
3 50 1 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 NO INACTIVE
3 50 1 NO INACTIVE
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
SQL> !ls /u01/app/oracle/oradata/DBdb/test101.dbf
/u01/app/oracle/oradata/DBdb/test101.dbf
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test101.dbf'
SQL> recover datafile 6;
ORA-00279: change 5518174 generated at 12/12/2017 00:06:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc
ORA-00280: change 5518174 for thread 1 is in sequence #496
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
没有归档文件可以使用进行恢复datafile 6，所以文件6不能使用。
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' size 10m;
Tablespace altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test102.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
十、重命名（修改存储位置）数据文件
步骤如下：
方法1
1、将包含数据文件的表空间或者只将某个数据文件离线。
2、使用操作系统命令修改数据文件名。
3、使用alter database ... rename file '' to '';语句改变数据库中的数据文件名。
4、备份数据库。
方法2：
1.将数据库shutdown 启动到mount下
2.使用操作系统命令修改数据文件名。
3.使用alter database ... rename file '' to '';语句改变数据库中的数据文件名。
4.启库、备份。
方法1：
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test102.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
SQL>
SQL> alter tablespace TEST1 rename datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' to '/u01/app/oracle/oradata/DBdb/test103.dbf';
alter tablespace TEST1 rename datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' to '/u01/app/oracle/oradata/DBdb/test103.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01121: cannot rename database file 8 - file is in use or recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/DBdb/test102.dbf'
[oracle@wang 2017_12_12]$ oerr ora 01145
01145, 00000, "offline immediate disallowed unless media recovery enabled"
// *Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE
// ... OFFLINE is only allowed if database is in ARCHIVELOG mode.
// *Action:Take tablespace offline normally or shutdown abort. Reconsider your
// backup strategy. You could do this if you were archiving your logs.
[oracle@wang 2017_12_12]$
开归档：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
尝试offline:
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' offline;
Database altered.
SQL>
在使用rename改变数据文件名称；
--先物理盘创建对应名称；
[oracle@wang 2017_12_12]$ cd /u01/app/oracle/oradata/DBdb/
[oracle@wang DBdb]$ cp test102.dbf test1022222.dbf
[oracle@wang DBdb]$ ls -lrt test*
-rw-r----- 1 oracle oinstall 10493952 Dec 12 00:10 test101.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 12 00:32 test2.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 12 00:32 test102.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 12 00:39 test1022222.dbf
[oracle@wang DBdb]$
--正式操作：
SQL> alter database rename file '/u01/app/oracle/oradata/DBdb/test102.dbf' to '/u01/app/oracle/oradata/DBdb/test1022222.dbf';
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test1022222.dbf TEST1 RECOVER ONLINE PERMANENT
SQL>
online新数据文件；
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test1022222.dbf' online;
alter database datafile '/u01/app/oracle/oradata/DBdb/test1022222.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/DBdb/test1022222.dbf'
--恢复；
SQL> recover datafile 8;
Media recovery complete.
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test1022222.dbf' online;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test1022222.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
方法2：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
rename数据文件：
SQL> alter database rename file '/u01/app/oracle/oradata/DBdb/test1022222.dbf' to '/u01/app/oracle/oradata/DBdb/test3333.dbf' ;
alter database rename file '/u01/app/oracle/oradata/DBdb/test1022222.dbf' to '/u01/app/oracle/oradata/DBdb/test3333.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 8 - new file '/u01/app/oracle/oradata/DBdb/test3333.dbf' not found
ORA-01110: data file 8: '/u01/app/oracle/oradata/DBdb/test1022222.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
未发现/u01/app/oracle/oradata/DBdb/test3333.dbf，进行创建：
[oracle@wang DBdb]$ pwd
/u01/app/oracle/oradata/DBdb
[oracle@wang DBdb]$
[oracle@wang DBdb]$ cp test1022222.dbf test3333.dbf
[oracle@wang DBdb]$
再次执行：
SQL> alter database rename file '/u01/app/oracle/oradata/DBdb/test1022222.dbf' to '/u01/app/oracle/oradata/DBdb/test3333.dbf' ;
Database altered.
SQL> alter database open;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test3333.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME;
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/users01.dbf USERS 3466.25 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/undotbs01.dbf UNDOTBS1 3330 ONLINE ONLINE UNDO
/u01/app/oracle/oradata/DBdb/sysaux01.dbf SYSAUX 740 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/system01.dbf SYSTEM 2800 SYSTEM ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/example01.dbf EXAMPLE 338.75 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test2.dbf TEST2 5 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test3333.dbf TEST1 10 ONLINE ONLINE PERMANENT
8 rows selected.
SQL>
十一、删除数据文件
从表空间内删除数据文件：语法
alter tablespace tablespace_name { ADD { DATAFILE | TEMPFILE }
[ file_specification [, file_specification ]... ]
| DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
| SHRINK TEMPFILE { 'filename' | file_number } [KEEP size_clause]
| RENAME DATAFILE 'filename' [, 'filename' ]...
TO 'filename' [, 'filename' ]...
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test3333.dbf';
Tablespace altered.
成功！！！！！！
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf';
alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TEST1 has only one file
报错表示，表空间内仅包含一个数据文件，该数据文件无法被删除。
SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/DBdb/test101bak.dbf' size 5m;
Tablespace altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test101bak.dbf TEST1 5 ONLINE ONLINE PERMANENT
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf';
alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST1
[oracle@wang DBdb]$ oerr ora 03263
03263, 00000, "cannot drop the first file of tablespace %s"
// *Cause: Trying to drop the first datafile with which ts is created
// *Action: Cannot drop the first datafile with which ts is created
[oracle@wang DBdb]$
报错表示不能删除表空间的第一个数据文件
注意：
1、从字典管理迁移到本地管理的只读表空间内的数据文件时不能被删除的。除此之外，其他的只读表空间内的数据文件可以删除。
2、系统表空间内的数据文件无法被删除。
3、如果一个本地管理的表空间被离线，则其内的数据文件无法被删除。
SQL> alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf';
alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
4、如果表空间内仅包含一个数据文件，该数据文件无法被删除。
SQL> alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf';
alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TEST_TBS has only one file
5、如果数据文件不为空，该数据文件无法被删除。
6、删除数据文件必须保证数据块处于打开状态。
7、不能删除表空间下的第一个创建的数据文件：
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf';
alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST1
到此，关于“datafile的create/offline/drop/rename方法怎么使用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
