oracle语句 trunc（）、to_char()、to_date()、add_months（）和误删恢复操作

整理的一些自己开发时遇到的oracle语句，有些是自己上网查找的。

oracle 误删数据库恢复方法：

（一）delete 误删数据

         原理：利用oracle提供的闪回方法，如果在删除数据后还没做大量的操作（只要保证被删除数据的块没被覆写），就可以利用闪回方式直接找回删除的数据

         *确定删除数据的时间（在删除数据之前的时间就行，不过最好是删除数据的时间点）
         *用以下语句找出删除的数据：select * from表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss')
         *把删除的数据重新插入原表：
         insert into 表名(select * from表名 asof timestamp to_timestamp('删除时间点','yyyy-mm-ddhh24:mi:ss'));注意要保证主键不重复。

如果表结构没有发生改变，还可以直接使用闪回整个表的方式来恢复数据。

         表闪回要求用户必须要有flash anytable权限
          ·alter table表名 enable row movement
          ·flashback table表名 to timestamp to_timestamp(删除时间点','yyyy-mm-dd hh24:mi:ss')

（二）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就是删除后在回收站中的存放表名

         flashbacktable "回收站中的表名(如：Bin$DSbdfd4rdfdfdfegdfsf==$0)" tobefore drop rename to 新表名

 flashbacktable原表名 to before drop

         使用数据库闪回功能，可以使数据库回到过去某一状态,语法如下：
SQL>alter database flashback on
SQL>flashback database to scn SCNNO;
SQL>flashback database to timestampto_timestamp('2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');

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

        --nvl（）判断num是否为空，若num为空，则选择0(0可任意换)

selectnvl(num,0)from table_name;

 

--trunc截取小数位数

selecttrunc(12.345,0)from table_name; --12

selecttrunc(12.345,1)from table_name; --12.3

selecttrunc(12.345,4)from table_name; --12.345

selecttrunc(12.345,-1)from table_name; --10

selecttrunc(12.345,-3)from table_name; --0

select trunc(12)from table_name; --12

selecttrunc(sysdate)from dual; --2016/7/7今天的日期为2016/7/7

selecttrunc(sysdate,'mm') from dual; --2016/7/1返回当月第一天.

selecttrunc(sysdate,'yy')from dual; --2016/1/1返回当年第一天

selecttrunc(sysdate,'dd')from dual; --2016/7/7返回当前年月日

selecttrunc(sysdate,'yyyy')from dual; --2016/1/1返回当年第一天

selecttrunc(sysdate,'d')from dual; --2016/7/3 (星期天)返回当前星期的第一天

selecttrunc(sysdate,'hh') from dual; --2016/7/7 20:00:00当前时间

select trunc(sysdate,'mi') from dual; --2016/7/7 20:18:00 TRUNC()函数没有秒的精确

--to_char（） 0是占位符，9为如果有数字就显示，若没有就不显示,

select to_char(12.345,'fm900.90000')from dual;  --12.34500

select to_char(12.345,'9.90')from dual; --若整位数位数不够则显示#####

select to_char(1234,'9,9,9,9')from dual; -- 1,2,3,4

select to_char(12.345,'fm900.999900')from dual; --12.345000

select to_char(12.345,'fm900.9999')from dual; --12.345

select to_char(12.34,'9,9,9,9.99')from dual; -- xx1,2.34

select to_char(12.345,'9,9,9,9,9')from dual; --       xxx1,2

select to_char(12.34,'9,9.000')from dual; --  1,2.340

select to_char(12.345,'9,9.$999')from dual; -- $1,2.345

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual; --2016-07-0808:04:24

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual; --日期转为字符串2016-07-0820:06:18

select to_char(sysdate,'yyyy')from dual; --2016

select to_char(sysdate,'yyyy-mm')from dual; --2016-07

select to_char(sysdate,'yyyy-mm-dd')from dual; --2016-07-08

select to_char(sysdate,'yy')from dual; --年的后两位 16

 

--to_date()

select to_date('2016-07-08 03:23:44','yyyy-mm-dd hh24:mi:ss')from dual; --2016/7/8 3:23:44

select to_char(to_date('2016-07-08','yyyy-mm-dd'),'day')from dual;  --求某天是星期几  星期五     

selectfloor(sysdate- to_date('20160710','yyyymmdd'))from dual;  --求两个日期间的天数20160708-20160710 -2

selectcount(*)

  from(select rownum - 1 rnum

          from all_objects

         whererownum <= to_date('2016-07-31','yyyy-mm-dd') -

              to_date('2016- 07-01','yyyy-mm-dd')+ 1)

 where to_char(to_date('2016- 07-01','yyyy-mm-dd') + rnum - 1,'D') not in('1','7')--查找2016-07-31至2016- 07-01间除星期一和七的天数 22

 

 --add_months（）

select add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year')from dual; --找出今年的天数 16年的天数366

select add_months(sysdate,3)from dual; --3个月之后的时间 2016/10/8 20:45:02

select trunc(sysdate,'year')from dual; --2016/1/1

select add_months (trunc(sysdate,'year'),12) from dual; --2017/1/1

select add_months(sysdate,-3)from dual; --3个月之前的时间 2016/4/8 20:45:55

SELECT ADD_MONTHS(TO_DATE('2016-07-08 19:15:26','yyyy-mm-dd HH24:MI:SS'), 2)FROM dual; --2016/9/8 19:15:26