数据文件管理

Managing Datafiles

创建控制文件

CREATE TABLESPACE

CREATE TEMPORARY TABLESPACE

ALTER TABLESPACE ... ADD DATAFILE

ALTER TABLESPACE ... ADD TEMPFILE

开启数据文件大小自动扩展

ALTER TABLESPACE users

ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M

AUTOEXTEND ON

NEXT 512K    每次最小增量

MAXSIZE 250M; 最大值

关闭数据文件大小自动扩展

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'

AUTOEXTEND OFF;

手动改变数据文件大小

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'

RESIZE 100M;

数据文件的在线和离线

归档模式下

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

如果没有事先将表空间离线，要上线，必须先进行介质恢复，然后才行。

SQL> alter database datafile '/u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c7qwdbq_.dbf' offline;

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

     FILE# NAME                                               STATUS  ENABLED

---------- -------------------------------------------------- ------- ----------

         1 /u01/app/oracle/oradata/OCP/datafile/o1_mf_system_ SYSTEM  READ WRITE

           9b06wpbf_.dbf

         2 /u01/app/oracle/oradata/OCP/datafile/o1_mf_sysaux_ ONLINE  READ WRITE

           9b06wpfq_.dbf

         3 /u01/app/oracle/oradata/OCP/datafile/o1_mf_undotbs ONLINE  READ WRITE

           1_9b06wphs_.dbf

         4 /u01/app/oracle/oradata/OCP/datafile/o1_mf_users_9 ONLINE  READ WRITE

           b06wpkz_.dbf

     FILE# NAME                                               STATUS  ENABLED

---------- -------------------------------------------------- ------- ----------

         5 /u01/app/oracle/oradata/OCP/datafile/o1_mf_example ONLINE  READ WRITE

           _9b0726g2_.dbf

         6 /u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c RECOVER READ WRITE

           7qwdbq_.dbf

         8 /u01/app/oracle/oradata/OCP/datafile/o1_mf_bigtbs_ ONLINE  READ WRITE

           9bx7v90b_.dbf

SQL> alter database datafile '/u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c7qwdbq_.dbf' online;

alter database datafile '/u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c7qwdbq_.dbf' online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c7qwdbq_.dbf'

SQL> recover datafile 6

Media recovery complete.

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

     FILE# NAME                                               STATUS  ENABLED

---------- -------------------------------------------------- ------- ----------

         1 /u01/app/oracle/oradata/OCP/datafile/o1_mf_system_ SYSTEM  READ WRITE

           9b06wpbf_.dbf

         2 /u01/app/oracle/oradata/OCP/datafile/o1_mf_sysaux_ ONLINE  READ WRITE

           9b06wpfq_.dbf

         3 /u01/app/oracle/oradata/OCP/datafile/o1_mf_undotbs ONLINE  READ WRITE

           1_9b06wphs_.dbf

         4 /u01/app/oracle/oradata/OCP/datafile/o1_mf_users_9 ONLINE  READ WRITE

           b06wpkz_.dbf

     FILE# NAME                                               STATUS  ENABLED

---------- -------------------------------------------------- ------- ----------

         5 /u01/app/oracle/oradata/OCP/datafile/o1_mf_example ONLINE  READ WRITE

           _9b0726g2_.dbf

         6 /u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c OFFLINE READ WRITE

           7qwdbq_.dbf

         8 /u01/app/oracle/oradata/OCP/datafile/o1_mf_bigtbs_ ONLINE  READ WRITE

           9bx7v90b_.dbf

7 rows selected.

SQL> alter database datafile '/u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c7qwdbq_.dbf' online;

Database altered.

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

     FILE# NAME                                               STATUS  ENABLED

---------- -------------------------------------------------- ------- ----------

         1 /u01/app/oracle/oradata/OCP/datafile/o1_mf_system_ SYSTEM  READ WRITE

           9b06wpbf_.dbf

         2 /u01/app/oracle/oradata/OCP/datafile/o1_mf_sysaux_ ONLINE  READ WRITE

           9b06wpfq_.dbf

         3 /u01/app/oracle/oradata/OCP/datafile/o1_mf_undotbs ONLINE  READ WRITE

           1_9b06wphs_.dbf

         4 /u01/app/oracle/oradata/OCP/datafile/o1_mf_users_9 ONLINE  READ WRITE

           b06wpkz_.dbf

     FILE# NAME                                               STATUS  ENABLED

---------- -------------------------------------------------- ------- ----------

         5 /u01/app/oracle/oradata/OCP/datafile/o1_mf_example ONLINE  READ WRITE

           _9b0726g2_.dbf

         6 /u01/app/oracle/oradata/OCP/datafile/o1_mf_test_9c ONLINE  READ WRITE

           7qwdbq_.dbf

         8 /u01/app/oracle/oradata/OCP/datafile/o1_mf_bigtbs_ ONLINE  READ WRITE

           9bx7v90b_.dbf

非归档模式下（表空间不离线）

必须要加上for drop，标记改数据文件将被删除。

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;

表空间所有数据文件上线离线

ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}

ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}

重命名数据文件

1.表空间离线或将数据库在mount状态下

ALTER TABLESPACE users OFFLINE NORMAL;

2.使用操作系统更改数据文件名

3. ALTER TABLESPACE users

RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',

'/u02/oracle/rbdb1/user2.dbf'

TO '/u02/oracle/rbdb1/users01.dbf',

'/u02/oracle/rbdb1/users02.dbf';、

删除数据文件

ALTER TABLESPACE lmtemp DROP DATAFILE '/u02/oracle/data/lmtemp02.dbf';

ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

注意：

1.数据库必须是open状态

2.被删除的数据文件必须是空的，否则不能删除。除非使用

DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

3.不能删除该表空间仅有的和第一个数据文件

4.不能删除只读或离线的表空间的数据文件。

5.不能删除system表空间的数据文件

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

转载于:http://blog.itpub.net/25427762/viewspace-1063894/