oracle瞎记-锁和高水位

cinszennpr

环境设置
SET SERVEROUTPUT ON         : 设置打印显示
SET PAGESIZE n                 : 设置显示宽度
SET TRANSACTION READ ONLY  : 设置只读事物，必须放在事物开始的第一条语句
ALTER SESSION SET

关键字
DESCRIBE tablename : 查看表结构
DISTINCT : 去掉重复行
|| : 表示字符链接
TRUNCATE tablename : 删除表的所有记录，并释放表空间，不能回滚

高水位问题(High Water Mark)
降低水位，执行下面命令：
alter table table_name enable row movement;
alter table table_name shrink space cascade;

--检查表FNPOPS用户空间是否正确
select *  from user_all_tables where tablespace_name <> 'tablespace_name';

--改变当前用户下表的表空间
select 'alter table  '|| table_name ||'  move tablespace tablespace_name;'  from user_all_tables;

--建立表空间
create tablespace tablespace_name datafile 'd:\tsn1.dbf' size 100M autoextend on;

--查询当前用户下的所有索引
select 'alter index '|| index_name ||' rebuild tablespace tablespace_name;' from user_indexes;

--统计当前用户下所有表的记录数
select 'select '||chr(39)||table_name||chr(39)||' as table_name, count(1) from '||table_name||' union all '
from user_all_tables;

--表分析
select 'analyze table  '|| table_name ||'  compute statistics;'  from user_all_tables;

锁的问题
/*
用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。   
  这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。   
  可以通过alter  system  kill  session  'sid,serial#' 来杀掉会话
*/
SELECT /*+  rule  */
s.username,
decode(l.type, 'TM', 'TABLE  LOCK', 'TX', 'ROW  LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.terminal,
s.machine,
s.program,
s.osuser
  FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT NULL
--alter  system  kill  session  '3214,4110'  immediate;

/*
锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待   
  以下的语句可以查询到谁锁了表，而谁在等待。
  
  以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
*/
SELECT /*+  rule  */
lpad('  ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
  FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC

/*
如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。
同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。   
*/
   
SELECT s.USERNAME,
       s.SID,
       s.SERIAL#,
       t.UBAFIL "UBA  filenum",
       t.UBABLK "UBA  Block  number",
       t.USED_UBLK "Number  os  undo  Blocks  Used",
       t.START_TIME,
       t.STATUS,
       t.START_SCNB,
       t.XIDUSN RollID,
       r.NAME RollName
  FROM v$session s, v$transaction t, v$rollname r
WHERE s.SADDR = t.SES_ADDR
   AND t.XIDUSN = r.usn