Moving the user table to another tablespace
BEGIN
FOR i IN (
SELECT * FROM ALL_tables where owner = :owner
and (tablespace_name is null or tablespace_name != :tbs)
and temporary != 'Y'
and partitioned != 'YES'
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' MOVE TABLESPACE ' || :tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_tab_partitions
WHERE table_owner = :owner and tablespace_name != :tbs
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '
|| i.table_name || ' MOVE PARTITION '
|| i.partition_name ||' TABLESPACE '|| :tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_tables where owner = :owner and partitioned = 'YES'
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '
|| i.table_name || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_Lobs WHERE owner = :owner
and (tablespace_name is null or tablespace_name != :tbs)
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' MOVE TABLESPACE ' || :tbs;
END LOOP;
END;
Moving indexes to another user tablespace:
BEGIN
FOR i IN (
SELECT * FROM ALL_indexes
where owner = :owner
and tablespace_name != :index_tbs
and index_type != 'IOT - TOP'
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' REBUILD TABLESPACE ' || :index_tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_indexes
where owner = :owner
and partitioned = 'YES'
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name
|| ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :index_tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_ind_partitions
where index_owner = :owner
and tablespace_name != :index_tbs
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '
|| i.index_name || ' REBUILD PARTITION '
|| i.partition_name || ' TABLESPACE ' || :index_tbs;
END LOOP;
END;
Moving Lob segment:
BEGIN
FOR i IN (
select * from user_lobs where (tablespace_name is null or tablespace_name != :tbs)
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' MOVE TABLESPACE ' || :tbs
|| ' LOB (' || i.column_name || ')'
|| ' STORE AS ' || i.segment_name || ' (TABLESPACE ' || :tbs ||' )';
END LOOP;
BEGIN
FOR i IN (
SELECT * FROM ALL_tables where owner = :owner
and (tablespace_name is null or tablespace_name != :tbs)
and temporary != 'Y'
and partitioned != 'YES'
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' MOVE TABLESPACE ' || :tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_tab_partitions
WHERE table_owner = :owner and tablespace_name != :tbs
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '
|| i.table_name || ' MOVE PARTITION '
|| i.partition_name ||' TABLESPACE '|| :tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_tables where owner = :owner and partitioned = 'YES'
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '
|| i.table_name || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_Lobs WHERE owner = :owner
and (tablespace_name is null or tablespace_name != :tbs)
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' MOVE TABLESPACE ' || :tbs;
END LOOP;
END;
Moving indexes to another user tablespace:
BEGIN
FOR i IN (
SELECT * FROM ALL_indexes
where owner = :owner
and tablespace_name != :index_tbs
and index_type != 'IOT - TOP'
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' REBUILD TABLESPACE ' || :index_tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_indexes
where owner = :owner
and partitioned = 'YES'
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name
|| ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :index_tbs;
END LOOP;
END;
BEGIN
FOR i IN (
SELECT * FROM ALL_ind_partitions
where index_owner = :owner
and tablespace_name != :index_tbs
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '
|| i.index_name || ' REBUILD PARTITION '
|| i.partition_name || ' TABLESPACE ' || :index_tbs;
END LOOP;
END;
Moving Lob segment:
BEGIN
FOR i IN (
select * from user_lobs where (tablespace_name is null or tablespace_name != :tbs)
) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' MOVE TABLESPACE ' || :tbs
|| ' LOB (' || i.column_name || ')'
|| ' STORE AS ' || i.segment_name || ' (TABLESPACE ' || :tbs ||' )';
END LOOP;
No comments:
Post a Comment