Oracle表空间大小及占用情况查询
侧边栏壁纸
  • 累计撰写 269 篇文章
  • 累计收到 275 条评论

Oracle表空间大小及占用情况查询

Karry Bai
2018-07-18 / 0 评论 / 249 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2020年12月25日,已超过551天没有更新,若内容或图片失效,请留言反馈。

查询Oracle表空间使用情况

SELECT 
-- '表空间名称'
a.tablespace_name as "Table Space Name", 
-- 表空间总大小(byte)
total as "Total(byte)", 
-- 表空间剩余大小(byte)
free as "Lave(byte)", 
-- 表空间已使用大小(byte)
(total - free) as "Use(byte)", 
-- 表空间总大小(G)
total / (1024 * 1024 * 1024) as "Total(G)" , 
-- 表空间剩余大小(G)
free / (1024 * 1024 * 1024) as "Lave(G)", 
-- 表空间已使用大小(G)
(total - free) / (1024 * 1024 * 1024) as "Use(G)" , 
round((total - free) / total, 4) * 100 as "Usage Rate(%)" 
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;

查询表空间下所有的表

Select Table_Name, Tablespace_Name From Dba_Tables Where Tablespace_Name = 'USERS';

说明:USERS为表空间名称,上面SQL执行表示查询USERS表空间下所有的表

查看表空间下的表占用空间情况

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) as "Use(M)"
from dba_segments t where t.tablespace_name = 'USERS' --and t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type

查看oracle表空间的名称及大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) "Size" 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name;

查看oracle表空间物理文件的名称及大小

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

查看oracle回滚段名称及大小

SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, 
(next_extent / 1024) nextextent, max_extents, v.curext curextent 
FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) 
ORDER BY segment_name;

查看oracle表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) as free_space, tablespace_name 
    FROM dba_free_space 
        GROUP BY tablespace_name; 
SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "%USED", 
    (c.bytes * 100) / a.bytes "%FREE"  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
    WHERE a.tablespace_name = b.tablespace_name  AND a.tablespace_name = c.tablespace_name;

注意:只能使用""而不能使用''

扩展表空间大小,向表空间里增加数据文件

ALTER TABLESPACE [tablespace_name] ADD DATAFILE '[filename]' SIZE [size];

tablespace_name为表空间名称;filename为表空间文件;size为表空间大小

向表空间里新增数据文件,并且允许数据文件自动增长

ALTER TABLESPACE USERS ADD DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\INNETDB\USERS02.DBF' SIZE [50M] AUTOEXTEND ON NEXT [5M] MAXSIZE [100M];

删除表空间中的数据文件

ALTER TABLESPACE [tablespace_name] DROP DATAFILE '[filename]';

tablespace_name为表空间名称;filename为表空间文件;

 

实战操作:

当表空间满了之后,使用delete删除数据后,表空间不会回收,存在高水位。

High Water Mark(HWM) 是Oracle(Segment)级别的概念。在仅有DML(比如delete,insert)操作时,高水位线只会增长,不会降低。具体来说,由于程序使用的delete语句不回收使用过的空间,数据虽然删除了,但是高水位线却没有降低,仍然处于之前的水位。

1、扩展表空间:将表空间USERS扩充20000M(自己已尝试,可行)

alter tablespace USERS add datafile 'D:\APP\ADMINISTRATOR\ORADATA\INNETDB\USERS02.DBF' size 20000M;

2、解决办法:降低表的高水位(自己未曾尝试)

语法:alter table my_objects shrink space;

需要说明的一点就是,使用truncate删除数据,不会有HWM的现象。

 

扩展:

查看oracle数据库库对象

SELECT owner, object_type, status, COUNT(*) count# FROM all_objects  GROUP BY owner, object_type, status;

查看oracle数据库的版本

SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';

查看oracle数据库的创建日期和归档方式

SELECT created, log_mode FROM v$database;

查看oracle控制文件目录

SELECT NAME FROM v$controlfile;

查看oracle日志文件目录

SELECT MEMBER FROM v$logfile;

查看索引信息

-- 查询表索引
select * from user_indexes ui where ui.table_name = 'T_S_HIS_ALARM_INFO';
-- 查询指定索引
select status  from user_indexes where index_name='IDX_HIS_TERMINALID'

设置表不写日志

alter table [table_name] NOLOGGING;

table_name 为表名称

 

0

评论 (0)

取消