How to check ACL access in oracle

Below is the simplest way to find out ACL access in Oracle DB. Execute the below query in oracle command window. SELECT¬†utl_http.request (‘http://localhost/’) from dual; If you get any result then it means ACL access is available. Note : Any Web Application should be available in localhost or if you have internet access in Oracle…


Script to get object count in each schema in oracle

select owner,
max( decode( object_type, ‘TABLE’, cnt, null ) ) Tables,
max( decode( object_type, ‘VIEW’, cnt, null ) ) Views,
max( decode( object_type, ‘TRIGGER’, cnt, null ) ) triggers,
max( decode( object_type, ‘PACKAGE’, cnt, null ) ) packages
from ( select owner, object_type, count(*) cnt
from dba_objects
group by owner, object_type )
group by owner;

Script to compile invalid objects in oracle

SET SERVEROUTPUT ON SIZE 1000000 BEGIN FOR cur_rec IN (SELECT owner, object_name, object_type, DECODE(object_type, ‘PACKAGE’, 1, ‘PACKAGE BODY’, 2, 2) AS recompile_order FROM dba_objects WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’) AND status != ‘VALID’ ORDER BY 4) LOOP BEGIN IF cur_rec.object_type = ‘PACKAGE BODY’ THEN EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner || ‘”.”‘ ||…


Code to send Mail through Oracle

CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2, p_from IN VARCHAR2, p_message IN VARCHAR2, p_smtp_host IN VARCHAR2, p_smtp_port IN NUMBER DEFAULT 25) AS l_mail_conn UTL_SMTP.connection; BEGIN l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);–Host name and port number of SMTP UTL_SMTP.helo(l_mail_conn, p_smtp_host); — SMTP Host Name UTL_SMTP.mail(l_mail_conn, p_from); — from mail id UTL_SMTP.rcpt(l_mail_conn, p_to); — to mail id,…