T100和TIPTOP中oracle表空单清理常用代码段整理

清理TEMP临时表空间、清理UNDO表空间、清理TEMPTABS表空间等等，有类似需求的朋友可以参考下哈

1.查询表空间使用情况:
sqlplus system/manager@topprod

SQL>@q_tbsFREE 

或者：

SELECT UPPER(F.TABLESPACE_NAME) "表空间名", 
D.TOT_GROOTTE_MB "表空间大小(M)", 
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", 
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比", 
F.TOTAL_BYTES "空闲空间(M)", 
F.MAX_BYTES "最大块(M)" 
FROM (SELECT TABLESPACE_NAME, 
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 
FROM SYS.DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F, 
(SELECT DD.TABLESPACE_NAME, 
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
FROM SYS.DBA_DATA_FILES DD 
GROUP BY DD.TABLESPACE_NAME) D 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
ORDER BY 4 DESC; 

2.查询temp使用方法:
sqlplus system/manager@topprod

SQL>SELECT 
d.tablespace_name tablespace_name 
, d.status tablespace_status 
, NVL(a.bytes, 0) tablespace_size 
, NVL(t.bytes, 0) used 
, TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct 
, NVL(s.current_users, 0) current_users 
FROM 
sys.dba_tablespaces d 
, ( select tablespace_name, sum(bytes) bytes 
from dba_temp_files 
group by tablespace_name 
) a 
, ( select tablespace_name, sum(bytes_cached) bytes 
from v$temp_extent_pool 
group by tablespace_name 
) t 
, v$sort_segment s 
WHERE 
d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = t.tablespace_name(+) 
AND d.tablespace_name = s.tablespace_name(+) 
AND d.extent_management like 'LOCAL' 
AND d.contents like 'TEMPORARY'; 

2.清理TEMP临时表空间：(在无用户连接的状况下操作,最好在清理之前重启一下数据库)

#重启数据库 
sqlplus '/as sysdba' 
SQL>shutdown immediate 
SQL>startup 
#创建一个临时表空间temp02，用作临时替换 
SQL>create temporary tablespace temp02 tempfile '/u2/oradb/oradata/topprod/temp02.dbf' size 10M autoextend on next 10M; 
#将系统临时表空间指向temp02 
SQL>alter database default temporary tablespace temp02; 
#删除原来的临时表空间temp 
SQL>drop tablespace temp including contents and datafiles; 
#创建新的临时表空间temp 
SQL>create temporary tablespace temp tempfile '/u2/oradb/oradata/topprod/temp01.dbf' size 4096M autoextend on next 100M; 
#将系统临时表空间指回temp 
SQL>alter database default temporary tablespace temp; 
#删除临时表空间temp02 
SQL>drop tablespace temp02 including contents and datafiles; 

3.清理UNDO表空间：(在无用户连接的状况下操作,最好在清理之前重启一下数据库)

#重启数据库 
sqlplus '/as sysdba' 
SQL>shutdown immediate 
SQL>startup 
#创建一个UNDO表空间undotbs2，用作临时替换 
SQL>create undo tablespace undotbs2 datafile '/u2/oradb/oradata/topprod/undotbs02.dbf' size 10M autoextend on next 10M; 
#将系统UNDO表空间指向undotbs2 
SQL>alter system set undo_tablespace=undotbs2 scope=both; 
#确保所有在UNDOTBS1的undo segment都已offline 
SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs; 
#删除原来的UNDO表空间undotbs1 
SQL>drop tablespace undotbs1 including contents and datafiles; 
#创建新的临时表空间undotbs1 
SQL>create undo tablespace undotbs1 datafile '/u2/oradb/oradata/topprod/undotbs01.dbf' size 4096M; 
#将系统UNDO表空间指回undotbs1 
SQL>alter system set undo_tablespace=undotbs1 scope=both; 
#删除UNDO表空间undotbs2 
SQL>drop tablespace undotbs2 including contents and datafiles; 

3.清理TEMPTABS表空间：

#删除TEMPTABS表空间 
SQL>drop tablespace temptabs including contents and datafiles; 
#创建TEMPTABS表空间 
SQL>create tablespace temptabs datafile '/u2/oradb/oradata/topprod/temptabs.dbf' size 4096M autoextend on next 100M; 
或者删除表 
[code] 
select 'drop table '||segment_name ||';' from dba_segments where tablespace_name='TEMPTABS' and segment_name like 'TT%' and segment_name not like '%_FILE'; 

4.增加系统表空间：

alter tablespace SYSTEM add datafile '/u2/oradb/oradata/topprod/system02.dbf' size 2000M autoextend on next 10M; 
alter tablespace SYSAUX add datafile '/u2/oradb/oradata/topprod/sysaux02.dbf' size 2000M autoextend on next 10M; 

转载请注明：赫非域 » T100和TIPTOP中oracle表空单清理常用代码段整理