web analytics

Oracle Data Modeler: unable to create Java instance

The following error was displayed during the start of Oracle SQL Developer Data Modeler (v3.3.0.734) on Windows:

Unable to create an instance of the Java Virtual Machine Located at path: ..\..\jdk\jre\bin\client\jvm.dll

This could be fixed by editing the file datamodeler\bin\datamodeler.conf : try to change the following line AddVMOption -Xmx768M to AddVMOption -Xmx512M

and restart Data […]

Oracle: error messages for read only database

The old Oracle database (8i) was switched to the read-only mode. The following messages are shown now in alert.log: ***Warning – Executing transaction without active Undo Tablespace

It’s possible, that the error is caused by Oracle bug 3270493 (EXCESSIVE QMNX TRACE FILES WHEN PLACING STANDBY IN READ ONLY MODE). Workaround is to set aq_tm_processes parameter […]

Oracle: converting timestamp to date

Here is the method to display timestamp in the convenient format:


Simple conversion of the timestamp:


Oracle: who is connected to the server

Here is the simple query, showing who is connected to the server.

col username FOR a15 col program FOR a25 trun SELECT sid, serial#, username, program, SUBSTR(module,1,10) module, logon_time FROM v$session ORDER BY 1,2;

This is almost the same query, limiting the output to the active users:

SELECT sid, serial#, username, program, SUBSTR(module,1,10) module, logon_time […]

Oracle: kill session

The command to kill Oracle is simple:


sid – session ID serial – session serial number

If the version 11g and this is RAC, additional syntax is possible:

ALTER SYSTEM KILL SESSION ‘&sid,&serial,@&inst_id’;

inst_id – instance id number (1, 2, 3 …)

Script to find the […]

mySQL: How to reset root password

If the password for the mysql “root” user is lost, it’s still possible reset it to some other value. The restart of the mySQL server will be necessary, of course.

The following should be done:

Stop the server It could be done normally:

/etc/init.d/mysql stop

or effectively:

ps -ef | grep mysql … mysql 25079 […]

Oracle: get the tablespace structure

The easiest method to get the tablespace structure is to use the dbms_metadata package:

SET linesize 200 LONG 50000 pagesize 5000 SELECT dbms_metadata.get_ddl(’TABLESPACE’,tablespace_name ) FROM dba_tablespaces WHERE tablespace_name IN ( ‘&your_tablespace’ );

Additionally, exp/imp (or expdp/impdp) utilities could be used. They could generate the DDL statements for all objects in the database, which could be […]

Oracle: buffer overflow (ORU-10027)

Sometimes it’s necessary to show the output of the PL/SQL script. Usually the dbms_output package is used:

SET serveroutput ON DECLARE  i NUMBER ; BEGIN  i:=0 ;  WHILE i< 100000 LOOP   i:=i+1 ;   dbms_output.put_line(’This is just test line #’ || TO_CHAR(i) ) ;  END LOOP ; END; /

After several thousands lines the […]

Oracle: how to clone profiles

Here is the simple script for cloning the Oracle profiles.

SET serveroutput ON DECLARE  CURSOR c_profiles IS   SELECT PROFILE, RESOURCE_NAME, LIMIT   FROM dba_profiles   ORDER BY PROFILE, resource_name;   s_PROFILE                     dba_profiles.PROFILE%TYPE ;   s_prev_PROFILE        dba_profiles.PROFILE%TYPE ;   s_RESOURCE_NAME     […]

Oracle: quotes and quote operator

There are several methods to put the quote into the string.

The first (and very traditional) one: use 2 quotes.

SELECT ‘This ” is quote’ FROM dual;

If there are more than one quote, it’s difficult to read and write such strings

The second: use chr(39)

SELECT ‘This ‘ || CHR(39) || ‘ is quote’ […]