ORACLE数据文件管理

实验数据库版本：ORACLE 11.2.0.1

1、查看表空间和数据文件的信息
SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB ,AUTOEXTENSIBLE FROM DBA_DATA_FILES ORDER BY 1 ;

TABLESPACE_NAME FILE_NAME                                             MB AUTOEXTEN
--------------- --------------------------------------------- ---------- ---------
EXAMPLE         /u01/app/oracle/oradata/orcl/example01.dbf           100 YES
SYSAUX          /u01/app/oracle/oradata/orcl/sysaux01.dbf            720 YES
SYSTEM          /u01/app/oracle/oradata/orcl/system01.dbf           2140 YES
TEST            /u01/app/oracle/oradata/orcl/test01.dbf                5 NO
TEST            /u01/app/oracle/oradata/orcl/test02.dbf                5 NO
UNDOTBS1        /u01/app/oracle/oradata/orcl/undotbs01.dbf          4195 YES
USERS           /u01/app/oracle/oradata/orcl/users01.dbf               5 YES
VPX             /u01/app/oracle/oradata/vpx01.dbf                    500 YES

8 rows selected.

2、建立新的表空间
SQL> CREATE TABLESPACE t1 datafile '/u01/app/oracle/oradata/orcl/t1.dbf' SIZE 5M;
Tablespace created.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB ,AUTOEXTENSIBLE FROM DBA_DATA_FILES ORDER BY 1 ;
TABLESPACE_NAME FILE_NAME                                             MB AUTOEXTEN
--------------- --------------------------------------------- ---------- ---------
EXAMPLE         /u01/app/oracle/oradata/orcl/example01.dbf           100 YES
SYSAUX          /u01/app/oracle/oradata/orcl/sysaux01.dbf            720 YES
SYSTEM          /u01/app/oracle/oradata/orcl/system01.dbf           2140 YES
T1              /u01/app/oracle/oradata/orcl/t1.dbf                    5 NO
TEST            /u01/app/oracle/oradata/orcl/test01.dbf                5 NO
TEST            /u01/app/oracle/oradata/orcl/test02.dbf                5 NO
UNDOTBS1        /u01/app/oracle/oradata/orcl/undotbs01.dbf          4195 YES
USERS           /u01/app/oracle/oradata/orcl/users01.dbf               5 YES
VPX             /u01/app/oracle/oradata/vpx01.dbf                    500 YES

9 rows selected.

3、为表空间加入新的数据文件
SQL> ALTER TABLESPACE T1 ADD DATAFILE '/u01/app/oracle/oradata/orcl/t1_02.dbf' SIZE 2M;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB ,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME FILE_NAME                                             MB AUTOEXTEN
--------------- --------------------------------------------- ---------- ---------
T1              /u01/app/oracle/oradata/orcl/t1.dbf                    5 NO
T1              /u01/app/oracle/oradata/orcl/t1_02.dbf                 2 NO

SQL> 

4、删除数据文件，删除的前提是数据文件中没有数据，而且不能使该表空间的第一个数据文件
SQL> CREATE TABLE T1_1 (X INT) TABLESPACE T1;
Table created.

SQL> INSERT INTO T1_1 VALUES(1);
1 row created.
SQL> COMMIT;
Commit complete.

SQL> ALTER TABLESPACE T1 DROP DATAFILE '/u01/app/oracle/oradata/orcl/t1_02.dbf';
Tablespace altered.
SQL> ALTER TABLESPACE T1 DROP DATAFILE '/u01/app/oracle/oradata/orcl/t1.dbf';
ALTER TABLESPACE T1 DROP DATAFILE '/u01/app/oracle/oradata/orcl/t1.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace T1 has only one file
SQL> 

5、改变数据文件的大小
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/t1.dbf' RESIZE 3M;
Database altered.

6、数据文件自动扩展
SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB ,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME FILE_NAME                                             MB AUTOEXTEN
--------------- --------------------------------------------- ---------- ---------
T1              /u01/app/oracle/oradata/orcl/t1.dbf                    3 NO
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/t1.dbf' autoextend on next 1m maxsize 10m;
Database altered.


SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB ,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME FILE_NAME                                             MB AUTOEXTEN
--------------- --------------------------------------------- ---------- ---------
T1              /u01/app/oracle/oradata/orcl/t1.dbf                    3 YES

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/t1.dbf' autoextend off;
Database altered.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB ,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME FILE_NAME                                             MB AUTOEXTEN
--------------- --------------------------------------------- ---------- ---------
T1              /u01/app/oracle/oradata/orcl/t1.dbf                    3 NO

SQL> 
7、设置表空间只读
SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME STATUS
--------------- --------------------
T1              ONLINE

