管理数据文件

一 Creating Datafiles and Adding Datafiles to a Tablespace 1 alter tablespace zx add datafile '/oracle/CRM2/CRM/zx04.dbf' size 1M; 2 alter tablespace ltemp add tempfile '/oracle/CRM2/CRM/ltemp01.dbf' size 200m;

二：Changing Datafile Size 1 开启或禁止数据文件自动扩展 通过查询dba_data_files视图字段autoextensible以确定数据文件是否自动扩展  select file_id,file_name,tablespace_name,autoextensible from  dba_data_files

   FILE_ID FILE_NAME                      TABLESPACE_NAME                AUT ---------- ------------------------------ ------------------------------ ---          8 /oracle/CRM2/CRM/zxbig1.dbf    ZXBIGTBS                       NO          7 /oracle/CRM2/CRM/zx3.dbf       ZX                             NO          6 /oracle/CRM2/CRM/undotbs2.dbf  UNDOTBS2                       NO          5 /oracle/CRM2/CRM/zx1.dbf       ZX                             NO          4 /oracle/CRM2/CRM/users01.dbf   USERS                          YES          3 /oracle/CRM2/CRM/sysaux01.dbf  SYSAUX                         YES          2 /oracle/CRM2/CRM/zx2.dbf       ZX                             NO          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM                         YES 更改数据文件为自动扩展 alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend on; 禁止数据文件的自动扩展 alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend off； eg：更改数据文件自动扩展

SQL> alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend on; Database altered. select file_id,file_name,tablespace_name,autoextensible from dba_data_files

   FILE_ID FILE_NAME                      TABLESPACE_NAME                AUT ---------- ------------------------------ ------------------------------ ---          8 /oracle/CRM2/CRM/zxbig1.dbf    ZXBIGTBS                       NO          7 /oracle/CRM2/CRM/zx3.dbf       ZX                             NO          6 /oracle/CRM2/CRM/undotbs2.dbf  UNDOTBS2                       NO          5 /oracle/CRM2/CRM/zx1.dbf       ZX                             YES          4 /oracle/CRM2/CRM/users01.dbf   USERS                          YES          3 /oracle/CRM2/CRM/sysaux01.dbf  SYSAUX                         YES          2 /oracle/CRM2/CRM/zx2.dbf       ZX                             NO          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM                         YES eg 禁止数据文件自动扩展 SQL> alter database datafile '/oracle/CRM2/CRM/zx1.dbf' autoextend off;

Database altered.

SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;

   FILE_ID FILE_NAME                      TABLESPACE_NAME                AUT ---------- ------------------------------ ------------------------------ ---          8 /oracle/CRM2/CRM/zxbig1.dbf    ZXBIGTBS                       NO          7 /oracle/CRM2/CRM/zx3.dbf       ZX                             NO          6 /oracle/CRM2/CRM/undotbs2.dbf  UNDOTBS2                       NO          5 /oracle/CRM2/CRM/zx1.dbf       ZX                             NO          4 /oracle/CRM2/CRM/users01.dbf   USERS                          YES          3 /oracle/CRM2/CRM/sysaux01.dbf  SYSAUX                         YES          2 /oracle/CRM2/CRM/zx2.dbf       ZX                             NO          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM                         YES

2  调整数据文件的大小 语句：alter database datafile  ...... resize xxx eg 增加数据文件大小 看当前数据文件的大小

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 ,autoextensible from dba_data_files;

   FILE_ID FILE_NAME                      TABLESPACE_NAME                BYTES/1024/1024 AUT ---------- ------------------------------ ------------------------------ --------------- ---          8 /oracle/CRM2/CRM/zxbig1.dbf    ZXBIGTBS                                  2048 NO          7 /oracle/CRM2/CRM/zx3.dbf       ZX                                           1 NO          6 /oracle/CRM2/CRM/undotbs2.dbf  UNDOTBS2                                   200 NO          5 /oracle/CRM2/CRM/zx1.dbf       ZX                                         100 NO          4 /oracle/CRM2/CRM/users01.dbf   USERS                                        5 YES          3 /oracle/CRM2/CRM/sysaux01.dbf  SYSAUX                                     280 YES          2 /oracle/CRM2/CRM/zx2.dbf       ZX                                          10 NO          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM                                     480 YES 增加数据文件/oracle/CRM2/CRM/zx3.dbf为10M;

SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' resize 10M;

