SCRIPTS - ORACLEAGENT BLOG

Get installed sqlpatches in database

— From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
(or)
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,action, status, description,version,patch_id, bundle_series FROM sys.dba_registry_sqlpatch;

— For 11g and below:
set lines 2000
select * from dba_registry_history;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

GET INDEX USAGE

—Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
— First enable monitoring usage for the indexes.

alter index INDEX_NAME.S_ASSET_TEST monitoring usage;

–Below query to find the index usage:

select * from v$object_usage;

Get sql query to html format

— We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “EMPLOYEE REPORT –

–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
spool report.html
Select * from scott.emp;
spool off
exit

row_count of all the tables of a schema

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype(‘select /+ PARALLEL(8) */ count() cnt from “&&SCHEMA_NAME”.’||table_name),’/ROWSET/ROW/CNT’))
rows_in_table from dba_TABLES
where owner=’&&SCHEMA_NAME’;

Disable or enable all triggers of schema

BEGIN
FOR j IN (SELECT trig_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE ‘ALTER TRIGGER ‘ || i.trig_name || ‘ DISABLE’;
END LOOP;
END;
/

Get size of the database

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Feb, 2021

Oracle database Health check scripts

PERFOMANCE TUNING SCRIPTS

MONITOR SESSION
SQL PROFILE & BASELINE
ALL IN ONE SCRIPT
HISTORY OF EXECUTED QUERIES
SQL HISTORY
WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS
MONITOR SQL’S

MAIL SCRIPTS

Get DDL ‘s For All objects in oracle database

TEMP TABLESPACE scripts

UNDO TABLESPACE scripts

Feb, 2021

Create and Configure ACLs in Oracle database

Let us see how to create and configure ACLs in Oracle database.

ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21

We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.

Below Steps need to follow configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

— Drop an access control list :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

— TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

— If we want to delete or revoke this permission :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for INDIA user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;
HOST                   LOWER_PORT      UPPER_PORT       PRIVILEGE       STATUS
------------------- -----------------  ---------------  ----------  ------------
10.0.06.01                25               25          connect           GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

–Status column must have value granted.

SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.

ACL                     PRINCIPAL    PRIVILEGE     IS_GRANT   START_DATE   END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml    INDIA       connect        true

Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-

ACL                     PRINCIPAL      PRIVILEGE     IS_GRANT    START_DATE    END_DATE
---------------------   -----------   -----------    --------     --------     -------
/sys/acls/utl_mail.xml       INDIA       connect      true
/sys/acls/utl_smtp.xml       INDIA       connect      true
/sys/acls/utl_tcp.xml        INDIA       connect      true

TO enable this ACL permission for a different USER other than above user(INDIA) :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

ACL                      PRINCIPAL      PRIVILEGE     IS_GRANT   START_DATE   END_DATE
-----------------       -------------   -----------   ----------  --------   ----------
/sys/acls/utl_smtp.xml     INDIA         connect        true
/sys/acls/utl_smtp.xml     TEST          connect        false

— This query has to be run with TEST user and Status column should have value “GRANTED“

SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;

HOST                      LOWER_PORT       UPPER_PORT        PRIVILEGE      STATUS
--------------------       ----------     -----------       ---------      -------
10.0.06.01                    25              25              connect       GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

Now check your procedure to send mail through test user , it will work.

After this configuration is completed. we can send a mail with and without attachment. Please check the link for further.

Feb, 2021

Create and Configure ACLs in Oracle database

Let us see how to create and configure ACLs in Oracle database.

ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21

Below Steps need to follow configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

— Drop an access control list :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

— TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

— If we want to delete or revoke this permission :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for INDIA user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;
HOST                   LOWER_PORT      UPPER_PORT       PRIVILEGE       STATUS
------------------- -----------------  ---------------  ----------  ------------
10.0.06.01                25               25          connect           GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

–Status column must have value granted.

SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.

ACL                     PRINCIPAL    PRIVILEGE     IS_GRANT   START_DATE   END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml    INDIA       connect        true

ACL                     PRINCIPAL      PRIVILEGE     IS_GRANT    START_DATE    END_DATE
---------------------   -----------   -----------    --------     --------     -------
/sys/acls/utl_mail.xml       INDIA       connect      true
/sys/acls/utl_smtp.xml       INDIA       connect      true
/sys/acls/utl_tcp.xml        INDIA       connect      true

TO enable this ACL permission for a different USER other than above user(INDIA) :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

ACL                      PRINCIPAL      PRIVILEGE     IS_GRANT   START_DATE   END_DATE
-----------------       -------------   -----------   ----------  --------   ----------
/sys/acls/utl_smtp.xml     INDIA         connect        true
/sys/acls/utl_smtp.xml     TEST          connect        false

— This query has to be run with TEST user and Status column should have value “GRANTED“

SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;

HOST                      LOWER_PORT       UPPER_PORT        PRIVILEGE      STATUS
--------------------       ----------     -----------       ---------      -------
10.0.06.01                    25              25              connect       GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

Now check your procedure to send mail through test user , it will work.

After this configuration is completed. we can send a mail with and without attachment. Please check the link for further.

SEND MAIL USING UTL_SMTP

UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).

We can send two types of mails using UTL_SMTP:-

Mails without attachment.
Mails with attachment.

Mails without attachment :-

SQL> DECLARE c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; 
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

DECLARE
*
ERROR at line 1:
ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access
denied by access control list (ACL)
ORA-06512: at line 25

If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database we have to configure ACL in order to use PL/SQL packages.

Once we configure ACL then we are able to successfully send mails using above mentioned code.

Please follow below steps :-

SQL> DECLARE
c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; 
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

PL/SQL procedure successfully completed.

Mail has been send now.

Category Archives: SCRIPTS

Feb, 2021

Feb, 2021

Let us see how to create and configure ACLs in Oracle database.

Below Steps need to follow configure :-

Feb, 2021

Let us see how to create and configure ACLs in Oracle database.

Below Steps need to follow configure :-

UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).