DBMS_LOCK Must Be Declared In Swingbench

Today I was simulating workload with Swingbench (Version 2.5). I fan of this free tool it is a workload simulator for Oracle databases, of course, HammerDB is a worderful tool too. When executed the first workload I received lots of errors from event log into the Console. It says me that SOE.ORDERENTRY package body was invalid.

When I tryed compile it get the next error;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Advertencia: Cuerpo del paquete modificado con errores de compilación.

Transcurrido: 00:00:00.34
XXXXX>  show err
Errores para PACKAGE BODY SOE.ORDERENTRY:

LINE/COL ERROR
________ _________________________________________________________________
79/13    PL/SQL: Statement ignored
79/13    PLS-00201: identifier 'DBMS_LOCK' must be declared
82/13    PL/SQL: Statement ignored
82/13    PLS-00201: identifier 'DBMS_LOCK' must be declared

Of course, I usually never use the sys user to install anything, in my case I installed Swingbench with system user. The solution is so easy like

 

 
os11> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 17 16:06:38 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>
SQL> grant execute on dbms_lock to soe;

Grant succeeded.

 

And recompile the package body again;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Cuerpo del paquete modificado.

Transcurrido: 00:00:00.42
XXXXX>  show err
No hay errores.

HTH – Antonio NAVARRO

 

Advertisements

SANDBOX Mode In MySQL

From MySQL (DOC 5.7.6 )
MySQL restricts the client to “sandbox mode” in which the server permits to the client only those operations necessary to reset the expired password. Which action is taken by the server depends on both client and server settings, as discussed later.

These operations are permitted If the server restricts the client to sandbox mode

  • The client can reset the account password with ALTER USER or SET PASSWORD. The server restores normal access. Sandbox don’t take effect
  • The client can use SET statements

How to activate the sandbok mode

  • Set disconnect_on_expired_password to disabled.
  • If disconnect_on_expired_password is enabled (is the default), the server disconnects the client with an ER_MUST_CHANGE_PASSWORD_LOGIN error.

HTH – Antonio NAVARRO

How To Know Wich Web Server Use A Website

Today a coworker ask me about how to know whick web server is using a website. Usually majority of websites use Microsoft IIS or Apache, of course, there are others. Which is the finality of this? In case of an attack against the website a very important step is know what software is working.

In general, the more we can know of an easier system is to attack it, if we can know the product, the version and even patch level (although this last one is more complex to see), we simplify the work a lot to an attacker that is going to focus , at least, to launch everything that is known and published against that product. If, on the other hand, you do not know what there is, you will have to launch multiple attacks in a trial and error technique.

In our case we are going to use a tool called curl, basically what it does is file transfer (xml, json, raw, …). Supports multiple FTP, FTPS, HTTP, HTTPS, TFTP, SCP, SFTP, Telnet, DICT, FILE and LDAP protocols.

If the website is not securize against this kind of attacks;

 
curl -s -i http://xxxxxx.xxxxxx.xxx

HTTP/1.1 404 Not Found
Content-Type: text/html; charset=us-ascii
Server: Microsoft-HTTPAPI/2.0
Date: Tue, 04 Apr 2018 16:17:36 GMT
Connection: close
Content-Length: 315


Not Found

<h2>Not Found</h2>
<hr><p>HTTP Error 404. The requested resource is not found.</p>

HTH – Antonio NAVARRO

How To Cancel A SQL Statement In 18c

Until now is you want to cancel a sql you must kill (in the worst of the cases) or disconnect (in the best) the session who execute the statement. In the new release of Oracle (18c) you can “kill” a statement without kill the session. There are a new clause “cancel sql” into the “alter system” to perform this action.

You need the sqlid and the sid and serial# for session executing the sql_id which you want to kill. The format is show below;

alter system cancel sql ‘sid, serial#, sql_id’;

and an example;

alter system cancel sql ‘403, 35122, 77jhfmw06g05u’;

HTH – Antonio NAVARRO

 

Error ORA-00933 Creating DB Link With Special Characters

Today I get the next error executing a create database link commandd;

ORA-00933: SQL command not properly ended

The syntax is show below

create database link unodos connect to Stevy identified by Pa**word using ‘remotedb.worl.es’;

In this case, the pass have special characters (two asterisks) and Oracle thinks it is sql syntax, you need specify it like string, you can this by user quotes like in the next sample;

create database link unodos connect to Stevy identified by “Pa**word” using ‘remotedb.worl.es’;

 

HTH – Antonio NAVARRO

crsctl stop has vs crsctl stop crs

Last week talking with a coworker, there was the issue of which command to use to stop a clutch, I personally have seen many people use the crsctl stop has, but we really did not know the difference to use stop has or use stop crs, a look at the documentation, always remember, RTFM (Read The Fine Manual) we clarified. Like show below;

use crsctl stop has command for stop a restart (one node OR single-instance)

use crsctl stop crs command for stop a cluster (two or more nodes)

HTH – Antonio NAVARRO

 

Get Stats Preferences

In a previous post I showed the way to get the stats preferences for all database. Today I post the method for all levels (Table, Schema and Database);

TABLE Level:

 
-- Replace  OWNER_USER  by owner user
-- Replace  TABLE_NAME by table name
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'INCREMENTAL'),         
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'GRANULARITY'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'STALE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'ESTIMATE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'CASCADE')
FROM DUAL
/

SCHEMA Level:

   
-- Replace OWNER_USER  by owner user             
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'INCREMENTAL'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'GRANULARITY'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'STALE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'ESTIMATE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'CASCADE') 
FROM DUAL
/

DATABASE Level:

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'INCREMENTAL'),
  DBMS_STATS.get_prefs(pname=>'GRANULARITY'),
  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'CASCADE')
FROM DUAL
/

For all cases you can see histograms like show below;

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt   
FROM DUAL
/

HTH – Antonio NAVARRO