datafile的create/offline/drop/rename方法怎么使用

这篇文章主要介绍“datafile的create/offline/drop/rename方法怎么使用”，在日常操作中，相信很多人在datafile的create/offline/drop/rename方法怎么使用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”datafile的create/offline/drop/rename方法怎么使用”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

一、创建表空间：  create tablespace test1datafile /u01/app/oracle/oradata/DBdb/test101.dbf size 10M  autoextend on next 1M maxsize 2Gextent 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 ONLYSYS> 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   ONLINESQL> 三、表空间重命名：（在线修改表空间名）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 selectedSQL> 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    PERMANENT6 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   OFFLINESQL> 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   PERMANENTSQL>七、删除表空间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                        SALESSYS> drop tablespace sales;drop tablespace sales*ERROR at line 1:ORA-01549: tablespace not empty, use INCLUDING CONTENTS option有对象的时候删除要用如下语句：drop tablespace sales INCLUDING CONTENTS;八：OMFSQL> show parameter db_createNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_create_file_dest                  stringdb_create_online_log_dest_1          stringdb_create_online_log_dest_2          stringdb_create_online_log_dest_3          stringdb_create_online_log_dest_4          stringdb_create_online_log_dest_5          stringSQL> !mkdir -p /u01/app/oracle/oradata/omfSQL> alter system set db_create_file_dest=/u01/app/oracle/oradata/omf;System altered.SQL> show parameter db_createNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_create_file_dest                  string      /u01/app/oracle/oradata/omfdb_create_online_log_dest_1          stringdb_create_online_log_dest_2          stringdb_create_online_log_dest_3          stringdb_create_online_log_dest_4          stringdb_create_online_log_dest_5          stringSQL> SQL> create tablespace test2;Tablespace created.SQL> col file_name for a804SQL> col file_name for a80SQL> 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_.dbfSQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafileo1_mf_test2_f2x469nq_.dbfSQL>  SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafileo1_mf_test2_f2x469nq_.dbfSQL> SQL> drop tablespace test2;Tablespace dropped.SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafileSQL> 删除表空间之后再查看操作系统物理路径下没有表空间对应的数据文件了。SQL> show parameter db_createNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_create_file_dest                  string      /u01/app/oracle/oradata/omfdb_create_online_log_dest_1          stringdb_create_online_log_dest_2          stringdb_create_online_log_dest_3          stringdb_create_online_log_dest_4          stringdb_create_online_log_dest_5          stringSQL> 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.dbfSQL> !ls -lrt /u01/app/oracle/oradata/omf/DBDB/datafile/total 0SQL> !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.dbfSQL> --关闭OMF:SQL> alter system set db_create_file_dest=;System altered.SQL> show parameter db_createNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_create_file_dest                  stringdb_create_online_log_dest_1          stringdb_create_online_log_dest_2          stringdb_create_online_log_dest_3          stringdb_create_online_log_dest_4          stringdb_create_online_log_dest_5          stringSQL> 九、修改数据文件可用性可以通过执行数据文件的在线和离线操作修改数据文件的可用性，离线的数据文件不能被数据库所访问，直到它恢复在线状态之前。只读表空间中的数据文件也可以被离线或在线，只读表空间内的数据文件的在线或离线不影响表空间自身的状态，不管怎么样，在表空间未处于读写状态之前，这些文件都是不可写的。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   PERMANENTSQL>  --修改表空间名：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 offlineORA-01110: data file 6: /u01/app/oracle/oradata/DBdb/test101.dbfSQL>  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    PERMANENTSQL> 9.2 归档模式下的数据文件离线SQL> alter tablespace TEST1 online;Tablespace altered.SQL>  archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     488Next log sequence to archive   490Current log sequence           490SQL> 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    PERMANENTSQL> 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 recoveryORA-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    PERMANENTSQL> 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    PERMANENTSQL> 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 bytesFixed Size                  2257840 bytesVariable Size             549456976 bytesDatabase Buffers          281018368 bytesRedo Buffers                2371584 bytesDatabase mounted.SQL> SQL> alter database noarchivelog;Database altered.SQL> alter database open;Database altered.SQL> archive log list;Database log mode              No Archive ModeAutomatic archival             DisabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     488Current log sequence           490SQL>  --查询当前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  CURRENT2         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.dbfSQL>  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 INACTIVE3         50          1 YES INACTIVESQL> 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    PERMANENTSQL> 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 INACTIVE3         50          1 YES INACTIVE9.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    PERMANENTSQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;    GROUP#     SIZE_M    MEMBERS ARC STATUS---------- ---------- ---------- --- ----------------         1         50          1 NO  CURRENT2         50          1 NO  INACTIVE         3         50          1 NO  INACTIVESQL> 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  ACTIVE2         50          1 NO  ACTIVE         3         50          1 NO  CURRENTSQL> 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  INACTIVESQL> 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    PERMANENTSQL> !ls /u01/app/oracle/oradata/DBdb/test101.dbf/u01/app/oracle/oradata/DBdb/test101.dbfSQL> alter database datafile 6 online;alter database datafile 6 online*ERROR at line 1:ORA-01113: file 6 needs media recoveryORA-01110: data file 6: /u01/app/oracle/oradata/DBdb/test101.dbfSQL> recover datafile 6;ORA-00279: change 5518174 generated at 12/12/2017 00:06:18 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arcORA-00280: change 5518174 for thread 1 is in sequence #496Specify log: {=suggested | filename | AUTO | CANCEL}autoORA-00308: cannot open archived log /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arcORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arcORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional 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    PERMANENTSQL> 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    PERMANENTSQL> 十、重命名（修改存储位置）数据文件步骤如下：方法11、将包含数据文件的表空间或者只将某个数据文件离线。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    PERMANENTSQL>  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 filesORA-01121: cannot rename database file 8 - file is in use or recoveryORA-01110: data file 8: /u01/app/oracle/oradata/DBdb/test102.dbf[oracle@wang 2017_12_12]$ oerr ora 0114501145, 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 bytesFixed Size                  2257840 bytesVariable Size             549456976 bytesDatabase Buffers          281018368 bytesRedo Buffers                2371584 bytesDatabase 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    PERMANENTSQL>  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 recoveryORA-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    PERMANENTSQL> 方法2：SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> SQL> startup mount;ORACLE instance started.Total System Global Area  835104768 bytesFixed Size                  2257840 bytesVariable Size             549456976 bytesDatabase Buffers          281018368 bytesRedo Buffers                2371584 bytesDatabase 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 filesORA-01141: error renaming data file 8 - new file /u01/app/oracle/oradata/DBdb/test3333.dbf not foundORA-01110: data file 8: /u01/app/oracle/oradata/DBdb/test1022222.dbfORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional 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    PERMANENTSQL>      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    PERMANENT8 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    PERMANENTSQL> 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 0326303263, 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 tablespace4、如果表空间内仅包含一个数据文件，该数据文件无法被删除。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 file5、如果数据文件不为空，该数据文件无法被删除。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方法怎么使用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！