原文地址为： Oracle删除数据文件

在我们详细介绍之前，我们必须说清楚一点：Oracle不提供如删除表、视图一样删除数据文件的方法，数据文件是表空间的一部分，所以不能“移走”表空间。

 

一、使用offline数据文件的方法

非归档模式使用：alter database datafile '...' offline drop;

归档模式使用：  alter database datafile '...' offline;

说明：

1)         以上命令只是将该数据文件OFFLINE，而不是在数据库中删除数据文件。该数据文件的信息在控制文件种仍存在。查询v$datafile，仍显示该文件。

2)         归档模式下offline和offline drop效果是一样的

3)         offline后，存在此datafile上的对象将不能访问

4)         noarchivelog模式下，只要online redo日志没有被重写，可以对这个文件recover后进行online操作

 

实际使用案例：

直接删除数据文件后无法进入系统的解决方案

 

正常情况下，删除表空间的正确方法为：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

 

如果没有通过以上命令删除而直接删除了数据文件，将导致数据库无法打开。

 

如果直接删除了数据文件

普通用户登录时，则报错：

ORA-01033: ORACLE initialization or shutdown in progress

sys用户可以正常登录

但进行操作时（SELECT count(1) FROM user_tables），则会报错：

ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询

如果执行命令alter database open以打开数据库时，又报如下错：

ORA-01157: 无法标识/锁定数据文件 12 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 12: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSTEST001.DBF'

 

说明数据库没找到这个数据文件

因为数据文件在没有被offline的情况下物理删除了,导致oracle的数据不一致,因此启动失败.

通过以下方法即可解决

 

解决方法：

sqlplus sys/orcl@orcl as sysdba;

SQL> alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSTEST001.DBF' offline drop;

SQL> alter database open;

SQL> drop tablespace CTBASEDATA;

 

二、Oracle 10G R2开始，可以采用：Alter tablespace tablespace_name drop datafile file_name;来删除一个空数据文件，并且相应的数据字典信息也会清除:

 

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

  2  where tablespace_name='USERS';

FILE_ID  FILE_NAME                        TABLESPACE_NAME

-------    --------------------------------------------       ------------------

4        /u01/app/oracle/oradata/orcl/users01.dbf    USERS

 

sys@ORCL>alter tablespace users add datafile

  2  '/u01/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend off;

Tablespace altered.

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

  2  where tablespace_name='USERS';

FILE_ID  FILE_NAME                          TABLESPACE_NAME

-------    --------------------------------------------        -------------------

4        /u01/app/oracle/oradata/orcl/users01.dbf     USERS

9        /u01/app/oracle/oradata/orcl/users02.dbf     USERS

 

sys@ORCL>drop table test;

Table dropped.

sys@ORCL>create table test tablespace users

  2  as

  3  select * from dba_objects;

Table created.

sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

  2  where file_id=9;

SEGMENT_NAME                      FILE_ID     BLOCKS

------------------------------                  ----------      ----------

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9        128

TEST                                    9        128

 

17 rows selected.

sys@ORCL>alter table test move tablespace PERFSTAT; --把表移动到其它表空间

Table altered.

sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

  2  where file_id=9;

no rows selected

sys@ORCL>alter tablespace users drop datafile

  2  '/u01/app/oracle/oradata/orcl/users02.dbf';

Tablespace altered.

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

  2  where tablespace_name='USERS';

FILE_ID  FILE_NAME                       TABLESPACE_NAME

-------    --------------------------------------------      ---------------------

4        /u01/app/oracle/oradata/orcl/users01.dbf   USERS

 

三、oracle 10g可以删除临时表空间的文件

alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;

 

ALTER DATABASE 与 ALTER TABLESPACE OFFLINE的区别

一．DataFile脱机或联机的两种方法：

① ALTER DATABASE 语句修改单独的DataFile

② ALTER TABLESPACE 语句修改所有的DataFile

1、在ARCHIVRLOG模式下的更改DataFile状态

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

或者用文件号来表示 :

ALTER DATABASE DATAFILE 5 ONLINE;

