Migrate Tables and Indexes from Tablespace_01 to Tablespace_02

=================================
General
=================================
Sometimes it is needed to move segments from one Tablespace to another.

Usage:

sqlplus system/password@igt @main_tables_flow.sql
=================================
Move Tables
=================================
main_tables_flow.sql
set_params.sql
chk_sessions.sql
tbs_objects_stats.sql
cre_new_table_tbs.sql
move_tables.sql
drop_old_table_tbs.sql

=================================
Move Indexes
=================================

main_indexes_flow.sql

set_params.sql

chk_sessions.sql
tbs_objects_stats.sql
cre_new_index_tbs.sql
move_indexes.sql
drop_old_index_tbs.sql

=================================
Code for Tables
=================================

main_tables_flow.sql
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_table_tbs.sql
@./move_tables.sql
@./drop_old_table_tbs.sql


=================================
Common Code
=================================
----------------------------------

set_params.sql
----------------------------------

define table_from_tbs=IGT_TABLE4
define table_to_tbs=IGT_TABLE2

define index_from_tbs=IGT_INDEX4
define index_to_tbs=IGT_INDEX2

define table_to_datafile=/oracle_db/db1/db_igt/ora_igt_table_02.dbf
define index_to_datafile=/oracle_db/db1/db_igt/ora_igt_index_02.dbf

define owner_name=V500_125

----------------------------------
chk_sessions.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
COL sessions_count FOR A20
SET HEADING ON
SET FEEDBACK ON
SET NEWPAGE NONE
SET VERIFY OFF


