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 Modeler after this.
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 to 0:
ALTER SYSTEM SET aq_tm_processes=0;
Here is the method to display timestamp in the convenient format:
SELECT TO_CHAR (SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') s_date
FROM DUAL;
Simple conversion of the timestamp:
SELECT CAST(SYSTIMESTAMP AS DATE) x_date FROM DUAL;
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
from v$session
where status = 'ACTIVE'
order by 1,2;
It’s important to set “MODULE” field for the running sessions. It makes the output more clear.
The setting could be done with the following command:
exec DBMS_APPLICATION_INFO.SET_MODULE( module_name=>'&module',action_name=>'&action' );
The command to kill Oracle is simple:
ALTER SYSTEM KILL SESSION '&sid,&serial';
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 TOAD session older than 1 day and kill them:
select
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' immediate;' CMD
from v$session
where
program like '%TOAD%'
and logon_time and status != 'KILLED'
;
If the session is killed on the Oracle level and still stays in the list for very long time with status ‘KILLED’, it could be killed on the OS level.
Warning! This is potentially dangerous operation! Use it only as the last resort.
select sess.sid, sess.serial#,
'OS:' || proc.spid SPID,
'App:' || sess.process process,
substr(sess.username,1,15) username ,
sess.program,
logon_time ,
sess.status
from v$session sess, v$process proc
where sess.paddr = proc.addr
and sess.status = 'KILLED'
order by 1,2;
Check if “App:” process still exist – maybe, it’s worth to stop the application first.
Otherwise, kill the “OS:” process.
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 1 0 Jan 31 ? 0:00 /usr/bin/mysqld
...
kill -9 25079
Please ensure that the proper process is killed!
Start MySQL server without security checking
mysqld_safe --skip-grant-tables &
Connect to mySQL without password
mysql -u root
Reset mySQL root password
use mysql;
update user set password=PASSWORD("NEW_PASSWORD") where user='root';
flush privileges;
Use the real new password instead of the string “NEW_PASSWORD”.
Restart mySQL server
/etc/init.d/mysql stop
/etc/init.d/mysql start
Check the connection
mysql -u root -p
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 very useful.
exp/imp
exp userid=" '/ as sysdba' " rows=n file=my.dmp full=y
imp userid=" '/ as sysdba' " file=my.dmp full=y show=y > full_structure.sql 2>&1
Warning! exp could not get the contents of the encrypted tablespaces.
expdp/impdp
expdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR content=metadata_only full=y
impdp "'/ as sysdba'" dumpfile=mydp.dmp directory=DATA_PUMP_DIR sqlfile=full_structure.sql
Sometimes it’s necessary to show the output of the PL/SQL script.
Usually the dbms_output package is used:
[cc lang=”oracle8″]
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;
/
[/cc]
After several thousands lines the following error message is displayed:
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 8
The error message means, that the internal buffer of dbms_output is full. How to increase it?
The following methods could be used:
[cc lang=”oracle8″]
…
dbms_package.enable(100000) ;
…
[/cc]
[cc lang=”oracle8″]
…
set serveroutput on size 100000
…
[/cc]
In Oracle 10gR2 the new addition “size unlimited” could be used:
[cc lang=”oracle8″]
…
set serveroutput on size unlimited
…
[/cc]
If these methods do not work, the temporary table could be used:
[cc lang=”oracle8″]
create sequence my_output_seq ;
create global temporary table my_output_table( my_row number, my_out varchar2(120) ) ;
…
insert into my_output_table values ( my_output_seq.nextval, ‘This is some string’ ) ;
…
select my_out from my_output_table order by my_row ;
drop table my_output_table ;
drop sequence my_output_seq ;
[/cc]
Here is the simple script for cloning the Oracle profiles.
[cc lang=”oracle8″]
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 dba_profiles.RESOURCE_NAME%type ;
s_LIMIT dba_profiles.LIMIT%type ;
begin
s_prev_PROFILE := ‘no_such_profile’ ;
dbms_output.enable(1000000);
open c_profiles;
loop
fetch c_profiles into s_PROFILE,s_RESOURCE_NAME,s_LIMIT ;
if ( s_prev_profile <> s_profile ) then
begin
dbms_output.put_line ( ‘–‘);
dbms_output.put_line ( ‘create profile “‘||s_profile||'” limit ‘ ||s_RESOURCE_NAME|| ‘ ‘ || s_LIMIT||’;’ ) ;
s_prev_profile := s_profile ;
end;
else
dbms_output.put_line ( ‘alter profile “‘||s_profile|| ‘” limit ‘ ||s_RESOURCE_NAME|| ‘ ‘ || s_LIMIT || ‘;’ ) ;
end if;
exit when c_profiles%NOTFOUND ;
end loop ;
close c_profiles ;
end;
/
[/cc]
There are several methods to put the quote into the string.
The first (and very traditional) one: use 2 quotes.
[cc lang=”oracle8″]
select ‘This ” is quote’ from dual;
[/cc]
If there are more than one quote, it’s difficult to read and write such strings
The second: use chr(39)
[cc lang=”oracle8″]
select ‘This ‘ || chr(39) || ‘ is quote’ from dual;
[/cc]
The third: put the quote into the variable
[cc lang=”oracle8″]
declare
s_quote varchar2(1) := ”” ;
begin
dbms_output.put_line(‘This ‘ || s_quote || ‘ is quote’ ) ;
end;
[/cc]
And, finally, Oracle 10g has added new feature: quote operator.
[cc lang=”oracle8″]
select q'[This ‘ is quote]’ from dual ;
[/cc]
The general form is q’X string X’. Here X is just some character. If the brackets are used, Oracle expects the closing bracket for the end of the string.
Here are the additional examples:
[cc lang=”oracle8″]
select q'(This ‘ is quote)’ from dual ;
select q’|This ‘ is quote|’ from dual ;
select q’#This ‘ is quote#’ from dual ;
select q’#This ‘ is quote#’ from dual ;
select q’?This ‘ is quote?’ from dual ;
select q’TThis ‘ is quoteT’ from dual ;
[/cc]