刘同学
文章
114
分类
11
评论
0

oracle表空间清理及扩容

| 数据库 •
字数总计 9130 | 阅读时长 22分钟 | 阅读量 32 
--查看数据库表空间文件
select * from dba_data_files
--查看临时表空间
select * from dba_temp_files
--赋予用户表空间权限
ALTER USER xggm QUOTA UNLIMITED ON "USERS";

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

select file_name,tablespace_name,bytes/1024/1024 "MB",autoextensible from dba_temp_files
--查看临时表空间
select tablespace_name, tablespace_size/1024/1024 size_mb, 
free_space/1024/1024 free_mb, 
round((1 - nvl(free_space,0)/tablespace_size)*100,2) used_percent 
from dba_temp_free_space; 


--查看表空间总大小、使用率、剩余空间
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name


--查看表空间使用率(包含temp临时表空间)
--查看表空间使用率(包含临时表空间)
select * from (
Select a.tablespace_name,
(a.bytes- b.bytes) "表空间使用大小(BYTE)",
a.bytes/(1024*1024*1024) "表空间大小(GB)",
b.bytes/(1024*1024*1024) "表空间剩余大小(GB)",
(a.bytes- b.bytes)/(1024*1024*1024) "表空间使用大小(GB)",
to_char((1 - b.bytes/a.bytes)*100,'99.99999') || '%' "使用率"
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
d.bytes_used "表空间使用大小(BYTE)",
c.bytes/(1024*1024*1024) "表空间大小(GB)",
(c.bytes-d.bytes_used)/(1024*1024*1024) "表空间剩余大小(GB)",
d.bytes_used/(1024*1024*1024) "表空间使用大小(GB)",
to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率"
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name

--扩展大小或增加表空间文件

1.更改表空间的dbf数据文件分配空间大小
alter database datafile ‘...\system_01.dbf’ autoextend on;
alter database datafile ‘...\system_01.dbf’ resize 1024M;
1.2更改临时表空间大小
alter database tempfile ‘/oradata/orcl/temp01.dbf’ resize 20000m;
--注：数据文件最大为32GB，如果现有数据文件已达到32GB将无法resize成更大值，只能缩小。

2.1 为表空间新增一个数据文件(表空间满32G不能扩展则增加表空间文件)
alter tablespace SYSTEM add datafile '/****' size 1000m autoextend on next 100m;

2.2 如果是temp临时表新增表空间会报错:
0RA-03217: 变更TEMPORARY TABLESPACE 无效的选项
解决方法: datafile改为tempfile
--修改原来的创建第二个文件
alter tablespace TEMP add tempfile'/****' size 1000m autoextend on next 100m;
--新创建一个表空间
create temporary tablespace TEMP01 TEMPFILE 'D:\oradata\temp01.dbf' 
SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; 

--把TEMP表空间回缩一下
 Alter tablespace TEMP coalesce;

--对临时表空间进行shrink（11g新增的功能） 
--将temp表空间收缩为20M 
alter tablespace temp shrink space keep 20M; 
--自动将表空间的临时文件缩小到最小可能的大小 
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;  
//查看表空间
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;


SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

//表空间扩容,自动100M扩展
ALTER DATABASE DATAFILE '/oracle/oracle/oradata/unit/users01.dbf'
AUTOEXTEND ON NEXT 100M  MAXSIZE 7480M;

ALTER DATABASE DATAFILE '/oracle/oracle/oradata/unit/system01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 7480M;

ALTER DATABASE DATAFILE '/oracle/oracle/oradata/unit/sysaux01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 7480M;

//清理表
truncate table t_qm_001;

//清理占用的表空间,开启行移动.
alter table XGGM_QUICK.T_QK_SCHEDULER_LOG enable row movement;
alter table XGGM_QUICK.T_QK_SCHEDULER_LOG shrink space;

针对temp临时表空间使用率爆满问题

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理，但有些时候我们会遇到临时段没有被释放，TEMP表空间几乎满使用率情况；

引起临时表空间增大主要使用在以下几种情况：

1、order by or group by (disc sort占主要部分)；

2、索引的创建和重创建；

3、distinct操作；

4、union & intersect & minus sort-merge joins；

5、Analyze 操作；

6、有些异常也会引起TEMP的暴涨。

解决方法一：用上述方法给temp增加表空间文件

解决方法二：在服务器资源空间有限的情况下，重新建立新的临时表空间替换当前的表空间

--1.查看当前的数据库默认表空间：
select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';

--2.创建新的临时表空间
create temporary tablespace TEMP TEMPFILE 'D:\oradata\temp01.dbf' 
SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; 

--3.更改默认临时表空间
alter database default temporary tablespace TEMP01;

--4.删除原来的临时表空间
drop tablespace TEMP02 including contents and datafiles;

--如果删除原来临时表空间报错ORA-60100：由于排序段，已阻止删除表空间...
--（说明有语句正在使用原来的临时表空间，需要将其kill掉再删除，此语句多为排序的语句）
--查询语句
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;

--删除对应的'sid,serial#'
alter system kill session 'sid,serial#'

以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正的治本的方法是找出数据库中消耗资源比较大的sql语句，然后对其进行优化处理。下面是查询在sort排序区使用的执行耗时的SQL：

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 su.username,su.Extents,tablespace,segtype,sql_text

from v$sort_usage su,v$sql s

Where su.SQL_ID = s.SQL_ID;

注：如果原临时表空间无用户使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是文件系统可以看看文件的时间戳。

kill

1、 使用如下语句a查看一下认谁在用临时段

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

2、kill正在使用临时段的进程

SQL>Alter system kill session 'sid,serial#';

3、把TEMP表空间回缩一下

SQL>Alter tablespace TEMP coalesce;

注：这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间（LMT:Local Managed Tablespace），不需要整理的。9i以后只能创建本地管理的表空间。

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL;

四、使用诊断事件，也是相对有效的一种方法

1、查询事件代码

SQL>select ts#, name from sys.ts$ ;

TS# NAME

---------- ------------------------------

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS2

2、 执行清理操作

SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4';

说明：temp表空间的TS# 为 3, So TS#+ 1= 4。

文章作者： 刘同学
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 刘同学的小站！
数据库 oracle Mysql
喜欢就支持一下吧