//启用约束
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';
Oracle 常用SQL整理
最新推荐文章于 2023-02-22 16:55:51 发布