Queries to find Locks And Active Sessions in Oracle

SELECT vs.username,
 vs.osuser,
 vh.sid locking_sid,
 vs.status status,
 vs.module module,
 vs.program program_holding,
 jrh.job_name,
 vsw.username,
 vsw.osuser,
 vw.sid waiter_sid,
 vsw.program program_waiting,
 jrw.job_name,
 'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command"
FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw,
 dba_scheduler_running_jobs jrh,
 dba_scheduler_running_jobs jrw
WHERE   (vh.id1, vh.id2)
        IN
        (
            SELECT id1, id2
            FROM v$lock
            WHERE request = 0
            INTERSECT
            SELECT id1, id2
            FROM v$lock
            WHERE lmode = 0
        )
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid
 AND vh.sid = jrh.session_id(+)
 AND vw.sid = jrw.session_id(+);



 select * from V$SESSION b, V$PROCESS a
 where b.paddr = a.addr
 and type = 'USER'
 order by spid;
 

JAX-WS Web service with Application Authentication, consumed by C# client

Web Service JAX-WS with application authentication

This listing corresponds to an extract of an EJB with a session bean published as web service.

The yellow background code is the relevant portion.

         ...

            @Resource

            WebServiceContext webServiceContext;

            /**

             * Default constructor.

             */

            public WsEmpleados() {

                        // TODO Auto-generated constructor stub

            }          

        // Las excepciones emitidas aqui se envian como SOAP Faults

            public ArrayList<Empleados> ObtenerEmpleados(ArrayList<Paises> paises,

                                   String fecha) throws NonExistentData, InvalidUser {

                       

                       

                        MessageContext messageContext = webServiceContext.getMessageContext();

                        // Mapeo de encabezados http

                        Map<?,?> requestHeaders = (Map<?,?>) messageContext.get(MessageContext.HTTP_REQUEST_HEADERS);

                        List<?> userheaders= (List<?>) requestHeaders.get("username");

                        List<?> paswordheaders= (List<?>) requestHeaders.get("password");

                        String username = "";

                        String password = "";

                        if (userheaders!= null) {

                                   username = userheaders.get(0).toString();

                        }

                        if (paswordheaders!= null) {

                                   password = paswordheaders.get(0).toString();

                        }

       

//The credential validation can be done by a lot of options this is just one sample easy to understand

                        if (!(username.equals("usr1") && password.equals("VFSkdaass"))) {

                                   throw new InvalidUser();

                        }

                       

                    ...

                        return empleados;

            }

}

C# code listing to consume the Java JAX-WS web service with application authentication using http headers

Once you have created the proxy using the Java Web service WSDL we can do the call of the exposed method by using the OperationContextScope in order to be able to send http headers or Soap headers depending on the WS Implementation.

//Web service proxy generated by Visual Studio

Empleados.WsEmpleadosRemoteClient  proxy = new wsEmpNPrec.Empleados.WsEmpleadosRemoteClient ();

//Arreglo con el resultado de la invocación

                    wsEmpNPrec.Empleados.Empleado[] empleados = null;

//Adding the http headers throught the OperationContext.

                    using (new OperationContextScope(proxy.InnerChannel))

                    {

                        HttpRequestMessageProperty userHeader = new HttpRequestMessageProperty();

                        userHeader.Headers.Add("username", "usr1");

                        userHeader.Headers.Add("password", "VFSkdaass");

                        OperationContext.Current.OutgoingMessageProperties[HttpRequestMessageProperty.Name] = userHeader;

                        empleados = proxy.ObtenerEmpleados  (parametros2);

                    }  

This is not a good way to implement application authentication but is still one and if you can add HTTPS to this, you can have a not to bad and fast option to do it, anyway the http headers can be used for other purposes too.

An approach like IDENTITY(INT,1,1) FOR TEMP TABLE IN SQL AZURE AND SQL 2014

Some times we need to add an ID for a rowset in SQL Server, the most common way to do it is by using a temp table, but for SQL Azure and SQL Server 2012 and 2014 the SELECT .. INTO.. clause can not be execute, so we have to use the ROW_NUMBER() OVER clause instead. The effect of GETDATE() and the COL1 and COL2 fields will get a unique key.


CREATE TABLE #NO_DEDUCIBLE (
ID INT NULL,
[CUENTA] VARCHAR(50) NULL,
[TOTAL_DEDUCIBLE] FLOAT NULL,
[TOTAL_NODEDUCIBLE] FLOAT NULL,
[ES_GASTO] INT NULL
)



INSERT INTO #NO_DEDUCIBLE
SELECT
ROW_NUMBER() OVER (ORDER BY GETDATE(), COL1, COL2 ) ID,
                        COL1,
                        COL2,
@CUENTA_NODEDUCIBLE [CUENTA],
sum(DCOM.NODEDUCIBLE) TOTAL_DEDUCIBLE,
0 TOTAL_NODEDUCIBLE,
1 ES_GASTO
FROM
...

WHERE DCOM.COMPROBACIONID = @COMPROBACIONID
                 GROUP BY COL1, COL2