生成批量迁移当前用户下的所有表和索引到新表空间的SQL脚本。
c:\>cat mv_tab_idx_to_tbs.sql
set echo off
column order_col1 noprint
column order_col2 noprint
set heading off
set verify off
set feedback off
set echo off
spool tmp.sql
SELECT DECODE (segment_type, 'TABLE', segment_name, table_name) order_col1,
DECODE (segment_type, 'TABLE', 1, 2) order_col2,
'alter '
|| segment_type
|| ' '
|| segment_name
|| DECODE (segment_type, 'TABLE', ' move ', ' rebuild ')
|| CHR (10)
|| ' tablespace &Move_to_Tablespace '
|| CHR (10)
|| ' storage ( initial '
|| initial_extent
|| ' next '
|| next_extent
|| CHR (10)
|| ' minextents '
|| min_extents
|| ' maxextents '
|| max_extents
|| CHR (10)
|| ' pctincrease '
|| pct_increase
|| ' freelists '
|| FREELISTS
|| ');'
FROM user_segments,
(SELECT table_name, index_name
FROM user_indexes)
WHERE segment_type IN ('TABLE', 'INDEX') AND segment_name = index_name(+)
ORDER BY 1, 2
/
spool off
set heading on
set verify on
set feedback on
set echo on
-- The End --
c:\>cat mv_tab_idx_to_tbs.sql
set echo off
column order_col1 noprint
column order_col2 noprint
set heading off
set verify off
set feedback off
set echo off
spool tmp.sql
SELECT DECODE (segment_type, 'TABLE', segment_name, table_name) order_col1,
DECODE (segment_type, 'TABLE', 1, 2) order_col2,
'alter '
|| segment_type
|| ' '
|| segment_name
|| DECODE (segment_type, 'TABLE', ' move ', ' rebuild ')
|| CHR (10)
|| ' tablespace &Move_to_Tablespace '
|| CHR (10)
|| ' storage ( initial '
|| initial_extent
|| ' next '
|| next_extent
|| CHR (10)
|| ' minextents '
|| min_extents
|| ' maxextents '
|| max_extents
|| CHR (10)
|| ' pctincrease '
|| pct_increase
|| ' freelists '
|| FREELISTS
|| ');'
FROM user_segments,
(SELECT table_name, index_name
FROM user_indexes)
WHERE segment_type IN ('TABLE', 'INDEX') AND segment_name = index_name(+)
ORDER BY 1, 2
/
spool off
set heading on
set verify on
set feedback on
set echo on
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-609176/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-609176/