怎样更改oracle指定数据库文件位置

Oracle更改数据文件位置

方法一：offline表空间

1、offline表空间：alter tablespace tablespace_name offline；

2、复制数据文件到新的目录；

3、rename修改表空间，并修改控制文件；

4、online表空间；

1、offline表空间zerone

SQL>select name from v$datafile

NAME

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS.DBF

已选择7行。

SQL>alter tablespace zerone offline

表空间已更改。

2、复制数据文件到新的目录

复制数据文件C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01.DBF到C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01.DBF。

3、rename修改表空间数据文件为新的位置，并修改控制文件

SQL>alter tablespace zerone rename datafile 'c:\oracle\product\10.2.0\oradata\zerone01.dbf' to 'c:\oracle\product\10.2.0\oradata\orcl\zerone01.dbf'

表空间已更改。

4、online表空间

SQL>alter tablespace zerone online

表空间已更改。

SQL>select name from v$datafile

NAME

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS.DBF

已选择7行。

SQL>select file_name,tablespace_name from dba_data_files where tablespace_name='ZERONE'

FILE_NAME TABLESPACE_NAME

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

ZERONE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01.DBF

方法二：SQL修改数据文件位置

1、关闭数据库；

2、复制数据文件到新的位置；

3、启动数据库到mount状态；

4、通过SQL修改数据文件位置；

5、打开数据库；

1、关闭数据库

SQL>select name from v$datafile

NAME

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS.DBF

已选择7行。

SQL>shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

2、复制数据文件到新的位置；

将数据文件C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01.DBF复制到C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01.DBF。

3、启动数据库到mount状态；

SQL>startup mount

ORACLE 例程已经启动。

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 96470372 bytes

Database Buffers 67108864 bytes

Redo Buffers 2945024 bytes

数据库装载完毕。

4、通过SQL修改数据文件位置；

SQL>alter database rename file 'c:\oracle\product\10.2.0\oradata\orcl\zerone01.dbf' to 'c:\oracle\product\10.2.0\oradata\zerone01.dbf'

数据库已更改。

5、打开数据库；

SQL>alter database open

数据库已更改。

SQL>select name from v$datafile

NAME

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS.DBF

已选择7行。

SQL>select file_name,tablespace_name from dba_data_files where tablespace_name='ZERONE'

FILE_NAME TABLESPACE_NAME

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

ZERONE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01.DBF

您好，很高兴为您解答。

源数据库数据文件位置：/u01/oradata/orcl

实验中想把数据文件迁移到的位置：/u01/oradata/orcl_test

源数据库数据文件位置：

sys@ORCL>select file_name from dba_data_files

FILE_NAME

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

/u01/oradata/orcl/system01.dbf

/u01/oradata/orcl/undotbs01.dbf

/u01/oradata/orcl/sysaux01.dbf

/u01/oradata/orcl/users01.dbf

sys@ORCL>select file_name from dba_temp_files

FILE_NAME

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

/u01/oradata/orcl/temp01.dbf

sys@ORCL>col member for a30

sys@ORCL>select member from v$logfile

MEMBER

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

/u01/oradata/orcl/redo01.log

/u01/oradata/orcl/redo02.log

/u01/oradata/orcl/redo03.log

需要移动所有的数据文件，采用alter database 方法

1、关闭数据库

sys@ORCL>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2、移动数据文件到/u01/oradata/orcl_test目录

[oracle@ora10gserv orcl]$ pwd

/u01/oradata/orcl

[oracle@ora10gserv orcl]$ mv * /u01/oradata/orcl_test/

[oracle@ora10gserv orcl]$ ls

[oracle@ora10gserv orcl]$ cd /u01/oradata/orcl_test/

[oracle@ora10gserv orcl_test]$ ls

control01.ctl control03.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf

control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

3、修改控制文件位置

[oracle@ora10gserv /]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 00:05:27 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>conn /as sysdba

Connected to an idle instance.

SQL>startup nomount

ORACLE instance started.

Total System Global Area 465567744 bytes

Fixed Size 1220024 bytes

Variable Size 146801224 bytes

Database Buffers 314572800 bytes

Redo Buffers2973696 bytes

SQL>create pfile='/u01/initorcl.ora' from spfile

File created.

SQL>host vi /u01/initorcl.ora

orcl.__db_cache_size=314572800

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=138412032

orcl.__streams_pool_size=0

*.audit_file_dest='/dba/admin/orcl/adump'

*.background_dump_dest='/dba/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_file_record_keep_time=14

*.control_files='/u01/oradata/orcl_test/control01.ctl','/u01/oradata/orcl_test/control02.ctl','/u01/oradata/orcl_test/control03.ctl'

*.core_dump_dest='/dba/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain='lsf.com.cn'

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/dba/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='location=/u01/arch_orcl'

*.open_cursors=300

*.pga_aggregate_target=154140672

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=2000

