Когда я у себя выловил эти же 35 ошибок, то дал гранты
Цитата: GRANT SELECT ON SYS.V_$INSTANCE TO SUPERMAG;
GRANT SELECT ON SYS.DBA_ROLES TO SUPERMAG;
GRANT SELECT ON SYS.V_$SESSION TO SUPERMAG;
GRANT EXECUTE ON SYS.DBMS_OUTPUT TO SUPERMAG;
GRANT SELECT ON SYS.DBA_CONSTRAINTS TO SUPERMAG WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_TAB_COLUMNS TO SUPERMAG WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_CONS_COLUMNS TO SUPERMAG WITH GRANT OPTION;
GRANT ADMINISTER DATABASE TRIGGER TO SUPERMAG;
GRANT ALTER ANY ROLE TO SUPERMAG;
GRANT ALTER USER TO SUPERMAG WITH ADMIN OPTION;
GRANT ANALYZE ANY TO SUPERMAG;
GRANT CREATE DATABASE LINK TO SUPERMAG;
GRANT CREATE LIBRARY TO SUPERMAG;
GRANT CREATE PUBLIC SYNONYM TO SUPERMAG;
GRANT CREATE ROLE TO SUPERMAG WITH ADMIN OPTION;
GRANT CREATE SNAPSHOT TO SUPERMAG;
GRANT CREATE TABLE TO SUPERMAG;
GRANT CREATE USER TO SUPERMAG WITH ADMIN OPTION;
GRANT DROP ANY ROLE TO SUPERMAG WITH ADMIN OPTION;
GRANT DROP PUBLIC SYNONYM TO SUPERMAG;
GRANT DROP USER TO SUPERMAG WITH ADMIN OPTION;
GRANT GRANT ANY ROLE TO SUPERMAG WITH ADMIN OPTION;
GRANT SELECT ON DBA_USERS TO SUPERMAG WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_JOBS TO SUPERMAG WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_USERS TO SUPERMAG WITH GRANT OPTION;
GRANT EXECUTE ON SYS.DBMS_ALERT TO SUPERMAG;
GRANT EXECUTE ON SYS.DBMS_LOCK TO SUPERMAG;
GRANT EXECUTE ON SYS.DBMS_PIPE TO SUPERMAG;
GRANT SELECT ON SYS.V_$SESSION TO SUPERMAG;
GRANT SELECT ANY TABLE TO SUPERMAG;
GRANT SELECT ON DBA_USERS TO PUBLIC;
GRANT SELECT ON "SYS"."V_$INSTANCE" TO "SUPERMAG";
После чего
Код:
@?/rdbms/admin/utlrp
а потом
Цитата: prompt ------------ compile views -----------;
begin
for c in (select owner,object_name name from dba_objects
where object_type = 'VIEW' and status='INVALID')
Loop
begin
execute immediate
'alter VIEW '||c.owner||'.' || c.name || ' compile';
exception when others then null;
end;
end loop;
end;
/
prompt ------------ compile matviews -----------;
begin
for c in (select owner,object_name name from dba_objects
where object_type = 'MATERIALIZED VIEW' and status='INVALID')
Loop
begin
execute immediate
'alter MATERIALIZED VIEW '||c.owner||'.' || c.name || ' compile';
exception when others then null;
end;
end loop;
end;
/
prompt ----- compile functions vs procs -----;
begin
for c in (select owner,object_type type,object_name name
from dba_objects
where object_type in ('FUNCTION','PROCEDURE')
and status='INVALID')
Loop
begin
execute immediate
'alter ' || c.type || ' '||c.owner||'.'|| c.name || ' compile';
exception when others then null;
end;
end loop;
end;
/
prompt -------------- compile packages ---------------;
begin
for c in (select owner,object_type type, object_name name from all_objects
where object_type in ('PACKAGE','PACKAGE BODY')
and status='INVALID')
Loop
begin
if (c.type='PACKAGE') then
execute immediate
'alter PACKAGE ' ||c.owner||'.'|| c.name || ' compile PACKAGE';
else
execute immediate
'alter PACKAGE ' ||c.owner||'.'|| c.name || ' compile BODY';
end if;
exception when others then null;
end;
end loop;
end;
/
prompt ----- compile triggers -----;
begin
for c in (select owner,object_type type,object_name name
from dba_objects
where object_type = 'TRIGGER'
and status='INVALID')
Loop
begin
execute immediate
'alter ' || c.type || ' '||c.owner||'.' || c.name || ' compile';
exception when others then null;
end;
end loop;
end;
/
Соответственно, проверяем правильность компиляции (в случае сбоя никаких ошибок в скриптах выше выводиться не будет)
Цитата: prompt ---------- invalid views -----------;
select 'VIEW' type,substr(object_name,1,90) name from dba_objects
where object_type = 'VIEW' and status='INVALID'
ORDER BY 2;
prompt ---------- invalid functions -----------------;
select 'FUNCTION' type,substr(object_name,1,90) name from dba_objects
where object_type = 'FUNCTION' and status='INVALID'
ORDER BY 2;
prompt ----------invalid procs ---------------;
select 'PROCEDURE' type,substr(object_name,1,90) name from dba_objects
where object_type = 'PROCEDURE' and status='INVALID'
ORDER BY 2;
prompt ----------- invalid packages -----------------;
select substr(object_type,1,13) type,substr(object_name,1,85) name from dba_objects
where object_type in ('PACKAGE','PACKAGE BODY') and status='INVALID'
ORDER BY 2,1;
prompt ----------- invalid triggers -----------------;
select 'TRIGGER' type,substr(object_name,1,90) name from dba_objects
where object_type = 'TRIGGER' and status='INVALID'
ORDER BY 2;