临时表空间清理维护

SELECT a.tb_name 表空间名称,
c.contents 类型,
c.extent_management 区管理,
b.total / 1024 / 1024 表空间大小m,
(b.total - a.free_sp) / 1024 / 1024 已使用m,
a.free_sp / 1024 / 1024 剩余m,
substr((b.total - a.free_sp) / b.total * 100, 1, 5) 利用率
FROM (SELECT tablespace_name tb_name, SUM(nvl(bytes, 0)) free_sp
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name tb_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b,
(SELECT tablespace_name tb_name,
contents contents,
extent_management extent_management
FROM dba_tablespaces) c
WHERE a.tb_name = b.tb_name
AND c.tb_name = b.tb_name;

create temporary tablespace TEMP3 TEMPFILE '/opt/oracle/oradata/cuss/TEMP2.dbf' size 20m reuse autoextend off; 
SQL>alter database default temporary tablespace "TEMP2" 一个TEMP3文件，初始大小1G，每次增长200M，最大限制为4G。 SQL> 
create temporary tablespace TEMP3 TEMPFILE '/opt/oracle/oradata/cuss/TEMP3.dbf' size 1000m reuse autoextend on next 200m maxsize 4000m;
把缺省临时表空间指向这个新建的TEMP3。 SQL>alter database default temporary tablespace "TEMP3" 
删除原有的临时表空间文件和操作系统中的对应物理文件，释放磁盘空间 
SQL>drop tablespace temp2 including contents and datafiles; 重新做exp导出，导出成功。至此调整结束。 
select TABLESPACE_NAME from dba_tablespaces;

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid;

select a.property_name, a.property_value from database_properties a where a.property_name like '%DEFAULT%';

create temporary tablespace dinpay_temp02 tempfile '/opt/app/oracle/oradata/dinpay/dinpay_temp02.dbf' size 2048m autoextend on next 50m maxsize unlimited;

create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/devdb/temp02.dbf' size 100m autoextend on next 10m maxsize unlimited;

create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/devdb/temp01.dbf' size 100m autoextend on next 10m maxsize unlimited;

create temporary tablespace temp2 tempfile '/opt/app/oracle/oradata/dinpay/temp02.dbf' size 1024m autoextend on next 100m maxsize unlimited;

alter database default temporary tablespace dinpay_temp02;

alter database default temporary tablespace temp02;

alter tablespace temp2 tempfile offline;

drop tablespace DINPAY_TEMP02 including contents and datafiles cascade constraints;

--如果之前的临时表空间还有用户在使用,使无法删除的
SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,
su.segtype, su.CONTENTS FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr
and tablespace= 'TEMP2';

alter system kill session '415,7451';

alter tablespace temp1 tempfile offline;

alter tablespace DINPAY_TEMP tempfile online;

alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;

SELECT 'alter database tempfile ''' || a.name || ''' resize ' || b.siz || 'M;' resize_command
FROM v$tempfile a,
(SELECT ceil(tmsize.maxblk * bk.value / 1024 / 1024) siz
FROM (SELECT nvl(MAX(segblk#), 128) maxblk FROM v$sort_usage) tmsize,
(SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size') bk) b;

select s.name tbsname, t.name, (t.bytes / 1024 / 1024) mb, t.status
from v$tablespace s, v$tempfile t
where s.ts# = t.ts#;

SELECT sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
FROM v$sort_usage sort, v$session sess, v$sql SQL
WHERE sort.SESSION_ADDR = sess.SADDR
AND SQL.ADDRESS = sess.SQL_ADDRESS
ORDER BY blocks DESC;

SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

作者：ritchy

原文链接：https://www.cnblogs.com/ritchy/p/10273472.html