Oracle checks table space usage and resolves full instance issues

This article brings you relevant knowledge about Oracle. The most common problem encountered in daily use of oralce is that the table space of oralce is full, the data cannot be written and an error is reported. The following is Let's take a look, I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

1. Check the table space usage

1. View the database table space files:

--查看数据库表空间文件
select * from dba_data_files;

2. View the total capacity of all table spaces:

--查看所有表空间的总容量
select dba.TABLESPACE_NAME, sum(bytes)/1024/1024 as MB  
from dba_data_files dba 
group by dba.TABLESPACE_NAME;

3. View the database table space usage

--查看数据库表空间使用率
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct 
from (
select tablespace_name, sum(bytes) /1024/1024 as MB 
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files group by tablespace_name) total     
where free.tablespace_name = total.tablespace_name 
order by used_pct desc;

4.1. View the table space Total size, usage, remaining 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

4.2. Check table space usage (including temp temporary table space)

--查看表空间使用率(包含临时表空间)
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

5. Check the space occupied by specific tables

--查看具体表的占用空间大小
select * from (
select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
from dba_segments t
where t.segment_type='TABLE'
group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc

2. Expand the size or increase the table space file

1. Change the dbf data file allocation space size of the table space

alter database datafile ‘...\system_01.dbf' autoextend on;
alter database datafile ‘...\system_01.dbf' resize 1024M;

2.1 Add a new data file to the table space (the table space is full 32G If it cannot be expanded, add the table space file)

alter tablespace SYSTEM add datafile '/****' size 1000m autoextend on next 100m;

2.2 If the new table space is a temp temporary table, an error will be reported:

0RA-03217: Invalid option to change TEMPORARY TABLESPACE

Solution: Change datafile to tempfile

alter tablespace TEMP01 add tempfile'/****' size 1000m autoextend on next 100m;

In response to the problem of full usage of temp temporary table space

The main purpose of temporary table space is to provide it when the database performs sorting operations, manages indexes, accesses views, etc. The temporary operation space will be automatically cleaned up by the system after the operation is completed, but sometimes we will encounter a situation where the temporary segment is not released and the TEMP table space is almost fully used;

causes the temporary table space to increase Large is mainly used in the following situations:

1. order by or group by (disc sort accounts for the main part);

2. Index creation and re-creation;

3. Distinct operation;

4. Union & intersect & minus sort-merge joins;

5. Analyze operation;

6. Some exceptions will also occur Causes a surge in TEMP.

Solution 1: Use the above method to add a table space file to temp

Solution 2: When the server resource space is limited, re-create a new temporary table space to replace the current table space

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

--2.创建新的临时表空间
create temporary tablespace TEMP01 tempfile 
'/home/temp01.dbf' size 31G;

--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#'

Attachment: Check whether the table space has the ability to automatically expand

--查看表空间是否具有自动扩展的能力     
SELECT T.TABLESPACE_NAME,D.FILE_NAME,     
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS     
FROM DBA_TABLESPACES T,DBA_DATA_FILES D     
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME     
 ORDER BY TABLESPACE_NAME,FILE_NAME;

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Oracle checks table space usage and resolves full instance issues. For more information, please follow other related articles on the PHP Chinese website!