spool tbs_objects_stats.log append
PROMPT
PROMPT ==========================================
PROMPT Checking Sessions of User '&&owner_name'
PROMPT ==========================================
PROMPT 
SELECT schemaname as SCHEMA_NAME,
       'Current Sessions: '||COUNT(*) AS sessions_count
  FROM V$SESSION 
 WHERE schemaname <> 'SYS'
   AND (schemaname IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY schemaname;
PROMPT 
SET HEADING ON
PROMPT ==========================================
spool off

----------------------------------
tbs_objects_stats.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
SET HEADING ON
SET FEEDBACK OFF
SET NEWPAGE NONE

spool tbs_objects_stats.log append
PROMPT
PROMPT ==========================================
SET HEADING OFF
SELECT 'Run Date: '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;
SET HEADING ON
PROMPT ==========================================
PROMPT Migrating for Owner: &&owner_name
PROMPT Migrating Tables from &&table_from_tbs to &&table_to_tbs
PROMPT Migrating Indexes from &&index_from_tbs to &&index_to_tbs
PROMPT ==========================================

PROMPT ==========================================
PROMPT Stats For &&owner_name and &&table_from_tbs and &&index_from_tbs
PROMPT ==========================================
SELECT owner||'.'||tablespace_name||'.'||'TABLES' as record_type, COUNT(*) 
 FROM DBA_SEGMENTS
 WHERE tablespace_name = '&&table_from_tbs'
   AND segment_type = 'TABLE'  
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'TABLES'
UNION ALL
SELECT table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES' as record_type, COUNT(*) 
FROM  DBA_TAB_PARTITIONS 
WHERE tablespace_name = '&&table_from_tbs' 
  AND ( table_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES'
UNION ALL
SELECT owner||'.'||tablespace_name||'.'||'INDEXES', COUNT(*)
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&index_from_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'INDEXES'
UNION ALL
SELECT index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES', COUNT(*)
 FROM DBA_IND_PARTITIONS 
WHERE tablespace_name = '&&index_from_tbs'
  AND ( index_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES';

PROMPT ==========================================
PROMPT Stats For &&owner_name and &&table_to_tbs and &&index_to_tbs
PROMPT ==========================================
SELECT owner||'.'||tablespace_name||'.'||'TABLES' as record_type, COUNT(*) 
 FROM DBA_SEGMENTS
 WHERE tablespace_name = '&&table_to_tbs'
   AND segment_type = 'TABLE'  
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'TABLES'
UNION ALL
SELECT table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES' as record_type, COUNT(*) 
FROM  DBA_TAB_PARTITIONS 
WHERE tablespace_name = '&&table_to_tbs' 
  AND ( table_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY table_owner||'.'||tablespace_name||'.'||'PARTITIONED TABLES'
UNION ALL
SELECT owner||'.'||tablespace_name||'.'||'INDEXES', COUNT(*)
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&index_to_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY owner||'.'||tablespace_name||'.'||'INDEXES'
UNION ALL
SELECT index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES', COUNT(*)
 FROM DBA_IND_PARTITIONS 
WHERE tablespace_name = '&&index_to_tbs'
  AND ( index_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') )
GROUP BY index_owner||'.'||tablespace_name||'.'||'PARTITIONED INDEXES';
PROMPT
spool off;

=================================
Migrate Tables
=================================

main_tables_flow.sql
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_table_tbs.sql
@./move_tables.sql
@./drop_old_table_tbs.sql

----------------------------------
cre_new_table_tbs.sql
----------------------------------
----------------------------
--Create new Tablespace for Tables
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK ON;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT CREATE TABLESPACE &&table_to_tbs DATAFILE '&&table_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M...
CREATE TABLESPACE &&table_to_tbs DATAFILE '&&table_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M;

----------------------------------
move_tables.sql
----------------------------------
----------------------------
--Generate sql files
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_tables_tbs.sql
SELECT 'PROMPT Start Moving Tables of Owner &&owner_name from Tablespace &&table_from_tbs to Tablespace &&table_to_tbs...' FROM DUAL;
SELECT 'ALTER TABLE '||owner||'.'||segment_name||' MOVE TABLESPACE &&table_to_tbs;' 
  FROM DBA_SEGMENTS
 WHERE tablespace_name = '&&table_from_tbs'
   AND segment_type = 'TABLE'  
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );
SELECT 'PROMPT Finished Moving Tables...' FROM DUAL;
spool off;


@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_tables_tbs_part2.sql
SELECT 'PROMPT Start Moving Tables of Owner &&owner_name from Tablespace &&table_from_tbs to Tablespace &&table_to_tbs...' FROM DUAL;
SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE &&table_to_tbs;' 
  FROM DBA_TABLES
 WHERE tablespace_name = '&&table_from_tbs'
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );
SELECT 'PROMPT Finished Moving Tables...' FROM DUAL;
spool off;
@gen_move_tables_tbs_part2

spool gen_move_part_tables_tbs.sql
SELECT 'PROMPT Start Moving Partitioned Tables of Owner &&owner_name from Tablespace &&table_from_tbs to Tablespace &&table_to_tbs...' FROM DUAL;
SELECT 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE PARTITION '||PARTITION_NAME||' TABLESPACE &&table_to_tbs;' 
  FROM  DBA_TAB_PARTITIONS 
 WHERE tablespace_name = '&&table_from_tbs' 
   AND ( table_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );  
SELECT 'PROMPT Finished Moving Partitioned Tables...' FROM DUAL;
spool off;

----------------------------
--execute Generated Files
----------------------------
@gen_move_tables_tbs.sql
@gen_move_part_tables_tbs.sql

----------------------------------
drop_old_table_tbs.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
SET HEADING ON
SET FEEDBACK OFF
SET NEWPAGE NONE

----------------------------
--Manual Check
----------------------------
ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT
SELECT COUNT(*) AS OBJECTS_ON_OLD_TBS FROM DBA_SEGMENTS WHERE tablespace_name = '&&table_from_tbs';
--Expected Result: 0
PROMPT
PROMPT
PROMPT DROP TABLESPACE &&table_from_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE &&table_from_tbs INCLUDING CONTENTS AND DATAFILES;
PROMPT

=================================
Code for Indexes
=================================
----------------------------------

main_indexes_flow.sql
----------------------------------
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_index_tbs.sql
@./move_indexes.sql
@./drop_old_index_tbs.sql


=================================
Migrate Indexes
=================================
----------------------------------
main_indexes_flow.sql
----------------------------------
@./chk_sessions.sql
@./tbs_objects_stats.sql
@./cre_new_index_tbs.sql
@./move_indexes.sql
@./drop_old_index_tbs.sql

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

cre_new_index_tbs.sql
----------------------------------

----------------------------
--Create new Tablespace for Indexes
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK ON;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT CREATE TABLESPACE &&index_to_tbs DATAFILE '&&index_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M...

CREATE TABLESPACE &&index_to_tbs DATAFILE '&&index_to_datafile' SIZE 2000M AUTOEXTEND ON MAXSIZE 30000M;

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

move_indexes.sql
----------------------------------
----------------------------
--Generate sql files
----------------------------
@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_indexes_tbs.sql
SELECT 'PROMPT Start Moving Indexes Owned by &&owner_name from Tablespace &&index_from_tbs to &&index_to_tbs ...' FROM DUAL;
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE &&index_to_tbs;' 
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&index_from_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );   
SELECT 'PROMPT Finished Moving Indexes...' FROM DUAL;
spool off

spool gen_move_part_indexes_tbs.sql
SELECT 'PROMPT Start Moving Partitioned Indexes Owned by &&owner_name from Tablespace &&index_from_tbs to &&index_to_tbs ...' FROM DUAL;
SELECT 'ALTER INDEX '||index_owner||'.'||index_name ||' REBUILD PARTITION '||partition_name||' TABLESPACE &&index_to_tbs;'
 FROM DBA_IND_PARTITIONS 
WHERE tablespace_name = '&&index_from_tbs'
  AND ( index_owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );  
SELECT 'PROMPT Finished Moving Partitioned Indexes...' FROM DUAL;
spool off;

----------------------------
--execute Generated Files
----------------------------
@gen_move_indexes_tbs.sql
@gen_move_part_indexes_tbs.sql

----------------------------------
drop_old_index_tbs.sql
----------------------------------
@./set_params.sql

SET LINESIZE 200
SET PAGESIZE 1000
COL record_type FOR A40
SET HEADING ON
SET FEEDBACK OFF
SET NEWPAGE NONE

----------------------------
--Manual Check
----------------------------
ALTER SESSION SET DDL_LOCK_TIMEOUT=600;
PROMPT
SELECT COUNT(*) AS OBJECTS_ON_OLD_TBS FROM DBA_SEGMENTS WHERE tablespace_name = '&&index_from_tbs';
--Expected Result: no rows selected
PROMPT
PROMPT
PROMPT DROP TABLESPACE &&index_from_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE &&index_from_tbs INCLUDING CONTENTS AND DATAFILES;
PROMPT




=================================
When dropping the default tablespace
=================================

DROP TABLESPACE IGT_TABLE INCLUDING CONTENTS AND DATAFILES;

ORA-12919: Can not drop the default permanent tablespace

CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db1/db_igt/igt_table_temp_01.dbf' size 10M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE_TEMP;

DROP TABLESPACE IGT_TABLE INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE IGT_TABLE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 30000M;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE;


DROP TABLESPACE IGT_TABLE_TEMP INCLUDING CONTENTS AND DATAFILES;


=================================
Moving SubPartitions
=================================

move_subpartitions.sql

set trimspool on

set timing off

SET LINESIZE 200

SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;

SET ECHO OFF

SET FEEDBACK OFF

SET TERM OFF

SET NEWPAGE 0

SET SPACE 0

SPOOL gen_move_subpartitions.sql

SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE '||'IGT_TABLE2' ;' 

FROM DBA_TAB_SUBPARTITIONS

WHERE table_owner = 'LAB_QANFV_ALLQQ'

  AND tablespace_name = 'IGT_TABLE';

spool off;
@gen_move_subpartitions.sql

=================================
Moving LOB Segments
=================================
The syntax is:

ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COLUMN) STORE AS (TABLESPACE NEW_TABLESPACE_NAME);

SELECT table_name FROM DBA_LOBS 
WHERE segment_name = 'SYS_LOB0000220773C00009$$';

For example:
ALTER TABLE SYS.SERVERERROR_LOG move lob (SQL_STATEMENT_ALL) STORE 
AS SYS_LOB0000220773C00009$$ (tablespace DWH_TABLE);

set trimspool on;

set timing off;

SET LINESIZE 200;

SET PAGESIZE 0;
SET HEADING OFF;
SET VERIFY OFF;

SET ECHO OFF;

SET FEEDBACK OFF;

SET TERM OFF;

SET NEWPAGE 0;

SET SPACE 0;

SPOOL move_lob.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB ('||column_name||') STORE AS '||segment_name||' (tablespace IGT_TABLE2);' AS sql_cmd

FROM DBA_LOBS 

WHERE 1=1

  AND tablespace_name = 'IGT_TABLE';

spool off

@move_lob.sql

=================================
Move Local Indexes, in the tables TBS
=================================

move_local_indexes_tbs.sql

@./set_params.sql
SET LINESIZE 200
SET PAGESIZE 0
SET HEADING OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

spool gen_move_local_indexes_tbs.sql
SELECT 'PROMPT Start Moving Indexes Owned by &&owner_name from Tablespace &&table_from_tbs to &&table_to_tbs ...' FROM DUAL;
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE &&table_to_tbs;' 
  FROM DBA_INDEXES 
 WHERE tablespace_name = '&&table_from_tbs'
   AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES)
   AND ( owner IN ('&&owner_name') OR ('&&owner_name' = 'ALL_USERS') );   
