-- PORTFOLOIO ¤ WHITE PAPERS ¤ HOME PAGE
--
--
-- Moving LOBS & CLOBS from standard tablespaces to their own tablesapce
-- First, create the tablespace:

CREATE TABLESPACE LOBS
DATAFILE
  'DRA4:[ORACLE.V92.ORADATA.PROD]LOBS01.DBF' SIZE 25 M 
AUTOEXTEND ON NEXT 1280 K MAXSIZE 512 M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1 M;

-- I like to have my lobs listed in a working table so I can get the list quickly 
-- over and over again as I develop. So here I create a working table:

CREATE TABLE MY_LOBS TABLESPACE users AS 
        SELECT  OWNER,
                TABLE_NAME, 
                COLUMN_NAME, 
                DATA_TYPE, 
                DATA_LENGTH, 
                DATA_PRECISION, 
                DATA_SCALE, 
                NULLABLE, COLUMN_ID
          FROM  DBA_TAB_COLS WHERE owner NOT IN ('SYS','SYSTEM')
           AND  data_type LIKE '%LOB%';

-- Now, I can get my list of lobs quickly and easily!

-- Time to build the script:

SET linesize 100
SELECT DISTINCT 'ALTER USER ' || owner || ' QUOTA UNLIMITED ON LOBS;' AS text
           FROM my_lobs
UNION ALL
SELECT    'ALTER TABLE '
       || owner
       || '.'
       || table_name
       || ' move LOB('
       || column_name
       || ') STORE AS ( TABLESPACE lobs );' 
  FROM my_lobs
UNION ALL
 SELECT  DISTINCT 'ALTER INDEX '
       || DBA_INDEXES.owner
       || '.'
       || DBA_INDEXES.index_name
       || ' REBUILD ONLINE;'
  FROM DBA_INDEXES, MY_LOBS
 WHERE DBA_INDEXES.table_owner = MY_LOBS.owner
   AND DBA_INDEXES.table_name = MY_LOBS.table_name AND 
   DBA_INDEXES.index_type <> 'LOB';
