Monday, January 30, 2012

Truncate Table From a Remote Database


Truncating from a remote database is not allowed. On a development database environment, developers are in need of truncating certain tables on a regular basis. Delete is allowed over database link, that’s an option. But, that’s a bad example for allowing fragmentation and high water mark over the period of time.

If you try to truncate a table on a remote database, you will get the following error:
ORA-02021: DDL operations are not allowed on a remote database.

As, procedure can be executed over a dblink, so following workaround can be a good option on such scenario.

1.    Create a procedure on Remote Database that can truncate
CREATE OR REPLACE PROCEDURE
Trunc_Rem_Tab(p_table_name VARCHAR2) AS
   v_sql_error_code PLS_INTEGER;
   v_sql_error_message VARCHAR2(512);
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;
EXCEPTION
   WHEN OTHERS THEN
      v_sql_error_code := SQLCODE;
      v_sql_error_message := SQLERRM(v_sql_error_code);
      DBMS_OUTPUT.ENABLE(5000);
      DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
      DBMS_OUTPUT.PUT_LINE(v_sql_error_message);
END Truncate_Remote_Table;

2.    Execute the procedure from local database.
BEGIN
   Trunc_Rem_Tab@REMOTE_LINK('TEST');
END;

DRG-10700: preference does not exist: CTXSYS.DEFAULT_WORDLIST

After data migration from 9i to 11g, was facing DEFAULT_WORDLIST text index creation issue.  After some research, it appeared that default_worldlist preference needs to be configured on ctxsys same as it was on old database.

Problem:
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_WORDLIST
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

SOLUTION:
Login as CTXSYS:
BEGIN
CTX_DDL.create_preference('CTXSYS.DEFAULT_WORDLIST', 'BASIC_WORDLIST');
END;

update dr$parameter
set par_value = 'CTXSYS.EMPTY_STOPLIST'
where par_value = 'CTXSYS.DEFAULT_STOPLIST';

BEGIN
CTX_DDL.create_preference('CTXSYS.DEFAULT_LEXER', 'BASIC_LEXER');
END;

Generating & sending email based excel reporting as attachment

Here is a sample demonstration of generating excel based oracle query report and send it via email as attachment.
Following query would generate an excel file.
$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF

set feedback off;
set feed off markup html on spool on
spool report.xls

select * from dba_data_files;

spool off
set markup html off spool off
EXIT
EOF

I’m working on Unix, redirecting script output to a file.
uuencode /home/oracle/report.xls report.xls > /home/oracle/out.mail

and then send that output as an attachment , following is an example.

mail -s "DB REPORT"  your_email_address@domain.com <./home/oracle/out.mail

This would send email as an attachment.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


After upgrading DB, Domain & Text indexes were failing. After some research, we found the issue is with CTXSYS schema. Some of the objects went invalid.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04063: package body "CTXSYS.DRIUTL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIUTL"
ORA-06512: at "CTXSYS.DRIPARSE", line 800
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 81

BEGIN
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvutl.GetInvoker
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 597
ORA-06512: at line 2

Solution is to reinstall Oracle Text usint catctx.sql that can rebuild ctxsys schema.

Connecting to database without TNS entry

Situations might arise that database TNS entry isn't added on client side, but you might have the entry details with you. Here is how you can connect to the database even if you don't have TNS file updated with connection details.

sqlplus username/password@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=linux01)(Port=1521))(CONNECT_DATA=(SID=test_db)))'

Similarly, a database link can be created without a TNS entry. Just mention the parameters and thats it.
CREATE  DATABASE LINK "TEST"  CONNECT TO "TEST_USER" IDENTIFIED BY "********"      USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=linux01)(Port=1521))(CONNECT_DATA=(SID=test_db)))'

Isn't it handy :)

Friday, January 27, 2012

ORA-24247 Trying To Send Email Using UTL_SMTP from 11g


After upgrading a database to 11gR trying to send emails using UTL_SMTP fail with
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 9

Starting with Oracle 11g "fine-grained access" was implemented to limit usage of packages like UTL_SMTP, UTL_HTTP connecting over the network to other services like mail server etc.

By default, the ports are blocked and ORA-24247 is raised to signal this.

To control the ACL, Package DBMS_NETWORK_ACL_ADMIN can be used to create ACL and grant access to particular user.
BEGIN
  -- Only uncomment the following line if ACL "network_services.xml" has already been created
  --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'network_services.xml',
    description => 'FTP ACL',
    principal => 'USER_NAME',
    is_grant => true,
    privilege => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'USER_NAME',
    is_grant => true,
    privilege => 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'network_services.xml',
    host => '*');
  COMMIT;
END;

Bingo, it works now!