原文地址为：
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删除数据文件

ClickHouse提供了DELETE，但是不能和传统的Mysql这类数据库的相比。因为
 
这种操作被称为Mutation查询
Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除
不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚
Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回，即删除语句执行完不代表数据删除，会有一个后台进程去删除数据操作，具体数据什么时候删除呢，可以在系统表 system.mutations查看
 
 
其中 is_done = 1代表删除完成
 
mutations表官方解释
 
表数据删除又分物理表和分布式表(Dictionary)
 
分布式表不支持删除，需要直接去删除物理表
 
ALTER TABLE sales_db.order_detail ON CLUSTER default_cluster DELETE WHERE customer_id in (11053,
            11064,
            11057,
            10154,
            10987);
 
 
 
说明 sales_db.order_detail 物理表名称
 
 
ON CLUSTER default_cluster 代表执行分布式DDL语句
 
 
WHERE 后面跟删除条件
 
 
关于我
 
​ 觉得文章不错请扫码关注我吧
 
​ 

1。select * from znjtresource.t_device_epolice as of timestamp to_timestamp('2019-3-21 15:20:00','yyyy-mm-dd hh24:mi:ss')
 2,。insert into znjtresource.t_device_epolice (select * from znjtresource.t_device_epolice as of timestamp to_timestamp('2019-3-21 15:20:00','yyyy-mm-dd hh24:mi:ss'));
 
 
 
1.delete误删除的解决方法
 原理：
 
利用oracle提供的闪回方法，如果在删除数据后还没做大量的操作（只要保证被删除数据的块没被覆写），就可以利用闪回方式直接找回删除的数据
 具体步骤为：
 
*确定删除数据的时间（在删除数据之前的时间就行，不过最好是删除数据的时间点）
 
*用以下语句找出删除的数据：select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss')
 
