90-Oracle 对于LOB的数据-新建表空间和分区表

原创于 2025-06-27 17:18:13 发布 · 442 阅读

CC 4.0 BY-SA版权

文章标签：

生产现场有些业务表，保存了超多的LOB字段的表，且逐年递增，无优化和减少的趋势。需要定期新增固定大小的表空间同时对历史表进行分区，分区不能影响其他年份或是继续的业务。和生产商量后，先建立EMRP新的表空间，大小参考去年的使用量（可以增加多个表空间文件），表空间建立后做一下表分区执行。

一、LOB字段分区存储基础

性能优化

分区修剪：按时间（如就诊日期）分区后，查询2023年数据仅扫描对应分区，避免全表扫描实现年度隔离）。
IO均衡：将BLOB/CLOB单独存储至EMRP52023等表空间，避免与结构化数据竞争IO资源（创建专用表空间）。

存储管理

滚动归档：年度分区表空间（如EMRP52022→EMRP52023）支持快速归档旧数据（ALTER TABLE ... DROP PARTITION）。
空间预分配：禁用自动扩展（AUTOEXTEND OFF），通过多数据文件预分配空间（创建多个固定大小的文件），避免碎片化。

运维安全

故障隔离：单分区损坏不影响整体数据（如2023年分区故障不影响2022年数据）。
独立备份：可单独备份EMRP52023表空间（文档中未展示但隐含此能力）。

二、操作步骤

步骤1、诊断存储状态

## 查询表空间大小和占用率
set linesize 300
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "ExtentManagement",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "TotalSizeM",
       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,'99999999.999') "UsedM",
       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "FreeM",
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),'990.00') "Used%"
  FROM sys.dba_tablespaces d,
       (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) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   ORDER BY ((a.bytes-f.bytes)/a.bytes) desc;            

#查询物理文件大小
SELECT
B.FILE_NAME 物理文件名,
B.TABLESPACE_NAME 表空间名称,
B.BYTES/1024/1024 大小M,
(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,
SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 使用率
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
ORDER BY B.TABLESPACE_NAME;

--查看表空间大小
set linesize 300
col file_name for a50;
col tablespace_name for a16;
col online_status for a10;
col autoextensible for a10;
SELECT file_id,file_name,bytes / 1024 / 1024 AS MB,tablespace_name,online_status,autoextensible FROM dba_data_files;

步骤2、查一下表空间数据文件的存放地址

查看表空间存储物理位置，便于以下新加表空间和表文件dbf

select tablespace_name, file_id, file_name, 
round(bytes/(1024*1024),0) total_space 
from dba_data_files 
order by tablespace_name; 
--
步骤2.创建表空间EMRP52023，作为2023年分区的表空间
create tablespace EMRP52023 datafile '*步骤1中查询出的存放地址*\EMRP5202301.dbf' size 表空间大小（根据生产运行情况设置）
--原dbf文件路径为： /oradata01/orcl/EMRP52022_05.dbf

创建表空间，要保证服务器有足够大小！

不建议自动扩展，同样是为了提高磁盘IO。如果认为表空间大小不够，可以增加表空间数据文件。

--新建表空间，便于以下分区表使用到最新的表空间，直接pl/sql中运行，等待完成
CREATE TABLESPACE EMRP52023 DATAFILE 
  '/oradata01/orcl/EMRP52023_01.dbf' SIZE 4G AUTOEXTEND OFF,
  '/oradata01/orcl/EMRP52023_02.dbf' SIZE 4G AUTOEXTEND OFF,
  '/oradata01/orcl/EMRP52023_03.dbf' SIZE 4G AUTOEXTEND OFF,
  '/oradata01/orcl/EMRP52023_04.dbf' SIZE 4G AUTOEXTEND OFF,
  '/oradata01/orcl/EMRP52023_05.dbf' SIZE 4G AUTOEXTEND OFF 
    LOGGING
    ONLINE
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    BLOCKSIZE 8 K
    SEGMENT SPACE MANAGEMENT AUTO
    FLASHBACK ON;
    ----如遇老的表空间容量不足，临时增加dbf文件
    alter tablespace EMRP52023 add datafile '/oradata01/orcl/EMRP52023_06.dbf' SIZE 4G; --20231109添加

步骤3、查看分区表信息

 select distinct table_name from dba_tab_partitions;
select table_name,partition_name,high_value from dba_tab_partitions;

步骤4.生成分区脚本，指导文件

select 'ALTER TABLE' || (tab_par.table_name) ||
       ' ADD PARTITION DATE2023' || ' VALUESLESS THAN  (TO_DATE(' || '''' ||
       '2024-1-1' || '''' || ',' || '''' ||'YYYY-MM-DD' || '''' || ')) ' ||
       'TABLESPACE EMRP52023 ;'
  from (select distinct table_name
          from dba_tab_partitions
         where table_owner = 'EMRP用户名') tab_par;

注意：此处若无法查询出分区脚本，可以将EMRP用户名中的英文改成大写字母后尝试执行！为原有OWNER为EMRP50的表，建立新的分区，并使用新的分区指定到分区的隔离时间，执行文件

步骤5.执行分区脚本并校验

复制查询出来的语句，执行！切换至EMRP50对应的有权限的用户，否则报错

--示例文件
1	ALTER TABLE RCD_OUT_RECORD_DATA ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
2	ALTER TABLE RCD_TEMPERATURE_SPECIAL_DATA ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
3	ALTER TABLE RCD_TEMPERATURE_LIFESIGNDATA ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
4	ALTER TABLE QCM_CRISIS_SCOREDETAIL ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
5	ALTER TABLE DIAG_WESTERN_DIAGNOSE ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
6	ALTER TABLE RCD_NURSE_RECORD_DATA ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
7	ALTER TABLE RCD_INPATIENT_RECORD ADD PARTITION DATE2023 VALUES LESS THAN  (TO_DATE('2024-1-1','YYYY-MM-DD')) TABLESPACE EMRP52023 ;
8

校验分区表是否成功，在窗口中执行完分区脚本，选几个表校验下是否成功。