oracle表空单清理

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;

 

或者删除表

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;

http://www.htsjk.com/oracle/21943.html www.htsjk.Com true http://www.htsjk.com/oracle/21943.html NewsArticle oracle表空单清理 oracle表空单清理 1.查询表空间使用情况: sqlplus system/manager@topprod SQL@q_tbsFREE 2.查询temp使用方法: sqlplus system/manager@topprod SQLSELECT d.tablespace_name tablespace_name , d.status tablespa...