oracle LOB 大对象的表Move方法

本文介绍了Oracle数据库中LOB类型数据的管理方法,包括如何通过ALTER TABLE MOVE语法迁移表及其LOB数据到新表空间,以及调整LOB存储参数以优化性能和空间使用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

       对于LOB型的表,数据量比较大的话,比如单个表的大对象有6G以上的话,使用导入和导出的方法是比较浪费时间的,并且不划算。本人测试过在P4 2.4G 1G内存 的一般工作站使用imp/dmp方法操作的话,需要4个小时。在Dell1950 的标准配置的服务器上进行单个表数据的导入,16G的数据花了5小时。使用alter table xx deallocate unused 或alter table xx deallocate unused keep 0 或alter tablespace xxx deallocate unused,都无法把表空间的利用率下降。除非是truncate table xxxx;   
 
      本人是刚接触oracle的,把自己的测试结果写出来而已。见笑了。
   以下是摘录自网上的文章,到现在暂时还没有做过。

在移植看注意研究了下ORACLE ALTER TABLE MOVE 的语法:

       ALTER TABLE table_name MOVE [ONLINE] tablespace_name;
通过上面的语句可以移植表到新表空间,

如果要移植LOB字典需要参考以下语法:
ALTER TABLE table_name LOB (lob_item) STORE AS [lob_segment]
      (
        TABLESPACE tablespace_name
                   (STORAGE.....)
           ENABLE|DISABLE STORAGE IN ROW
           CHUNK integer
           PCTVERSION integer
            RETENTION
            FREEPOOLS integer
            CACHE|NOCACHE|CACHE READS
           INDEX lobindexname

(TABLESPACE tablesapce_name

 ((STORAGE.....))
)

....

注解:

LOB (lob_item):表中的lob字段
STORE AS [lob_segment]:
每个lob字段在表创建后系统都会自动单独创建一个段,可以通过这个参数手动指定一个段名
   tablespace_name:LOB
字段新的存储表空间
(STORAGE.....):
指定tablespace_name的存储属性
    ENABLE STORAGE IN ROW:
如果设置了enable storage in row 那么oracle会自动将小于4000bytes的数据存储在行内, 这是ORACLE的默认值,
对于大于4000字节的lob字段保存在lob段(同disable storage in row),在表段将保留36-84字节的控制信息。对于disable storage in rowOraclelob字段分开保存在lob段中,而仅仅在行位置保留20字节的指针。对于相当于disable storage in row的这部分(也就是单独保存在LOB段的这部分数据),UNDO仅仅是记录指针与相关lob索引改变,如果发生更新操作等DML操作,原始数据将保留在LOB段。

    DISABLE STORAGE IN ROW:
如果DISABLE这个属性,那么lob数据会在行外存储,行内只存储该lob值得指针,而且这个属性在表
创建后只能在MOVE表时才可以被改变
    CHUNK:
是一个很特别的属性,对一次LOB数据的操作(插入或更新),因该分配多少存储空间,指定的值最好是数据库块的倍数,而且指定的值不能大于表空间区间中NEXT的值, 要不然ORACLEreturn一个错误,如果以前已经设置这个值了,那么在后期指定的值是不能被改变的。

storage as ( CHUNK bytes )表示对于disable storage in row的这部分,最小的LOB块的大小,必须是数据库块(DB_BLOCK_SIZE)的整数倍。一个chunk最多只保留一行LOB数据,也就是说,如果你设置了32KCHUNK,但是如果LOB字段大小只有4K,也将占用32K的空间

storage as(cache|nocahce)表示是否允许lob段经过buffer cache并缓存。默认是nocache,表示直接读与直接写,不经过数据库的data buffer所以,默认情况下,对于单独保存在LOB段的这部分数据,在发生物理读的时候,是直接读,如direct path read (lob)

storage as(nocache logging |nocache nologging)logging/nologging属性只对nocache方式生效,默认是logging,如果是nologging方式,对于 保存在行外的log部分,在updateDML操作时将不记录redo日志。

    PCTVERSION integerRETENTION:都是ORACLE用来管理LOB字段镜像数据的。在LOB 数据的更新过程中,
ORACLE
没有用UNDO TABLESPACE空间,而是从LOB字段所在的表空间里划分一段空间来做镜像空间的,
这个空间的大小由PCTVERSION参数控制,默认值为10,代表划分表空间的10%作为镜像空间,
每个镜像空间的单元大小由CHUNK参数指定,pctversion可以使用在manual undo modeautomatic undo mode 环境中.
retention
应用了automatic undo mode中的undo_retention通过时间来管理lob镜像空间.
pctversion
retention不能同时被指定.建议数据库在automatic undo mode下使用retention参数。
FREEPOOLS integer:
LOG segment指定free list.RAC环境下integer为实例的个数.单实例环境下为1.automatic undo modeoracle默认采用
FREEPOOLS
来管理空闲块列表。除非我们在表的storage配置中指定了freelist groups参数.
CACHE|NOCACHE|CACHE READS:
指定lob块是否在database buffer中缓存.
INDEX lobindexname (TABLESPACE tablesapce_name ((STORAGE.....):
lob列指定索引存储参数
举例:
SQL> show parameter db_create_file_dest

SQL> create tablespace test datafile size 100M autoextend off;
SQL> create table test(a varchar2(100), b clob, d blob) pctfree 10 tablespace test;

SQL> desc test
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name='TEST';

我们发现每个LOB字段单独有一个LOGSEGMENTLOBINDEX;

SQL> set linesize 200
col table_name format a5
col column_name format a5
SELECT b.table_name,
a.segment_name,
b.index_name,
a.segment_type,
b.column_name,
a.tablespace_name,
b.chunk,
b.cache,
b.freepools,
b.pctversion,
b.retention
FROM dba_segments a,dba_lobs b
WHERE a.segment_name = b.segment_name
AND a.tablespace_name = 'TEST'

/

SQL>

从上面的结果我们可以观察到LOB字段的各个属性.
下面我们对LOB字段move到另一个表空间

SQL> create tablespace lob_test datafile size 100M autoextend off;

SQL> ALTER TABLE TEST MOVE LOB(B) STORE AS TEST_B (
TABLESPACE lob_test
DISABLE STORAGE IN ROW
CHUNK 16384
RETENTION
FREEPOOLS 1
NOCACHE);

SQL> ALTER TABLE TEST MOVE LOB(D) STORE AS TEST_D (
TABLESPACE lob_test
DISABLE STORAGE IN ROW
CHUNK 16384
RETENTION
FREEPOOLS 1
NOCACHE);
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name='TEST';

SQL> set linesize 200
col table_name format a5
col column_name format a5
SELECT b.table_name,
a.segment_name,
b.index_name,
a.segment_type,
b.column_name,
a.tablespace_name,
b.chunk,
b.cache,
b.freepools,
b.pctversion,
b.retention
FROM dba_segments a,dba_lobs b
WHERE a.segment_name = b.segment_name
AND a.tablespace_name = 'LOB_TEST'
/SQL>

在一些复杂情况下可能需要连表一起移植
alter table table_name move [tablespace_name] lob (lob_item) store as [lobsegmentname] (tablespace tablespace_name.....);
移植分区中lob
alter table table_name move partition [partition_name] lob (lob_item) store as [logsegmentname] (tablespace_name.....);
移植分区表
alter table table_name move partition [partition_name] tablespace_name lob (lob_item) store as [logsegmentname] (tablespace_name.....);
如果不需要修改lobsegmentname,可以同时移植多个列
alter table table_name move lob (lob_item1,lob_item2,lob_item3...) store as [lobsegmentname] (tablespace tablespace_name.....);

 

    LOB段也可以利用move来重整数据,以下的语句会将表与lob字段move到指定的表空间:

   alter table table_name move [tablespace tbs_name]

lob(lob_field1,lob_field2) store as (tablespace new_tbs_name);

如果LOB字段在分区表中,则增加partition关键字,如

   alter table table_name move [partition partname] [tablespace tbs_name]

lob(field) store as (tablespace new_tbs_name);

 

在数据库中合理的存储LOB,不仅可以提升性能,而且还可以有效的管理存储空间.

<think>好的,用户问的是Oracle中如何收缩含有大字段(LOB)的,也就是使用SHRINK命令处理有大字段的。首先,我得回忆一下Oracle的SHRINK SPACE操作。记得这个命令是用来回收碎片和释放未使用空间的,但LOB字段的处理可能和普通不同。 首先,要确认用户是否了解SHRINK的基本使用,比如普通的收缩步骤。可能需要先介绍常规操作,再讲LOB的特殊情况。因为LOB数据通常存储在单独的段中,可能使用ASSM(自动段空间管理),所以收缩时可能需要分开处理。 接下来,考虑用户可能的场景:他们的有大量LOB数据,导致空间占用过大,想要通过收缩来释放空间。但LOB字段可能无法像普通数据那样轻易移动,需要特定的方法,比如开启ROW MOVEMENT,或者单独处理LOB段。 然后,需要检查Oracle的版本是否支持LOB段的收缩。可能从10g或11g开始支持?需要确认具体的版本要求。另外,ALTER TABLE SHRINK SPACE CASCADE是否会自动处理LOB段,或者是否需要单独对LOB列进行操作。 还可能需要注意,收缩操作需要支持行移动,并且可能引起索引的重建,这会导致锁,影响业务。所以需要提醒用户在维护窗口操作,并做好备份。 另外,要提到DBA_LOBS视图,用来查询LOB字段所在的空间和段信息,帮助用户确认LOB存储位置。可能还需要使用DBMS_REDEFINITION在线重定义,如果直接收缩不可行的话。 用户可能遇到的问题包括:收缩后空间未释放,可能是因为LOB数据未被有效处理,或者HWM(高水位线)未调整。需要解释HWM的概念,以及如何通过MOVE或SHRINK来降低HWM。 最后,整理步骤:先开启行移动,收缩级,再处理LOB段,可能需要CASCADE参数,或者单独执行ALTER TABLE MODIFY LOB (...) (SHRINK SPACE)。还要提醒空间是否返回给空间,取决于存储参数和是否启用了自动扩展。 需要确保回答结构清晰,分步骤说明,注意事项和可能的问题,帮助用户全面理解操作过程和潜在风险。</think>在Oracle数据库中,对大字段(LOB进行空间收缩需要特殊处理。以下是分步骤说明: ### 1. 收缩前的准备工作 - **开启行移动**(Row Movement): ```sql ALTER TABLE 名 ENABLE ROW MOVEMENT; ``` 这是SHRINK操作的前提条件,允许Oracle物理重组行位置。 - **确认LOB存储类型**: ```sql SELECT table_name, column_name, tablespace_name, segment_name FROM dba_lobs WHERE table_name = '名'; ``` 确认LOB字段是否存储在独立段(BASICFILE/SECUREFILE)中。 ### 2. 普通收缩(非LOB字段) ```sql ALTER TABLE 名 SHRINK SPACE COMPACT; -- 仅碎片整理 ALTER TABLE 名 SHRINK SPACE; -- 释放空间并重置HWM ``` ### 3. LOB字段的特殊处理 - **方法1:级联收缩**(CASCADE) ```sql ALTER TABLE 名 SHRINK SPACE CASCADE; ``` 该命令会级联收缩: - 段(TABLE) - 索引段(INDEX) - LOB段(LOBSEGMENT) - LOB索引段(LOBINDEX) - **方法2:单独收缩LOB列** ```sql ALTER TABLE 名 MODIFY LOB(LOB字段名) (SHRINK SPACE); ``` ### 4. 验证收缩效果 ```sql SELECT segment_name, bytes/1024/1024 MB FROM dba_segments WHERE segment_name IN ('名', 'LOB段名'); ``` ### 注意事项 1. **版本兼容性**: - 需要Oracle 11g及以上版本才支持LOB字段的SHRINK操作 - SECUREFILE类型LOB支持更高效的压缩 2. **锁机制**: - 操作期间会获取级排他锁(TM) - 建议在维护窗口操作 3. **空间回收限制**: - 无法回收已分配给空间的未使用空间(需配合数据文件resize操作) - LOB字段的碎片化空间可能无法完全回收 4. **性能影响**: - 会产生大量重做日志(建议开启NOLOGGING模式) - 可能触发全扫描和行迁移 ### 替代方案 对于超大型LOB,推荐使用: ```sql ALTER TABLE MOVE TABLESPACE 新空间 LOB(字段名) STORE AS (TABLESPACE LOB空间); ``` 通过物理迁移重组存储结构,更彻底地回收空间。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值