ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How do you rename a datafile in Oracle?

How do you rename a datafile in Oracle?

原创 Linux操作系统 作者:todayboy 时间:2012-03-05 09:36:57 0 删除 编辑

  在一个测试库上向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';

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

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';

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/barcode05.dbf                             OFFLINE
6 rows selected.

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';

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/barcode05.dbf                             ONLINE

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-24

  • 博文量
    75
  • 访问量
    242661