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
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
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
Yesterday Microsoft released a porting of 2016 version on Linux, of course, at the moment only for Red Hat, Ubuntu and Docker. It represents a change in the world’s database. All multiplatform databases allways are wellcome. For download this version, please refer the next web link;
HTH – Antonio NAVARRO
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
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
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
