疯狂

STANDING ON THE SHOULDERS OF GIANTS
posts - 481, comments - 486, trackbacks - 0, articles - 1
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

Oracle 更改表空间位置的具体步骤

Posted on 2011-05-17 10:13 疯狂 阅读(1725) 评论(0)  编辑  收藏 所属分类: database

[oracle]> sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 03:46:29 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1977208 bytes
Variable Size 104862856 bytes
Database Buffers 58720256 bytes
Redo Buffers 2211840 bytes
Database mounted.
Database opened.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> host mv /opt/oracle/oradata/ora10g/users01.dbf /opt/oracle/oradata_bak/
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle]> ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle]> mv /opt/oracle/oradata/ora10g/users01.dbf /opt/oracle/oradata_bak/
[oracle]> sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 03:52:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter tablespace users
2 rename datafile ‘/opt/oracle/oradata/ora10g/users01.dbf’ to ‘/opt/oracle/oradata_bak/users01.dbf’;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
见效果
=============================================
RMAN> report schema
2> ;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM *** /opt/oracle/oradata/ora10g/system01.dbf
2 30 UNDOTBS1 *** /opt/oracle/oradata/ora10g/undotbs01.dbf
3 240 SYSAUX *** /opt/oracle/oradata/ora10g/sysaux01.dbf
4 5 USERS *** /opt/oracle/oradata_bak/users01.dbf
5 100 EXAMPLE *** /opt/oracle/oradata/ora10g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 22 TEMP 32767 /opt/oracle/oradata/ora10g/temp01.dbf
恢复回去…
[oracle]> pwd
/opt/oracle/oradata/ora10g
[oracle]> mv /opt/oracle/oradata_bak/users01.dbf .
[oracle]> ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle]> sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 04:07:47 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter tablespace users rename datafile ‘/opt/oracle/oradata_bak/users01.dbf’ to ‘/opt/oracle/oradata/ora10g/users01.dbf’;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
效果如下
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM *** /opt/oracle/oradata/ora10g/system01.dbf
2 30 UNDOTBS1 *** /opt/oracle/oradata/ora10g/undotbs01.dbf
3 240 SYSAUX *** /opt/oracle/oradata/ora10g/sysaux01.dbf
4 5 USERS *** /opt/oracle/oradata/ora10g/users01.dbf
5 100 EXAMPLE *** /opt/oracle/oradata/ora10g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 22 TEMP 32767 /opt/oracle/oradata/ora10g/temp01.dbf

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/star33375249/archive/2009/10/26/4728635.aspx


只有注册用户登录后才能发表评论。


网站导航: