How do you rename a datafile in Oracle?

最新推荐文章于 2024-09-30 20:09:33 发布

citeng7919

最新推荐文章于 2024-09-30 20:09:33 发布

阅读量68

点赞数

文章标签：数据库

在一个测试库上向tablespace 中加datafile时，一时大意，把数据文件名写错了(barcode2010_02.dbf rename to barcode05.dbf) ，需要更改一下datafile 名称，记录一下：

SQL> select a.name,b.name,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# and a.name='BARCODE';

NAME                                                         NAME                                                         STATUS
------------------------------------------------------------ ------------------------------------------------------------ -------
BARCODE                                                      /u02/oradata/hanmi/barcode04.dbf                             ONLINE
BARCODE                                                      /u02/oradata/hanmi/barcode03.dbf                             ONLINE
BARCODE                                                      /u02/oradata/hanmi/barcode02.dbf                             ONLINE
BARCODE                                                      /u02/oradata/hanmi/barcode06.dbf                             ONLINE
BARCODE                                                      /u02/oradata/hanmi/barcode01.dbf                             ONLINE
BARCODE                                                      /u02/oradata/hanmi/barcode2010_02.dbf                        OFFLINE

SQL> alter tablespace barcode read only;

Tablespace altered.

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
BJHANMI                        ONLINE
GSP                            ONLINE
BARCODE                        READ ONLY
BARCODE2010                    ONLINE
BARCODE2012                    ONLINE

10 rows selected.

SQL> alter database datafile '/u02/oradata/hanmi/barcode2010_02.dbf' offline;

Database altered.

SQL> select a.name,b.name,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# and a.name='BARCODE';

cd /u02/oradata/hanmi

[oracle@oracle hanmi]$ ll
total 76733104
-rw------- 1 oracle oinstall 10791952384 Feb 1 09:54 barcode01.dbf
-rw------- 1 oracle oinstall 11001667584 Feb 1 09:54 barcode02.dbf
-rw------- 1 oracle oinstall 11001667584 Feb 1 09:54 barcode03.dbf
-rw------- 1 oracle oinstall 10791952384 Feb 1 09:54 barcode04.dbf
-rw------- 1 oracle oinstall 4294975488 Feb 1 09:54 barcode06.dbf
-rw------- 1 oracle oinstall 4294975488 Feb 1 09:36 barcode2010_01.dbf

mv /u02/oradata/hanmi/barcode2010_02.dbf barcode05.dbf

SQL> alter database rename file '/u02/oradata/hanmi/barcode2010_02.dbf' to '/u02/oradata/hanmi/barcode05.dbf' ;

Database altered.

SQL> select a.name,b.name,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# and a.name='BARCODE';

SQL> alter database datafile '/u02/oradata/hanmi/barcode05.dbf' online;

Database altered.

SQL> select a.name,b.name,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# and a.name='BARCODE';

6 rows selected.

SQL> alter tablespace barcode read write;

Tablespace altered.
SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
BJHANMI                        ONLINE
GSP                            ONLINE
BARCODE                        ONLINE
BARCODE2010                    ONLINE
BARCODE2012                    ONLIN

reference:http://wiki.answers.com/Q/How_do_you_rename_a_datafile_in_Oracle

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

转载于:http://blog.itpub.net/7583803/viewspace-717696/