ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

临时表空间的维护

内容发布:犀利的3娃| 发布时间:2019-8-21 10:42:38
临时表空间满后的处理方法

-- 检查临时表空间状态信息

select * from dba_temp_files;
select * from dba_tablespace;
select * from v$tempfile;

--1.增加临时文件

alter tablespace temp add tempfile '/../../../tempxx.dbf' size xx autoextend off;

--2.修改临时文件

alter database tempfile '/../../../tempxx.dbf' resize xx;

--3.shrinking(在线,避开业务高峰期)

alter tablespace temp shrink space keep xxx;
alter tablespace temp shrink tempfile '/../../../tempxx.dbf';


--修改默认临时表空间

--1检查临时表空间状态信息

select username,temporary tablespace from dba_users;
select * from v$tempfile;
select * from dba_tablespaces where contents='TEMPORARY';
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--2创建一个新的临时表空间

create temporary tablespace tempxx tempfile '/../../../tempxx.dbf' size xx autoextend off;

--3修改默认临时表空间为新的临时表空间

alter database default temporary tablespace tempxx;
----alter user xxxx temporary tablespace tempxx;

4.检查新的临时表空间的状态

select username,temporary tablespace from dba_users;
select * from v$tempfile;
select * from dba_tablespaces where contents='TEMPORARY';
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

5.删除原来的默认临时表空间

drop tablespace temp including contents and datafiles;

6.再次检查状态

7.重建原来的临时表空间

create temporary tablespace temp tempfile '/../../../tempxx.dbf'size xx autoextend off;

8.还原默认临时表空间

alter database default temporary tablespace temp;
----alter user xxxx temporary tablespace temp;

9.删除中转临时表空间tempxx

drop tablespace tempxx including contents and datafiles;

-- 查看有哪些SQL在使用临时表空间

select se.username,
        se.sid,
        se.serial#,
        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;

--杀死相关进程

alter system kill session 'xxx,xxxx';

10.查看临时表空间使用率

select tu.tablespace_name as "TABLESPACE_NAME", tt.total - tu.used as "FREE(G)", tt.total as "TOTAL(G)", round(nvl(tu.used, 0) / tt.total * 100, 3) as "USED(%)", round(nvl(tt.total - tu.used, 0) * 100 / tt.total, 3) as "FREE(%)"from (select tablespace_name,  sum(bytes_used) / 1024 / 1024 / 1024 used from gv_$temp_space_header group by tablespace_name) tu , (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 as total from dba_temp_files group by tablespace_name) ttwhere tu.tablespace_name = tt.tablespace_name;


上一篇:1111111111111111
下一篇:oracle启动过程追踪和控制文件dump
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表