database

warning: Creating default object from empty value in /data/web/1/000/027/003/273448/htdocs/panticz.de/modules/taxonomy/taxonomy.pages.inc on line 33.

Install SQuirreL SQL

wget https://raw.githubusercontent.com/panticz/installit/master/install.squirrel-sql.sh -O - | bash -

#!/bin/bash

# download link
URL=http://switch.dl.sourceforge.net/project/squirrel-sql/1-stable/3.5.0-plainzip/squirrel-sql-3.5.0-standard.tar.gz

# install Java JRE
wget http://installit.googlecode.com/hg/install.java-jre.sh -O - | bash -

# download
wget ${URL} -O /tmp/squirrel-sql-3.5.0-standard.tar.gz

# extract
sudo tar xzf /tmp/squirrel-sql-3.5.0-standard.tar.gz -C /usr/lib/
sudo chown root:root -R /usr/lib/squirrel-sql
sudo chmod +r -R /usr/lib/squirrel-sql
sudo ln -s /usr/lib/squirrel-sql-3.5.0-standard /usr/lib/squirrel-sql

# create starter
cat <<EOF> /tmp/squirrel-sql.desktop
[Desktop Entry]
Name=SQuirreL SQL
Comment=SQuirreL SQL Client
Exec=/usr/lib/squirrel-sql-3.5.0-standard/squirrel-sql.sh
Icon=/usr/lib/squirrel-sql-3.5.0-standard/icons/acorn.xpm
Terminal=false
Type=Application
Categories=GTK;Database;Development;Application
EOF

sudo mv /tmp/squirrel-sql.desktop /usr/share/applications/squirrel-sql.desktop
sudo chmod +r /usr/share/applications/squirrel-sql.desktop

# cleanup
rm /tmp/squirrel-sql-3.5.0-standard.tar.gz

installSqlDeveloper.sh

Get Oracle SQL Developer package
1. go to Homepage: http://www.oracle.com/technology/software/products/sql/index.html
2. accept the licence
3. login
4. download "Oracle SQL Developer for other platforms"

http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-4.1.0.18.37-no-jre.zip

wget https://raw.githubusercontent.com/panticz/installit/master/install.sqldeveloper.sh -O - | bash -

#!/bin/bash

if [ -z "$1" ]; then
  echo "Please enter the url to download"
  exit
else
  URL=$1
fi

FILE=${URL##*/}
[ -n "$2" ] && USER=$2

# install Java JRE
wget -q https://raw.githubusercontent.com/panticz/installit/master/install.java-jdk.sh -O - | bash -

# get file
if [ ${URL:0:4} == "http" ]; then
  wget -q ${URL} -O /tmp/${FILE}
else
  cp ${URL} /tmp/${FILE}
fi

case ${FILE: -3} in
rpm)
  # convert rpm package to debian
  sudo apt-get install -y alien
  sudo alien /tmp/${FILE}
  sudo dpkg -i /tmp/${FILE/.rpm/.deb}
  ;;
deb)
  # install debian package
  sudo dpkg -i /tmp/${FILE}
  ;;
zip)
  # extract
  [ -d /opt ] || sudo mkdir /opt
  sudo unzip -q /tmp/${FILE} -d /opt/
  sudo chmod +x /opt/sqldeveloper/sqldeveloper.sh
  ;;
*)
  echo "Unknown file format"
  exit
  ;;
esac

# fix "Error: SQL Developer can't recognize the JDK version" error
sudo sed -i '1a unset GNOME_DESKTOP_SESSION_ID' /opt/sqldeveloper/sqldeveloper.sh

# create startet
cat << EOF | sudo tee -a "/usr/share/applications/oracle-sql-developer.desktop"
[Desktop Entry]
Version=1.0
Encoding=UTF-8
Name=Oracle SQL Developer
Type=Application
Terminal=false
Exec=/opt/sqldeveloper/sqldeveloper.sh
Icon=/opt/sqldeveloper/icon.png
Categories=Application;Development;
EOF

# create user config to the latest installed JDK version
[ -d /home/${USER}/.sqldeveloper ] || mkdir /home/${USER}/.sqldeveloper
[ -f /home/${USER}/.sqldeveloper/jdk ] || echo "/usr/lib/jvm/$(ls /usr/lib/jvm | grep oracle | tail -1)/" > /home/${USER}/.sqldeveloper/jdk

# set owner
chown -R ${USER}:${USER} /home/${USER}/.sqldeveloper/

# reduced memory usage
sed -i 's|-Xmx640M|-Xmx384M|g' /opt/sqldeveloper/ide/bin/ide.conf

# ToDo
# http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-4.0.2.15.21-1.noarch.rpm?AuthParam=XXX_XXX
# use a repository
# configure global

GWT + MySQL + Netbeans Demo

# INFO
url : jdbc:mysql://localhost:3306/Bible
user: gwt-examples
pass: password
 
