This week I was asked a question about ALTER TABLE … MOVE in relation to LOB and XML segments. I thought I knew the answer, but wasn’t 100% confident. After some testing I found that it didn’t behave as I thought. Here’s a summary of how this command works.
Begin by creating some tablespaces to work with, and a table with a normal data segment, a LOB segment and an XML segment.
CREATE TABLESPACE ts1
/
CREATE TABLESPACE ts2
/
CREATE TABLESPACE ts3
/
CREATE TABLESPACE ts4
/
CREATE TABLE t_test
( col_vc2 VARCHAR2(4000)
, col_clob CLOB
, col_xml XMLTYPE
)
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE t_test_clob (TABLESPACE ts2 DISABLE STORAGE IN ROW RETENTION CACHE)
XMLTYPE COLUMN col_xml STORE AS SECUREFILE BINARY XML t_test_xml (TABLESPACE ts3 DISABLE STORAGE IN ROW RETENTION CACHE)
/
INSERT INTO t_test VALUES
( lpad('X', 4000, 'X')
, lpad('X', 4000, 'X')
, xmlelement("X", lpad('X', 4000, 'X'))
)
/
COMMIT
/
Check where the segments have been created:
SELECT segment_name
, tablespace_name
, header_file
, header_block
FROM dba_segments
WHERE segment_name LIKE 'T_TEST%'
ORDER BY 1
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST TS1 9 130
T_TEST_CLOB TS2 10 129
T_TEST_XML TS3 11 129
Now let’s move the table, without giving any further specification:
ALTER TABLE t_test MOVE
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST TS1 9 162
T_TEST_CLOB TS2 10 129
T_TEST_XML TS1 9 137
From this it can be seen that the table data segment has moved within TS1 as you would expect. However the LOB segment has not moved; it is still in TS2 at the same position. What is more interesting is that the XML segment has moved to TS1, the same as the table data segment!
The next test is to specify the tablespace for the move:
ALTER TABLE t_test MOVE
TABLESPACE ts4
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST TS4 12 154
T_TEST_CLOB TS2 10 129
T_TEST_XML TS4 12 129
Again, the XML segment has followed the table data segment and the LOB segment is untouched. Leaving the XML for now, let’s make sure the LOB segment moves:
ALTER TABLE t_test MOVE
TABLESPACE ts4
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts4)
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST TS4 12 178
T_TEST_CLOB TS4 12 129
T_TEST_XML TS4 12 153
So to move the LOB segment(s) associated with a table, you must specify them explicitly. Let’s see if we can control the XML segment too. Here we will try to move all the segments within their original tablespaces:
ALTER TABLE t_test MOVE
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts2)
LOB (col_xml) STORE AS SECUREFILE (TABLESPACE ts3)
/
ORA-00904: "COL_XML": invalid identifier
As might be expected, we cannot refer to the XML LOB segment using its column name, we need to use the internal column name (for example, by querying DBA_LOBS).
ALTER TABLE t_test MOVE
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts2)
LOB (SYS_NC00004$) STORE AS SECUREFILE (TABLESPACE ts3)
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST TS1 9 138
T_TEST_CLOB TS2 10 161
T_TEST_XML TS3 11 161
So in summary:
- LOB segments will not move by default; you need to be explicit if you want to include them in the move.
- XML segments are inconsistent in this respect, by default they move to the table data segment’s tablespace (whether specified or not); you must be explicit to ensure they will remain in the correct tablespace (but they will always move).
Platform Information
The material in this article was most recently tested against Oracle 11.2.0.3.6 on 64-bit Linux.
Mike Pargeter 