oracle瞎记-锁和高水位

环境设置
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