数据库小知识

1. 恢复某个表的数据到某个时间点

select * from 表名 as of timestamp to_timestamp('2009-12-11 20:53:57','yyyy-mm-dd hh24:mi:ss');
create table 表名 as select * from 表名 as of timestamp to_timestamp('2009-12-11 20:53:57','yyyy-mm-dd hh24:mi:ss');

2. 查询锁表

select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode  from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;

3. 解锁

alter system kill session '597,5388'; 

4. 表数据误删除。批量恢复表数据。查询所有需要删除的数据。

select 'flashback table '||original_name||' to before drop;' from recyclebin where original_name like '%GJBB%' and to_date(droptime,'yyyy-mm-dd hh24:mi:ss') >to_date('2021-07-10:00:00:00','yyyy-mm-dd hh24:mi:ss');

5. 查看表空间使用情况，剩余情况

SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

6. 查询数据库当前用户下，某个表的字段及对应的注释

select * from (select t1.table_name, t1.column_name, t2.comments from user_tab_columns t1 left join user_col_comments t2 on t1.table_name = t2.table_name and t1.column_name = t2.column_name) where table_name like '%T_BYYTKCGL%';

7. 当前数据库产生锁的sql

select l.session_id, sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, l.os_user_name, s.machine, s.terminal, a.sql_text from v$sqlarea a, v$session s, v$locked_object l where l.session_id = s.sid and s.prev_sql_addr = a.address order by sid, s.serial#

8. 批量删除表的sql

select replace(to_char(wm_concat('drop table '||table_name)),',',';'||chr(10)) from user_tab_comments where table_name like '%T_WTHDGL%';

9. 海量数据库锁表语句

select
  lock.locktype,
  lock.pid,
  lock.mode,
  lock.granted,
  stat.usename,
  stat.query,
  stat.query_start,
  now() - stat.query_start as duration,
  stat.state

from
  pg_locks lock
join pg_stat_activity stat on
  lock.pid = stat.pid
where
  lock.relation = 'T_ZG_QYDS_DSJBXX'::regclass
  or lock.relation::regclass::text = 'T_ZG_QYDS_DSJBXX'
order by
  duration desc;

10. 海量数据库释放锁语句

SELECT pg_terminate_backend(70427225441856);