Database altered.

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 ,autoextensible from dba_data_files;

   FILE_ID FILE_NAME                      TABLESPACE_NAME                BYTES/1024/1024 AUT ---------- ------------------------------ ------------------------------ --------------- ---          8 /oracle/CRM2/CRM/zxbig1.dbf    ZXBIGTBS                                  2048 NO          7 /oracle/CRM2/CRM/zx3.dbf       ZX                                          10 NO          6 /oracle/CRM2/CRM/undotbs2.dbf  UNDOTBS2                                   200 NO          5 /oracle/CRM2/CRM/zx1.dbf       ZX                                         100 NO          4 /oracle/CRM2/CRM/users01.dbf   USERS                                        5 YES          3 /oracle/CRM2/CRM/sysaux01.dbf  SYSAUX                                     280 YES          2 /oracle/CRM2/CRM/zx2.dbf       ZX                                          10 NO          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM                                     480 YES    eg 减小数据文件大小 注意能否减少取决于数据文件当前使用值。

SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' resize 1M;

Database altered.

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files;

   FILE_ID FILE_NAME                      TABLESPACE_NAME                BYTES/1024/1024 AUT ---------- ------------------------------ ------------------------------ --------------- ---          8 /oracle/CRM2/CRM/zxbig1.dbf    ZXBIGTBS                                  2048 NO          7 /oracle/CRM2/CRM/zx3.dbf       ZX                                           1 NO          6 /oracle/CRM2/CRM/undotbs2.dbf  UNDOTBS2                                   200 NO          5 /oracle/CRM2/CRM/zx1.dbf       ZX                                         100 NO          4 /oracle/CRM2/CRM/users01.dbf   USERS                                        5 YES          3 /oracle/CRM2/CRM/sysaux01.dbf  SYSAUX                                     280 YES          2 /oracle/CRM2/CRM/zx2.dbf       ZX                                          10 NO          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM                                     480 YES

三 更改数据文件可用性 那些情况需要我们offline数据文件 a 执行一个离线备份 b 重命名或者迁移数据文件，必须先离线数据文件 c 数据文件丢失或者损坏，打开数据之前，这些文件必须offline 注意，对只读表空间的数据文件offline后online并不会影响表空间的只读状态。 1 归档模式下offline或者online 数据文件 语句 alter database datafile ........ online|offline; 不过注意，offline的数据online的时候需要recover eg SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS ---------- ---------------------------------------- -------          1 /oracle/CRM2/CRM/system01.dbf            SYSTEM          2 /oracle/CRM2/CRM/zx2.dbf                 ONLINE          3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE          4 /oracle/CRM2/CRM/users01.dbf             ONLINE          5 /oracle/CRM2/CRM/zx1.dbf                 ONLINE          6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE          7 /oracle/CRM2/CRM/zx3.dbf                 ONLINE          8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE

8 rows selected.

SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' offline;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS ---------- ---------------------------------------- -------          1 /oracle/CRM2/CRM/system01.dbf            SYSTEM          2 /oracle/CRM2/CRM/zx2.dbf                 ONLINE          3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE          4 /oracle/CRM2/CRM/users01.dbf             ONLINE          5 /oracle/CRM2/CRM/zx1.dbf                 ONLINE          6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE          7 /oracle/CRM2/CRM/zx3.dbf                 RECOVER          8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE

8 rows selected.

SQL> alter database datafile '/oracle/CRM2/CRM/zx3.dbf' online; 注意这里，不能直接online，上面status字段已经提示需要recover alter database datafile '/oracle/CRM2/CRM/zx3.dbf' online * ERROR at line 1: ORA-01113: file 7 needs media recovery ORA-01110: data file 7: '/oracle/CRM2/CRM/zx3.dbf'

