sql

warning: Creating default object from empty value in /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

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 queries

-- find constraint
SELECT *
FROM all_constraints
WHERE constraint_name = 'SYS_C00381400';
 
-- select a random row from table
SELECT column FROM 
   SELECT column FROM TABLE
   ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM = 1
 
-- update materialized view
BEGIN
   dbms_snapshot.refresh('SCHEMA.MATERIALIZED_VIEW_NAME'); 
END;
 
-- view scheduler jobs
SELECT * FROM user_scheduler_jobs
 
-- get lock on table (NOWAIT)
SELECT username,v$lock.sid,TRUNC(id1/POWER(2,16)) rbs,BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot,id2 seq,lmode,request
FROM v$lock, v$session
WHERE v$lock.TYPE = 'TX

Export MySQL Database

Login
mysql -u root -p

Links
phpMyAdmin - phpMyAdmin downloads

GUI
apt-get install -y mysql-admin

Allow access from outsice
sed -i 's|bind-address|#bind-address|g' /etc/mysql/my.cnf
/etc/init.d/mysql restart

Allow access to database magento for user dbo_magento from any host
GRANT ALL PRIVILEGES ON magento.* TO dbo_magento@'%' IDENTIFIED BY 'your_pass'

# dont LOCK TABLE on export / import

Syndicate content