首页 » ORACLE 9i-23ai » 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 都会级联的影响到。

打赏

对不起,这篇文章暂时关闭评论。