oracle常用操作整理

1.undo表空间管理 查看当前使用回滚段的用户： select s.username, u.name from v$transaction t,v$rollstat r,v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; 查询目前回滚段使用情况： select s

1.undo表空间管理

查看当前使用回滚段的用户：
select s.username, u.name from v$transaction t,v$rollstat r,v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;

查询目前回滚段使用情况：
select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) and TABLESPACE_NAME='UNDOTBS2' order by segment_name ;

select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;

创建新的表空间并自动扩展：
create undo tablespace undotbs2 datafile '/home/oracle/sagllf/undotbs1.dbf' size 5000m reuse autoextend on next 800m maxsize unlimited;

设置undo表空间：
alter system set undo_tablespace='undotbs2';

删除原表空间：
drop tablespace UNDOTBS1 including contents and datafiles;

设置undo_retention时间
ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH;

2.查询表空间使用率

SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
MAXBYTES MAXBYTES,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND(((SPACE - NVL(FREE_SPACE, 0)) /
DECODE(MAXBYTES, 0, 1, MAXBYTES)) * 100,
2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
ROUND(SUM(DECODE(AUTOEXTENSIBLE, 'NO', BYTES, MAXBYTES)) / (1024 * 1024),
2) MAXBYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY D.TABLESPACE_NAME;

3.查询锁表：
SELECT SESSION_ID FROM V$LOCKED_OBJECT,USER_OBJECTS WHERE V$LOCKED_OBJECT.OBJECT_ID = USER_OBJECTS.OBJECT_ID；
SELECT SERIAL# FROM V$SESSION WHERE SID= SESSION_ID；
ALTER SYSTEM KILL SESSION '768, 223'；