*.sga_target=463470592

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/dba/admin/orcl/udump'

SQL>shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>create spfile from pfile='/u01/initorcl.ora'

File created.

4、启动数据库到mount状态，更改数据文件位置

idle>startup mount

ORACLE instance started.

Total System Global Area 465567744 bytes

Fixed Size 1220024 bytes

Variable Size 146801224 bytes

Database Buffers 314572800 bytes

Redo Buffers2973696 bytes

Database mounted.

idle>alter database rename file '/u01/oradata/orcl/system01.dbf' to '/u01/oradata/orcl_test/system01.dbf'

Database altered.

idle>alter database rename file '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/orcl_test/undotbs01.dbf'

Database altered.

idle>alter database rename file '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/orcl_test/sysaux01.dbf'

Database altered.

idle>alter database rename file '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/orcl_test/users01.dbf'

Database altered.

5、继续接着第4步，更改联机日志文件位置

idle>alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/orcl_test/redo01.log'

Database altered.

idle>alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/orcl_test/redo02.log'

Database altered.

idle>alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/orcl_test/redo03.log'

Database altered.

6、打开数据库

idle>alter database open

Database altered.

idle>conn /as sysdba

Connected.

sys@ORCL>select file_name from dba_data_files

FILE_NAME

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

/u01/oradata/orcl_test/system01.dbf

/u01/oradata/orcl_test/undotbs01.dbf

/u01/oradata/orcl_test/sysaux01.dbf

/u01/oradata/orcl_test/users01.dbf

sys@ORCL>select file_name from dba_temp_files

FILE_NAME

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

/u01/oradata/orcl/temp01.dbf

sys@ORCL>col member for a40

sys@ORCL>select member from v$logfile

MEMBER

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

/u01/oradata/orcl_test/redo01.log

/u01/oradata/orcl_test/redo02.log

/u01/oradata/orcl_test/redo03.log

sys@ORCL>col name for a50

sys@ORCL>select name from v$controlfile

NAME

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

/u01/oradata/orcl_test/control01.ctl

/u01/oradata/orcl_test/control02.ctl

/u01/oradata/orcl_test/control03.ctl

7、临时文件的处理

由于控制文件不记录临时文件的信息，所以不能使用alter database rename file 命令完成，只能删除掉原来的再创建一个

sys@ORCL>create temporary tablespace temp02 tempfile '/u01/oradata/orcl_test/temp02.dbf' size 200m autoextend on extent management local uniform size 1m

Tablespace created.

sys@ORCL>alter database default temporary tablespace temp02

Database altered.

sys@ORCL>drop tablespace temp including contents and datafiles

Tablespace dropped.

sys@ORCL>select file_name from dba_temp_files

FILE_NAME

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

/u01/oradata/orcl_test/temp02.dbf

[oracle@ora10gserv orcl_test]$ ls -l

total 1427572

-rw-r----- 1 oracle oinstall 7061504 Aug 2 00:20 control01.ctl

-rw-r----- 1 oracle oinstall 7061504 Aug 2 00:20 control02.ctl

-rw-r----- 1 oracle oinstall 7061504 Aug 2 00:20 control03.ctl

-rw-r----- 1 oracle oinstall 52429312 Aug 2 00:20 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Aug 2 00:15 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Aug 2 00:15 redo03.log

-rw-r----- 1 oracle oinstall 251666432 Aug 2 00:20 sysaux01.dbf

-rw-r----- 1 oracle oinstall 503324672 Aug 2 00:20 system01.dbf

-rw-r----- 1 oracle oinstall 209723392 Jul 21 22:00 temp01.dbf

-rw-r----- 1 oracle oinstall 209723392 Aug 2 00:20 temp02.dbf

-rw-r----- 1 oracle oinstall 419438592 Aug 2 00:20 undotbs01.dbf

-rw-r----- 1 oracle oinstall 104865792 Aug 2 00:15 users01.dbf

[oracle@ora10gserv orcl_test]$ rm -rf temp01.dbf

sys@ORCL>select username,temporary_tablespace from dba_users

USERNAME TEMPORARY_TABLESPACE

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

MGMT_VIEW TEMP02

SYSTEMP02

SYSTEM TEMP02

DBSNMP TEMP02

SYSMAN TEMP02

OUTLN TEMP02

MDSYS TEMP02

ORDSYS TEMP02

CTXSYS TEMP02

ANONYMOUS TEMP02

EXFSYS TEMP02

DMSYS TEMP02

WMSYS TEMP02

XDBTEMP02

ORDPLUGINS TEMP02

SI_INFORMTN_SCHEMA TEMP02

OLAPSYSTEMP02

MDDATA TEMP02

DIPTEMP02

TSMSYS TEMP02

20 rows selected.

如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】

希望我的回答对您有所帮助，望采纳！

~ O(∩_∩)O~

---