临时表空间清理维护

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;