Monday, October 1, 2012

My Aunt Polly Game

Likes
Dislikes
Soccer, Football
Cricket
Nashville
Mt Juliet
Trees
Plants
Moon
Stars
Speed
Fast
Food
Eat
Tennessee
Kentucky
Jeff, Willie
Everybody else
Glass
Window
Beer
Wine

For secret of the game, please scroll down.







































My aunt Polly like each word with double letters.

Tuesday, May 1, 2012

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

Friday, April 27, 2012

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.

Wednesday, January 25, 2012

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$.