SQL> recover datafile 7; Media recovery complete. SQL> alter database datafile 7 online;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS ---------- ---------------------------------------- -------          1 /oracle/CRM2/CRM/system01.dbf            SYSTEM          2 /oracle/CRM2/CRM/zx2.dbf                 ONLINE          3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE          4 /oracle/CRM2/CRM/users01.dbf             ONLINE          5 /oracle/CRM2/CRM/zx1.dbf                 ONLINE          6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE          7 /oracle/CRM2/CRM/zx3.dbf                 ONLINE          8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE 2 同时更改表空间所有数据文件状态 语句 alter tablespace ..... datafile   offline|online alter tablespace ...... tempfile  offline|online 注意 a 该语句影响表空间的所有数据文件，而不影响表空间的状态。 b 对于离线system，undo，默认临时表空间所有数据文件时，数据库必须mount。而其它表空间数据文件的离线无限制，mount open状态都可以。 eg ：测试离线表空间所有数据文件而表空间状态不变 当前表空间状态 SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS ------------------------------ --------- SYSTEM                         ONLINE SYSAUX                         ONLINE TEMP                           ONLINE USERS                          ONLINE UNDOTBS2                       ONLINE ZX                             ONLINE ZXBIGTBS                       ONLINE LTEMP1                         ONLINE LTEMP2                         ONLINE 离线zx表空间所有数据文件 SQL> alter tablespace zx datafile offline; Tablespace altered. SQL> select file#,name,status from v$datafile;

     FILE# NAME                           STATUS ---------- ------------------------------ -------          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM          2 /oracle/CRM2/CRM/zx2.dbf       RECOVER          3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE          4 /oracle/CRM2/CRM/users01.dbf   ONLINE          5 /oracle/CRM2/CRM/zx1.dbf       RECOVER          6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE          7 /oracle/CRM2/CRM/zx3.dbf       RECOVER          8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS ------------------------------ --------- SYSTEM                         ONLINE SYSAUX                         ONLINE TEMP                           ONLINE USERS                          ONLINE UNDOTBS2                       ONLINE ZX                             ONLINE ZXBIGTBS                       ONLINE LTEMP1                         ONLINE LTEMP2                         ONLINE

eg：测试system表空间所有数据文件和undo表空间所有数据文件只能在数据库mount状态下离线。 SQL> alter tablespace system datafile offline; alter tablespace system datafile offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> alter tablespace undotbs2 datafile offline; alter tablespace undotbs2 datafile offline * ERROR at line 1: ORA-30021: Operation not allowed on undo tablespace

SQL> startup force mount; ORACLE instance started.

Total System Global Area  322961408 bytes Fixed Size                  2020480 bytes Variable Size              96471936 bytes Database Buffers          218103808 bytes Redo Buffers                6365184 bytes Database mounted. SQL> alter tablespace system datafile offline;

Tablespace altered.

SQL> alter tablespace undotbs2 datafile offline;

Tablespace altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS ---------- ---------------------------------------- -------          1 /oracle/CRM2/CRM/system01.dbf            SYSOFF          2 /oracle/CRM2/CRM/zx2.dbf                 ONLINE          3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE          4 /oracle/CRM2/CRM/users01.dbf             ONLINE          5 /oracle/CRM2/CRM/zx1.dbf                 ONLINE          6 /oracle/CRM2/CRM/undotbs2.dbf            RECOVER          7 /oracle/CRM2/CRM/zx3.dbf                 ONLINE          8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE 四 重命名和迁移数据文件 注意：对于重命名或者迁移系统表空间数据文件，默认临时表空间数据文件、或者还原表空间数据文件则必须使用alter database 方式。 重命名单个表空间的数据文件步骤: 1 normal离线表空间的所有数据文件 alter tablespace zx offline normal； 2 在操作系统上更改数据文件名 3 使用alter tablespace ........rename datafile 语句改变数据文件名字 4 online表空间，查询相应视图确认更改效果 -------------------------------------------------------------------------- 迁移单个表空间数据文件的步骤: 1 normal离线表空间的所有数据文件 2 在操作系统上拷贝数据文件到目标位置 3 使用alter tablespace ........rename datafile 语句改变数据文件位置。 4 online表空间，查询相应视图确认更改效果 --------------------------------------------------------------------------- 重命名或者迁移多个表空间数据文件的步骤： 1 确保数据库处于mount状态 2 基于操作系统拷贝或者重命名数据文件 3 使用alter database语句更改文件位置或者名字。 ----------------------------------------------------------------------------- 以下三个例子分别展示如何操作： eg1：更改zx表空间中所有数据文件名字 查询zx表空间中所有数据文件名字： select tb.name as tablespace_name,df.file#,df.name as datafile,df.status from v$tablespace tb,v$datafile df where tb.ts#=df.ts#

