Freitag, 1. September 2017

SecureFile LOB - move online in 12.2

Today we had a "new features in 12.2" workshop. Thank you Mathias Zarick for this great event.
One of the new features was Online Table Move. But the doc was quite unspecific if this also includes LOBs:
Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.
Data maintenance on nonpartitioned tables does not require any maintenance window since it does not impact any DML or query operation.
So it's worth a test-case in my 12.2 sandbox:

There are 2 tables (test_blobs and test_blobs2) of this structure:
create table test_blobs (
        id              number(5),
  comments        varchar2(80),
        file_content    blob
) 
SEGMENT CREATION IMMEDIATE 
NOCOMPRESS 
NOPARALLEL 
TABLESPACE  USERS
lob (file_content) store as SECUREFILE(
        disable storage in row
        chunk 16K
        tablespace users
)
;

test_blobs2 has 2 rows (id 1 & 2) with different blobs.

Session 1:

20:58:57 SQL> update test_blobs set file_content=(select file_content from test_blobs2 where id=1);                                                                                                                     

1 row updated.
         

Session 2:

20:58:58 SQL>                                                                                                                                                                                                           
                                                                                                                                                                                                                        
ALTER TABLE test_blobs MOVE LOB(file_content) 
20:59:09 SQL> 20:59:09   2    STORE AS ( TABLESPACE users2) online;                                                                                                                                                     
                                                                                                                                                                                                                        
               

Session 1:

20:59:10 SQL> commit;                                                                                                                                                                                                   

Commit complete.   


Session 2:

Table altered.
                                                                                                                                                
20:59:18 SQL> 

Here the ALTER TABLE ... MOVE LOB ... STORA AS (TABLESPACE ...) ONLINE works.
That's very good news!

The Segment is really there:
21:02:33 SQL> select segment_name, tablespace_name from dba_segments where owner='BERX' and segment_name in                                                                                                             
21:02:34   2  (select segment_name from dba_lobs where table_name='TEST_BLOBS' and column_name='FILE_CONTENT');                                                                                                         

SEGMENT_NAME                             TABLESPACE_NAME
---------------------------------------- ------------------------------
SYS_LOB0000058821C00003$$                USERS2

And the move just waited until the row lock completed.

I really would like to have this feature in 12.1, but it's good to know it will come to my DBs some-when in the future.
Kommentar veröffentlichen