moving all the objects inside the tablespace to another tablespace and the droping or shrinking the empty datafiles
steps are
create new tablespace
move all the tables to the new tablespace
move all the indexes to the new tablespace
move all the partitions of a partioned tables to the new tablespace
move all the LOBS to the new tablespace
check if still any object is left in the Older tablespace
shrink the old data files or drop the old tablespace
grant default tablespace and quota to the specified users to use new tablespace
note : while moving the indexes sometime the status of indexes goes to unused.
take structure of those indexes drop those indexes and create them again.or try rebuilt again.
------ to see the free space the datafiles------------------
SELECT SUBSTR (df.NAME, 1, 60) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;
-----move tables -------------------
SELECT 'ALTER TABLE RFC_PROMO2.' || OBJECT_NAME ||' MOVE TABLESPACE '||' PROMO02; '
FROM ALL_OBJECTS
WHERE OWNER = 'RFC_PROMO2'
AND OBJECT_TYPE = 'TABLE';
-----move indexes -------------------
SELECT 'ALTER INDEX RFC_PROMO2.'||INDEX_NAME||' REBUILD TABLESPACE PROMO02 ONLINE;'
FROM ALL_INDEXES
WHERE OWNER = 'RFC_PROMO2';
-----move lobs-----------------
SELECT 'ALTER TABLE RFC_PROMO2.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE PROMO02);'
FROM DBA_TAB_COLS
WHERE OWNER = 'RFC_PROMO2' AND DATA_TYPE like '%LOB%';
-----confirm if any object is still left in old tablespace
select segment_name,segment_type,owner
from dba_segments
where tablespace_name ='PROMO1';
-----to see the names of portioned tables and their partitions
SELECT TABLESPACE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tablename';
-----move partioned table partition
alter table partitioned move partition part_3 tablespace users;
SELECT 'ALTER TABLE ' ||table_name ||' MOVE PARTITION ' ||partition_name ||' TABLESPACE REPORT;' FROM all_tab_partitions WHERE table_name = 'requestLog';
---------------shrinking temp file
SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';
alter database tempfile 'D:\APP\ADMINISTRATOR\ORADATA\MONETA\TEMP01.DBF' resize 256M;
--- to shrink datafiles:
select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||'m;' as cmd, bytes/1024/1024 from dba_data_files a, ( select file_id, max(block_id+blocks-1) as hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0 ;
