ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 某Q友添加sysaux表空间tablespace数据文件datafile路径写错如何处理

某Q友添加sysaux表空间tablespace数据文件datafile路径写错如何处理

原创 Oracle 作者：wisdomone1 时间：2015-09-02 18:53:53 0 删除 编辑

结论

1,是否ORACLE自建表空间的数据文件皆可通过alter tablespace drop datafile 命令删除

2,关于删除表空间数据文件的操作限制，见下述具体测试部分

3,如果执意想删除或迁移表空间第2个数据文件或非第1个数据文件，可以通过dbms_rowid.relative_fno把表相关记录暂存到中间表，然后迁移到对应表空间

4,即使通过上述操作删除了表空间第2个数据文件，必须通过alter table move重组，方可使用alter tablespace drop datafile 命令删除
   表空间的数据文件，否则表分配相关区的底层数据字典信息仍保留在表空间第2个数据文件

5,我这里讨论仅测试适用于存储表对象，如果存储其它的非表对象，就要具体分析具体处理了

引言

加表空间的时候写错路径了




 某Q友想删除这个添加错误的SYSAUX表空间的数据文件，

测试


1,数据库版本
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL>
2，sysaux表空间相关信息
SQL> select ts$.ts#,ts$.name,v$datafile.name from v$datafile,ts$ where ts$.ts#=v$datafile.ts# and ts$.ts#=1 order by 1;

TS# NAME NAME
---------- -------------------------------------------------- --------------------------------------------------
1 SYSAUX /oracle/oradata/guowang/sysaux01.dbf
1 SYSAUX /oracle/oradata/guowang/sysaux02.dbf
1 SYSAUX /oracle/oradata/guowang/eoPg.dbf

3,离线直接删除sysaux表空间某个数据文件
SQL> alter database datafile '/oracle/oradata/guowang/eoPg.dbf' offline for drop;

Database altered.

4，同时监控告警日志，发现也记录下上述操作信息
Wed Sep 02 05:18:13 2015
alter database datafile '/oracle/oradata/guowang/eoPg.dbf' offline for drop
Completed: alter database datafile '/oracle/oradata/guowang/eoPg.dbf' offline for drop

5，好像删除的数据文件并未成功删除
SQL> select ts$.ts#,ts$.name,v$datafile.name from v$datafile,ts$ where ts$.ts#=v$datafile.ts# and ts$.ts#=1 order by 1;

TS# NAME NAME
---------- -------------------------------------------------- --------------------------------------------------
1 SYSAUX /oracle/oradata/guowang/sysaux01.dbf
1 SYSAUX /oracle/oradata/guowang/sysaux02.dbf
1 SYSAUX /oracle/oradata/guowang/eoPg.dbf

SQL> host ls -lh /oracle/oradata/guowang/eoPg.dbf
-rw-r----- 1 oracle oinstall 11M Sep 2 04:57 /oracle/oradata/guowang/eoPg.dbf

6，会不会因为SYSAUX是系统自创建的表空间，所以删除不了哟，创建一个用户定义的表空间，对比一下
SQL> create tablespace nonoracle datafile '/oracle/oradata/guowang/nonoracle1.dbf' size 10m;

Tablespace created.

SQL> alter tablespace nonoracle add datafile '/oracle/oradata/guowang/nonoracle2.dbf' size 10m;

Tablespace altered.

SQL> select ts$.ts#,ts$.name,v$datafile.name from v$datafile,ts$ where ts$.ts#=v$datafile.ts# and ts$.ts#=16 order by 1;

TS# NAME NAME
---------- -------------------------------------------------- --------------------------------------------------
16 NONORACLE /oracle/oradata/guowang/nonoracle1.dbf
16 NONORACLE /oracle/oradata/guowang/nonoracle2.dbf

7,看来和是否ORACLE自建表空间无关，alter offline drop语句确实不能删除表空间中的数据文件
SQL> alter database datafile '/oracle/oradata/guowang/nonoracle2.dbf' offline drop;

Database altered.

SQL> select ts$.ts#,ts$.name,v$datafile.name from v$datafile,ts$ where ts$.ts#=v$datafile.ts# and ts$.ts#=16 order by 1;

TS# NAME NAME
---------- -------------------------------------------------- --------------------------------------------------
16 NONORACLE /oracle/oradata/guowang/nonoracle1.dbf
16 NONORACLE /oracle/oradata/guowang/nonoracle2.dbf

8,我们换一个思路换另一个删除数据文件的语句

 如下为删除表空间数据文件的一些操作限制：

9，这个报错是因为上述命令把数据文件offline
SQL> alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf';
alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace

10,恢复离线数据文件
SQL> alter database datafile '/oracle/oradata/guowang/nonoracle2.dbf' online;
alter database datafile '/oracle/oradata/guowang/nonoracle2.dbf' online
*
ERROR at line 1:
ORA-01113: file 18 needs media recovery
ORA-01110: data file 18: '/oracle/oradata/guowang/nonoracle2.dbf'


