REORG STEPS:

Step 1 :

Create a new tablespace;

CREATE BIGFILE TABLESPACE CDMDM_NEW DATAFILE '+SPARSE' SIZE 20G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Step 2 :

Move all tables to new tablespace by below dynamic query :

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE ONLINE TABLESPACE CDMDM_NEW compress for Archive high;' from dba_tables where owner='CDMDM' ;

****

Partition tables also need to move to new tablespace.

select 'ALTER TABLE '||a.OWNER||'.'||a.TABLE_NAME||' move PARTITION '||b.PARTITION_NAME||' TABLESPACE CDMDM_NEW compress for Archive high;' from dba_part_tables a, dba_segments b where a.table_name=b.segment_name and a.owner='CDMDM' and b.tablespace_name='CDMDM';

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' move PARTITION '||PARTiTION_NAME||' TABLESPACE NONGWDATA1;' from dba_tab_partitions where tablespace_name in ('NONGWDATA_NEW','NONGWDATA');

****
If there Bitmap Indexes presents , 1st need to unusable those indexes. Otherwise it will fail .

select 'alter INDEX '||OWNER||'.'||INDEX_NAME||' UNUSABLE;' from dba_indexes where owner='CDMDM' and index_type like '%BITMAP%' and table_name in ('W_CLAIMELMNT_D','W_EMPLOYEE_D','WC_UNIT_FINANCIAL_F','W_CLAIMELMNT_D_PZ','WC_MM_BILL_HEADER_F','W_EMPLOYEE_D_OLD');

Step 3 :

Move all indexes to new tablespace by below dynamic query :

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE TABLESPACE CDMDM_NEW compress ADVANCED high parallel 20;' from dba_indexes where owner='CDMDM' and tablespace_name='CDMDM' and index_type <> 'LOB';

Step 4 :

** Rebuild all Indexes are in UNUSABLE State :

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD parallel 20;' from dba_indexes where status='UNUSABLE' ;

Convert all indexes to noparallel

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' noparallel;' from dba_indexes where owner='CDMDM' and degree > 1;

Step 5 :

Compress all LOBS where securefile is YES

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' modify LOB ( '||COLUMN_NAME||' ) ( COMPRESS HIGH );' from dba_lobs where owner='CDMDM' and tablespace_name='CDMDM' and securefile='YES';

Step 6 :

Move all LOBS to new tablespace

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' move LOB ( '||COLUMN_NAME||' ) STORE AS ( tablespace CDMDM_NEW );' from dba_lobs where owner='CDMDM' and tablespace_name='CDMDM';

Move LOB Partitions :

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as (tablespace NONGWDATA1);' FROM DBA_LOB_PARTITIONS WHERE tablespace_name in ('NONGWDATA_NEW','NONGWDATA');

Move SDO LOB segments :

ALTER TABLE CCUSER."CC_USER" MOVE TABLESPACE OP VARRAY "SPATIALPOINTDENORM"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "SPATIALPOINTDENORM"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

ALTER TABLE CCUSER."CC_ADDRESS" MOVE TABLESPACE OP VARRAY "SPATIALPOINT"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "SPATIALPOINT"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

ALTER TABLE CCUSER."CC_CLAIM" MOVE TABLESPACE OP VARRAY "LOSSLOCATIONSPATIALDENORM"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "LOSSLOCATIONSPATIALDENORM"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

Step 7 :

Validation :

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='CDMDM';

select owner,object_name,object_type from dba_objects where status <> 'VALID' and owner='CDMDM';

select * from dba_segments where tablespace_name='CDMDM';

select * from dba_tables where tablespace_name='CDMDM';

select * from dba_indexes where tablespace_name='CDMDM';

select * from dba_lobs where tablespace_name='CDMDM';

select * from dba_tab_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

select * from dba_ind_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

select * from dba_lob_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

DATAFILE MOVEMENT Dynamic QUERY:

