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;

或者删除表

[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;

免责声明：本站所有文章和图片均来自用户分享和网络收集，文章和图片版权归原作者及原出处所有，仅供学习与参考，请勿用于商业用途，如果损害了您的权利，请联系网站客服处理。