How To See Recovery Model In SQL Server

In this blog entry I like show how to see the backup recovery model for all database in SQL Server. Of course, there are many ways to see this information but in this case with this simple query you can see it for all databases into the instance;

 

SELECT NAME AS [DATABASE NAME],
RECOVERY_MODEL_DESC AS [RECOVERY MODEL] FROM SYS.DATABASES
GO

HTH – Antonio NAVARRO

Advertisements

ORA-00034 Error When Commit In Curren Session

This co-worker ask me about the next error last morning when I was executing a pl/sql script;

 
BBDD_12> SELECT DBMS_METADATA.GET_DDL ('TABLESPACE','F_MOVISTAR_DAT') FROM DUAL;
ERROR:
ORA-00034: no se puede ROLLBACK en la sesión PL/SQL actual
ORA-06512: en "SYS.KUPU$UTILITIES_INT", línea 735
ORA-00034: no se puede COMMIT en la sesión PL/SQL actual
ORA-06512: en "SYS.DBMS_METADATA", línea 6069
ORA-06512: en "SYS.DBMS_METADATA", línea 8666
ORA-06512: en línea 1

I tell him it is a “logical lock” that I use when to execute stored procedure or anonymous pl/sql of mine or other persons and not perform a commit in an irresponsible way. In my login.sql I have the next entry;

alter session disable commit in procedure;

To solve it you must only switch it to enable;

BBDD_12> ALTER SESSION ENABLE COMMIT IN PROCEDURE; 

HTH – Antonio NAVARRO

 

RDBMS 32 Or 64 Bits?

In this entry, I like show how to know  if platform is 32 Bits or 64 Bits, for this simple question you can user the next query.

SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

Output example for 32 bits platform;

BBDD*ANTO> SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

PLATFORM
__________
32

Output example for 64 bits platform;

BBDD*ANTO> SELECT DISTINCT(length(addr)*4) “PLATFORM” FROM v$process;

PLATFORM
__________
64

HTH – Antonio NAVARRO

A Powerfull Inverse Dictionary

Recently I have discovered a powerfull inverse dictionary web-based. A inverse dictionary use the hash (password encryted) to get the password (in readble format). Usually you can get the hash in the next ways;

For Oracle database, versions 8,9,10 (in 11 and 12 it is possible too but you need query another view);

select passsword from dba_user where username =’SYSTEM’

For MySQL;

use mysql

select password from user where username=’root’ — Remember this engine is case sensitive by default
;

The web link to this tool;

crackstations inverse dictionary

It supports a lot of algorithms but not support hash with salt, salt concept in cryptography is similar to create a random number and use “add” to clear password or username with the propourse the create hash specific for one system (machine, database, app).

 
Inverse dictionary is a table with all or many possibles passwords for a specific user (usually system, root…). I have tried a pair the mysql database (root user) and it works fine. In the Oracle case doesn’t because of since version 11 Oracle uses salted hash.

 
HTH – Antonio NAVARRO

A Pair Of Curiosities

In this blog entry I would like talk about a pair of curiosities about OPENVMS system. Many people don’t know this powerfull operanting system, but it is maybe one of the bests systems never created. I’m a unix fanboy but I must recognized that VMS, actually, continues to better than unix/linux in many features.

Now, if you enter the next command;

$ EXIT %X34b4

You get the output below;

%SYSTEM-F-GAMEOVER, all your base are belong to us

one more, write;

$ EXIT %Xb70

And the output is;

%SYSTEM-W-FISH, my hovercraft is full of eels

HTH – Antonio NAVARRO

Generating Normal Distribution

Many times need create a tables, normally for testcases porpouses, with random data sets. Create those datas look easy, but it depends on we need. Usually you’ll create data sets in way similar to this;

 
DB12> create table tab1 (data number);

Tabla creada.

DB12> insert into tab1 select level from dual connect by level <= 10;

10 filas creadas.

DB12> select count (*) from tab1;

  COUNT(*)
__________
        10

Okay, it is correct but this create a uniform distribution. As I said before, sometimes we will need data sets with specific data distributions, especially to play with the histograms when manipulating the statistics. Oracle only include the normal distribution, maybe later to take other types of distributions, such as logarithmic, included in the engine, so you can implement by programming, Java is a good option.

Let’s look at the example to call the normal distribution;

 
DB12> declare
  2  begin
  3    for i IN 1..10 LOOP
  4       dbms_output.put_line (trunc (dbms_random.value (1,10)));
  5    END LOOP;
  6  END;
  7  /
3
2
7
4
8
7
1
7
4
6

In this example we have generated ten numbers, between 1 and 10, with normal distribution.

HTH – Antonio NAVARRO