整理的一些自己开发时遇到的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