SQL> recover datafile 18;
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/guowang/nonoracle2.dbf' online;

Database altered.

11，再次删除表空间数据文件
SQL> alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf';

Tablespace altered.

SQL> select ts$.ts#,ts$.name,v$datafile.name from v$datafile,ts$ where ts$.ts#=v$datafile.ts# and ts$.ts#=16 order by 1;

TS# NAME NAME
---------- -------------------------------------------------- --------------------------------------------------
16 NONORACLE /oracle/oradata/guowang/nonoracle1.dbf

SQL> alter tablespace sysaux drop datafile '/oracle/oradata/guowang/eoPg.dbf';
alter tablespace sysaux drop datafile '/oracle/oradata/guowang/eoPg.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace


12，删除sysaux表空间数据文件，成功了呵呵
SQL> alter database datafile '/oracle/oradata/guowang/eoPg.dbf' online;
alter database datafile '/oracle/oradata/guowang/eoPg.dbf' online
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/oracle/oradata/guowang/eoPg.dbf'


SQL> recover datafile 13;
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/guowang/eoPg.dbf' online;

Database altered.

SQL> alter tablespace sysaux drop datafile '/oracle/oradata/guowang/eoPg.dbf';

Tablespace altered.

13，继续学习，从上述删除表空间数据文件的操作限制可知，如果数据文件已有数据时，就不能删除了
SQL> alter tablespace nonoracle add datafile '/oracle/oradata/guowang/nonoracle2.dbf' size 10m;

Tablespace altered.

SQL> select ts$.ts#,ts$.name,v$datafile.name from v$datafile,ts$ where ts$.ts#=v$datafile.ts# and ts$.ts#=16 order by 1;

TS# NAME NAME
---------- -------------------------------------------------- --------------------------------------------------
16 NONORACLE /oracle/oradata/guowang/nonoracle2.dbf
16 NONORACLE /oracle/oradata/guowang/nonoracle1.dbf

14,创建测试表使用上述的测试表空间
SQL> create table zxy_table(a int,b int) tablespace nonoracle;

Table created.

15，测试表使用测试表空间中的哪些数据文件，目前仅使用第1个数据文件
SQL> select segment_name,segment_type,tablespace_name,file_id,v$datafile.name,extent_id from dba_extents,v$datafile where dba_extents.file_id=v$datafile.file# and segment_name='ZXY_TABLE';


SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 0


16，插入数据到测试表，直至使用测试表空间第2个数据文件

SQL> insert into zxy_table select level,level from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

17,这下测试表的数据横跨测试表空间2个数据文件
SQL> select segment_name,segment_type,tablespace_name,file_id,v$datafile.name,extent_id from dba_extents,v$datafile where dba_extents.file_id=v$datafile.file# and segment_name='ZXY_TABLE';


SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 0
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 0


SQL> select segment_name,segment_type,tablespace_name,file_id,v$datafile.name,extent_id from dba_extents,v$datafile where dba_extents.file_id=v$datafile.file# and segment_name='ZXY_TABLE';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 24
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 22
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 20
中间略
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 0
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 7

33 rows selected.

SQL>

17，尝试删除测试表空间第2个数据文件,直接报数据文件不为空，不能删除

SQL> alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf';
alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

18,能不能把隶属于第2上数据文件的表数据移动到测试表空间的第1个数据文件或其它数据文件

我准备把测试表隶属第2个数据文件的表数据迁移到第1个数据文件，所以开启第1个数据文件的自动扩展
SQL> alter database datafile 17 autoextend on;

Database altered.

19，迁移测试表隶属第2个数据文件的表数据先中转到一个中间临时表
SQL> create table t_mid_trans tablespace tbs_test as select a,b from zxy_table where dbms_rowid.rowid_relative_fno(rowid)=13;

Table created.

SQL> select count(*) from t_mid_trans;

COUNT(*)
----------
487914

20，删除测试表隶属第2个数据文件的表记录
SQL> delete from zxy_table where dbms_rowid.rowid_relative_fno(rowid)=13;

487914 rows deleted.

SQL> commit;

Commit complete.

21,删除测试表空间第2个数据文件,说明第2个数据文件还有内容
SQL> alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf';
alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

22，果然第2个数据文件仍有内容
SQL> select segment_name,segment_type,tablespace_name,file_id,v$datafile.name,extent_id from dba_extents,v$datafile where dba_extents.file_id=v$datafile.file# and segment_name='ZXY_TABLE';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 24
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 22
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 20
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 18
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 26
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 32
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 30
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 28
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 16
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 9
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 10

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 11
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 12
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 13
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 14
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 15
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 17
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 19
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 21
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 23
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 25
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 27

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 29
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 8
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 31
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 6
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 5
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 4
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 3
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 2
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 1
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 0
ZXY_TABLE TABLE NONORACLE 17 /oracle/oradata/guowang/nonoracle1.dbf 7

