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

查询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 为表名称

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

添加新评论

标签