SQL> ALTER TABLESPACE T1 READ ONLY;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME STATUS
--------------- --------------------
T1              READ ONLY

SQL> CREATE TABLE T1_2 (X INT) TABLESPACE T1;
CREATE TABLE T1_2 (X INT) TABLESPACE T1
*
ERROR at line 1:
ORA-01647: tablespace 'T1' is read-only, cannot allocate space in it

SQL> 

8、OFFLINE表空间
SQL> ALTER TABLESPACE T1 OFFLINE;
Tablespace altered.

SQL> INSERT INTO T1_1 VALUES(2);
INSERT INTO T1_1 VALUES(2)
            *
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/t1.dbf'

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='T1';
TABLESPACE_NAME STATUS
--------------- --------------------
T1              OFFLINE

SQL> 

9、更改表空间名称
system和sysaux表空间不能更改名称
SQL> ALTER TABLESPACE T1 RENAME TO T2;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES ;
TABLESPACE_NAME STATUS
--------------- --------------------
SYSTEM          ONLINE
SYSAUX          ONLINE
UNDOTBS1        ONLINE
TEMP            ONLINE
USERS           ONLINE
EXAMPLE         ONLINE
TEST            ONLINE
VPX             ONLINE
T2              ONLINE

9 rows selected.

10、修改数据文件名称
步骤
1）查看现有文件位置
2）offline数据文件或者表空间
3）复制到新的名称
4）ALTER DATABASE RENAME FILE 'OLD' TO 'NEW';
5）ONLINE
6）查看DBA_DATA_FILES验证
方法一、表空间离线（非归档模式下的唯一办法）
SQL> ALTER TABLESPACE T2 OFFLINE;
Tablespace altered.
SQL> SELECT A.NAME,A.STATUS,B.TABLESPACE_NAME FROM V$DATAFILE A,DBA_DATA_FILES B WHERE A.NAME=B.FILE_NAME AND B.TABLESPACE_NAME='T2';
NAME                           STATUS               TABLESPACE_NAME
------------------------------ -------------------- ---------------
/u01/app/oracle/oradata/orcl/t OFFLINE              T2
1.dbf
SQL> host cp /u01/app/oracle/oradata/orcl/t1.dbf /u01/app/oracle/oradata/orcl/t2.dbf 

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/t1.dbf' to '/u01/app/oracle/oradata/orcl/t2.dbf';
Database altered.


SQL> ALTER TABLESPACE T2 ONLINE;
Tablespace altered.


SQL> SELECT A.NAME,A.STATUS,B.TABLESPACE_NAME FROM V$DATAFILE A,DBA_DATA_FILES B WHERE A.NAME=B.FILE_NAME AND B.TABLESPACE_NAME='T2';
NAME                           STATUS               TABLESPACE_NAME
------------------------------ -------------------- ---------------
/u01/app/oracle/oradata/orcl/t ONLINE               T2
2.dbf
SQL> 

方法2：将单个数据文件离线
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/t2.dbf' offline;
Database altered.

SQL> SELECT A.NAME,A.STATUS,B.TABLESPACE_NAME FROM V$DATAFILE A,DBA_DATA_FILES B WHERE A.NAME=B.FILE_NAME AND B.TABLESPACE_NAME='T2';
NAME                           STATUS               TABLESPACE_NAME
------------------------------ -------------------- ---------------
/u01/app/oracle/oradata/orcl/t RECOVER              T2
2.dbf
SQL> host cp /u01/app/oracle/oradata/orcl/t2.dbf /u01/app/oracle/oradata/orcl/t2_new.dbf 
SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/orcl/t2.dbf' to '/u01/app/oracle/oradata/orcl/t2_new.dbf';
Database altered.

SQL> 
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/t2_new.dbf' online;
alter database datafile '/u01/app/oracle/oradata/orcl/t2_new.dbf' online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/t2_new.dbf'

SQL> recover datafile '/u01/app/oracle/oradata/orcl/t2_new.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/t2_new.dbf' online;
Database altered.

SQL> SELECT A.NAME,A.STATUS,B.TABLESPACE_NAME FROM V$DATAFILE A,DBA_DATA_FILES B WHERE A.NAME=B.FILE_NAME AND B.TABLESPACE_NAME='T2';
NAME                           STATUS               TABLESPACE_NAME
------------------------------ -------------------- ---------------
/u01/app/oracle/oradata/orcl/t ONLINE               T2
2_new.dbf

11、删除数据文件
只有当数据文件中没有数据的时候，才可以删除数据文件，语法如下
ALTER TABLESPACE TABLESPACE_NAME DROP DATAFILE 'xxx';

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

转载于:http://blog.itpub.net/27056898/viewspace-2126471/