TEXT                                                                                                
----------------------------------------------------------------------------------------------------
ALTER USER ALUMNI QUOTA UNLIMITED ON LOBS;                                                          
ALTER USER FAISMGR QUOTA UNLIMITED ON LOBS;                                                         
ALTER USER GENERAL QUOTA UNLIMITED ON LOBS;                                                         
ALTER USER SATURN QUOTA UNLIMITED ON LOBS;                                                          
ALTER TABLE GENERAL.GXRRSQL move LOB(GXRRSQL_WHERE_CLAUSE) STORE AS ( TABLESPACE lobs );            
ALTER TABLE GENERAL.GXRRSQL move LOB(GXRRSQL_PARSED_SQL) STORE AS ( TABLESPACE lobs );              
ALTER TABLE GENERAL.GXTSVBT move LOB(GXTSVBT_DATA) STORE AS ( TABLESPACE lobs );                    
ALTER TABLE GENERAL.GXTXMLD move LOB(GXTXMLD_XMLDOC) STORE AS ( TABLESPACE lobs );                  
ALTER TABLE SATURN.SXRQAN move LOB(SXRQAN_LONG_ANSWER) STORE AS ( TABLESPACE lobs );              
ALTER TABLE SATURN.SXBCDTL move LOB(SXBCDTL_TEXT_NARRATIVE) STORE AS ( TABLESPACE lobs );           
ALTER TABLE SATURN.SXBDESC move LOB(SXBDESC_TEXT_NARRATIVE) STORE AS ( TABLESPACE lobs );           
ALTER TABLE SATURN.SXRSYLO move LOB(SXRSYLO_LEARNING_OBJECTIVES) STORE AS ( TABLESPACE lobs );      
ALTER TABLE SATURN.SXRSYRM move LOB(SXRSYRM_REQUIRED_MATERIALS) STORE AS ( TABLESPACE lobs );       
ALTER TABLE SATURN.SXRSYTR move LOB(SXRSYTR_TECHNICAL_REQUIREMENT) STORE AS ( TABLESPACE lobs );    
ALTER TABLE SATURN.SXRAREG move LOB(SXRAREG_COMMENTS) STORE AS ( TABLESPACE lobs );                 
ALTER TABLE SATURN.SXRCMNT move LOB(SXRCMNT_TEXT_NAR) STORE AS ( TABLESPACE lobs );                 
ALTER TABLE SATURN.SXBDESC move LOB(SXBDESC_TEXT_NARRATIVE) STORE AS ( TABLESPACE lobs );           
ALTER TABLE SATURN.SXRSYLO move LOB(SXRSYLO_LEARNING_OBJECTIVES) STORE AS ( TABLESPACE lobs );      
ALTER TABLE SATURN.SXRSYRM move LOB(SXRSYRM_REQUIRED_MATERIALS) STORE AS ( TABLESPACE lobs );       
ALTER TABLE SATURN.SXRSYTR move LOB(SXRSYTR_TECHNICAL_REQUIREMENT) STORE AS ( TABLESPACE lobs );    
ALTER TABLE ALUMNI.AXRJPST move LOB(AXRJPST_REQMNT) STORE AS ( TABLESPACE lobs );                   
ALTER TABLE FAISMGR.RXTXMLD move LOB(SYS_NC00003$) STORE AS ( TABLESPACE lobs );                    
ALTER INDEX ALUMNI.AXRJPST_EMPL_INDEX REBUILD ONLINE;                           
ALTER INDEX ALUMNI.AXRJPST_EMPR_INDEX REBUILD ONLINE;                           
ALTER INDEX FAISMGR.PK_RXTXMLD REBUILD ONLINE;                                  
ALTER INDEX GENERAL.GXTSVBT_KEY_INDEX REBUILD ONLINE;                           
ALTER INDEX GENERAL.PK_GXRRSQL REBUILD ONLINE;                                  
ALTER INDEX GENERAL.PK_GXTSVBT REBUILD ONLINE;                                  
ALTER INDEX GENERAL.PK_GXTXMLD REBUILD ONLINE;                                  
ALTER INDEX SATURN.PK_SXRQAN REBUILD ONLINE;                                   
ALTER INDEX SATURN.PK_SXRAREG REBUILD ONLINE;                                   
ALTER INDEX SATURN.PK_SXBDESC REBUILD ONLINE;                                   
ALTER INDEX SATURN.PK_SXRSYLO REBUILD ONLINE;                                   
ALTER INDEX SATURN.PK_SXRSYRM REBUILD ONLINE;                                   
ALTER INDEX SATURN.PK_SXRSYTR REBUILD ONLINE;                                   
ALTER INDEX SATURN.SXRAREG_START_DATE_INDEX REBUILD ONLINE;                     
ALTER INDEX SATURN.SXRAREG_TERM_CRN_INSTRUC_INDEX REBUILD ONLINE;               
ALTER INDEX SATURN.SXRCMNT_INDEX REBUILD ONLINE;                                
ALTER INDEX SATURN.UK_SXBCDTL REBUILD ONLINE;                                   
ALTER INDEX SATURN.UK_SXBDESC REBUILD ONLINE;                                   
ALTER INDEX SATURN.UK_SXRSYLO REBUILD ONLINE;                                   
ALTER INDEX SATURN.UK_SXRSYRM REBUILD ONLINE;                                   
ALTER INDEX SATURN.UK_SXRSYTR REBUILD ONLINE;                                                                                      
44 rows selected

-- after running this script on test, a look at the lobs tablespace 
-- indicates the datafile has grown from 25 megs to 39 megs. So, before running this on 
-- PROD, I am going to grow the datafile to 50 megs just to have some additional room 
-- preallocated.

ALTER DATABASE DATAFILE 'DRA4:[ORACLE.V92.ORADATA.PROD]LOBS.DBF' RESIZE 50M;

-- Then I just run the script on PROD!

-- Double check that all indexes that needed to be rebuilt have been rebuilt:
SELECT index_name, status
  FROM DBA_INDEXES
 WHERE status NOT IN ('VALID', 'N/A');
 
-- As clean up, I drop my working table:
DROP TABLE MY_LOBS;