Using DBMS_DDL Package To Compile

Today I like post a different way to compile. We can use the DBMS_DDL package to compile objects, in this case I show the example of a trigger that is invalid status, using the DBMS_DDL package We compile it get a valid status.

 

INFO*ANTO> r
  1* select OWNER, status from dba_objects where object_name ='TG_BU_INCENT_AGENTS'

OWNER     STATUS
_________ _______________  

INFO      INVALID

1 row selected.

INFO*ANTO> EXEC DBMS_DDL.alter_compile('TRIGGER','INFO','TG_BU_INCENT_AGENTS');

Procedimiento PL/SQL terminado correctamente.

INFO*ANTO> select OWNER, status from dba_objects where object_name ='TG_BU_INCENT_AGENTS';

OWNER     STATUS
_________ _______________  

INFO      VALID

1 row selected.

HTH – Antonio NAVARRO

 

RMAN Doesn’t Work In OpenVMS

This morning at work a coworker ask me about a problem He has when execute a rman session in an OpenVMS box. Yes, I know VMS is a older operating system but I’m reponsaible of some of them. The problem is when type rman a command line prompt it looks do nothing, returning the DCL prompt. Like example below;

 

$ rman

It is a bug (5703287), corrected on release 10.2.0.4.0. As a workaround you can pass a parameter to solve the problem like the following example;

 

$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Thu Apr 28 16:46:39 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: OPTA (DBID=797648421)

HTH – Antonio NAVARRO

MySQL Synonyms?

This morning at work a coworked ask me about how he can create a synonym in a MySQL database for a new application which he are developing. The problem (or maybe is not a problem) is that MySQL doesn’t support synonyms, however you can create a view for replace the functionality of synonyms.

HTH – Antonio NAVARRO

SQL Server Error: 18456

Today I have reciving lots of errors like show below in a SQL Server (Version 2008 R2).

Login failed for user ‘WORLD\SYS_SCOMAction’. Reason: Failed to open the explicitly specified database. [CLIENT: <named pipe>]
Error: 18456, Severity: 14, State: 38.

The problem looks be in an application what maybe is set wrong or reference inexisting database. The issue is how I can localize which database the process is trying connect. For this task You can use the SQL SERVER PROFILER

start the Profiler and set the default template using below two category of events:

1.Security Audit, keep the “Audit Login Failed” event.

2.Errors and Warning, check the “ErrorLog” and “User Error Message” events.

Start the trace, after running trace you will see error message saying:

“Cannot open database requested by the login. The login failed.”

Here you can see the database which the process is trying connect.

HTH – Antonio NAVARRO

What Are CTE Tables

A Common Table Expression, or CTE is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can be thought of as alternatives to derived tables (subquery), views, and inline user-defined functions.

Common table expressions are supported by Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), Oracle calls CTEs “subquery factoring.

Below We have a example of a CTE;

WITH MY_TABLE AS 
(
  SELECT 
    USERNAME, 
    OSUSER, 
    PROGRAM 
  FROM 
    V$SESSION 
  WHERE 
    STATUS ='ACTIVE'
)
SELECT A.USERNAME 
FROM MY_TABLE A
/

HTH – Antonio NAVARRO

Error: 18056, Severity: 20, State: 29 On SQL Server

Today I have been reported this error from the development team. Looking at MSDN

Message
The client was unable to reuse a session with SPID XXXX, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

Reviewing previous messages appear messages like, in my case I have the next error and similars;

Message
SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\FAST_DELIVERY_1.LDF] in database [DELIVERY] (30). The OS file handle is 0x0000000000000978. The offset of the latest long I/O is: 0x00000016506800

It seems that there has been some problem with the physical layer, may be the problem was on the server or the NAS. The problem has made the process crashsed possibly also lacks the error handling process. After a while reconnects the process without problems and works fine.

HTH – Antonio NAVARRO