朋友......我正在尝试调试过程以移动和重建分区表的索引 .
我试着在下面做:
-
将分区表移动到新表空间
-
重建#1分区表的索引
对于#2我使用的是dba_ind_partitions,但由于它没有table_name,因此table_owner很难过滤掉 .
PROCEDURE moveParTbl (OldTbs in varchar2, NewTbs in varchar2)
AUTHID CURRENT USER
IS
CURSOR curTable IS
SELECT table_owner, table_name, partition_name, tablespace_name
FROM dba_tab_partition
WHERE tablespace_name = OldTbs
ORDER BY TABLE_NAME;
CURSOR curIndex (lParNam IN varchar2)
IS
SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_partitions
WHERE partition_name = lParNam;
BEGIN
FOR rec1 IN curTable LOOP
EXECUTE IMMEDIATE 'alter table ' || rec1.table_owner || '.' || rec1.table_name ||
' move partition ' || rec1.partition_name || ' tablespace ' ||NewTbs;
-- Starting curIndex
FOR rec2 IN curIndex (rec1.partition_name) LOOP
EXECUTE IMMEDIATE 'rebuild index ' || rec2.index_owner || '.' || rec2.index_name ||
' rebuild partition ' || rec2.partition_name || ' tablespace ' ||NewTbs || ' online';
END LOOP; -- index cursor loop
END LOOP; --curTable for loop
END moveParTbl;
什么是错误:
1 Table: Tab-A, Tab-B, Tab-C
2. Index: Tab-A (Ind1, Ind2, Ind3)
Tab-A 3个索引在每个分区表移动后重建,而不是在Tab-A移动后重建一次 .
*下面是错的
Move Tab-A
Rebuild ind1, ind2, ind3
Move TAb-B
Rebuild ind1, ind2, ind3
Move Tab-C
Rebuld ind1, ind2, ind3
*它应该如何
Move Tab-A
Rebuild ind1, ind2, ind3
Move TAb-B
Move Tab-C
1 回答
您可能在表中重复了分区名称(oracle不会抱怨它,因为完整的segment_name由
[object_name]_[partition_name]
组成) . 如果要仅获取与该特定表分区相关的索引分区,则需要加入dba_tables
和dba_indexes
(在table_name
和table_owner
上)和dba_ind_partitions
(在index_name
和index_owner
上) . 您的光标curIndex
应如下所示:当然,您还需要将owner和table_name提供给它,而不仅仅是partition_name .