ALTER DATABASE DATAFILE 5 OFFLINE;

注：只有在ARCHIVELOG模式下才可使用ALTER DATABASE来更改DataFile

 

2、在NOARCHIVELOG模式下使DataFile脱机

由于在NOARCHIVELOG模式下，数据文件脱机后会造成数据的遗失，所以只能使用ALTER DATABASE语句下带有DATAFILE和OFFLINE DROP子句的选项将该DataFile直接取消，例如该DataFile只包含临时段数据，并没有备份时

 

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users3.dbf' OFFLINE DROP;

3、修改TableSpace中所有DataFile或TempFile的可用性

ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}

ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}

 

注：修改某TableSpace中的所有数据文件，但是TableSpace本身的状态不改变。

总结：

① ALTER TABLESPACE可以在数据库装载状态时发布，无需打开

② 涉及到系统表空间、撤销表空间、默认临时表空间时，必须是未打开的数据库

③ ALTER DATABASE DATAFILE 语句中必须填入文件全名

 

二． 表空间 与 数据文件 脱机的区别

1. ALTER TABLESPACE ... OFFLINE

Does a checkpoint on the datafiles

Takes the datafiles offline

表空间Offline时，数据文件的SCN会被冻结，而且表空间的数据文件offline/online时又会发生文件检查点，使单个数据文件SCN和数据库其他问题不一致。

表空间online时，Oracle会取得当前SCN，解冻offline文件SCN，和当前SCN同步。

tablespace offline有几种选项可供选择normal, temporary,immediate, for recovery,而在datafile中则没有这些选项。

 

2.  ALTER DATABASE DATAFILE ... OFFLINE

单纯的offline datafile，将不会触发文件检查点，只有针对offline tablespace的时候才会触发文件检查点，这也是为什么online datafile需要media recovery而online tablespace不需要。

注：只有在ARCHIVELOG模式下才可使用ALTER DATABASE来更改DataFile

转载请注明本文地址： Oracle删除数据文件

记一次数据库删除数据非常慢的处理流程

问题描述：

单表数据3000条左右，根据主键删除需要509秒

处理流程

一. 首先查看SQL的执行计划：执行计划正常，cost只有1，用到了主键索引。
二. 查看等待事件：

SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid;

先执行目标sql，在执行上面的sql获得sid

select * from v$session_wait where sid = (上面sql查询的sid)

显示的event是db file sequential read,也没有异常。

三. 测试其他其他表和系统io是否有问题，没问题则进行下一步
四. 追踪sql日志：

alter session set events='10046 trace name context forever,level 12';

delete from t_table1 where id = xxx（这里是目标sql）

alter session set events='10046 trace name context off';

若不知道日志文件存放位置执行以下sql会返回路径

SELECT VALUE  FROM V$PARAMETER WHERE NAME = 'user_dump_dest' 

找到最新的trace文件
执行—>tkprof orcl_ora_3708.trc myoutput.txt将trc文件转换为简单明了的txt文件
查看执行删除过程中有哪个操作时间过长

我发现的问题是在实行删除的过程中这个表有外键关联主表，会先查一遍这个表里有没有那个设备的记录，elapsed表示耗时
解决办法，外键加索引，或者取消外键代码里维护外键。

感谢：https://www.cnblogs.com/smallrock/p/4129085.html 提供的思路

转载自：

https://www.cnblogs.com/gaoyuchuanIT/articles/4151231.html

 

https://blog.51cto.com/2681882/2133524

一 现象描述

Delete是oracle数据库中的常用操作，尤其是在自动化测试中，初始化环境、前置准备都不可避免的进行增删操作，但持续一时间后，可能会碰到表空间不足这类报错现象，这就不禁纳闷儿了，明明插入数据前会有删除的，数据总量并没有呈现明显的量级变化，为什么表占用空间却在偷偷增大呢？

 

二 现象分析

出现上述现象的原因是Delete操作并不会释放占用的空间。在讲解原因之前，先了解下oracle中高水位线的概念，有助于理解delete操作产生的这种现象。

