当前位置：首页 > news >正文

ORACLE-表空间和分区控制

news 来源：原创 2025/6/18 17:19:04

--查询最后更新的统计信息时间

SELECT table_name, last_analyzed
FROM dba_tables
WHERE table_name = '表名';

--更新统计信息
-----按分区
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'XI_SF', -- 模式名称
tabname => '表名', -- 表名称
partname => 'p202411', -- 要更新的分区名称
cascade => TRUE, -- 是否级联更新索引统计信息
degree => NULL -- 并行度，NULL表示使用默认值
);
END;
/
--全表更新
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => '空间名', tabname => '表名');
END;

--查看分区占用条数
SELECT * FROM 表名 (P202309);

--truncate 分区数据不释放空间
ALTER TABLE 表名 TRUNCATE PARTITION P202411 update indexes;--不更新索引会导致索引失效需要重建

----------查看表空间占用大小

select upper(f.tablespace_name) as "表空间名称",
round(d.availb_bytes, 4) as "表空间分配大小(G)",
round((d.availb_bytes - f.free_bytes), 4) as "分配空间已使用大小(G)",
round(f.free_bytes, 4) as "分配空间剩余大小(G)",
to_char(round((d.availb_bytes - f.free_bytes) / d.availb_bytes * 100,
2),
'999.99') || '%' as "分配空间使用率",
round((d.max_bytes - d.availb_bytes + f.free_bytes), 4) as "可用空间大小(G)",
round(d.max_bytes, 4) as "表空间最大大小(G)",
to_char(round((d.max_bytes - d.availb_bytes + f.free_bytes) /
d.max_bytes * 100,
4),
'999.9999') || '%' as "表空间空闲率"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024 * 1024), 6) free_bytes,
round(max(bytes) / (1024 * 1024 * 1024), 6) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024 * 1024), 6) availb_bytes,
round(sum(decode(dd.maxbytes, 0, dd.bytes, dd.maxbytes)) /
(1024 * 1024 * 1024),
6) max_bytes
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name

--------------查看表空间的位置，以及各个数据文件
SELECT a.tablespace_name,
a.file_name,
a.file_id,
ROUND(a.bytes / 1024 / 1024, 2) AS "分配大小_M",
ROUND(c.bytes / 1024 / 1024, 2) AS "剩余大小_M",
ROUND(a.maxbytes / 1024 / 1024, 2) AS "最大大小_M",
a.autoextensible
FROM dba_data_files a
JOIN (select file_id, sum(bytes) as bytes
from dba_free_space
where tablespace_name = 'XISOFT'
group by file_id) c
ON a.file_id = c.file_id;

---------XISOFT_2.DBF这个文件名自己命名，建议表空间名字+排序号
alter tablespace XISOFT add datafile 'D:\SOFT\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SOFT_2.DBF' size 1024m autoextend on next 100m;

相关文章：