SELECT 'PROMPT Finished Moving Indexes...' FROM DUAL;
spool off

=================================
Move Indexes on IOT
=================================

For example:

SELECT owner, index_name, index_type, table_name, tablespace_name 

FROM DBA_INDEXES 

WHERE INDEX_TYPE LIKE 'IOT%' 

  AND owner = 'LAB_QANFV_ALLQQ';

spool gen_move_iot_indexes_tbs.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE  '||to_tbs||';'

FROM DBA_INDEXES 

WHERE INDEX_TYPE LIKE 'IOT%' 

  AND owner = 'LAB_QANFV_ALLQQ'

  AND tablespace_name = 'from_tbs';

spool off

@gen_move_iot_indexes_tbs.sql

=================================
Handle Temporary segments
=================================

Search for TEMPORARY segments

SELECT tablespace_name, owner, segment_name ,sum(bytes/1024/1024) 

FROM DBA_SEGMENTS

WHERE segment_type = 'TEMPORARY' 

GROUP BY tablespace_name, owner, segment_name;

To fix:

ALTER TABLESPACE IGT_TABLE COALESCE;

=================================
Rebuild Indexes
=================================

Rebuild Indexes

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET VERIFY OFF

SET FEEDBACK OFF

spool gen_rebuild_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES 