# create database and user
mysql -u root -p
create database Bible;
CREATE USER 'gwt-examples' IDENTIFIED BY 'password';
grant all on Bible.* to 'gwt-examples'@'%' identified by "password";
#grant all on Bible.* to 'gwt-examples'@'192.168.1.10' identified by "password";
#grant all on Bible.* to 'gwt-examples'@'localhost' identified by "password";
commit;
quit;
 
# get gwt examples 
cd ${HOME}
svn checkout http://gwt-examples.googlecode.com/svn/trunk/ gwt-examples
 
# fill database
wget http://ken

Install Oracle XE

# new version:
https://raw.githubusercontent.com/panticz/installit/master/install.oracle-xe.sh
 
# fix
W: GPG error: https://oss.oracle.com unstable Release: The following signatures were invalid: KEYEXPIRED 1378511808 KEYEXPIRED 1378511808 KEYEXPIRED 1378511808
 
 
 
# add oracle repository
wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | apt-key add -
cat <<EOF> /etc/apt/sources.list.d/oracle.list
deb http://oss.oracle.com/debian unstable main non-free
EOF
 
# update repository
apt-get update
 
# install oracle
apt-get install -y oracle-xe
 
# configure
/etc/init.d/oracl

update_customer_payment.sql

BEGIN
DBMS_SCHEDULER.create_job (
    job_name        => 'update_snapshots',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'UPDATE_SNAPSHOT',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=5',
    end_date        => NULL,
    enabled         => TRUE
);
END
 
 
-- 10xe
DBMS_SCHEDULER.create_job (
    job_name        => 'update_invoice_positions',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'K.INVOICE;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date    

job_show.sql

SET pagesize 50000
SET linesize 240
SET echo off
 
column job          format 9999  heading 'Job'          justify right
column schema_user  format a20   heading 'Oracle User'  justify left
column broken       format a6    heading 'Broken'       justify left
column last_date    format a20   heading 'Last Date'    justify left
 
column this_date    format a20   heading 'This Date'    justify left
column next_date    format a20   heading 'Next Date'    justify left
column INTERVAL     format a60   heading 'Interval'     justify left
column what         format a80   heading 'What'      

job_set_ndxsys.sql - will setup the package as job 12 at 21:00 hours today

SET echo off;
 
clear screen;
 
PROMPT
 
PROMPT COLLECTING USER INFORMATION
PROMPT
 
ACCEPT database_sid   CHAR PROMPT 'DATABASE TO CONNECT TO    [ORCL]: ' DEFAULT ORCL
ACCEPT mhsys_password CHAR PROMPT 'PASSWORD FOR USER MHSYS  [MHSYS]: ' DEFAULT MHSYS HIDE
PROMPT
 
 
prompt  CONNECTING USER MHSYS
CONNECT MHSYS/&mhsys_password@&database_sid;
 
SET serveroutput ON;
SET linesize 100;
spool job_set_ndxsys.LOG;
 
 
prompt SHOWING JOBS
SELECT JOB, SCHEMA_USER,
       TO_CHAR(NEXT_DATE, 'DD-MON-YYYY, HH24:MI') NEXT_DATE,
       INTERVAL, WHAT
FROM dba_jobs
 
 
ORDER BY job;
 
 
p

job_remove_ndxsys.sql

SET serveroutput ON;
SET linesize 100;
SET echo ON;
spool job_remove.LOG;
 
SELECT JOB, SCHEMA_USER, BROKEN, WHAT
FROM dba_jobs;
EXECUTE DBMS_JOB.REMOVE(&job_number_to_remove)
 
COMMIT;
 
SELECT JOB, SCHEMA_USER, BROKEN, WHAT
FROM dba_jobs;
 
spool off;

Oracle Jobs

view jobs
select *
from user_scheduler_jobs

create job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'YOUR_JOB_NAME',
job_type => 'stored_procedure',
job_action => 'testproc',
start_date => TRUNC(SYSDATE),
repeat_interval => 'freq=DAILY;byhour=5',
-- repeat_interval => 'freq=hourly;byminute=5',
-- repeat_interval => 'freq=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',
enabled => TRUE
);
END;

delete job
exec dbms_scheduler.drop_job('YOUR_JOB_NAME')

MySQL Queries

-- view hostname
select @@hostname;
 
-- view version
select version();
 
-- view engines
show engines;
 
-- list databases
show databases;
 
-- change database
use mysql;
 
-- show tables in database
show tables;
 
-- show table informations
show table status;
 
-- show engines
show engines;
 
-- show bin_log info
show variables like '%log_bin%';
 
-- list users
select user, host from mysql.user;
 
-- delete user
DROP USER 'USER_NAME'@'%';
 
-- set user password
--SET PASSWORD FOR 'USER_NAME'@'%' = PASSWORD('NEW_PASSWORD')
use mysql;
update user set password=PASSWORD(
Syndicate content