TABLESPACE_NAME                     FILE# DATAFILE                                 STATUS ------------------------------ ---------- ---------------------------------------- ------- SYSTEM                                  1 /oracle/CRM2/CRM/system01.dbf            SYSTEM SYSAUX                                  3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE USERS                                   4 /oracle/CRM2/CRM/users01.dbf             ONLINE UNDOTBS2                                6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE ZX                                      5 /oracle/CRM2/CRM/zx1.dbf                 ONLINE ZX                                      7 /oracle/CRM2/CRM/zx3.dbf                 ONLINE ZX                                      2 /oracle/CRM2/CRM/zx2.dbf                 ONLINE ZXBIGTBS                                8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE

normal离线zx表空间： SQL> alter tablespace zx offline normal; Tablespace altered.

操作系统上重命名zx表空间： SQL> host; [oracle@oracle ~]$ mv /oracle/CRM2/CRM/zx1.dbf /oracle/CRM2/CRM/zxa.dbf [oracle@oracle ~]$ mv /oracle/CRM2/CRM/zx2.dbf /oracle/CRM2/CRM/zxb.dbf [oracle@oracle ~]$ mv /oracle/CRM2/CRM/zx3.dbf /oracle/CRM2/CRM/zxc.dbf [oracle@oracle ~]$ ls -l /oracle/CRM2/CRM/zx* -rw-r----- 1 oracle oinstall  104865792 Nov  2 20:18 /oracle/CRM2/CRM/zxa.dbf -rw-r----- 1 oracle oinstall   10493952 Nov  2 20:18 /oracle/CRM2/CRM/zxb.dbf -rw-r----- 1 oracle oinstall 2147491840 Nov  2 19:47 /oracle/CRM2/CRM/zxbig1.dbf -rw-r----- 1 oracle oinstall    1056768 Nov  2 20:18 /oracle/CRM2/CRM/zxc.dbf 使用alter tablespace ........rename datafile 语句改变数据文件名字： 语句:alter tablespace zx                rename datafile '/oracle/CRM2/CRM/zx1.dbf',                                            '/oracle/CRM2/CRM/zx2.dbf',                                            '/oracle/CRM2/CRM/zx3.dbf'                         to               '/oracle/CRM2/CRM/zxa.dbf',                                           '/oracle/CRM2/CRM/zxb.dbf',                                           '/oracle/CRM2/CRM/zxc.dbf' ;           SQL> alter tablespace zx   2                 rename datafile '/oracle/CRM2/CRM/zx1.dbf',   3                                            '/oracle/CRM2/CRM/zx2.dbf',   4                                            '/oracle/CRM2/CRM/zx3.dbf'   5                              to          '/oracle/CRM2/CRM/zxa.dbf',   6                                           '/oracle/CRM2/CRM/zxb.dbf',   7                                           '/oracle/CRM2/CRM/zxc.dbf' ;

Tablespace altered. online表空间并检查更改效果如下：

SQL> alter tablespace zx online;

Tablespace altered.

SQL> select tb.name as tablespace_name,df.file#,df.name as datafile,df.status from v$tablespace tb,v$datafile df where tb.ts#=df.ts#;

TABLESPACE_NAME                     FILE# DATAFILE                                 STATUS ------------------------------ ---------- ---------------------------------------- ------- SYSTEM                                  1 /oracle/CRM2/CRM/system01.dbf            SYSTEM SYSAUX                                  3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE USERS                                   4 /oracle/CRM2/CRM/users01.dbf             ONLINE UNDOTBS2                                6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE ZX                                      5 /oracle/CRM2/CRM/zxa.dbf                 ONLINE ZX                                      7 /oracle/CRM2/CRM/zxc.dbf                 ONLINE ZX                                      2 /oracle/CRM2/CRM/zxb.dbf                 ONLINE ZXBIGTBS                                8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE

eg2:迁移表空间zx所有数据文件到位置/oracle/CRM2/ 查询zx表空间中所有数据文件名字： select tablespace_name,file_id,file_name,status from dba_data_files where tablespace_name like 'ZX'

