Oracle

Describe Oracle here.

GUI Client for MYSQL

Download Oracle sqldeveloper.

Info from: http://kb.dbatoolz.com/tp/2998.connecting_to_mysql_using_oracle_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 http://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

http://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 &
Toolbox
Favorite Categories