所谓的高水位（HWM），通俗的讲就是一个标记，用来记录已经有多少数据块（Block）分配给表，可以拿水库的历史最高水位来类比，当使用delete操作后，数据虽然被删除了，但这个高水位的标记并没有降低，就好比水库的历史最高水位不会因为水被释放了而降低。因而，原则上在没有外部干预的条件下，这个高水位标记值只会增大，不会降低。

 

三 实战模拟重现现象

根据上面的现象描述和分析，接下来，我会用具体的实例模拟该现象，使大家可以更直观的了解。

第1，创建一张测试表test，具体字段不需要关心，只要知道初始了存储空间为100M，如图所示：

 

第2，创建完成后，我们查看下数据表占用的空间，如图所示：

 

 

其中，查询前需要对表进行分析，使用命令为：ANALYZE TABLE test ESTIMATE STATISTICS;查询语句为：SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST';

注意上面三个字段的结果：BLOCKS=0;  EMPTY_BLOCKS=13312;  NUM_ROWS=0，即当前表占用的块数为0，默认1 BLOCK = 8kb，预分配的块为13312，行数为0。

一切都没有问题，新创建的表，没有数据嘛，当然行数为0，占用块数为0喽。

 

第3，写一个语句块，循环插入1000条语句，再次对test表进行分析、查询，结果如下：

 

 

从图中可以看到，占用BLOCKS=222，NUM_ROWS=1000，合乎逻辑，插入了1000条数据，占用了空间嘛。

 

第4，使用Delete语句删除1000条数据，再次对test表进行分析、查询，结果却是如下：

 

从上图中可以清楚的看到，数据被删除后，NUM_ROWS=0了，但是BLOCKS并没有被置为0，也就是这部分数据块仍然被认为是占用的。

因此，就出现了本文一开始就提到的现象，随着不断的插入、删除数据，BLOCKS也会不断扩大，尽快delete操作后，可能表中数据量很少，但表占用的存储空间未减少。

 

四 解决方法

针对delete操作引起的空间不释放现象，或者，更正式一点的说法，如何降低高水位线，方法有很多种，如，shrink space；move tablespace；create table xxx as select * from xxx 重建表等。使用这些方法前，我们的原则是：

如果可以truncate，直接truncate，该操作会重置高水位线，BLOCKS会被置为0，NUM_ROWS置为0；否则，优先使用shrink space，该方法不需要重建索引。

接着上面第4步，我们使用shrink space降低高水位线，释放空间，其中，使用shrink space命令前，需要先alter table test enable row movement;开启行移动，再次对表进行分析、查询，结果如下：

 

 

从图中可以看出，此时BLOCKS已经被置为0了，但是，细心的你可能也发现， EMPTY_BLOCKS已经不是初始的13312，而是此时的40，这说明shrink space不仅会释放高水位线以下的空间，也会释放申请的空间，即高水位线上下都有操作，这也是与move、truncate的不同，它们只能释放高水位线以下的空间。

 

五 shrink space常用操作命令

Shrink space的常用命令如下：

alter table tablename enable row movement;--打开行移动

alter table tablename shrink space cascade;--压缩表及相关数据段，并下调HWM

alter table tablename shrink sapce compact;--只压缩，不下调HWM

alter table tablename shrink sapce ;--下调HWM

alter table tablename disable row movement;--关闭行移动

 

六 Delete操作的潜在影响

根据上述分析，delete操作产生的潜在影响如下：

1. 全表扫描通常要读出直到HWM标记的所有属于该表的数据块，即使该表中没有任何数据；（造成查询变慢）

2. 插入操作时使用append关键字，即使HWM以下有空闲的数据库块，插入时使用HWM以上的数据块；（造成HWM自动增大）

 

七 总结

通过上文的现象描述和分析，随着insert的不断操作，高水位线也随着不断增加，尽管delete了数据，但高水位线并没有下降，导致表占用的空间没有释放。因此，在实际应用中，如果可能，尽量使用truncate，而且该操作高效、快速；否则要考虑下delete操作遗留的影响，使用合适的方法整理空间