/*
This script needs to be run as system schema
*/
CREATE OR REPLACE PACKAGE info_gen_movescript_api
AS
PROCEDURE info_gen_movescript_api(source_tablespace_name in varchar2,target_tablespace_name in varchar2);
END info_gen_movescript_api;
/
CREATE OR REPLACE PACKAGE BODY info_gen_movescript_api
AS
PROCEDURE info_gen_movescript_api(source_tablespace_name in varchar2,target_tablespace_name in varchar2)
IS
CURSOR main IS select table_name from dba_tables where tablespace_name=source_tablespace_name;
CURSOR info_table(h_table_name varchar2) IS
SELECT 'alter table '||table_name||' move tablespace '||target_tablespace_name||'
storage (
initial '||initial_extent||'
next '||next_extent||'
minextents '||min_extents||'
maxextents '||max_extents||'
pctincrease '||pct_increase||'
)
pctfree '||pct_free||'
pctused '||pct_used||';' table_name
FROM dba_tables
WHERE table_name=h_table_name;
CURSOR info_index(c_table_name varchar2) IS
SELECT 'alter index '||index_name||' rebuild tablespace '||target_tablespace_name||'
storage (
initial '||initial_extent||'
next '||next_extent||'
minextents '||min_extents||'
maxextents '||max_extents||'
pctincrease '||pct_increase||'
)
pctfree '||pct_free||';' index_name
FROM user_indexes
WHERE table_name=c_table_name;
BEGIN
FOR for_main IN main
LOOP
FOR for_info_table IN info_table(for_main.table_name)
LOOP
INSERT INTO info_gen_move_tbl
(TEXT)
VALUES
(for_info_table.table_name);
END LOOP;
FOR for_info_index IN info_index(for_main.table_name)
LOOP
INSERT INTO info_gen_move_tbl
(TEXT)
VALUES
(for_info_index.index_name);
END LOOP;
END LOOP;
END info_gen_movescript_api;
END info_gen_movescript_api;
/