-- 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;