1、利用闪回把删除的记录恢复回来或者查看当时该记录的状态值;
在数据库操作中,删除数据可能会操作失误,也可能当时想删除,但是没有备份,现在又想恢复回来的情况
select * from tx_veh as of timestamp ('20210303 10:00:00','yyyymmdd hh24:mi:ss')
where +条件 可以查询某个时间点该记录的状态, 导出sql,再重新插入即可恢复。
2、去除单个表中的重复数据
该句式可删除rowid较大的重复数据,rowid也可改为其他较为明显区别重复数据的其他字段
delete from veh_cx a
where rowid <>( select min(rowid) from veh_cx b where a.name = b.name and a.dsr = b.dsr)
3、并行查询数据
---/*+parallel(4)*/ 4为并行数,但不是并行数越多越好 当表数据量较大时增加查询效率
select /*+parallel(4)*/ * from veh_cx where glbm like '3601%';
4、统计单个表里的数据同比环比问题
SELECT SUBSTR(A.DLSJ, 0, 4) || '年' || SUBSTR(A.DLSJ, 5, 6) || '月' as 登录日期,
A.ZS AS 总数,
ROUND(A.ZS / B.ZS, 2) * 100 || '%' AS 同比,ROUND(A.ZS / C.ZS, 2) * 100 || '%' AS 环比
FROM (SELECT to_char(dlsj, 'yyyymm') DLSJ, count(*) ZS
from frm_login_log
where to_date(to_char(dlsj, 'yyyymm'), 'yyyymm') - 12 <
to_date('202103', 'yyyymm')
group by to_char(dlsj, 'yyyymm')) A
, (select to_char(dlsj, 'yyyymm') DLSJ, count(*) ZS
from frm_login_log
where to_date(to_char(dlsj, 'yyyymm'), 'yyyymm') <=
ADD_MONTHS(to_date('202103', 'yyyymm'), -12)
group by to_char(dlsj, 'yyyymm')) B
,
(select to_char(dlsj, 'yyyymm') DLSJ, count(*) ZS
from frm_login_log
where to_date(to_char(dlsj, 'yyyymm'), 'yyyymm') <=
ADD_MONTHS(to_date('202103', 'yyyymm'), -1)
group by to_char(dlsj, 'yyyymm')) CWHERE ADD_MONTHS(TO_DATE(A.DLSJ, 'YYYYMM'), -12) =
TO_DATE(B.DLSJ, 'YYYYMM')
AND ADD_MONTHS(TO_DATE(A.DLSJ, 'YYYYMM'), -1) =
TO_DATE(C.DLSJ, 'YYYYMM')
ORDER BY A.DLSJ DESC
效果如图
5、批量联表更新数据
update t_veh a set a.bm=(select b.glbm from t_pool b where a.bm=b.glbm) ;功能:更新a表字段bm,关联取值另一张b表的glbm的值;
6、插入其他表数据到本表
insert into t_veh select t.* ,sysdate from t_veh_bak ; 功能:把t_veh_bak表记录加sysdate字段插入到t_veh 表中;
7、查看数据库连接数消耗情况
select b.machine, b.program, b.username, count(*) from v$process a, v$session b where a.addr= b.paddr and b.username is not null
group by b.machine, b.program, b.username order by count(*) desc ;可查看具体机器,连接方式,数据库用户,占用连接数情况;
8、查看数据库用户表及表字段备注信息
select a.owner,a.table_name,a.column_name,a.data_type,b.comments from all_tab_columns a,user_col_comments b where a.owner='RM'
and a.table_name=b.table_name and a.column_name=b.column_name ;
all_tab_columns ##所有字段表;
user_col_comments ##当前用户有权限查看的字段备注信息表;
user_tab_comments ##当前用户有权限查看的表备注信息表;
后续继续更新积累