Question: I've got a corrupted data file, and Oracle won't start. I don't need the data so how do I open the database and drop data file?
==================================================================
Ans:
If you are running in Noarchivelog mode1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline drop
3. open the database - alter database open
4. check all objects belong to that tablespace:
select owner, segment_name, segment_type
from dba_segments
where tablespace_name='tbs_name'
5. export out all the objects in that tablespace
6. drop the tablespace - drop tablespace tbs_name including contents
7. Delete the physical datafiles belonging to the tablespace
8. Recreate the tablespace, import back the objects
If you are running in Archivelog mode1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline(Note: the datafile is still part of the database and is ed only as offline in the controlfile. Just make sure you don't use the same data file name again)3. Remove the physical data file at OS level
4. open the database - alter database open
5. At the right time, you can
export the objects belong to this tablespace,
drop the tablespace,
create back the tablespace with appropriate datafiles and
import the objects back.
==================================================================
Ans:
If you are running in Noarchivelog mode1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline drop
3. open the database - alter database open
4. check all objects belong to that tablespace:
select owner, segment_name, segment_type
from dba_segments
where tablespace_name='tbs_name'
5. export out all the objects in that tablespace
6. drop the tablespace - drop tablespace tbs_name including contents
7. Delete the physical datafiles belonging to the tablespace
8. Recreate the tablespace, import back the objects
If you are running in Archivelog mode1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline(Note: the datafile is still part of the database and is ed only as offline in the controlfile. Just make sure you don't use the same data file name again)3. Remove the physical data file at OS level
4. open the database - alter database open
5. At the right time, you can
export the objects belong to this tablespace,
drop the tablespace,
create back the tablespace with appropriate datafiles and
import the objects back.
解决方案 »
- SQL如何查找以字符"["开头的字符串呢
- 求一个更新日期格式的语句,在线等,多谢了。
- 邹大侠请进,关于sql 2000里面的全文目录
- 在线急等~ 18456,严重性: 14,状态: 16!SQL不停的报错
- dawugui ,我一个表有100个字段,全部用ntext不会出错吧,浪费空间么
- 菜鸟请教。。。。。。
- 求SQL:从Table1中选出批号不在Table2 中的数据
- 如何自动创建试图或更新 ASP .net (C#) & SQL server
- 要开发一个档案管理(MIS)或连锁店经营管理程序,用PB好还是用ASP、PHP、JAVA???
- 求~~增删改查的代码 要详细 有注备!!
- 诚聘程序员
- 共享数据库?
先用ORACLE命令逻辑删除,再用OS命令物理删除
2 ALTER TABLESPACE tablespace1 OFFLINE;
DROP TABLESPACE tablespace1 INCLUDING CONTENTS CASCADE CONSTRAINTS
假设你的表空间为tablespace1,往两个裸设备上写数据'dev/dev1' 和'dev/dev2',删除'dev/dev1' 想上面那样可以,但要重建表空间,这个方法主要使用于某一裸设备硬盘故障下。 表空间重包含多个数据文件,不同的数据文件建在不同的裸设备上时,删除一个datafile就比较危险,因为offline后会被recovery,所以再这种情况下,offline drop只能顶一阵子。
1、export数据;
2、删除表空间+删除数据文件(OS)
3、重建表空间;
4、import数据;
5、all over!