Oracle 常用SQL整理

//启用约束
alter table 表名 enable  constraint 约束名
//禁用约束
alter table 表名 disable  constraint 约束名
//查询约束所属的表
select table_name from dba_constraints where constraint_name = 约束名 and constraint_type = 'R';
//查询表名和表的记录数
analyze table 表名 compute statistics;
select t.table_name,t.num_rows from user_tables t
select t.table_name,t.num_rows from user_tables t  （此sql不精确）
//精确统计
先创建function
create or replace function count_rows(table_name in varchar2,
                                      owner      in varchar2 default null)
  return number authid current_user IS
  num_rows number;
  stmt     varchar2(2000);
begin
  if owner is null then
    stmt := 'select count(*) from "' || table_name || '"';
  else
    stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
  end if;
  execute immediate stmt
    into num_rows;
  return num_rows;
end;
再执行sql：
select table_name, count_rows(table_name) nrows from user_tables where  count_rows(table_name) >=1；


//查询哪些表被锁住
select p.spid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
  from v$process p, v$session a, v$locked_object b, all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id;
查看 哪些表 被锁了 
SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 
AND l.session_id = s.sid; 
解除锁定：
--alter system kill session 'sid, serial#'; 
ALTER system kill session 'xx, xx'; 

//查询oracle时区
select dbtimezone from dual; // 数据库时区
select sessiontimezone from dual; //数据库当前会话时区


// 根据sqlid 查询杀会话的sql
select 'alter system kill session '''|| t.SID||','||t.SERIAL#||''';' from v$session t where t.SQL_ID='0nx7fbv1w5xg2';