oracle

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.

Create Oracle 10 XE Xen DomU domain

# download debian 32 bit domU installer 
wget http://ftp.debian.org/debian/dists/squeeze/main/installer-i386/current/images/netboot/xen/initrd.gz -O /tmp/initrd.gz
wget http://ftp.debian.org/debian/dists/squeeze/main/installer-i386/current/images/netboot/xen/vmlinuz -O vmlinuz
 
# create lvm for domU
lvcreate --name oracle-disk --size 16G vg1
lvcreate --name oracle-swap --size 16G vg1
 
# create domU config for installation
cat <<EOF> /etc/xen/oracle 
kernel      = '/tmp/vmlinuz'
ramdisk     = '/tmp/initrd.gz'
vcpus       = '2'
memory      = '2048'
root        = '/dev/xvda2 ro'
di

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')

Oracle queries

-- output parameter
SET wrap off;
SET pagesize 0;
SET linesize 120;
column owner format a30;
 
-- show configuration
show parameters;
 
-- list users
SELECT username FROM all_users 
 
-- 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
Syndicate content