表空间数据文件迁移（一）

文件系统快慢了，需要将表空间对应的某个文件系统迁移到其他的文件系统，故做了如下测试实验：

实验环境：windows7+Oracle 10.2.0.1单实例（非ASM环境）

---用此方法，要求此数据文件既不属于SYSTEM表空间，也不属于含有ACTIVE回滚段或临时段的表空间。

测试过程如下：

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

SQL>
SQL> create tablespace zaki datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\zaki01.dbf' size 1G;

表空间已创建。

SQL> create user zaki identified by zaki default tablespace zaki;

用户已创建。

SQL> grant dba to zaki;

授权成功。

SQL> desc dba_free_space
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> set linesize 1200
SQL>
SQL> l
  1* select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name
SQL> /

TABLESPACE_NAME                                              SUM(BYTES)/1024/1024
------------------------------------------------------------ --------------------
UNDOTBS1                                                                   5.0625
SYSAUX                                                                     3.1875
USERS                                                                         4.5
ZAKI                                                                    1023.9375
SYSTEM                                                                    10.0625

SQL>
SQL> conn zaki/zaki
已连接。
SQL>

SQL> select count(*) from test;

  COUNT(*)
----------
  33554432

已用时间:  00: 00: 23.32
SQL>
SQL>
SQL> select count(*) from test;

  COUNT(*)
----------
  33554432

已用时间:  00: 00: 02.08
SQL>
SQL> select count(1) from test;

  COUNT(1)
----------
  33554432

已用时间:  00: 03: 49.35
SQL>
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME                                              SUM(BYTES)/1024/1024
------------------------------------------------------------ --------------------
UNDOTBS1                                                                   1.6875
SYSAUX                                                                      3.125
USERS                                                                         4.5
ZAKI                                                                     623.9375
SYSTEM                                                                    10.0625

SQL> alter tablespace zaki offline;

表空间已更改。

已用时间:  00: 00: 01.39
SQL>
SQL> --在操作系统中将文件转移至需要的新地址
SQL> alter tablespace zaki rename datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZAKI01.DBF' to 'D:\ORACLE\PRODUCT\10.2
.0\ORADATA\ZAKI01.DBF';

表空间已更改。

已用时间:  00: 00: 00.07
SQL>
SQL> alter tablespace zaki online;

表空间已更改。

已用时间:  00: 00: 00.24
SQL>
SQL> select name from v$datafile;

NAME
------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ZAKI01.DBF

已用时间:  00: 00: 00.12
SQL>