33 rows selected.

SQL>

23，我们分析下第2个数据文件到底存储什么内容
SQL> select segment_name,segment_type,tablespace_name,file_id,v$datafile.name,extent_id from dba_extents,v$datafile where dba_extents.file_id=v$datafile.file# and segment_name='ZXY_TABLE' and v$datafile.file#=13;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_ID NAME EXTENT_ID
--------------- ------------------ -------------------- ---------- -------------------------------------------------- ----------
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 16
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 18
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 20
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 22
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 24
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 26
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 28
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 30
ZXY_TABLE TABLE NONORACLE 13 /oracle/oradata/guowang/nonoracle2.dbf 32

9 rows selected.
可见隶属第2个数据文件的表记录确实已删除了，只是底层与区分配的字典管理的信息仍保留在第2个数据文件中
SQL> select a,b from zxy_table where dbms_rowid.rowid_relative_fno(rowid)=13;

no rows selected

24,重组表后，与区分配的字典管理的信息仍保留在第2个数据文件已经清除了
SQL> alter table zxy_table move;

Table altered.

SQL> select segment_name,segment_type,tablespace_name,file_id,v$datafile.name,extent_id from dba_extents,v$datafile where dba_extents.file_id=v$datafile.file# and segment_name='ZXY_TABLE' and v$datafile.file#=13;

no rows selected

25，删除第2个测试表空间的数据文件，这个成功了
SQL> alter tablespace nonoracle drop datafile '/oracle/oradata/guowang/nonoracle2.dbf';

Tablespace altered.

26，这里仅讨论了一个测试表，如果第2个数据文件里面存储了多个表或其它对象，这里处理就更为复杂了，要针对情况具体分析


最理智聪明的方法

SQL> create tablespace t_err datafile '/oracle/oradata/t_err1.dbf' size 10m autoextend off;


Tablespace created.


SQL> select tablespace_name,file_name from dba_data_files;


TABLESPACE_NAME                                    FILE_NAME
-------------------------------------------------- --------------------------------------------------
USERS                                              /oracle/oradata/guowang/users01.dbf
TBS_UNIFORM                                        /oracle/oradata/guowang/uniform1.dbf
SYSAUX                                             /oracle/oradata/guowang/sysaux01.dbf
SYSTEM                                             /oracle/oradata/guowang/system01.dbf
NEW_UNDOTBS                                        /oracle/oradata/guowang/new_undo1.dbf
T_ERR                                              /oracle/oradata/t_err1.dbf


6 rows selected.


SQL> alter tablespace t_err offline;


Tablespace altered.


SQL> alter database rename file '/oracle/oradata/t_err1.dbf'  to '/oracle/oradata/guowang/t_err1.dbf';
alter database rename file '/oracle/oradata/t_err1.dbf'  to '/oracle/oradata/guowang/t_err1.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file '/oracle/oradata/guowang/t_err1.dbf' not found
ORA-01110: data file 6: '/oracle/oradata/t_err1.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3




SQL> host mv /oracle/oradata/t_err1.dbf /oracle/oradata/guowang


SQL> alter database rename file '/oracle/oradata/t_err1.dbf'  to '/oracle/oradata/guowang/t_err1.dbf';


Database altered.


SQL> alter tablespace t_err online;


Tablespace altered.


SQL> select tablespace_name,file_name from dba_data_files;


TABLESPACE_NAME                                    FILE_NAME
-------------------------------------------------- --------------------------------------------------
USERS                                              /oracle/oradata/guowang/users01.dbf
TBS_UNIFORM                                        /oracle/oradata/guowang/uniform1.dbf
SYSAUX                                             /oracle/oradata/guowang/sysaux01.dbf
SYSTEM                                             /oracle/oradata/guowang/system01.dbf
NEW_UNDOTBS                                        /oracle/oradata/guowang/new_undo1.dbf
T_ERR                                              /oracle/oradata/guowang/t_err1.dbf


6 rows selected.


个人简介


8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院     
河北廊坊新奥集团公司

 项目经验:
中国电信3G项目AAA系统数据库部署及优化
      中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg 
       贵州移动crm及客服数据库性能优化项目
       贵州移动crm及客服务数据库sql审核项目
       深圳穆迪软件有限公司数据库性能优化项目

联系方式：
手机：18201115468
qq   :   305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900    
itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/

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

0
0
上一篇： 分析某客户数据库10.2.0.5监听日志listener log运行是否正常
下一篇： 在redhat5.4测试ncurse.h到底隶属于哪个rpm软件介质包
请登录后发表评论 登录
全部评论
wisdomone1
提供针对oracle初学者及进阶的数据库培训，欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历： 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间：2008-04-04

  • 博文量
    2189
  • 访问量
    12689910

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593