1、在其它表空间定义的存储过程，可以正常执行。
定义如下：
create PROCEDURE BATCH_REBUILD_INDEX
IS
S_SQL VARCHAR2(500);
ACCOUNT NUMBER := 0;
BEGIN
FOR LINE2 IN (select owner, segment_name, tablespace_name
from dba_extents
where segment_type=‘INDEX’
and owner=(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
group by owner,segment_name,tablespace_name
having count() >10
order by count() desc
) LOOP
S_SQL:= ‘alter index ’ || LINE2.OWNER ||’.’|| LINE2.segment_name || ’ rebuild online PARALLEL 10 nologging’;
ACCOUNT := ACCOUNT + 1;
EXECUTE IMMEDIATE S_SQL;
END LOOP;
DBMS_OUTPUT.PUT_LINE(ACCOUNT);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END BATCH_REBUILD_INDEX;
/
2、在datagrip 中执行报错
call BATCH_REBUILD_INDEX()
ORA-06575: 程序包或函数 BATCH_REBUILD_INDEX 处于无效状态
3、删除重建后还是报错，删除后也清除了回收站
重新编译提示
7:25:PL/SQL: ORA-00942: 表或视图不存在.
6:17:PL/SQL: SQL Statement ignored.
14:31:PLS-00364: 循环索引变量 ‘LINE2’ 的使用无效.
14:5:PL/SQL: Statement ignored.
评论
有用 0select owner, segment_name, tablespace_name
from dba_extents
where segment_type=‘INDEX’
and owner=(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
group by owner,segment_name,tablespace_name
having count() >10
order by count() desc
查询一下这个sql的结果
评论 有用 0select owner, segment_name, tablespace_name
from dba_extents
where segment_type=‘INDEX’
and owner=(SELECT SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) ur FROM DUAL)
group by owner,segment_name,tablespace_name
having count() >10
order by count() desc
这个执行是OK的，如果把这个查询改成
select user owner, segment_name, tablespace_name
from user_extents
where segment_type=‘INDEX’
group by owner,segment_name,tablespace_name
having count() >10
order by count() desc
就能编译通过，不知道为什么
评论 有用 0
墨值悬赏