Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- [oracle@stormking cdb12102 lob]$ cat rename.lob.seg.sql
- @ conn.pdbm.u.sql
- set serveroutput on
- set verify off
- drop table ltbl;
- create table ltbl
- (
- n number,
- l clob
- );
- insert into ltbl ( n, l ) values ( 0 , 'A' );
- commit;
- @ lob.seg.details.sql U LTBL L
- alter table U.LTBL move lob(L) store as LSEG;
- @ lob.seg.details.sql U LTBL L
- quit
- [oracle@stormking cdb12102 lob]$ cat lob.seg.details.sql
- column segment_name format a30
- set linesize 200
- set trimspool on
- define owner=&&1
- define tablename=&&2
- define columnname=&&3
- select l.segment_name, o.object_id, o.data_object_id, e.block_id
- from dba_lobs l
- join dba_objects o on o.owner = l.owner and o.object_name = l.segment_name
- join dba_extents e on e.owner = o.owner and e.segment_name = l.segment_name
- where l.owner = '&&owner'
- and l.table_name = '&&tablename'
- and l.column_name = '&&columnname'
- and o.object_type = 'LOB'
- and e.partition_name is null;
- [oracle@stormking cdb12102 lob]$ sqlplus /nolog @ rename.lob.seg.sql
- SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 26 11:59:12 2016
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- Connected.
- Table dropped.
- Table created.
- 1 row created.
- Commit complete.
- SEGMENT_NAME OBJECT_ID DATA_OBJECT_ID BLOCK_ID
- ------------------------------ ---------- -------------- ----------
- SYS_LOB0000097896C00002$$ 97897 97897 740640
- Table altered.
- The data object id and the block id have changed, meaning that the LOB segment has moved:
- SEGMENT_NAME OBJECT_ID DATA_OBJECT_ID BLOCK_ID
- ------------------------------ ---------- -------------- ----------
- LSEG 97897 97900 740696
Advertisement
Add Comment
Please, Sign In to add comment