This project has born to “takedown” Oracle Database from any organization that wants to be “free as in freedom” using any or many of the available free options in the “GPL” world.

ssh dodger@192.168.56.100
tmux a
sqlplus '/ as sysdba'

-- object information
SELECT owner, object_type, object_name FROM dba_objects WHERE owner='HR' ORDER BY object_type ;
SELECT object_name FROM dba_objects WHERE owner='HR' AND object_type='TABLE' ORDER BY object_type ;
 
-- table row information
ALTER SESSION SET CURRENT_SCHEMA=HR ;
SET feed off
SELECT COUNT(*) AS REGIONS FROM REGIONS ; 
SELECT COUNT(*) AS COUNTRIES FROM COUNTRIES ; 
SELECT COUNT(*) AS LOCATIONS FROM LOCATIONS ; 
SELECT COUNT(*) AS DEPARTMENTS FROM DEPARTMENTS ; 
SELECT COUNT(*) AS JOBS FROM JOBS ; 
SELECT COUNT(*) AS EMPLOYEES FROM EMPLOYEES ; 
SELECT COUNT(*) AS JOB_HISTORY FROM JOB_HISTORY ; 

Using fully customizable ora2my script:

ssh dodger@192.168.56.100
tmux a
 
 
cd /home/dodger/ora2my_transfer
cat tables.txt
bash ora2my_sync_v002.sh recreate "$(cat tables.txt)"

Check:

mysql -p$(cat mysql_password.txt)
use takedown_hr
 
-- table count
select count(*) as REGIONS from REGIONS ; 
select count(*) as COUNTRIES from COUNTRIES ; 
select count(*) as LOCATIONS from LOCATIONS ; 
select count(*) as DEPARTMENTS from DEPARTMENTS ; 
select count(*) as JOBS from JOBS ; 
select count(*) as EMPLOYEES from EMPLOYEES ; 
select count(*) as JOB_HISTORY from JOB_HISTORY ; 
 
-- table description
show create table REGIONS ; 
show create table COUNTRIES ; 
show create table LOCATIONS ; 
show create table DEPARTMENTS ; 
show create table JOBS ; 
show create table EMPLOYEES ; 
show create table JOB_HISTORY ;

Demonstration on how We can move oracle LOB (Large OBjects) to Amazon S3 (or any compatible technology, in this demo we will use min.io:

cd
./minio server data

Check: http://192.168.56.200:9000/

Using fully customizable script kill_tbuffer.py:

cd ~/kill_tbuffer/
bash get_all_metadata.sh nosync HR

This script will:

• Connect Oracle
• Connect S3
• Get LOB's from Oracle and store them in S3 (it will not temporary store the objects on disk, but is customizable).

Using fully customizable script get_all_metadata.sh:

cd ~/get_all_metadata
bash get_all_metadata.sh nosync HR

This script will:

• Create destination directory (if not exists)
• Based on a pre-defenied list of objects, obtain the objects of all that types owned by the schema owner.
• Generate a sql file (${SQLGENERATOR} inside script), which will generate one .sql file for every object in the configured database/scheme.
• Execute the ${SQLGENERATOR} in the target database, this will generate 1 sql file for every object.
• The script can upload the generated files to github.

MariaDB 10.3 introduces the “Oracle Mode” So we can take PL/SQL from oracle and plug in MariaDB with a little work.

Oracle SECURE_DML:

  CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."SECURE_DML"
IS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR (-20205,
                'You may only make changes during normal office hours');
  END IF;
END secure_dml;
/

MySQL SECURE_DML:

SET sql_mode=ORACLE;
DROP PROCEDURE SECURE_DML;
DELIMITER $$
CREATE PROCEDURE SECURE_DML
IS
BEGIN
  IF TIME_FORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '08:00' AND '18:00'
        OR DATE_FORMAT (SYSDATE(), '%w') NOT BETWEEN 1 AND 6 THEN
        signal sqlstate '20205' SET message_text = 'You may only make changes during normal office hours!';     
  END IF;
END SECURE_DML;
$$
DELIMITER ;

Test:

CALL SECURE_DML ;

Maybe you want test it out of the secure time window:

DROP PROCEDURE SECURE_DML;
DELIMITER $$
CREATE PROCEDURE SECURE_DML
IS
BEGIN
  IF TIME_FORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '11:00' AND '11:01'
        OR DATE_FORMAT (SYSDATE(), '%w') NOT BETWEEN 0 AND 1 THEN
        signal sqlstate '20205' SET message_text = 'You may only make changes during normal office hours!';     
  END IF;
END SECURE_DML;
$$
DELIMITER ;

Oracle:

  CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."ADD_JOB_HISTORY"
  (  p_emp_id          job_history.employee_id%TYPE
   , p_start_date      job_history.start_date%TYPE
   , p_end_date        job_history.end_date%TYPE
   , p_job_id          job_history.job_id%TYPE
   , p_department_id   job_history.department_id%TYPE
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/

MySQL:

SET sql_mode=ORACLE;
DROP PROCEDURE ADD_JOB_HISTORY;
DELIMITER $$
CREATE PROCEDURE ADD_JOB_HISTORY (
   p_emp_id          JOB_HISTORY.EMPLOYEE_ID%TYPE ,
   p_start_date      JOB_HISTORY.START_DATE%TYPE ,
   p_end_date        JOB_HISTORY.END_DATE%TYPE ,
   p_job_id          JOB_HISTORY.JOB_ID%TYPE ,
   p_department_id   JOB_HISTORY.DEPARTMENT_ID%TYPE )
AS
BEGIN
  INSERT INTO JOB_HISTORY (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END ADD_JOB_HISTORY;
$$
DELIMITER ;

Test:

SELECT * FROM JOB_HISTORY ;
--
CALL ADD_JOB_HISTORY(666, '1997-09-21 00:00:00.000000', '1998-09-21 00:00:00.000000', 'DBA', 99) ;
--
SELECT * FROM JOB_HISTORY ;