Run Procedure with additional priviledge

Following example show how to create a procedure which allow other users to run it with original user's privilege

CREATE OR REPLACE procedure PORTAL_DEV.test_db_links
AUTHID DEFINER
is
cursor links_to_test is
select db_link from user_db_links;
begin

for link in links_to_test
loop

begin
execute immediate 'select * from dual@' || link.db_link ;
execute immediate 'commit';
execute immediate 'ALTER SESSION CLOSE DATABASE LINK ' || link.db_link ;
dbms_output.put_line ( link.db_link || ' OK.');
exception
when others then
dbms_output.put_line ( link.db_link || ' Failed.');
end;

end loop;

end;
/
grant execute on PORTAL_DEV.test_db_links to health;

Advance troubleshooting for compile SYS.DBMS_XPLAN in Oracle 10g

In Oracle 10g, if full import is performed from Oracle 9i, it will cause following DBMS_STATS related package failed to compile, due to conflict name

alter package sys.dbms_xplan compile body;

Errors for PACKAGE BODY DBMS_XPLAN:

show error

LINE/COL ERROR
-------- -----------------------------------------------------------------
1566/9 PL/SQL: SQL Statement ignored
1570/34 PL/SQL: ORA-00980: synonym translation is no longer valid
1577/9 PL/SQL: SQL Statement ignored
1579/34 PL/SQL: ORA-00980: synonym translation is no longer valid
1585/9 PL/SQL: SQL Statement ignored
1599/27 PL/SQL: ORA-00980: synonym translation is no longer valid

Solution:
2 public synonyms which used by the package are the root cause. Drop them to resolve name resolution

drop public synonym existsnode;
drop public synonym extract;

catalog.sql cannot resolve this error. If it is executed, it will cause utlrp.sql to throw following error

BEGIN dbms_registry_sys.validate_components; END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_REGISTRY_SYS"
ORA-06512: at line 1

Resolve it by re-create it:
@?/rdbms/admin/prvtcr.plb
@?/rdbms/admin/utlrp

Please use following PayPal donate if my post helped

RMAN shared_pool_size requirement

Following shows that RMAN needs 13.49981689453125 MB of shared_pool_size in order to run

The demonstration shown initial with about 10 MB shared pool free, RMAN failed to allocate memory from shared pool. Then 8 MB is added, and it is able to run. By subtracting the free size before and after ran, it is found RMAN needs about 13.5 MB of shared_pool

select * from v$sgastat where name = 'free memory' and pool = 'shared pool';

POOL         NAME           BYTES
------------ ----------- --------
shared pool  free memory 10552632

RMAN failed immediately with memory allocation error

$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Aug 5 15:10:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ORACLE error from target database:
ORA-04031: unable to allocate 41152 bytes of shared memory ("shared pool","DBMS_RCVMAN","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_RCVMAN"

error executing package DBMS_RCVMAN in TARGET database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN

select component, current_size/1024/1024 mb, max_size from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                              MB   MAX_SIZE
------------------------------ ---------- ----------
shared pool                            84          0
large pool                              0          0
java pool                              24          0
streams pool                            0          0
DEFAULT buffer cache                   48          0
KEEP buffer cache                       0          0
RECYCLE buffer cache                    0          0
DEFAULT 2K buffer cache                 0          0
DEFAULT 4K buffer cache                 0          0
DEFAULT 8K buffer cache                 0          0
DEFAULT 16K buffer cache                0          0
DEFAULT 32K buffer cache                0          0
ASM Buffer Cache                        0          0

Shrink java_pool_size to free up 8 MB, and add it into shared_pool_size

select * from v$SGA_DYNAMIC_FREE_MEMORY;

CURRENT_SIZE
------------
0

select component, current_size/1024/1024 mb, min_size/1024/1024 min_size, max_size from V$SGA_DYNAMIC_COMPONENTS where component in ( 'java pool' , 'shared pool' );

COMPONENT   MB   MIN_SIZE   MAX_SIZE
----------- -- ---------- ----------
shared pool 84         84          0
java pool   24         24          0

alter system set java_pool_size = 16m;

select * from v$SGA_DYNAMIC_FREE_MEMORY;

CURRENT_SIZE
------------
8388608

alter system set shared_pool_size = 92m;

select component, current_size/1024/1024 mb, min_size/1024/1024 min_size, max_size from V$SGA_DYNAMIC_COMPONENTS where component in ( 'java pool' , 'shared pool' );

COMPONENT    MB   MIN_SIZE   MAX_SIZE
----------- --- ---------- ----------
shared pool  92         84          0
java pool    16         16          0

Now RMAN is able to run. After RMAN executed, check free shared pool memory again

select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
POOL NAME BYTES
------------ ----------- ----------
shared pool free memory 4785656

So RMAN shared pool memory usage is
10552632 + 8192*1024 - 4785656
= 14155584 bytes
= 13.49981689453125 MB

Please note that shared pool memory won't free immediately after RMAN completion. This is normal behavior. If RMAN failed to run with similar error again, either adjust the shared_pool_size to larger, or pin its package to shared pool. Alternatively, set shared_pool_reserved_size to 13.5 MB, or some value which will helps depending on database usage

Please use following PayPal donate if my post helped