Moving Database Objects between tablespaces
Oracle provided recent templates for applications R12 (Vision install) unfortunately does not go well with tablespace / datafiles management. Instead of limited data files of higher sizes, surprisingly provides lots of them with smaller and non-uniform size.
For example: APPS_TS_TX_IDX contains 26 data files of sizes from 100M for a 36G tablespace.
Target is to drop these tablespacse to have new big file tablespaces. I am not claiming this is the best way or the only way, but this is how I did it successfully.
This can obviously be used for any other Oracle databases as well.
Steps are pretty simple. Here is Oracle template provided tablespace APPS_TS_TX_IDX:
SQL> SELECT FILE_ID, FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE, MAXBYTES/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_TS_TX_IDX';
FILE_ID FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------- -------------------------------------------------------- --------------- --- ------------------
225 /u01/VIS/db/apps_st/data/APPS_TS_TX_IDX02.dbf 285 NO 0
229 /u01/VIS/db/apps_st/data/APPS_TS_TX_IDX01.dbf 215 NO 0
3 /u01/VIS/db/apps_st/data/tx_idx11.dbf 641 YES 32767.9844
24 /u01/VIS/db/apps_st/data/tx_idx12.dbf 1950 NO 0
27 /u01/VIS/db/apps_st/data/tx_idx13.dbf 1565 NO 0
33 /u01/VIS/db/apps_st/data/tx_idx14.dbf 1759 NO 0
39 /u01/VIS/db/apps_st/data/tx_idx15.dbf 483 NO 0
44 /u01/VIS/db/apps_st/data/tx_idx1.dbf 2000 NO 0
59 /u01/VIS/db/apps_st/data/tx_idx2.dbf 301 NO 0
60 /u01/VIS/db/apps_st/data/tx_idx3.dbf 1910 NO 0
61 /u01/VIS/db/apps_st/data/tx_idx4.dbf 291 NO 0
FILE_ID FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------- -------------------------------------------------------- --------------- --- ------------------
62 /u01/VIS/db/apps_st/data/tx_idx5.dbf 370 NO 0
63 /u01/VIS/db/apps_st/data/tx_idx6.dbf 2000 NO 0
67 /u01/VIS/db/apps_st/data/tx_idx7.dbf 1765 NO 0
70 /u01/VIS/db/apps_st/data/tx_idx8.dbf 302 NO 0
71 /u01/VIS/db/apps_st/data/tx_idx9.dbf 601 NO 0
73 /u01/VIS/db/apps_st/data/tx_idx10.dbf 1000 NO 0
80 /u01/VIS/db/apps_st/data/tx_idx16.dbf 950 NO 0
85 /u01/VIS/db/apps_st/data/tx_idx17.dbf 1946 NO 0
115 /u01/VIS/db/apps_st/data/tx_idx18.dbf 446 NO 0
128 /u01/VIS/db/apps_st/data/tx_idx19.dbf 2000 NO 0
133 /u01/VIS/db/apps_st/data/tx_idx20.dbf 2000 NO 0
FILE_ID FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------- -------------------------------------------------------- --------------- --- ------------------
145 /u01/VIS/db/apps_st/data/tx_idx21.dbf 1010 NO 0
150 /u01/VIS/db/apps_st/data/tx_idx22.dbf 2000 NO 0
179 /u01/VIS/db/apps_st/data/tx_idx23.dbf 885 NO 0
187 /u01/VIS/db/apps_st/data/tx_idx24.dbf 1894 NO 0
26 rows selected.
1. Create dummy tablespace (say APPS_TS_TX_IDX2).
SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_TS_TX_IDX';
SUM(BYTES)/1024/1024/1024
-------------------------
29.8525391
SQL> CREATE BIGFILE TABLESPACE APPS_TS_TX_IDX2 DATAFILE '/u01/VIS/db/apps_st/data/APPS_TS_TX_IDX2.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE 40000M;
Tablespace created.
2. Dynamic SQL scripts needs to be generated to move objects into new tablespace APPS_TS_TX_IDX2. The limitation on moving objects is - unfortunately tables with ‘long’ data type columns cannot be moved. One needs to use export/import for such tables. The script move_tablespace.sh will generate SQlscripts to move object between the tablespaces. No need to mention to edit source and dummy target tablespace names in the script.
-bash-3.2$ cat move_tablespace.sh
#!/bin/sh
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# #
# $Header: move_tablespace.sh 100.3 24/09/2013 05:03 ngujar ship $ #
# *=========================================================================+ #
# | Copyright (c) 2013 N@V Solutions, | #
# | All rights reserved | #
# | Applications Division | #
# | xnavin@gmail.com | #
# +=========================================================================+ #
# #
# Dynamic script that creates scripts to move Tablespace objects #
# #
# Sequence : #
# MOVE_TABLE_PARTITIONS.sql, MOVE_LOB_SEG.sql, MOVE_TABLES.sql, #
# MOVE_IOT_INDEXES.sql, MOVE_INDEX_SUBPARTS.sql, MOVE_INDEX_PARTS.sql #
# MOVE_INDEXES.sql #
# #
# Notes : #
# Tables with Long datatype columns cannot be moved, one must need #
# to use export and import. #
# #
# Steps : #
# a. Create dummy target tablespace #
# b. Edit parameter source and target tablespaces #
# c. Move objects to dummy target tablespace #
# d. Export remaining tables, if any, with long datatype columns #
# e. Drop source tablespace #
# f. Alter tablespace rename dummy to original #
# #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
srcTbs='APPS_TS_TX_IDX'
destTbs='APPS_TS_TX_IDX2'
if [ -f MOVE_TABLE_PARTITIONS.sql ] ; then
rm MOVE_TABLE_PARTITIONS.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_TABLE_PARTITIONS.sql
SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION '||PARTITION_NAME|| ' TABLESPACE $destTbs;' FROM dba_tab_partitions where TABLESPACE_NAME='$srcTbs';
EXIT;
EOSQL
if [ -f MOVE_LOB_SEG.sql ] ; then
rm MOVE_LOB_SEG.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_LOB_SEG.sql
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE $destTbs);' FROM DBA_LOBS WHERE TABLESPACE_NAME='$srcTbs';
EXIT;
EOSQL
if [ -f MOVE_TABLES.sql ] ; then
rm MOVE_TABLES.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_TABLES.sql
SELECT 'ALTER TABLE '||OWNER||'.'||SEGMENT_NAME||' MOVE TABLESPACE $destTbs;' FROM dba_segments where TABLESPACE_NAME='$srcTbs' AND SEGMENT_TYPE='TABLE';
EXIT;
EOSQL
if [ -f MOVE_IOT_INDEXES.sql ] ; then
rm MOVE_IOT_INDEXES.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_IOT_INDEXES.sql
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE $destTbs;' FROM DBA_INDEXES WHERE TABLESPACE_NAME='$srcTbs' AND INDEX_TYPE='IOT - TOP';
EXIT;
EOSQL
if [ -f MOVE_INDEX_SUBPARTS.sql ] ; then
rm MOVE_INDEX_SUBPARTS.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_INDEX_SUBPARTS.sql
SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD SUBPARTITION '||SUBPARTITION_NAME||' TABLESPACE $destTbs;' FROM DBA_IND_SUBPARTITIONS WHERE TABLESPACE_NAME='$srcTbs';
EXIT;
EOSQL
if [ -f MOVE_INDEX_PARTS.sql ] ; then
rm MOVE_INDEX_PARTS.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_INDEX_PARTS.sql
SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||' TABLESPACE $destTbs;' FROM DBA_IND_PARTITIONS WHERE TABLESPACE_NAME='$srcTbs';
EXIT;
EOSQL
if [ -f MOVE_INDEXES.sql ] ; then
rm MOVE_INDEXES.sql
fi
sqlplus -s '/as sysdba' << EOSQL
SET HEAD OFF
SET LINE 200
SET PAGES 1000
SET FEEDBACK OFF
SPOOL MOVE_INDEXES.sql
SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME||' REBUILD TABLESPACE $destTbs;' FROM dba_segments where TABLESPACE_NAME='$srcTbs' AND SEGMENT_TYPE='INDEX';
EXIT;
EOSQL
-bash-3.2$
3. The script generates below scripts to move objects:
-bash-3.2$ ls -1
MOVE_INDEXES.sql
MOVE_INDEX_PARTS.sql
MOVE_INDEX_SUBPARTS.sql
MOVE_IOT_INDEXES.sql
MOVE_LOB_SEG.sql
MOVE_TABLE_PARTITIONS.sql
move_tablespace.sh
MOVE_TABLES.sql
Run all these scripts in the order mentioned in the script header. You may derive multiple scripts to run in parallel to save time (I usually do). It may take long time (It was a bit less than an hour for 31G Tablespace with more than 34K indexes on a low capacity test server)
4. Check for the errors, if any. If tablespace still contains any objects, probably will be only those having columns as a long data type. One needs to export these tables and import back into the new tablespace. I shall post export/import steps later in the document.
Check for recycle bin if you would like to purge. Ensure that the tablespce is empty.
SQL> SELECT COUNT(*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='APPS_TS_TX_IDX';
COUNT(*)
----------
0
4. Drop the original tablespace.
SQL> DROP TABLESPACE APPS_TS_TX_IDX INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
5. Rename dummy target tablespace to original one.
SQL> ALTER TABLESPACE APPS_TS_TX_IDX2 RENAME TO APPS_TS_TX_IDX;
Tablespace altered.
6. Optionally you can move or rename data file if required.
Hope this helps,
Regards,
N@vin
No comments:
Post a Comment