ORACLE中表、索引的表空间的批量更改方法
1、查询
当前用户下
的所有表
select 'alter table '|| table_name ||' move tablespace tablespacename ;' from user_all_tables;
select 'alter table '|| table_name ||' move tablespace tablespacename ;' from user_all_tables;
select 'ALTER TABLE '|| table_name ||' MOVE TABLESPACE tablespacename;' from user_tables;
2、查询当前用户下的所有索引
select 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes;
3、在当前用户下将查询结果批处理执行即可!
查看所有的unusable索引
SELECT *
FROM user_indexes
WHERE status NOT IN ('VALID', 'N/A')
ORDER BY index_name;
-- Description : Displays unusable indexes for the specified schema or all schemas.
-- Requirements : Access to the DBA views.
-- Call Syntax : @unusable_indexes (schema-name or all)
SET VERIFY OFF
SELECT owner,
index_name
FROM dba_indexes
WHERE owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))
AND status NOT IN ('VALID', 'N/A')
ORDER BY owner, index_name;