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 killtbuffer.py: <code bash> cd ~/killtbuffer/ bash getall_metadata.sh nosync HR </code> 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). ==== Extracting DDL/Metadata from Oracle ==== Using fully customizable script getallmetadata.sh: <code bash> cd ~/getallmetadata bash getallmetadata.sh nosync HR </code> 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. ==== PL/SQL in MySQL ==== MariaDB 10.3 introduces the “Oracle Mode” So we can take PL/SQL from oracle and plug in MariaDB with a little work. === SECURE_DML Procedure === Oracle SECUREDML:

  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 SECUREDML'': <code sql> SET sqlmode=ORACLE; DROP PROCEDURE SECUREDML; DELIMITER $$ CREATE PROCEDURE SECUREDML IS BEGIN IF TIMEFORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '08:00' AND '18:00' OR DATEFORMAT (SYSDATE(), '%w') NOT BETWEEN 1 AND 6 THEN signal sqlstate '20205' set messagetext = 'You may only make changes during normal office hours!';
END IF; END SECUREDML; $$ DELIMITER ; </code> Test: <code sql> CALL SECURE_DML ; </code> Maybe you want test it out of the secure time window: <code sql> DROP PROCEDURE SECUREDML; DELIMITER $$ CREATE PROCEDURE SECUREDML IS BEGIN IF TIMEFORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '11:00' AND '11:01' OR DATEFORMAT (SYSDATE(), '%w') NOT BETWEEN 0 AND 1 THEN signal sqlstate '20205' set messagetext = 'You may only make changes during normal office hours!';
END IF; END SECUREDML; $$ DELIMITER ; </code> === ADDJOBHISTORY Procedure === Oracle: <code sql> CREATE OR REPLACE EDITIONABLE PROCEDURE “HR”.“ADDJOBHISTORY” ( pempid jobhistory.employeeid%type , pstartdate jobhistory.startdate%type , penddate jobhistory.enddate%type , pjobid jobhistory.jobid%type , pdepartmentid jobhistory.departmentid%type ) IS BEGIN INSERT INTO jobhistory (employeeid, startdate, enddate, jobid, departmentid) VALUES(pempid, pstartdate, penddate, pjobid, pdepartmentid); END addjobhistory; / </code> MySQL: <code sql> SET sqlmode=ORACLE; DROP PROCEDURE ADDJOBHISTORY; DELIMITER $$ CREATE PROCEDURE ADDJOBHISTORY ( pempid JOBHISTORY.EMPLOYEEID%TYPE , pstartdate JOBHISTORY.STARTDATE%TYPE , penddate JOBHISTORY.ENDDATE%TYPE , pjobid JOBHISTORY.JOBID%TYPE , pdepartmentid JOBHISTORY.DEPARTMENTID%TYPE ) AS BEGIN INSERT INTO JOBHISTORY (EMPLOYEEID, STARTDATE, ENDDATE, JOBID, DEPARTMENTID) VALUES(pempid, pstartdate, penddate, pjobid, pdepartmentid); END ADDJOBHISTORY; $$ DELIMITER ; </code> Test: <code sql> select * from JOBHISTORY ; – CALL ADDJOBHISTORY(666, '1997-09-21 00:00:00.000000', '1998-09-21 00:00:00.000000', 'DBA', 99) ; – select * from JOB_HISTORY ; </code>