In a case like this where nothing significant has changed I think you are best off bringing this up on the PostgreSQL email lists. Not only will you get help with your issue in terms of workarounds but if there is a planner bug somewhere the development team will be given notice so they can fix it.
This may be one of those cases where the level of help you can expect there is far better than you can expect here.
I'm afraid your requirements cannot be met. If you want to use UTL_HTTP
functions (and those of other network-related packages), you'll need to define ACLs in 11gR2. There is no alternative.
If you don't care about the security implications, the simplest thing to do would be to create an ACL that allows access to everything, and assign it to all the database users that need to use the functions.
The Fine-Grained Access to Network Services in Oracle Database 11g article has an example of how to do that:
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'open_acl_file.xml',
description => 'Allow access to all hosts',
principal => 'TEST', -- put your user there
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'open_acl_file.xml',
host => '*', -- this allows all hosts
lower_port => 1, -- on ports 1 to 9999
upper_port => 9999);
COMMIT;
END;
/
If you need to assign that ACL to other users, use:
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'open_acl_file.xml',
principal => 'TEST2', -- your other user here
is_grant => TRUE,
privilege => 'connect',
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
END;
/
Best Answer
Apparently, you have a view,
blocking_procs
on top ofpg_stat_activity
. You will need to drop that view and reinstall it after the upgrade.