|
1.查询表空间使用情况:
sqlplus system/manager@topprod
<div class="codetitle"><a style="CURSOR: pointer" data="20759" class="copybut" id="copybut20759" onclick="doCopy('code20759')"> 代码如下:<div class="codebody" id="code20759">
SQL>@q_tbsFREE
2.查询temp使用方法:
sqlplus system/manager@topprod
<div class="codetitle"><a style="CURSOR: pointer" data="12473" class="copybut" id="copybut12473" onclick="doCopy('code12473')"> 代码如下:<div class="codebody" id="code12473">
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
,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临时表空间：(在无用户连接的状况下操作,最好在清理之前重启一下数据库)
<div class="codetitle"><a style="CURSOR: pointer" data="90446" class="copybut" id="copybut90446" onclick="doCopy('code90446')"> 代码如下:<div class="codebody" id="code90446">
#重启数据库
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表空间：(在无用户连接的状况下操作,最好在清理之前重启一下数据库)
<div class="codetitle"><a style="CURSOR: pointer" data="29967" class="copybut" id="copybut29967" onclick="doCopy('code29967')"> 代码如下:<div class="codebody" id="code29967">
#重启数据库
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表空间：
<div class="codetitle"><a style="CURSOR: pointer" data="11405" class="copybut" id="copybut11405" onclick="doCopy('code11405')"> 代码如下:<div class="codebody" id="code11405">
#删除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.增加系统表空间：
<div class="codetitle"><a style="CURSOR: pointer" data="93651" class="copybut" id="copybut93651" onclick="doCopy('code93651')"> 代码如下:<div class="codebody" id="code93651">
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;
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
