oracle表空单清理常用代码段整理

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

sql>@q_tbsfree

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;