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 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'；