*把删除的数据重新插入原表：
 
     insert into 表名 (select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss'));注意要保证主键不重复。
 
如果表结构没有发生改变，还可以直接使用闪回整个表的方式来恢复数据。
 
具体步骤为：
 
表闪回要求用户必须要有flash any table权限
 
 
 
 --开启行移动功能 
 
 ·alter table 表名 enable row movement
 
 --恢复表数据
  ·flashback table 表名 to timestamp to_timestamp(删除时间点','yyyy-mm-dd hh24:mi:ss')
 
 --关闭行移动功能 ( 千万别忘记 )
 
 ·alter table 表名 disable row movement
 

 2.drop误删除的解决方法
 
原理：由于oracle在删除表时，没有直接清空表所占的块,oracle把这些已删除的表的信息放到了一个虚拟容器“回收站”中，而只是对该表的数据块做了可以被覆写的标志，所以在块未被重新使用前还可以恢复。
 
具体步骤：
 
*查询这个“回收站”或者查询user_table视图来查找已被删除的表:
 
 · select table_name,dropped from user_tables
 
 · select object_name,original_name,type,droptime from user_recyclebin
 
在以上信息中，表名都是被重命名过的，字段table_name或者object_name就是删除后在回收站中的存放表名
 
*如果还能记住表名，则可以用下面语句直接恢复：
 
  flashback table 原表名 to before drop
 
 如果记不住了，也可以直接使用回收站的表名进行恢复，然后再重命名，参照以下语句：
 
  flashback table "回收站中的表名(如：Bin$DSbdfd4rdfdfdfegdfsf==$0)" to before drop rename to 新表名
 
oracle的闪回功能除了以上基本功能外，还可以闪回整个数据库：
 
使用数据库闪回功能，可以使数据库回到过去某一状态, 语法如下：
 
SQL>alter database flashback on
 SQL>flashback database to scn SCNNO;
 SQL>flashback database to timestamp to_timestamp('2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');
 

 总结：
 oracle提供以上机制保证了安全操作，但同时也代来了另外一个问题，就是空间占用，由于以上机制的运行，使用drop一个表或者delete数据后，空间不会自
 
动回收，对于一些确定不使用的表，删除时要同时回收空间，可以有以下2种方式：
 
  1、采用truncate方式进行截断。（但不能进行数据回恢复了）
 
  2、在drop时加上purge选项：drop table 表名 purge
 
     该选项还有以下用途：
 
  也可以通过删除recyclebin区域来永久性删除表 ,原始删除表drop table emp cascade constraints
    purge table emp;
    删除当前用户的回收站:
     purge recyclebin;
    删除全体用户在回收站的数据:
    purge dba_recyclebin

es 删除数据的三种方法
1  因为高版本的es并不支持批量删除，所以第一个方法思路，首先查询es 获取主键id,然后根据id逐个删除

def scrollScanDeleteByTopic(client:TransportClient,index:String,topic:String)= {
  var searchResponse = client.prepareSearch(index).setTypes("docs")
    .setQuery(QueryBuilders.boolQuery().must(QueryBuilders.matchQuery("topicName",topic)))
    .setSearchType(SearchType.DEFAULT)
    .addStoredField("id")
    .setSize(1000).setScroll(TimeValue.timeValueMinutes(5))
    .execute().actionGet()

  var num = searchResponse.getHits.getHits.length
  // 循环直到遍历所有数据
  //loop.breakable{
  while (num != 0) {
    println(" num " + num)
    val res = searchResponse.getHits.getHits
    res.foreach { x =>
     val response = client.prepareDelete(index, "docs", x.getId).execute().actionGet()
     // println(response)
    }
    println(s"========================= $index 删除成功 ${res.length}==${topic}  ==========")
    searchResponse = client.prepareSearchScroll(searchResponse.getScrollId)
      .setScroll(TimeValue.timeValueMinutes(5))
      .execute().actionGet()
    num = searchResponse.getHits.getHits.length
  } // }
}
2 发现逐个删除的效率有点低，在此基础上改进，查询之后采用多线程来删除
def scrollScanDeleteByTopic(client:TransportClient,index:String,topic:String)= {
  var searchResponse = client.prepareSearch(index).setTypes("docs")
    .setQuery(QueryBuilders.boolQuery().must(QueryBuilders.matchQuery("topicName",topic)))
    .setSearchType(SearchType.DEFAULT)
    .addStoredField("id")
    .setSize(1000).setScroll(TimeValue.timeValueMinutes(5))
    .execute().actionGet()

  var num = searchResponse.getHits.getHits.length
  // 循环直到遍历所有数据
  //loop.breakable{
  while (num != 0) {
    println(" num " + num)
    val res = searchResponse.getHits.getHits.map(_.getId)
     deleteDocuments(4,res)
    
    println(s"========================= $index 删除成功 ${res.length}==${topic}  ==========")
    searchResponse = client.prepareSearchScroll(searchResponse.getScrollId)
      .setScroll(TimeValue.timeValueMinutes(5))
      .execute().actionGet()
    num = searchResponse.getHits.getHits.length
  } // }
}

def deleteDocuments(num:Int,arr:Array[String])= {
  val step = if (arr.length % num == 0) arr.length / num else arr.length + 1
  for (i <- 0 until num) {
    new Thread(new Runnable {
      override def run(): Unit = {
        val client = Es_test.getDeleteClient()
        val beginNum = i * step
        val endNum = (i + 1) * step - 1
        for (j <- 0 until arr.length if (j >= beginNum && j <= endNum)) {
          client.prepareDelete("ods_wj_scenes_detail", "docs", arr(i)).execute().actionGet()
        }
        Es_test.close(client)
      }
    })
  }





3 网上发现一个插件elete-by-query，可以实现批量删除

"org.elasticsearch.plugin" % "delete-by-query" % "2.4.1" % Test

val queryBuilder =  QueryBuilders.boolQuery()
queryBuilder.must(QueryBuilders.matchAllQuery())
val start = new Date().getTime
val response = DeleteByQueryAction.INSTANCE.newRequestBuilder(client).filter(queryBuilder).source("ods_wj_scenes_detail").get()
val deleted = response.getDeleted
val end = new Date().getTime
println(s"=================$deleted=====${end-start}==============")

结果 方法一的效率比较低 方法二效率有提升 采用4个线程删除数据，76万数据耗时182883毫秒，方法三 74万数据 耗时51410毫秒

 可以说效率是逐渐提升的，方法三效率更高，其中也发现了一个问题，逐渐提高方法二的线程数，耗时并不会变少，瓶颈主要在查询上，经测量发现load 1万数据耗时2s左右，如果能提高查询效率，方法二的效率也会得到提升

还有 这个插件目前是测试版，和其他部分的兼容性并不是特别好，应用中遇到一个jar包问题，其依赖的jar包缺少一个关键的类ReflectUtil
log4j-slf4j-impl.2.8 ,解决的方法就是采用这个jar包的最新版本，我下的是2.9，完美解决这个问题