TABLESPACE_NAME                   FILE_ID FILE_NAME                      STATUS ------------------------------ ---------- ------------------------------ --------- ZX                                      7 /oracle/CRM2/CRM/zxc.dbf       AVAILABLE ZX                                      5 /oracle/CRM2/CRM/zxa.dbf       AVAILABLE ZX                                      2 /oracle/CRM2/CRM/zxb.dbf       AVAILABLE normal离线zx表空间： SQL> alter tablespace zx offline normal; Tablespace altered. SQL> select file#,name,status from v$datafile;

     FILE# NAME                           STATUS ---------- ------------------------------ -------          1 /oracle/CRM2/CRM/system01.dbf  SYSTEM          2 /oracle/CRM2/CRM/zxb.dbf       OFFLINE          3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE          4 /oracle/CRM2/CRM/users01.dbf   ONLINE          5 /oracle/CRM2/CRM/zxa.dbf       OFFLINE          6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE          7 /oracle/CRM2/CRM/zxc.dbf       OFFLINE          8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE 在操作系统上拷贝数据文件到目标位置： SQL> host; [oracle@oracle ~]$ cp /oracle/CRM2/CRM/zx*.dbf /oracle/CRM2/ [oracle@oracle ~]$ ls -l /oracle/CRM2/ total 2213032 -rw-r----- 1 oracle oinstall  104865792 Nov  2 22:37 zxa.dbf -rw-r----- 1 oracle oinstall   10493952 Nov  2 22:37 zxb.dbf -rw-r----- 1 oracle oinstall    1056768 Nov  2 22:39 zxc.dbf 使用alter tablespace ........rename datafile 语句改变数据文件位置 语句：alter tablespace zx                            rename datafile  '/oracle/CRM2/CRM/zxa.dbf',                                                        '/oracle/CRM2/CRM/zxb.dbf',                                                        '/oracle/CRM2/CRM/zxc.dbf'                                                 to   '/oracle/CRM2/zxa.dbf',                                                        '/oracle/CRM2/zxb.dbf',                                                       '/oracle/CRM2/zxc.dbf'; SQL> alter tablespace zx   2            rename datafile  '/oracle/CRM2/CRM/zxa.dbf',   3                                         '/oracle/CRM2/CRM/zxb.dbf',   4                                         '/oracle/CRM2/CRM/zxc.dbf'   5                         to            '/oracle/CRM2/zxa.dbf',   6                                         '/oracle/CRM2/zxb.dbf',   7                                        '/oracle/CRM2/zxc.dbf';

Tablespace altered. online表空间，查询相应视图确认更改效果: select tablespace_name,file_id,file_name,status from dba_data_files where tablespace_name like 'ZX'

TABLESPACE_NAME                   FILE_ID FILE_NAME                      STATUS ------------------------------ ---------- ------------------------------ --------- ZX                                      7 /oracle/CRM2/zxc.dbf           AVAILABLE ZX                                      5 /oracle/CRM2/zxa.dbf           AVAILABLE ZX                                      2 /oracle/CRM2/zxb.dbf           AVAILABLE eg3：移动system表空间数据文件和更改数据文件名字 启动数据库到mount状态： SQL> startup force mount; ORACLE instance started. Total System Global Area  322961408 bytes Fixed Size                  2020480 bytes Variable Size              96471936 bytes Database Buffers          218103808 bytes Redo Buffers                6365184 bytes Database mounted.

拷贝数据文件到目标位置： SQL> host; [oracle@oracle ~]$ cp /oracle/CRM2/CRM/system01.dbf  /oracle/CRM2/ [oracle@oracle ~]$ ls -l /oracle/CRM2 total 2705044 -rw-r----- 1 oracle oinstall  503324672 Nov  2 23:17 system01.dbf

通过alter database rename file .....to 移动system表空间位置： alter database rename file '/oracle/CRM2/CRM/system01.dbf'  to '/oracle/CRM2/system01.dbf';

启动数据库到open状态并确认更改有效： SQL> alter  database open; Database altered. SQL> col name for a30 SQL> select file#,name,status from v$datafile;

     FILE# NAME                           STATUS ---------- ------------------------------ -------          1 /oracle/CRM2/system01.dbf      SYSTEM          2 /oracle/CRM2/zxb.dbf           ONLINE          3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE          4 /oracle/CRM2/CRM/users01.dbf   ONLINE          5 /oracle/CRM2/zxa.dbf           ONLINE          6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE          7 /oracle/CRM2/zxc.dbf           ONLINE          8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE

更改system表空间数据文件的名字： SQL> startup force mount; ORACLE instance started.

Total System Global Area  322961408 bytes Fixed Size                  2020480 bytes Variable Size              96471936 bytes Database Buffers          218103808 bytes Redo Buffers                6365184 bytes Database mounted. SQL> host; [oracle@oracle ~]$ mv /oracle/CRM2/system01.dbf  /oracle/CRM2/system1.dbf [oracle@oracle ~]$ ls /oracle/CRM2/ CRM  ERP  system1.dbf  zxa.dbf  zxb.dbf  zxbig1.dbf  zxc.dbf