WHERE tablespace_name = 'IGT_INDEX'

  AND owner = 'MY_SCHEMA'

  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);

spool off

spool gen_rebuild_part_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME ||' REBUILD PARTITION '||PARTITION_NAME||' ;' 

FROM  DBA_SEGMENTS 

WHERE SEGMENT_TYPE LIKE '%INDEX%'  

  AND owner = 'MY_SCHEMA'

  AND PARTITION_NAME IS NOT NULL;  

spool off

SET FEEDBACK ON

@rebuild_indexes_tbs.sql

@move_part_indexes_tbs.sql

spool gen_rebuild_unusable.sql

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES 

WHERE tablespace_name = 'IGT_INDEX'

  AND owner = 'MY_SCHEMA'

  AND status <> 'VALID'

  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);

spool off

@gen_rebuild_unusable.sql

spool gen_list_unusable.sql

SELECT 'Unusable Indexes After Rebuild' FROM DUAL;'

SELECT OWNER||'.'||INDEX_NAME||' - '||STATUS FROM DBA_INDEXES 

WHERE tablespace_name = 'IGT_INDEX'

  AND owner = 'MY_SCHEMA'

  AND status <> 'VALID'

  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);

spool off

SET FEEDBACK ON

@gen_list_unusable.sql

=================================
Gather Schema Stats
=================================

Gather schema stats

BEGIN

  DBMS_STATS.gather_schema_stats(ownname => 'MY_SCHEMA', estimate_percent => 15);

END;

/

Manual Option

SET LINESIZE 200

SET PAGESIZE 0

SET FEEDBACK OFF

spool rebuild_indexes.sql

--Regular Index

SELECT 'PROMPT HANDLE INDEX '||index_name||CHR(10)||'ALTER INDEX ' ||USER_INDEXES.INDEX_NAME||' REBUILD ONLINE;'

FROM USER_INDEXES

ORDER BY INDEX_NAME;

--Partitioned Index

spool rebuild_partitioned_indexes.sql

SELECT 'PROMPT HANDLE Partitioned INDEX '||index_name||CHR(10)||'ALTER INDEX ' ||USER_IND_PARTITIONS.index_name||' REBUILD PARTITION '||USER_IND_PARTITIONS.partition_name||' ONLINE;'

FROM USER_IND_PARTITIONS, 

     USER_PART_INDEXES

WHERE USER_PART_INDEXES.table_name = 'SFI_CUSTOMER_OPTIONS'

AND USER_IND_PARTITIONS.index_name = USER_PART_INDEXES.index_name

ORDER BY USER_IND_PARTITIONS.index_name, USER_IND_PARTITIONS.partition_name;

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/data/ora_igt_table_01.dbf' RESIZE 1000M;