ORA-24247 Error

Today get the next error when create a new procedure that send a mail from database.

ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP

The problem is that version is 11g, and use ACL control. We need direct access (using dbms_network_acl_admin package) or using the “AUTHID  CURRENT_USER” clause;

CREATE PROCEDURE proc_name (…..) AUTHID CURRENT_USER AS

HTH – Antonio NAVARRO

ORA-20006 Error

Today, I get the next error;

ORA-20006: Unable to restore statistics , statistics history not available

I was executing the next package;

execute dbms_stats.restore_table_stats (‘SIEBELA’,’S_EVT_ACT’,’25-DEC-14 12.00.00.00000 PM +02:00′);

The problem is that dba_tab_stats_history table have not rows for this date. There is not statistics for this time.

HTH – Antonio NAVARRO

RMAN-00571 And RMAN-06135 Errors Executing Backup

Running the backup (full and hot) in a database fails with the following error;

host command complete
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06135: error executing host command: Additional information: 256

Looking at MOS see that is affected by CF type locks, these occur when acquiring a lock on the file control. Examined the code  sql that was running at the time in the database, create appear several indices. On another note on metalink I find this;

When performing DML operations using either NOLOGGING or UNRECOVERABLE option, then oracle records the unrecoverable SCN in the controlfiles. Typically you will see an increase in waits appearing for ‘control file parallel write’ as well however the session is not blocked for this wait event but rather the session performing the controlfile write will be holding the CF enqueue and the other sessions performing the unrecoverable (nologging) operation will be waiting to get a CF enqueue to update the controlfile with the unrecoverable SCN

Typical operations that can produce

direct load (SQL*Loader)
direct-load INSERT
CREATE TABLE … AS SELECT
CREATE INDEX
ALTER TABLE … MOVE PARTITION
ALTER TABLE … SPLIT PARTITION
ALTER INDEX … SPLIT PARTITION
ALTER INDEX … REBUILD
ALTER INDEX … REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

HTH – Antonio NAVARRO

ORA-12841 Error When Enable Parallel DML

This is a typical error when activated using Parallel DML in the sesión;

 

ALTER SESSION ENABLE PARALLEL DML;

 

If this command is not the beginning of the transaction, for which we have made a commit or rollback. Otherwise receive the following error;

 

ORA-12841: Cannot alter the session parallel DML state within a transaction

 

HTH – Antonio NAVARRO

 

Table Growth Trend

Since 10g exist the DBMS_SPACE package, and more concrete the OBJECT_GROWTH_TREND function. This function show us the growth pattern for existing objects (like Tables or Indexes). It predict (more or less) the futere, Ess;

 -- EXAMPLE FOR A TABLE ----------
SELECT *
FROM   TABLE(DBMS_SPACE.OBJECT_GROWTH_TREND ('SIEBEL','S_OM_TEMP','TABLE'))
ORDER BY TIMEPOINT;


-- EXAMPLE FOR A INDEX ----------
SELECT *
FROM   TABLE(DBMS_SPACE.OBJECT_GROWTH_TREND ('SIEBEL','S_OM_TEMP_M1','INDEX'))
ORDER BY TIMEPOINT;

HTH – Antonio NAVARRO