select 'alter database move datafile '''||FILE_NAME||''' to ''+DATAC1'';' from dba_data_files where file_name like '%SPARSE%';

Verify Default attribute tablespace:

select * from dba_part_tables where DEF_TABLESPACE_NAME like '%del%'

SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE NONGWDATA1;' from dba_part_tables where DEF_TABLESPACE_NAME ='%del%'

Table Online Redefination :

exec DBMS_REDEFINITION.REDEF_TABLE(uname=>'CDH_GWODS',tname=>'CDH_BATCH_CTRLID',table_part_tablespace=>'CDH');

============================================================

col USERNAME for a30

col TABLESPACE_NAME for a40

SELECT USERNAME, TABLESPACE_NAME, BYTES, MAX_BYTES FROM DBA_TS_QUOTAS ORDER BY USERNAME, TABLESPACE_NAME;

alter database datafile '/u02/oracle/oradata/u7bcs1d7/tools_01.dbf' resize 26g autoextend on next 250m maxsize unlimited;

========================

ALTER TABLE MEDIATION.QRTZ_JOB_DETAILS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_TRIGGERS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_BLOB_TRIGGERS move LOB ( BLOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_CALENDARS move LOB ( CALENDAR ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.REST_TEMPLATE move LOB ( TEMPLATE ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.EVENT_HISTORY_DATA move LOB ( DETAILS ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.NOTIFICATIONS move LOB ( NOTIFICATION ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.NOTIFICATIONHISTORY move LOB ( NOTIFICATION ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.SOAPHEADER move LOB ( HEADER ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.CREATE$JAVA$LOB$TABLE move LOB ( LOB ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPADMIN move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPGWA move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPSCHEDULE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPCONFIG move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPDATAPATH move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPPOLICY move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPTEMPLATE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.TASKPAYLOAD move LOB ( TASK ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.JMSCONTEXT move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPSCHEDULE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPTEMPLATE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPADMIN move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPGWA move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SOAPHEADER move LOB ( HEADER ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.JMSCONTEXT move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.TOAD_PLAN_TABLE move LOB ( OTHER_XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_BLOB_TRIGGERS move LOB ( BLOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_CALENDARS move LOB ( CALENDAR ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_JOB_DETAILS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_TRIGGERS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.XMLRESPONSE move LOB ( RESPONSEXML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SYS_IMPORT_FULL_01 move LOB ( XML_CLOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SYS_IMPORT_FULL_01 move LOB ( TARGET_XML_CLOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.BATCH_STEP_EXECUTION_CONTEXT move LOB ( SERIALIZED_CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.BATCH_JOB_EXECUTION_CONTEXT move LOB ( SERIALIZED_CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.CREATE$JAVA$LOB$TABLE move LOB ( LOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPCONFIG move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPDATAPATH move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPPOLICY move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.JMSTEST move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

When moving a LOB column, if the LOB is stored as a SecureFile, and you want to maintain or explicitly specify that the LOB should continue to be stored as a SecureFile, you should include the SECUREFILE keyword in your ALTER TABLE statement.

Checking if a LOB is SecureFile or BasicFile

To check whether a LOB column is stored as SecureFile or BasicFile, you can query the DBA_LOBS view:

sql
SELECT TABLE_NAME, COLUMN_NAME, SECUREFILE
FROM DBA_LOBS
WHERE TABLE_NAME = 'your_table_name' AND COLUMN_NAME = 'your_column_name';

• SECUREFILE: This column will show YES if the LOB is stored as a SecureFile, and NO if it's stored as a BasicFile.

Moving a SecureFile LOB

If the LOB is a SecureFile, and you want to maintain that storage type while moving it, you should use the following syntax:

sql
ALTER TABLE schema_name.table_name
MOVE LOB (column_name)
STORE AS SECUREFILE (TABLESPACE tablespace_name);

Example

Here is how you would move a SecureFile LOB column while ensuring it stays a SecureFile:

sql
ALTER TABLE MEDIATION.QRTZ_JOB_DETAILS 
MOVE LOB (JOB_DATA) 
STORE AS SECUREFILE (TABLESPACE DATA);

If you omit SECUREFILE, the column might revert to the BasicFile format depending on your Oracle database's default LOB storage settings.

Best Practice

• For SecureFile LOBs: Always specify STORE AS SECUREFILE to ensure the LOB remains in the SecureFile format after the move.
• For BasicFile LOBs: You don't need to specify anything special; just using the STORE AS (TABLESPACE tablespace_name) syntax is sufficient.

If you want to check the existing storage type before moving, always query DBA_LOBS to avoid inadvertently changing the LOB type.

SELECT OWNER, TABLE_NAME, COLUMN_NAME 

FROM ALL_TAB_COLUMNS 

WHERE DATA_TYPE = 'SDO_GEOMETRY';

DATA_TYPE

--------------------------------------------------------------------------------

DATE

CLOB

CHAR

TIMESTAMP(6)

BLOB

RAW

NUMBER

UNDEFINED

VARCHAR2

LONG

TIMESTAMP(3)

NVARCHAR2