ExaGridDba

Rename LOB is an expensive operation

Jun 26th, 2016
163
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.74 KB | None | 0 0
  1. [oracle@stormking cdb12102 lob]$ cat rename.lob.seg.sql
  2. @ conn.pdbm.u.sql
  3. set serveroutput on
  4. set verify off
  5.  
  6. drop table ltbl;
  7. create table ltbl
  8. (
  9. n number,
  10. l clob
  11. );
  12. insert into ltbl ( n, l ) values ( 0 , 'A' );
  13. commit;
  14.  
  15. @ lob.seg.details.sql U LTBL L
  16. alter table U.LTBL move lob(L) store as LSEG;
  17. @ lob.seg.details.sql U LTBL L
  18.  
  19. quit
  20. [oracle@stormking cdb12102 lob]$ cat lob.seg.details.sql
  21. column segment_name format a30
  22. set linesize 200
  23. set trimspool on
  24.  
  25. define owner=&&1
  26. define tablename=&&2
  27. define columnname=&&3
  28.  
  29. select l.segment_name, o.object_id, o.data_object_id, e.block_id
  30. from dba_lobs l
  31. join dba_objects o on o.owner = l.owner and o.object_name = l.segment_name
  32. join dba_extents e on e.owner = o.owner and e.segment_name = l.segment_name
  33. where l.owner = '&&owner'
  34. and l.table_name = '&&tablename'
  35. and l.column_name = '&&columnname'
  36. and o.object_type = 'LOB'
  37. and e.partition_name is null;
  38. [oracle@stormking cdb12102 lob]$ sqlplus /nolog @ rename.lob.seg.sql
  39.  
  40. SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 26 11:59:12 2016
  41.  
  42. Copyright (c) 1982, 2014, Oracle. All rights reserved.
  43.  
  44. Connected.
  45.  
  46. Table dropped.
  47.  
  48.  
  49. Table created.
  50.  
  51.  
  52. 1 row created.
  53.  
  54.  
  55. Commit complete.
  56.  
  57.  
  58. SEGMENT_NAME OBJECT_ID DATA_OBJECT_ID BLOCK_ID
  59. ------------------------------ ---------- -------------- ----------
  60. SYS_LOB0000097896C00002$$ 97897 97897 740640
  61.  
  62.  
  63. Table altered.
  64.  
  65. The data object id and the block id have changed, meaning that the LOB segment has moved:
  66.  
  67. SEGMENT_NAME OBJECT_ID DATA_OBJECT_ID BLOCK_ID
  68. ------------------------------ ---------- -------------- ----------
  69. LSEG 97897 97900 740696
Advertisement
Add Comment
Please, Sign In to add comment