Query to find unused (not referenced in other DB packages, procedures, function) package

Following query can be used to find unused (not referenced in other DB packages, procedures, function) packages. Packages can still be used in DBMS Jobs / Scheduler Jobs / Application select distinct owner || '.' || name from dba_DEPENDENCIES where name in ( select referenced_name from dba_DEPENDENCIES where type != 'SYNONYM' and owner in ('SCOTT', 'SCOTT2') group by referenced_name having count(*) = 1) and owner in ('SCOTT', 'SCOTT2') and type in ('PACKAGE BODY', 'PACKAGE') order by 1

Oracle ACL Error Resolution:ORA-24247: network access denied by access control list (ACL)

We were often seeing following network ACL error.

ORA-24247: network access denied by access control list (ACL)

Query to check for existing ACLs.
SELECT NACL.ACLID,ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE NACL.ACLID = ACE.ACLID ;

We had to run following steps to resolve the issue.


EXEC DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'mails.xml' );


EXEC DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'mails.xml', description => 'Mail ACL', principal => 'SCOTT', is_grant => TRUE, PRIVILEGE => 'connect');
Principal ==> says which schema will primarily own the ACL
is_grant => TRUE will allow other schema in DB to access this ACL (this line needs verification)

BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl          => 'mails.xml',
                                         principal    => 'SCOTT', --Schema name
                                         is_grant     => TRUE, --same as above
                                         PRIVILEGE    => 'connect',
                                         position     => NULL,
                                         start_date   => SYSTIMESTAMP,
                                         end_date     => NULL);

   COMMIT;
END;
/


EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'mails.xml', HOST => 'mailhost', lower_port => 21, upper_port => 30);

BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl          => 'mails.xml',
                                         principal    => 'SCHEMA2',
                                         is_grant     => TRUE,
                                         PRIVILEGE    => 'connect',
                                         position     => NULL,
                                         start_date   => SYSTIMESTAMP,
                                         end_date     => NULL);

   COMMIT;
END;
/

For more details: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm

Parallel Hint

SELECT /*+ PARALLEL(a) */ COUNT(*) FROM ABCD a ;

Parallel Hint makes operation faster. It does so by providing more threads to the current SQL statement. Not advisable to run in Production Environment during Peak Hours.

Oracle Unique Constraint sys.i_procedure1 violated while creating package

Recently I faced issue in Oracle 11g, where after dropping a package, I was not able to recreate it. Recreating package was giving error, Unique Constraint (sys.i_procedure1) violated.

It was an issue with Oracle Dictionary table not getting updated while dropping package.

DBAs had to take following steps to resolve the issue.

select * from obj$ where name = 'PACK1' ;

select * from user$ where name = 'ABC' ; --This query is to get owner#, which can be joined with obj$

After getting obj#, we had to delete that object from 3 tables.

procedure$, source$ and obj$.

Oracle XML to TABLE using XMLTABLE

Query:
SELECT seq
, ID
, NAME
FROM XMLTABLE('/xml/emp'
PASSING XMLTYPE('ArpitVenkat')
COLUMNS seq FOR ORDINALITY
, ID VARCHAR2(3) PATH '@id'
, NAME varchar2(10) path 'name'
) AS tbl

Output:
SEQ ID NAME
---------- --- ----------
1 3 Arpit
2 4 Venkat