SQL> alter database rename file '/oracle/CRM2/system01.dbf' to '/oracle/CRM2/system1.dbf';

Database altered.

SQL> alter  database open;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                           STATUS ---------- ------------------------------ -------          1 /oracle/CRM2/system1.dbf       SYSTEM          2 /oracle/CRM2/zxb.dbf           ONLINE          3 /oracle/CRM2/CRM/sysaux01.dbf  ONLINE          4 /oracle/CRM2/CRM/users01.dbf   ONLINE          5 /oracle/CRM2/zxa.dbf           ONLINE          6 /oracle/CRM2/CRM/undotbs2.dbf  ONLINE          7 /oracle/CRM2/zxc.dbf           ONLINE          8 /oracle/CRM2/CRM/zxbig1.dbf    ONLINE 五  Dropping Datafiles 语句：alter tablespace xxx drop datafile 'xxxxxxxxxx';       alter tablespace xxx drop tempfile 'xxxxxxxxxxxx'; restrictions for drop datafile 1 数据库必须open 2 system表空间的数据文件不能drop 3 如果表空间离线，则数据文件不能drop 4 如果表空间有一个数据文件，则该数据文件不能drop 5 对于大表空间drop datafile语句不适用。 eg1大表空间数据文件drop  select tablespace_name,file_name,autoextensible from dba_data_files

TABLESPACE_NAME                FILE_NAME                      AUT ------------------------------ ------------------------------ --- ZXBIGTBS                       /oracle/CRM2/CRM/zxbig1.dbf    NO ZX                             /oracle/CRM2/zxc.dbf           NO UNDOTBS2                       /oracle/CRM2/CRM/undotbs2.dbf  NO ZX                             /oracle/CRM2/zxa.dbf           NO USERS                          /oracle/CRM2/CRM/users01.dbf   YES SYSAUX                         /oracle/CRM2/CRM/sysaux01.dbf  YES ZX                             /oracle/CRM2/zxb.dbf           NO SYSTEM                         /oracle/CRM2/system1.dbf       YES

SQL> alter tablespace zxbigtbs drop datafile '/oracle/CRM2/CRM/zxbig1.dbf '; alter tablespace zxbigtbs drop datafile '/oracle/CRM2/CRM/zxbig1.dbf ' * ERROR at line 1: ORA-01565: error in identifying file '/oracle/CRM2/CRM/zxbig1.dbf '

eg2:测试表空间只有一个数据文件能不能drop select tablespace_name,file_name,autoextensible from dba_data_files

TABLESPACE_NAME                FILE_NAME                      AUT ------------------------------ ------------------------------ --- ZXBIGTBS                       /oracle/CRM2/CRM/zxbig1.dbf    NO ZX                             /oracle/CRM2/zxc.dbf           NO UNDOTBS2                       /oracle/CRM2/CRM/undotbs2.dbf  NO ZX                             /oracle/CRM2/zxa.dbf           NO USERS                          /oracle/CRM2/CRM/users01.dbf   YES SYSAUX                         /oracle/CRM2/CRM/sysaux01.dbf  YES ZX                             /oracle/CRM2/zxb.dbf           NO SYSTEM                         /oracle/CRM2/system1.dbf       YES

SQL> alter tablespace users drop datafile '/oracle/CRM2/CRM/users01.dbf'; alter tablespace users drop datafile '/oracle/CRM2/CRM/users01.dbf' * ERROR at line 1: ORA-03261: the tablespace USERS has only one file eg3：测试表空间离线，则数据文件不能删除 SQL> alter tablespace zx offline;

Tablespace altered.

SQL> alter tablespace zx drop datafile '/oracle/CRM2/zxc.dbf'; alter tablespace zx drop datafile '/oracle/CRM2/zxc.dbf' * ERROR at line 1: ORA-03264: cannot drop offline datafile of locally managed tablespace eg4：要删除数据文件，数据库必须open SQL> select open_mode from v$database;

OPEN_MODE ---------- MOUNTED SQL> alter tablespace  zx drop datafile '/oracle/CRM2/zxc.dbf'; alter tablespace  zx drop datafile '/oracle/CRM2/zxc.dbf' * ERROR at line 1: ORA-01109: database not open eg5 system表空间数据文件不能drop SQL> alter tablespace system drop datafile '/oracle/CRM2/system1.dbf '; alter tablespace system drop datafile '/oracle/CRM2/system1.dbf ' * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary