首页 » ORACLE 9i-23c » Oracle有没有可能move Segment对象到指定的数据文件在同一个表空间?
Oracle有没有可能move Segment对象到指定的数据文件在同一个表空间?
在asktom上也有相同的问题How to move contents data from one datafile to another? 和move table from one datafile to another datafile in same tablespace. 试想一种场景：如果一个tablespace有100个数据文件， 90%+表空间USED，后来drop table 70%, 现在因为ASM DISKGROUP 紧张，要回收部分datafile（drop）归还ASM 给其它TABLESPACE使用 , 原表空间move table/lob 都是按oracle内部算法find free extent 从所有可用datafile(bitmap in header for LMT )， 目前除了新扩存储空间，move到新表空间还有其他方法吗？这是一个难题。
正常的做法：
a) create a new tablespace with however many datafiles you want
b) move the contents of this tablespace into that new one
c) drop the old tablespace
d) rename the new tablespace back to the old name.
其它做法：
1， 查表空间上完全没有数据对象的datafile,从表空间删除
2， 查找数据文件的HWM, resize datafile to HWM
3, 查看数据文件上free空间最多，所包含的对象大小，小于某个阀值(如5G)，move到其它表空间临时，从表空间删除 datafile
4， 查找datafile 尾部的对象，小于某个阀值(如5G)，move到其它表空间临时，resize datafile to HWM
5， 新建一个对象（占位），手动allocate extent(size xx datafile xx),或DML把free空间较大的数据文件占有，再次原表空间MOVE 对象，再扩展该对象，再MOVE 原有对象，如次循环，datafile除了新建临时对象无其它业务对象后，drop占位对象，从表空间删除 datafile
做起来实际上还会有很多麻烦，如虽然一个datafile上就几百M的extent, 但move的对象粒度是segment, 很有可能这个extent的segment有上TB。 而且无指定move 的对象到某个datafile上，顺序也并不是按file_id 先后顺序使用，oracle还是尽可能的按extent分散不同的datafile提高并发能力。这有点像ORACLE ASM,但又没有ASM的drop ASM DISK, 在线reblance的功能。前几天在《如何Onine Move LOB段到其它表空间在Oracle 12c+ ?》记录过Online move不同的表空间, 但是在同一个表空间move 从一个datafile move到另一个datafile是没有简单的办法的， 下面记录一些测试中的问题。
move online 顺序使用?
SYS@orcl1>create table anbob.test_clob(
id1 number,
clob_col clob);
Table created.
insert into anbob.test_clob values (1,rpad('a',32760,'a'));
SYS@orcl1>declare
large_string clob := rpad('c',32760,'c');
begin
update anbob.test_clob set clob_col = nvl(large_string,clob_col) where id1 = 1;
commit;
end;
/
SYS@orcl1>@lob ANBOB.TEST_CLOB
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------ ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
ANBOB TEST_CLOB CLOB_COL SYS_LOB0000074091C00002$$ USERS SYS_IL0000074091C00002$$
SYS@orcl1>select * from dba_extents where segment_name='SYS_LOB0000074091C00002$$';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------------------------- -------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
ANBOB SYS_LOB0000074091C00002$$ LOBSEGMENT USERS 0 14 144 131072 16 14
ANBOB SYS_LOB0000074091C00002$$ LOBSEGMENT USERS 1 22 4736 65536 8 22
ANBOB SYS_LOB0000074091C00002$$ LOBSEGMENT USERS 2 14 256 1048576 128 14
SYS@orcl1>ALTER TABLE ANBOB.TEST_CLOB MOVE LOB( CLOB_COL) STORE AS clob_sec_lob (TABLESPACE users) online ;
Table altered.
SYS@orcl1>@lob ANBOB.TEST_CLOB
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION W FORMAT PARTITION SECUREFIL SEGMENT_C RETENTION_TYPE RETENTION_VALUE
------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------------------------ --------------------- ------------ ------------------ ------------------------------- --------------------------------------------- --------- --------- --------- --------------------- ---------------
ANBOB TEST_CLOB CLOB_COL CLOB_SEC_LOB USERS 074091C00002$$ 8192 NO YES NO NO NO ENDIAN NEUTRAL NO YES YES DEFAULT
SYS@orcl1>@seg anbob.CLOB_SEC_LOB
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------------ ---------- ---------- ----------
1 ANBOB CLOB_SEC_LOB LOBSEGMENT USERS 144 14 153
SYS@orcl1>select * from dba_extents where segment_name='CLOB_SEC_LOB';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ -------------------------- ---------------------- ----------------- ---------- ---------- ---------- ---------- ------------
ANBOB CLOB_SEC_LOB LOBSEGMENT USERS 0 14 152 131072 16 14
ANBOB CLOB_SEC_LOB LOBSEGMENT USERS 1 24 256 1048576 128 24
Note:
开始test_clob的LOB有三个extent ,分布在14#和24# 文件上， move online后LOB段为2个extent在14#和24# 文件上，而table段头在14# 文件上.但看block_id,其实位置是移动了的。
There are three structures for internal LOBs:
kolbl: LOB Locator (20 bytes)
kdlinode: LOB Inode (16 bytes minimum)
Data array
如果总长度<4000bytes, 没有禁用inlink, lob是in-line存在表段，否则只有kolbl和kdlinode in-link， dat是在独立的LOB段.
SYS@orcl1>@ls users TABLESPACE_NAME FILE_ID EXT MB MAXSZ FILE_NAME ------------------------------ ---------- --------- ---------- ---------- --------------------------------------------------------------------------------------- USERS 14 YES 18.75 32767.98 +DATA/ORCL/0967ADEB02367ECCE0637F1614AC855D/DATAFILE/users.283.1152130493 USERS 22 NO 300 +FRA/ORCL/0967ADEB02367ECCE0637F1614AC855D/DATAFILE/users.306.1158178721 USERS 24 NO 100 +FRA/ORCL/0967ADEB02367ECCE0637F1614AC855D/DATAFILE/users.319.1158339987 SYS@orcl1>@seg anbob.CLOB_SEC_LOB SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------------ ---------- ---------- ---------- 1 ANBOB CLOB_SEC_LOB LOBSEGMENT USERS 144 14 153 SYS@orcl1>@seg anbob.TEST_CLOB SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------------ ---------- ---------- ---------- 0 ANBOB TEST_CLOB TABLE USERS 8 14 202
Note:
表和LOB小于2M，也并没有全放在最小的文件14#上,可见free extent的使用并没有按文件号从小到大顺序。
预分配extent到指定文件?
SQL>show parameter segment PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ --------------------------------- ---------------------------------------------------------------------------- deferred_segment_creation boolean TRUE rollback_segments string transactions_per_rollback_segment integer 5 SYS@orcl1>create table anbob.test_clob1 as select * from anbob.test_clob where 1=0; Table created. SYS@orcl1>@seg anbob.test_clob1 no rows selected ---no segment -- 22# datafile SYS@orcl1>alter table anbob.test_clob1 allocate extent (size 5m datafile '+FRA/ORCL/0967ADEB02367ECCE0637F1614AC855D/DATAFILE/users.306.1158178721'); Table altered. SYS@orcl1>SYS@orcl1>@lob anbob.TEST_CLOB1 OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION W FORMAT PARTITION SECUREFIL SEGMENT_C RETENTION_TYPE RETENTION_VALUE --------------- -------------------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------------------------ --------------------- ------------ ------------------ ------------------------------- --------------------------------------------- --------- --------- --------- --------------------- --------------- ANBOB TEST_CLOB1 CLOB_COL SYS_LOB0000074172C00002$$ USERS 074172C00002$$ 8192 NO YES NO NO NO ENDIAN NEUTRAL NO YES YES DEFAULT SYS@orcl1>@seg anbob.SYS_LOB0000074172C00002$$ SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ----------------------------- ---------- ---------- ---------- 0 ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 16 14 209 SYS@orcl1>@seg anbob.TEST_CLOB SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ----------------------------- ---------- ---------- ---------- 5 ANBOB TEST_CLOB1 TABLE USERS 648 14 138 0 ANBOB TEST_CLOB TABLE USERS 8 14 202 SYS@orcl1> insert into anbob.test_clob1 select * from anbob.test_clob; 1 row created. SYS@orcl1>select * from dba_extents where segment_name='TEST_CLOB1'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO -------------- ------------------- ------------------------ ----------------- ---------- ---------- ---------- ---------- ------------ ANBOB TEST_CLOB1 TABLE USERS 0 14 136 65536 8 14 ANBOB TEST_CLOB1 TABLE USERS 1 22 128 1048576 128 22 ANBOB TEST_CLOB1 TABLE USERS 2 22 256 1048576 128 22 ANBOB TEST_CLOB1 TABLE USERS 3 22 384 1048576 128 22 ANBOB TEST_CLOB1 TABLE USERS 4 22 512 1048576 128 22 ANBOB TEST_CLOB1 TABLE USERS 5 22 640 1048576 128 22 6 rows selected. SYS@orcl1>@lob anbob.test_clob1 OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION W FORMAT PARTITION SECUREFIL SEGMENT_C RETENTION_TYPE RETENTION_VALUE ------------- ------------------- ------------------------------ ------------------------------ ------------------------------------------- ---------- ---------- ---------- ---------- ------------------------------ --------------------- ------------ ------------------ ------------------------------- --------------------------------------------- --------- --------- --------- --------------------- --------------- ANBOB TEST_CLOB1 CLOB_COL SYS_LOB0000074172C00002$$ USERS 074172C00002$$ 8192 NO YES NO NO NO ENDIAN NEUTRAL NO YES YES DEFAULT SYS@orcl1>select * from dba_extents where segment_name='SYS_LOB0000074172C00002$$'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------- ------------------------------ ------------------- ----------------- ---------- ---------- ---------- ---------- ------------ ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 0 14 208 131072 16 14 ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 1 22 4736 65536 8 22 ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 2 14 256 1048576 128 14
Note:
预创建了延迟段创建的表，手动allocate extent到指定文件，但是table段头segment header 块区并没有按我们按我们分配的文件上创建, LOB段也不会.
创建表指定file_id？
select * from BOOTSTRAP$ ;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE PROXY_ROLE_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"ROLE#" NUMBER NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 25 EXTENTS (FILE 1 BLOCK 264))
SYS@orcl1>create table anbob.test_clob2(
id1 number )
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 74177 EXTENTS (FILE 24 block 394 ) ) 2 3 ;
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 74177 EXTENTS (FILE 24 block 394 ) )
*
ERROR at line 3:
ORA-00900: invalid SQL statement
SYS@orcl1>create table anbob.test_clob2(
id1 number )
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 EXTENTS (FILE 24 block 394 ) ) 2 3 ;
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 EXTENTS (FILE 24 block 394 ) )
*
ERROR at line 3:
ORA-00900: invalid SQL statement
SYS@orcl1> create table anbob.test_clob2(
id1 number ) tablespace system
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 EXTENTS (FILE 10 block 38020 ) ) 2 3 ;
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 EXTENTS (FILE 10 block 38020 ) )
*
ERROR at line 3:
ORA-00900: invalid SQL statement
Note:
参考BOOTSTRAP$ 创建表指定file id 语法也是不允许的。
MOVE table段会级联move lob段吗？
SYS@orcl1>@lob anbob.test_clob1 OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION IN_ROW FORMAT PARTITION SECU REFIL SEGMENT_C RETENTION_TYPE RETENTION_VALUE ------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ ----------------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------------------------ --------- ------------ ------------ ------------------ --------------------------------------------- --------- --------------------------------------------- --------- ---- ----- --------- --------------------- --------------- ANBOB TEST_CLOB1 CLOB_COL SYS_LOB0000074172C00002$$ USERS SYS_IL0000074172C00002$$ 8192 NO YES NO NO NO YES ENDIAN NEUTRAL NO YES YES DEFAULT SYS@orcl1>select * from dba_extents where segment_name='SYS_LOB0000074172C00002$$'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ------------------------------ ------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 0 14 208 131072 16 14 ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 1 22 4736 65536 8 22 ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 2 14 256 1048576 128 14 SYS@orcl1>alter table anbob.test_clob1 move online; Table altered. SYS@orcl1>select * from dba_extents where segment_name='SYS_LOB0000074172C00002$$'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ------------------------------ ------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 0 14 240 131072 16 14 ANBOB SYS_LOB0000074172C00002$$ LOBSEGMENT USERS 1 24 128 1048576 128 24
Note:
可见只是move了table 段，但是lob段的extent的个数和位置都发生了改变。
MOVE LOB段会级联move table段吗？
SYS@orcl1>@lob anbob.test_clob OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION IN_ROW FORMAT PARTITION SECUREFIL SEGMENT_C RETENTION_TYPE RETENTION_VALUE ------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------------------------ --------------------- ------------ ------------------ --------------------------------------------- --------- --------------------------------------------- --------- --------- --------- --------------------- --------------- ANBOB TEST_CLOB CLOB_COL CLOB_SEC_LOB USERS SYS_IL0000074091C00002$$ 8192 NO YES NO NO NO YES ENDIAN NEUTRAL NO YES YES DEFAULT SYS@orcl1>select * from dba_extents where segment_name='TEST_CLOB'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ANBOB TEST_CLOB TABLE USERS 0 14 200 65536 8 14 SYS@orcl1>ALTER TABLE ANBOB.TEST_CLOB MOVE LOB( CLOB_COL) STORE AS clob_sec_lob (TABLESPACE users) online ; Table altered. SYS@orcl1>select * from dba_extents where segment_name='TEST_CLOB'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ANBOB TEST_CLOB TABLE USERS 0 22 152 65536 8 22
Note:
虽然只是move了表上一个lob段，结果table的位置都发了改变，从14号文件移动到了22号文件，这也是为什么不加online会有不相于索引会失效的原因。 如果表上有多个lob会怎么样？不再测试
Summary:
当前oracle不允许在同一个表空间内，使用move命令，把数据对象从datafile（数据文件）的move到指定的datafile（数据文件）上。手动allocate extent预分配区再insert导入，但是段头目前没有办法手动指定数据文件。 另外move table 或lob 都会级联的影响到。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。