Oracle

GUI Client for MYSQL

Download Oracle sqldeveloper.

Info from: tomysqlusingoracle_sqldeveloper.html kb.dbatoolz.com/tp/2998.connectingtomysqlusingoracle_sqldeveloper.html

Download mysql JDBC connector from 

http://dev.mysql.com/downloads/connector/j/5.1.html

extract it on your PC and take the following file 

mysql-connector-java-5.1.5-bin.jar

... place it into sqldeveloper\sqldeveloper\extensions

then go to sqldeveloper -> Tools -> Preferences

Database -> Third Party JDBC Drivers

click "Add Entry" and select mysql-connector-java-5.1.5-bin.jar

restart sqldeveloper and you should be able to connect to mysql at this point.

Snippets of code

Update

update pv_property t
set t.resource''parameter''value = 'Takahe_FMS'
where t.resource''id = 10061 and t.resource''parameter_id = 7

UPDATE PV''RESOURCE B SET B.RESOURCE''NAME = ? where B.RESOURCE''PARENT''ID = (
SELECT DISTINCT RESOURCE''ID AS PARENT''ID FROM PV''RESOURCE A where A.RESOURCE''NAME = ?
) AND B.RESOURCE_NAME = ?

Use a value to determine what text to return from the query (the case statement)

select a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) "Size (MB)",
ROUND(b.BYTES/1024000) "Free (MB)",
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "% USED",
CASE
 when (((a.BYTES-b.BYTES)/a.BYTES)*100) > 85  THEN 'CRITICAL'
 when (((a.BYTES-b.BYTES)/a.BYTES)*100) > 80  THEN 'WARNING'
 ELSE 'NORMAL'
END as "Status"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba''data''files
group by TABLESPACE_NAME
) a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba''free''space
group by TABLESPACE_NAME
) b
where a.TABLESPACE''NAME=b.TABLESPACE''NAME
and a.TABLESPACE''NAME like 'PV''%'
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

JDBC Classes vs JVM version vs Oracle version

Oracle JDBC Version	Java JDK Version	JDBC File Name
12.1.0	1.7.x\\1.6.x	ojdbc7.jar\\ojdbc6.jar
11.2.0	1.7.x**\\1.6.x\\1.5.x	ojdbc6.jar\\ojdbc6.jar\\ojdbc5.jar
11.1.0	1.6.x\\1.5.x	ojdbc6.jar\\ojdbc5.jar
10.2.0	1.5.x\\1.4.x\\1.3.x\\1.2.x	ojdbc14.jar\\ojdbc14.jar\\classes12.jar\\classes12.jar
10.1.0	1.4.x\\1.3.x\\1.2.x	ojdbc14.jar\\classes12.jar\\classes12.jar
9.2.0	1.4.x\\1.3.x\\1.2.x\\1.1.x	ojdbc14.jar\\classes12.zip\\classes12.zip\\classes111.zip

Links www.stechno.net/sap-notes.html?view=sapnote&id=867176 http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html

Identify sessions and kill them

http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php Identify

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
: s.sid,
: s.serial#,
: p.spid,
: s.username,
: s.program
FROM   gv$session s
: JOIN gv$process p ON p.addr = s.paddr AND p.inst''id = s.inst''id
WHERE  s.type != 'BACKGROUND';

KILL

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

Modify the tablespace file sizes

http://www.oracle-ckpt.com/how-to-shrink-datafiles-adjusting-hwm/

Find out how much disk each is taking, and how much they can be shrunk down to

set verify off
column file''name format a50 word''wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db''block''size';
/
select file_name,
: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
: ceil( blocks*&&blksize/1024/1024) currsize,
: ceil( blocks*&&blksize/1024/1024) -
: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba''data''files a,
: ( select file''id, max(block''id+blocks-1) hwm
: from dba_extents
: group by file_id ) b
where a.file''id = b.file''id(+) order by savings desc
/

Older oracle

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace''name, file''name, file''size, hwm, file''size-hwm can_save
FROM (SELECT /'''+ RULE '''/ ddf.tablespace''name, ddf.file''name file_name,
ddf.bytes/1048576 file''size,(ebf.maximum + de.blocks-1)*dbs.db''block_size/1048576 hwm
FROM dba''data''files ddf,(SELECT file''id, MAX(block''id) maximum FROM dba''extents GROUP BY file''id) ebf,dba_extents de,
(SELECT value db''block''size FROM v$parameter WHERE name='db''block''size') dbs
WHERE ddf.file''id = ebf.file''id
AND de.file''id = ebf.file''id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Then alter the size to suit

alter database datafile '/u02/oradata/cpdevdb/cp''jrnl''idx3.dbf' resize 13700m;

mysql

Reset the root mysql password

blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/

remember to start the instance of the db with the following flags if you have changed them..

$ ./mysqld''safe --skip-grant-tables --basedir=/appl/proviso/mysql --datadir=/appl/proviso/data/mysql''data &