• 技术文章 >数据库 >Oracle

    Oracle查看表空间使用率以及实例解决爆满问题

    长期闲置长期闲置2022-07-29 21:04:18转载324
    本篇文章给大家带来了关于Oracle的相关知识,在日常的oralce使用中最长遇到的问题就是oralce的表空间满了,数据无法写入报错,下面一起来看一下,希望对大家有帮助。

    推荐教程:《Oracle视频教程

    一、查看表空间使用率

    1.查看数据库表空间文件:

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

    2.查看所有表空间的总容量:

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

    3.查看数据库表空间使用率

    --查看数据库表空间使用率
    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.查看表空间总大小、使用率、剩余空间

    --查看表空间总大小、使用率、剩余空间
    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.查看表空间使用率(包含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

    5.查看具体表的占用空间大小

    --查看具体表的占用空间大小
    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

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

    1.更改表空间的dbf数据文件分配空间大小

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

    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 TEMP01 add tempfile'/****' size 1000m autoextend on next 100m;

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

    附:查看表空间是否具有自动扩展的能力

    --查看表空间是否具有自动扩展的能力     
    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;

    推荐教程:《Oracle视频教程

    以上就是Oracle查看表空间使用率以及实例解决爆满问题的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:脚本之家,如有侵犯,请联系admin@php.cn删除
    专题推荐:oracle
    上一篇:Oracle的序列SEQUENCE解析 下一篇:plsql和oracle的区别是什么
    VIP课程(WEB全栈开发)

    相关文章推荐

    • 【活动】充值PHP中文网VIP即送云服务器• 完全掌握Oracle数据库备份与还原• Oracle数据库表空间整理回收与释放操作• Oracle示例详解分组数据• php7.2怎么连接oracle数据库?OCI8扩展的安装• 浅析Oracle体系结构
    1/1

    PHP中文网