How to Move Lob Segment to another Tablespace?




SQL> desc SCHEDULER$_JOB_OUTPUT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOG_ID                                             NUMBER
 ERRORS                                             BLOB
 OUTPUT                                             BLOB






set linesize 300
col OWNER         for a20
col TABLE_NAME    for a30
col SEGMENT_NAME  for a30
select e.owner,l.table_name,l.segment_name,e.TABLESPACE_NAME,l.TABLESPACE_NAME Lob_tablespace
from dba_extents e, dba_lobs l
where e.owner = l.owner
and e.segment_name = l.segment_name
and e.segment_type = 'LOBSEGMENT'
--and l.segment_name like 'SYS_LOB0000008917C00003$$'
and TABLE_NAME='SCHEDULER$_JOB_OUTPUT'
;


OWNER                TABLE_NAME                     SEGMENT_NAME                   TABLESPACE_NAME                LOB_TABLESPACE
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                  SCHEDULER$_JOB_OUTPUT          SYS_LOB0000008919C00002$$      SYSAUX                         SYSAUX
SYS                  SCHEDULER$_JOB_OUTPUT          SYS_LOB0000008919C00003$$      SYSAUX                         SYSAUX



alter table sys.SCHEDULER$_JOB_OUTPUT move lob(ERRORS) store as (tablespace USERS) parallel 16 update indexes;


alter table sys.SCHEDULER$_JOB_OUTPUT move lob(ERRORS) store as (tablespace USERS) parallel 16 update indexes;
Table altered.

alter table sys.SCHEDULER$_JOB_OUTPUT move lob(OUTPUT) store as (tablespace USERS) parallel 16 update indexes;
Table altered.



OWNER                TABLE_NAME                     SEGMENT_NAME                   TABLESPACE_NAME                LOB_TABLESPACE
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                  SCHEDULER$_JOB_OUTPUT          SYS_LOB0000008919C00002$$      USERS                          USERS
SYS                  SCHEDULER$_JOB_OUTPUT          SYS_LOB0000008919C00003$$      USERS                          USERS





alter table sys.SCHEDULER$_JOB_OUTPUT move lob(OUTPUT) store as (tablespace SYSAUX) parallel 16 update indexes;
Table altered.

alter table sys.SCHEDULER$_JOB_OUTPUT move lob(ERRORS) store as (tablespace SYSAUX) parallel 16 update indexes;
Table altered.



OWNER                TABLE_NAME                     SEGMENT_NAME                   TABLESPACE_NAME                LOB_TABLESPACE
-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                  SCHEDULER$_JOB_OUTPUT          SYS_LOB0000008919C00002$$      SYSAUX                         SYSAUX
SYS                  SCHEDULER$_JOB_OUTPUT          SYS_LOB0000008919C00003$$      SYSAUX                         SYSAUX






COL SEGMENT_NAME FORMAT A30
COL OWNER FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL SEGMENT_TYPE FORMAT A30
SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024/1024 "SIZE(GB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' 
ORDER BY BYTES DESC) WHERE ROWNUM<=10;



col owner for a15
col segment_name for a40
select * from
(select owner,segment_name||'~'||partition_name segment_name,segment_type,bytes/(1024*1024*1024) size_G
from dba_segments
where 1=1
and OWNER='SYS'
ORDER BY BLOCKS desc
) 
where rownum < 11;