Using Email (utl_smtp) in Oracle 11g (results in ORA-24247) – Network Access Control List (ACL).

Using UTL_SMTP to send email from your oracle database has changed in Oracle 11g. Or – to be more precise – accessing the remote network has changed. …

Initializing a remote connection using UTL_SMTP will typically result in error below if you did not configure your database network access lists (ACL) for the user involved. …

-1. the error
=== …

SQL> DECLARE
  2    v_mailsever_host VARCHAR2(30) := 'mail.<domain>.nl';
  3    v_mailsever_port PLS_INTEGER  := 25;
  4    l_mail_conn  UTL_SMTP.CONNECTION;
  5  BEGIN
  6    l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at line 6

SQL> sho user
USER is "SCOTT"
SQL>

“wat is dit nu weer !!!”

Allright – in Oracle 11g – you have to configure (grant) each and every network access point using so called Access Control Lists (ACL’s). Obviously user SCOTT is not configured to access the network point it’s trying to access here. …

The docs are here in ‘Managing Fine-Grained Access to External Network Services’ – //docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012 …

-0. check for current existing ACL’s
Let’s check out all currently presentAccess Control Lists in the database;
=== …

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

no rows selected

SQL>

Allright – none present – let’s create a new network access list using DBMS_NETWORK_ACL_ADMIN …

— privilege – Use ‘connect’ for UTL_TCP, UTL_SMTP, UTL_MAIL and …

Read more →

Oracle Advisor – DBMS_ADVISOR

Some ADDM dumps. …

Remove all advisory reports (ADM).
[REMI@DB01.REMIDIAN.COM]
SQL> select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' from dba_advisor_tasks;

'EXECDBMS_ADVISOR.DELETE_TASK('''||TASK_NAME||''')'
----------------------------------------------------------------
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_1300592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2700592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2220592007' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33108' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33113' )

[REMI@DB01.REMIDIAN.COM]
SQL>
Read more →

new explain plan options (dbms_xplan)

I still need to upgrade to 10g … …

I still tend to use this old fashioned ‘set autot trace exp’
[REMI@DB01.REMIDIAN.COM]
SQL> set autotrace trace exp
[REMI@DB01.REMIDIAN.COM]
SQL> select * from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes
          =2)



While in 10g the explain plan is pretty awseome – dbms_xplan package
[REMI@DB01.REMIDIAN.COM]
SQL> explain plan for select * from dual;

Explained.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

[REMI@DB01.REMIDIAN.COM]
SQL> 
Read more →

Invalidated synonyms

In Oracle 10G synonyms are marked invalid when you perform DDL on the referenced object. Luckily the synonym is marked valid again when you simply touch the invalidated synonym. The status of the synonym becomes valid again and the DBA_OBJECTS.LAST_DDL_TIME and DBA_OBJECTS.TIMESTAMP columns reflect the time the synonym was validated again. Listing below shows an example for a create or replace of a view with an associated synonym. …

[REMI@DB01.REMIDIAN.COM]
SQL> create view v_demo as select * from dual;

View created.

[REMI@DB01.REMIDIAN.COM]
SQL> create synonym s_demo for remi.v_demo;

Synonym created.

[REMI@DB01.REMIDIAN.COM]
SQL> select object_name, object_type, status
  2    from all_objects
  3   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME                      OBJECT_TYPE    STATUS
-------------------------------- -------------- ---------------
S_DEMO                           SYNONYM        VALID
V_DEMO                           VIEW           VALID

[REMI@DB01.REMIDIAN.COM]
SQL> create or replace view v_demo as select * from dual;

View created.

[REMI@DB01.REMIDIAN.COM]
SQL> select object_name,
  2         object_type,
  3         status,
  4       
Read more →

overcome SP2-0734 when using purge command

Issue new Oracle 10G ‘purge‘ command results in ‘SP2-0734: unknown command beginning “purge user…” – rest of line ignored.’ …

Purge Command in pre 10G SQL*Plus release

Whenever you want to issue the ‘purge’ command in any pre 10G SQL*Plus client you will notice that SQL*Plus denies to send your command to the server and it will raise an ‘SP2-0734’ error. You can easiliy overcome this by making use of dynamic sql (or just upgrade your client of course). …

[REMI@DB01.REMIDIAN.COM]
SQL> create table demo(k int);

Table created.

[REMI@DB01.REMIDIAN.COM]
SQL> drop table demo;

Table dropped.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BIN$HE/mKBNyRajgRAADuhSWrA==$0 TABLE

[REMI@DB01.REMIDIAN.COM]
SQL> 
[REMI@DB01.REMIDIAN.COM]
SQL> drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0";
drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0"
           *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


[REMI@DB01.REMIDIAN.COM]
SQL> [strong]purge user_recyclebin;
SP2-0734: unknown command beginning "purge user..." - rest of line ignored.[/strong]
[REMI@DB01.REMIDIAN.COM]
SQL> prompt &_SQLPLUS_RELEASE
902000700

Solution is beautiful in it’s simplicity, use dynamic sql and off you go. …

Purge recycle bin with a pre 10G SQL*Plus client
[REMI@DB01.REMIDIAN.COM]
SQL> begin 
  2    execute immediate 'purge user_recyclebin';
  3  end;
  4  /

PL/SQL procedure successfully completed.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;

no rows selected
Read more →

Automatic Workload Repository

A very short howto for the ‘Automatic Workload Repository’, the successor of perfstat. …

Interval

Some queries; …

Listing below shows one snapshot is taken every 20 minutes, the snapshot is stored for a week (7 days). …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 00:20:00.0                +00007 00:00:00.0

Alter the interval to once every hour and store it for a month (31 days); …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings
  3        ( interval   => 60
  4        , retention  => 31 * 24 * 60);
  5  end;
  6  /

PL/SQL procedure successfully completed.

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 01:00:00.0                +00031 00:00:00.0

History

Five latest snapshots …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select *
  2  from   (
  3         select snap_id, startup_time, begin_interval_time, end_interval_time
  4         from   dba_hist_snapshot
Read more →