This content has been marked as final. Show 5 replies
To identify the segments in a tablespaces :
The generic command to move a table is
select owner, segment_type, segment_name, bytes/1048576 Size_MB from dba_segments where tablespace_name = '&tablespace_to_check' order by 1,2,3 /
If you move a table, you have to rebuild it's indexes. You can also move the indexes with the rebuild
alter table TABLENAME move NEWTABLESPACENAME nologging;
You could generate a move/rebuild script as
alter index INDEXNAME rebuild NEWTABLESPACENAME nologging;
Test the generated script for the first few objects to see that the script is correct.
select 'alter ' || segment_type || ' ' || owner || '.' || segment_name || ' ' || decode (segment_type, table, 'move', index, 'rebuild') || ' tablespace NEWTABLESPACENAME' || ' ;' from dba_segments where tablespace_name = '&tablespace_to_check' and segment_type in ('TABLE','INDEX') order by 1
After moving tables / indexes, you should update the schema statistics with
For other segment types (LOBSEGMENT / LOBINDEX, TABLE PARTITION, INDEX PARTITION), look up the ALTER TABLE and ALTER INDEX syntax documentation.
TAKE A DATABASE BACKUP BEFORE ATTEMPTING A RE-ORG.
Hemant K Chitale
Edited by: Hemant K Chitale on Aug 2, 2009 4:27 PM
Thanks for the reply and one more thing.
what are all the schema objects that can be moved from one Table space to other.(Is it only tables and Indexes) and what is the syntax for other objects if any?
As I said, the ALTER TABLE syntax documentation covers LOB segments.
Similarly, ALTER TABLE and ALTER INDEX also cover TABLE PARTITION and INDEX PARTITION.
Hemant K Chitale
what are all the schema objects that can be moved from one Table space to other.Any/every object which resides in "old" tablespace can be moved to "new" tablespace.
SELECT DISTINCT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS WHERE TABLESPACE_NAME = <OLD_TS_NAME>
you can move tables and indexes because all other objects will not occupy space in the database.