ORA-06575: 程序包或函数 BATCH_REBUILD_INDEX 处于无效状态

我来答

2021-08-06

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、删除重建后还是报错，删除后也清除了回收站

我来答

添加附件

问题补充

4条回答

默认

最新

Edward

重新编译提示
7:25:PL/SQL: ORA-00942: 表或视图不存在.
6:17:PL/SQL: SQL Statement ignored.
14:31:PLS-00364: 循环索引变量 ‘LINE2’ 的使用无效.
14:5:PL/SQL: Statement ignored.

有用 0

打赏 0

文成

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

复制

查询一下这个sql的结果

有用 0

打赏 0

Edward

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

这个执行是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

打赏 0