1、sqlplus登录时显示客户端软件版本; 2、SELECT SUM(a.BYTES) / 1048576 FROM dba_data_files a WHERE a.TABLESPACE_NAME IN (SELECT b.TABLESPACE_NAME FROM dba_tablespaces b WHERE b.contents = 'UNDO'); 3、ALTER ROLLBACK SEGMENT rollback_segment SHRINK [TO integer [K|M]];
1、查看版本: C:\Documents and Settings\xieyu>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 23 20:23:36 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> connect / as sysdba; 已连接。SQL> SELECT VERSION FROM V$INSTANCE;VERSION ----------------- 10.2.0.1.0SQL> SELECT VERSION_TIME FROM V$DATABASE;VERSION_TIME -------------- 25-12月-092、查看回滚段表空间: SQL> --方法1(通过表自己个数统计): SQL> SELECT SUM(BYTES) / (1024 * 1024) "UNDO表空间分配大小", 2 SUM(USER_BYTES) / (1024 * 1024) "UNOD表空间可使用大小" 3 FROM DBA_DATA_FILES T 4 WHERE T.TABLESPACE_NAME IN 5 (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO');UNDO表空间分配大小 UNOD表空间可使用大小 ------------------ -------------------- 523 522.875SQL> --方法2(通过块个数统计): SQL> SELECT SUM(BLOCKS) / (1024/8) "UNDO表空间分配大小", 2 SUM(USER_BLOCKS) / (1024/8) "UNOD表空间可使用大小" 3 FROM DBA_DATA_FILES T 4 WHERE T.TABLESPACE_NAME IN 5 (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO');UNDO表空间分配大小 UNOD表空间可使用大小 ------------------ -------------------- 523 522.875 3、清空回滚段表空间,应该是回收吧: 首先通过下面SQL查询出那个回滚段(将SEGMENT_NAME取出来): SELECT SEGMENT_NAME, OWNER FROM DBA_ROLLBACK_SEGS T;ALTER ROLLBACK SEGMENT <你要回收的回滚段名称> SHRINK TO 30M; 或者使用一下修改,让ORACLE自动回收: ALTER ROLLBACK SEGMENT <你要自动回收的回滚段名称> storage {optimal 30m}
2、SELECT SUM(a.BYTES) / 1048576
FROM dba_data_files a
WHERE a.TABLESPACE_NAME IN
(SELECT b.TABLESPACE_NAME FROM dba_tablespaces b WHERE b.contents = 'UNDO');
3、ALTER ROLLBACK SEGMENT rollback_segment SHRINK [TO integer [K|M]];
C:\Documents and Settings\xieyu>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 23 20:23:36 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> connect / as sysdba;
已连接。SQL> SELECT VERSION FROM V$INSTANCE;VERSION
-----------------
10.2.0.1.0SQL> SELECT VERSION_TIME FROM V$DATABASE;VERSION_TIME
--------------
25-12月-092、查看回滚段表空间:
SQL> --方法1(通过表自己个数统计):
SQL> SELECT SUM(BYTES) / (1024 * 1024) "UNDO表空间分配大小",
2 SUM(USER_BYTES) / (1024 * 1024) "UNOD表空间可使用大小"
3 FROM DBA_DATA_FILES T
4 WHERE T.TABLESPACE_NAME IN
5 (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO');UNDO表空间分配大小 UNOD表空间可使用大小
------------------ --------------------
523 522.875SQL> --方法2(通过块个数统计):
SQL> SELECT SUM(BLOCKS) / (1024/8) "UNDO表空间分配大小",
2 SUM(USER_BLOCKS) / (1024/8) "UNOD表空间可使用大小"
3 FROM DBA_DATA_FILES T
4 WHERE T.TABLESPACE_NAME IN
5 (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO');UNDO表空间分配大小 UNOD表空间可使用大小
------------------ --------------------
523 522.875
3、清空回滚段表空间,应该是回收吧:
首先通过下面SQL查询出那个回滚段(将SEGMENT_NAME取出来):
SELECT SEGMENT_NAME, OWNER FROM DBA_ROLLBACK_SEGS T;ALTER ROLLBACK SEGMENT <你要回收的回滚段名称> SHRINK TO 30M; 或者使用一下修改,让ORACLE自动回收:
ALTER ROLLBACK SEGMENT <你要自动回收的回滚段名称> storage {optimal 30m}