Convert matrix data in Excel /csv or text file into Oracle table in Raw format

We want to convert following matrix data into to raw format .

Copy this data in excel and save it in .csv format named mydata.csv.

Time	Latitude	Temperature Cover (%)Longitude(60-80)
		60	60.1	60.2	60.3	60.4	60.5	60.6	60.7	60.8	60.9	61	61.1	61.2
00Z01AUG2015	23	23.8965	44.0527	48.6152	48.6152	62.5586	91.3066	97.3848	100	100	100	100	100	100
00Z01AUG2015	23.1	20.1816	20.5078	28.5332	28.5332	59.543	69.7168	87.7266	100	100	100	100	100	100
00Z01AUG2015	23.2	0	19.7207	25.5566	25.5566	39.8457	69.7168	91.502	91.502	91.502	99.9395	100	100	100
01Z01AUG2015	23.3	0	19.7207	25.5566	25.5566	39.8457	69.7168	91.502	91.502	91.502	99.9395	100	100	100
00Z01AUG2015	23.4	16.8008	19.7207	20.7754	20.7754	36.1016	53.9766	57.2246	63.9258	63.9258	75.6562	62.7617	80.5586	92.6621
00Z01AUG2015	24.9	9.33203	5.06641	10.8008	10.8008	11.75	12.709	13.0117	13.4883	13.4883	13.5254	20.5293	34.25	44.2461
00Z01AUG2015	25	13.293	6.91602	13.9023	13.9023	13.4043	56.5078	56.0312	14.5918	14.5918	65.084	74.0371	91.3945	76.4277
00Z01AUG2015	25.1	22.9375	14.3984	14.7734	14.7734	16.0293	56.9629	57.4316	57.4316	57.4316	70.4473	85.1523	85.8809	76.4277
00Z01AUG2015	25.2	62.002	57.2988	22.3496	22.3496	14.4746	57.9766	57.8535	35.9023	35.9023	70.4473	73.127	80.502	85.9414
00Z01AUG2015	25.3	62.002	57.2988	22.3496	22.3496	14.4746	57.9766	57.8535	35.9023	35.9023	70.4473	73.127	80.502	85.9414
00Z01AUG2015	25.4	61.2637	59.8574	9.69336	9.69336	14.4746	55.9375	58.998	20.9961	20.9961	30.2812	81.0508	83.0996	73.3652
00Z01AUG2015	25.5	61.2637	65.0254	65.0254	65.0254	82.6895	72.084	59.8789	34.9941	34.9941	39.6641	80.5527	75.7246	69.2969
00Z01AUG2015	25.6	62.041	86.4336	88.4277	88.4277	74.0996	60.6797	60.6797	74.2754	74.2754	94.7402	94.7402	75.2793	86.2031
00Z01AUG2015	25.7	83.4902	92.8145	86.5801	86.5801	82.9453	82.9453	83.2461	72.5547	72.5547	72.5547	88.8457	75.2793	74.9043
00Z01AUG2015	25.8	83.4902	92.8145	86.5801	86.5801	82.9453	82.9453	83.2461	72.5547	72.5547	72.5547	88.8457	75.2793	74.9043
00Z01AUG2015	25.9	59.7734	85.4707	92.1621	92.1621	83.2598	88.8789	83.2461	92.7461	92.7461	86.4648	68.2441	71.6484	92.416
00Z01AUG2015	26	92.0527	92.0527	83.252	83.252	90.3535	89.0508	93.7207	84.6875	84.6875	92.6348	56.6328	87.4727	72.209
00Z01AUG2015	26.1	58.6934	58.7188	66.1367	66.1367	62.8516	16.3477	60.5254	92.6309	92.6309	92.6309	63.4512	82.0742	75.3535
00Z01AUG2015	26.2	17.1348	17.0234	15.1914	15.1914	15.1914	15.0527	16.3008	16.5703	16.5703	13.375	63.4512	9.21094	92.0859
00Z01AUG2015	26.3	17.1348	17.0234	15.1914	15.1914	15.1914	15.0527	16.3008	16.5703	16.5703	13.375	63.4512	9.21094	92.0859

Now We are ready to convert the above data into the raw format using following script.

Execute following script on Oracle sql prompt or any Oracle client tool used to execute Oracle queries.

Following script uses External table to convert .csv files into oracle tables then
uses oracle commands to convert matrix data into Oracle raw table format.

Click here to see how to create and used external table to import data into Oracle.

set serveroutput on ;
declare
cursor tabcolumns is
select column_name, ' '||data_type||' ('||DATA_PRECISION||','||data_scale||')' data_types
from user_tab_columns
where table_name = 'LODADED_DATA'
and COLUMN_NAME  not in ('DATESTIME','LATITUDE')
order by column_id;

TOTAL_COLUMNS NUMBER:=0;
cursor matrix_tabcolumns is
select column_name
from user_tab_columns
where table_name = 'MATRIX_DATA'
and COLUMN_NAME  not in ('DATESTIME','LATITUDE')
AND COLUMN_ID<=TOTAL_COLUMNS
order by column_id;

columms varchar2(20000):='';
column_val varchar2(1000):='';
create_table varchar2(30000):='';
val_count number:=0;
TABLE_MISSING EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_MISSING,-00942);
begin
    begin
        execute immediate 'ALTER TABLE EXT_TABLE LOCATION(''mydata.csv'')';
        execute immediate 'DROP TABLE LODADED_DATA';
        execute immediate 'CREATE TABLE LODADED_DATA  AS SELECT * FROM EXT_TABLE';
        exception WHEN TABLE_MISSING then
       execute immediate 'CREATE TABLE LODADED_DATA  AS SELECT * FROM EXT_TABLE';
    end;

  for rec_cols in tabcolumns  loop
  execute immediate 'select count('||rec_cols.column_name||') from LODADED_DATA ' into val_count;
    if val_count <> 0 then
       columms:=columms||','||rec_cols.column_name;
       execute immediate 'select '||'replace(''A_''||'||rec_cols.column_name||',''.'',''_'') '||' from LODADED_DATA where rownum=1' into column_val;
       create_table:=create_table||','||column_val||rec_cols.data_types;
       TOTAL_COLUMNS:=TOTAL_COLUMNS+1;
       -- dbms_output.put_line (rec_cols.column_name);
    end if;
  end loop;
  begin
    dbms_output.put_line ('Table created to load matrix data created');
    execute immediate 'drop table MATRIX_DATA';
    execute immediate 'create table MATRIX_DATA (DATESTIME varchar2(100),LATITUDE number(10,5)'||create_table|| ' )';
    exception WHEN TABLE_MISSING then
    execute immediate 'create table MATRIX_DATA (DATESTIME varchar2(100),LATITUDE number(10,5)'||create_table|| ' )';
  end;
  -- dbms_output.put_line (columms);
   execute immediate 'insert into MATRIX_DATA select DATESTIME,LATITUDE'||columms||' from LODADED_DATA where rowid <> (select rowid from LODADED_DATA where rownum=1)';
  commit;
 columms:='';
  for matrix_rec in matrix_tabcolumns loop
    columms:=columms||','||matrix_rec.column_name;
  end loop;
  execute immediate 'drop  table raw_data';
  execute immediate 'create table RAW_DATA as select DATESTIME,LATITUDE,REPLACE(REPLACE(LONGITUDE,''A_'',''''),''_'',''.'') LONGITUDE ,quantity from MATRIX_DATA  UNPIVOT (quantity FOR  LONGITUDE IN ('||substr(columms,2)||'))';
end;


-- select * from RAW_DATA;
-- drop table RAW_DATA;
--where longitude=60;

Restore Cold backup on different host.

In this article we are required to duplicate database using cold backup.

We have two servers named oracle1 and oracle2;

Source database path: D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB
Destination Database Path: C:\oracle\product\10.2.0\oradata\PDADB

We want to duplicate /clone the database  PDADB on oracle1 machine to Oracle2 machine.

For this first Install Oracle on the Oracle2 machine and create a database with same name ie PDADB.

Now  one Oracle2 machine stop oracle services and rename folder that contains database files;

In my case it is C:\oracle\product\10.2.0\oradata\PDADB.

So I renamed PDADB to PDADB_FRESH_BACKUP.

Now copy database folder from folder D:\ORACLE\PRODUCT\10.2.0\ORADATA on your Oracle1 machine to Oracle2 oradata  C:\oracle\product\10.2.0\oradata\ folder.

Start database in mount state.

sqlplus / as sysdba

startup mount;

Execute following command to create contolfile backup in text format, This will help us to verify files and their path on the Oracle1 server that needs to be changed on oracle2 server.


--- backup controlfile to trace;

alter database backup controlfile to trace as 'c:\PDAdb_controlfile.txt';


If your Oracle1 server database path and Oraocle2 server database path are different then execute following command to change path accordingly.

--------rename datafile location --------

alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\REDO01.LOG' to 'C:\oracle\product\10.2.0\oradata\PDADB\REDO01.LOG';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\REDO02.LOG' to 'C:\oracle\product\10.2.0\oradata\PDADB\REDO02.LOG';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\REDO03.LOG' to 'C:\oracle\product\10.2.0\oradata\PDADB\REDO03.LOG';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\SYSTEM01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\SYSTEM01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\UNDOTBS01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\UNDOTBS01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\SYSAUX01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\SYSAUX01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\USERS01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\USERS01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\EXAMPLE01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\EXAMPLE01.DBF';
alter database rename file'E:\ORACLEDATA\PDADB01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\e_oradata\PDADB01.DBF';
alter database rename file'E:\ORACLEDATA\PDAAOSDB01.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\e_oradata\PDAAOSDB01.DBF';
alter database rename file'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PDADB\PDADB.DBF' to 'C:\oracle\product\10.2.0\oradata\PDADB\PDADB.DBF';


Execute following command to view the changed path
--- backup controlfile to trace;

alter database backup controlfile to trace as 'c:\PDAdb_controlfile.txt';


If all paths are ok now we can start database using following command;

Alter database open;

Duplicate process is complete.

Script to add DML audit for one user in Mysql Using Triggers.

-- Following scripts create audit tables and then also create audit triggers for full database or for only one table as per need.


 If user provides only schema name parameter then following process will create audit triggers for       all tables in the schema.
And if user provides schema and table_name parameters then only for that table audit triggers is created.

Three procedures are provided.

1. First procedure can create tables to store audit data.
2. Second procedure generates scripts to add DML audit triggers for the above tables.
3. Third procedure calls above two procedures to create audit tables and generate triggers scripts.
4. Follow the next steps to execute script generated with above procedure to create triggers.

Third procedure can be called in two ways
   1. With schema name and table name parameter (For only one table)
   2. Only schema name (For all tables in schema)


Issues that can appear 

1. If your table contains table with large column size i.e size not supported by Mysql below script will not be able to create audit table but it still it will provide script that you can modify to create audit table manually.

2. If table contains column with spaces in column name then again you need to modify script manually generated by following procedures.

Create a user to be audited or use existing user.

create new user by following command

GRANT USAGE ON *.* TO `myuser`@`%` IDENTIFIED BY 'mypassword' REQUIRE NONE;


1. Following Procedure create audit tables to store audit data.

use mydb;

DELIMITER $$
DROP PROCEDURE IF EXISTS create_aud_tabs$$
CREATE PROCEDURE create_aud_tabs (IN p_db_name varchar(64),IN tbl_name varchar(64))
BEGIN
 DECLARE v_column_list varchar(10000) default '';
 DECLARE v_query varchar(10000) DEFAULT '';
 declare has_error int default 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
 SET SESSION group_concat_max_len=15000;
 select GROUP_CONCAT(distinct 'old_', column_name,' ',column_type,',new_',COLUMN_NAME,' ',column_type)
 into v_column_list
 from information_schema.COLUMNS
 where TABLE_SCHEMA=table_schema
 and TABLE_NAME=tbl_name
 and table_name not like 'aud\_%'
 order by ordinal_position ;
 set v_query=concat ('create table ',p_db_name,'.aud_',tbl_name,'( audit_id int NOT NULL AUTO_INCREMENT,',v_column_list,',audit_type varchar(30),audit_user varchar(100),audit_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,primary key (audit_id))');
 select v_query;
 SET @sql = v_query;
  PREPARE stmt FROM @sql;
  EXECUTE stmt ;
  if has_error=1 then
   select v_query;
   set has_error=0;
  end if;
  END$$
DELIMITER ;


--  2. Following Procedure  Create Scripts to add audit for DML operations.

DELIMITER $$
 DROP PROCEDURE IF EXISTS add_aud_triggers $$
CREATE PROCEDURE add_aud_triggers (IN P_db_name varchar(64),IN tbl_name varchar(64),in p_user_to_audit varchar(64) ,in p_trigger_script_path varchar(1000))
BEGIN

 DECLARE v_column_list varchar(20000) DEFAULT '';
 DECLARE v_del_val_list varchar(20000);
 DECLARE v_ins_val_list varchar(20000);
 DECLARE v_upd_val_list varchar(20000) DEFAULT '';
 DECLARE u_trigger varchar(20000)DEFAULT '';
 Declare d_trigger varchar (20000)DEFAULT '';
 Declare i_trigger varchar (20000)DEFAULT '';
 Declare has_error int;
 Declare CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
 SET SESSION group_concat_max_len=20000;
 set v_column_list='';
 set v_upd_val_list='';
 set v_del_val_list= '';
 set v_ins_val_list= '';

 SELECT GROUP_CONCAT( distinct 'old_', column_name,',new_',COLUMN_NAME)
 into v_column_list
 FROM information_schema.COLUMns
 where TABLE_SCHEMA=table_schema
 and TABLE_NAME=tbl_name
 order by ordinal_position ;

 --  select v_column_list;

SELECT GROUP_CONCAT(distinct 'old.', column_name,',new.',COLUMN_NAME)
 into v_upd_val_list
 FROM information_schema.COLUMns
 where TABLE_SCHEMA=table_schema
 and TABLE_NAME=tbl_name
 order by ordinal_position ;

SELECT GROUP_CONCAT(distinct 'old.', column_name,',null')
 into v_del_val_list
 FROM information_schema.COLUMns
 where TABLE_SCHEMA=table_schema
 and TABLE_NAME=tbl_name
 order by ordinal_position ;

 -- -------------------

 SELECT GROUP_CONCAT(distinct 'null,','new.', column_name)
 into v_ins_val_list
 FROM information_schema.COLUMns
 where TABLE_SCHEMA=table_schema
 and TABLE_NAME=tbl_name
 order by ordinal_position ;
-- ---------------------------------------
 SET u_trigger = '';
 SET u_trigger = CONCAT('DELIMITER $$ \n DROP TRIGGER IF EXISTS tra_',tbl_name,'_upd_aud$$\n');
 SET u_trigger = CONCAT(u_trigger,'CREATE TRIGGER tra_',tbl_name,'_upd_aud AFTER update ON ',tbl_name,' FOR EACH ROW \n');
 SET u_trigger = CONCAT(u_trigger,'BEGIN \n');
 SET u_trigger = CONCAT(u_trigger,'if SUBSTRING_INDEX(SESSION_USER(),''@'',1)= ''',p_user_to_audit,''' then \n');
 set u_trigger = concat (u_trigger,concat ('insert into ',P_db_name,'.aud_',tbl_name,'(',v_column_list,',audit_type ,audit_user ,audit_date) \nvalues (',v_upd_val_list,',''update'',SESSION_USER(),CURRENT_TIMESTAMP); \n' ));
 SET u_trigger = CONCAT(u_trigger,'END IF;\n');
 SET u_trigger = CONCAT(u_trigger,'END$$ \nDELIMITER ; \n');
 select u_trigger from dual into @triggertxt ;
 SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', p_trigger_script_path ,'/upd_trigger_',tbl_name,'.sql', '"');
 PREPARE stmt5 FROM @savestr;
 EXECUTE stmt5;
 DEALLOCATE PREPARE stmt5;
--  -------------------------------------
 SET d_trigger = '';
 SET d_trigger = CONCAT('DELIMITER $$ \n DROP TRIGGER IF EXISTS tra_',tbl_name,'_del_aud$$\n');
 SET d_trigger = CONCAT(d_trigger,'CREATE TRIGGER tra_',tbl_name,'_del_aud AFTER delete ON ',tbl_name,' FOR EACH ROW \n');
 SET d_trigger = CONCAT(d_trigger,'BEGIN \n');
 SET d_trigger = CONCAT(d_trigger,'if SUBSTRING_INDEX(SESSION_USER(),''@'',1)= ''',p_user_to_audit,''' then \n');
 set d_trigger=concat (d_trigger,concat ('insert into ',P_db_name,'.aud_',tbl_name,'(',v_column_list,',audit_type ,audit_user ,audit_date) \nvalues (',v_del_val_list,',''delete'',SESSION_USER(),CURRENT_TIMESTAMP); \n' ));
 SET d_trigger = CONCAT(d_trigger,'END IF;\n');
 SET d_trigger = CONCAT(d_trigger,'END$$ \nDELIMITER ; \n');
 select d_trigger from dual into @triggertxt ;
 SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', p_trigger_script_path ,'/del_trigger_',tbl_name,'.sql', '"');
 -- select @savestr;
 PREPARE stmt5 FROM @savestr;
 EXECUTE stmt5;
 DEALLOCATE PREPARE stmt5;
 --  -------------------------------------
 SET i_trigger = '';
 SET i_trigger = CONCAT('DELIMITER $$ \n DROP TRIGGER IF EXISTS tra_',tbl_name,'_ins_aud$$\n');
 SET i_trigger = CONCAT(i_trigger,'CREATE TRIGGER tra_',tbl_name,'_ins_aud AFTER insert ON ',tbl_name,' FOR EACH ROW \n');
 SET i_trigger = CONCAT(i_trigger,'BEGIN \n');
 SET i_trigger = CONCAT(i_trigger,'if SUBSTRING_INDEX(SESSION_USER(),''@'',1)= ''',p_user_to_audit,''' then \n');
 set i_trigger=concat (i_trigger,concat ('insert into ',P_db_name,'.aud_',tbl_name,'(',v_column_list,',audit_type ,audit_user ,audit_date) \nvalues (',v_ins_val_list,',''insert'',SESSION_USER(),CURRENT_TIMESTAMP); \n' ));
 SET i_trigger = CONCAT(i_trigger,'END IF;\n');
 SET i_trigger = CONCAT(i_trigger,'END$$ \nDELIMITER ; \n');
 select i_trigger from dual into @triggertxt ;
 SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', p_trigger_script_path ,'/ins_trigger_',tbl_name,'.sql', '"');
 -- select @savestr;
 PREPARE stmt5 FROM @savestr;
 EXECUTE stmt5;
 DEALLOCATE PREPARE stmt5;
 --  --------------------------------------------
set v_column_list ='';
END$$

DELIMITER ;


3. Following procedure call above created procedures to create audit tables and then generate scrip to create audit triggers for update and delete operations.

DELIMITER $$
DROP PROCEDURE IF EXISTS create_audit$$
CREATE PROCEDURE create_audit (in p_db_name varchar(64),in p_table_name varchar(64),in p_user_to_audit varchar(64),in p_trigger_script_path varchar(1000))
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;

 DECLARE v_table_name varchar(64);

 DEClARE tab_cursor CURSOR FOR
select  table_name
from information_schema.tables
where TABLE_SCHEMA=p_db_name
and (TABLE_NAME=p_table_name or p_table_name is null)
and table_name not like 'aud\_%'
order by table_name;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER  FOR NOT FOUND SET v_finished = 1;

 OPEN tab_cursor;
 set v_table_name='';
 get_table: LOOP
 -- select v_finished;
 FETCH tab_cursor INTO v_table_name;
 --  select v_finished;
 IF v_finished = 1 THEN
 LEAVE get_table;
 END IF;
  select v_table_name;
 call create_aud_tabs(p_db_name,v_table_name);
 call  add_aud_triggers(p_db_name,v_table_name,p_user_to_audit,p_trigger_script_path);

 END LOOP get_table;
 CLOSE tab_cursor;
END$$
DELIMITER ;


To create audit tables and trigger script for complete schema.

call create_audit('mydb,null,'myuser','/usr/local/mysql/data/triggers');

To create audit tables and trigger script for only on table in schema.
call create_audit('mydb,'table_name','myuser','/usr/local/mysql/data/triggers');

To create Audit table for one table use following Procedure.

call create_aud_tabs('mydb','mytable');

To get script for audit trigger of one table use following script.

call add_aud_triggers('mydb','mytable','myuser','/usr/local/mysql/data/triggers');

Note: path "/usr/local/mysql/data/triggers" must be created manually before executing the procedures 

-----------------------------------------------------------------------------------------------------

4. Follow the next steps to execute script generated with above procedure to create triggers.

now the audit script is created and we can proceed  with following commands.

-- Execute following query and and paste result in file called triggers.sql and place this file with other scripts generated at "/usr/local/mysql/data/mydb/triggers"  as given in the above procedures.

select concat ('source ' ,filename )
from (
select  concat('del_trigger_',table_name,'.sql') filename,table_name
from information_schema.tables
where TABLE_SCHEMA='mydb'
and table_name not like 'aud\_%'
union all
select  concat('upd_trigger_',table_name,'.sql') filename,table_name
from information_schema.tables
where TABLE_SCHEMA='mydb'
and table_name not like 'aud\_%'
union all
select  concat('ins_trigger_',table_name,'.sql') filename,table_name
from information_schema.tables
where TABLE_SCHEMA='mydb'
and table_name not like 'aud\_%') files
order by table_name  ;


Now go to the location "/usr/local/mysql/data/mydb/triggers"  and execute following command to create triggers.

-- Command to execute create trigger scripts

 /usr/local/mysql/bin/mysql -u root -h hostname -p -f  mydb < triggers.sql

Helping Script with above procedures


-- Query to generate drop script for audit tables
select concat ('drop table ',table_schema,'.',table_name,';')
from information_schema.tables
where table_schema='mydb'
and table_name like 'aud\_%';


-- Query to count tables
select count(0)
from information_schema.tables t
where t.table_schema='mydb'
and t.table_type='BASE TABLE'
and t.table_name not like 'aud\_%';

-- Query to count Audit tables

select count(0)
from information_schema.tables t
where t.table_schema='mydb'
and t.table_type='BASE TABLE'
and t.table_name like 'aud\_%';


-- Query to find tables for which script is Unable to create audit table

select CONCAT ('SELECT * FROM ',t.table_name,';')
from information_schema.tables t  left outer join information_schema.tables a
on(t.TABLE_NAME=substr(a.table_name,5))
and a.table_schema='mydb'
and a.table_name like 'aud\_%'
where a.table_name is null
and t.table_schema='mydb'
and t.table_name not like 'aud\_%';


-- Query to find tables for which script is Unable to create audit triggers

select CONCAT ('SELECT * FROM ',a.table_name,';')
from information_schema.tables a left outer join information_schema.TRIGGERS t
on (t.EVENT_OBJECT_TABLE=a.TABLE_NAME)
where t.EVENT_OBJECT_TABLE is null
and a.table_schema='mydb'
and a.table_name not like 'aud\_%';

-----------------------




-- Query to find table which have 3 or less triggers (ins,upd,del)

select EVENT_OBJECT_TABLE,tb.TABLE_NAME
 from (
 select  t.EVENT_OBJECT_TABLE
 from information_schema.TRIGGERS  t
 group by t.EVENT_OBJECT_TABLE
 having count(0)>=3 ) tr right outer join information_schema.tables tb
 on (tr.EVENT_OBJECT_TABLE=tb.TABLE_NAME)
where tb.TABLE_SCHEMA='mydb'
 --  and tb.table_name='mytable'
  and tr.EVENT_OBJECT_TABLE is null
 and tb.TABLE_name not like 'aud\_%';

Following query will give you the script to Grant priviliges to new user on the database.

select  concat ('grant select ,insert ,update ,delete on mydb.',table_name ,' to ','`myuser`@`%`;')
from information_schema.tables
where TABLE_SCHEMA='mydb'
-- and (TABLE_NAME=p_table_name or p_table_name is null)
and table_name not like 'aud\_%';

MySql usefull queries

1. Query to kill process by a host 

Execute following query this will give you the list of processes executed by the host given in the where clause.

select concat('KILL ',id,';') ,p.*
from information_schema.processlist  p
where host like '192.168.10.11%';

Copy the results and execute on Mysql to kill all process by the host.



2. Query to find master and child table and column names with master table name

SELECT rc.`CONSTRAINT_CATALOG`   AS `ChildTable_Catalog`
, rc.`CONSTRAINT_SCHEMA`         AS `ChildTable_Schema`
, rc.`TABLE_NAME`                AS `ChildTable`
, rc.`CONSTRAINT_NAME`           AS `ChildTable_ForeignKey`
, GROUP_CONCAT(DISTINCT fk.`COLUMN_NAME` ORDER BY fk.`ORDINAL_POSITION` ASC) AS `ChildTable_ForeignKey_Columns`
, rc.`UNIQUE_CONSTRAINT_CATALOG` AS `ParentTable_Catalog`
, rc.`UNIQUE_CONSTRAINT_SCHEMA`  AS `ParentTable_Schema`
, rc.`REFERENCED_TABLE_NAME`     AS `Parent_Table`
, rc.`UNIQUE_CONSTRAINT_NAME`    AS `ParentTable_UniqueKey`
, GROUP_CONCAT(DISTINCT uk.`COLUMN_NAME` ORDER BY fk.`ORDINAL_POSITION` ASC) AS `ParentTable_UniqueKey_Columns`
-- constraint relation
FROM INFORMATION_SCHEMA.`REFERENTIAL_CONSTRAINTS` AS rc
-- foreign key
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS fk
    ON rc.`CONSTRAINT_CATALOG`        = fk.`CONSTRAINT_CATALOG`
   AND rc.`CONSTRAINT_SCHEMA`         = fk.`CONSTRAINT_SCHEMA`
   AND rc.`TABLE_NAME`                = fk.`TABLE_NAME`
   AND rc.`CONSTRAINT_NAME`           = fk.`CONSTRAINT_NAME`
-- unique key
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS uk
    ON rc.`UNIQUE_CONSTRAINT_CATALOG` = uk.`CONSTRAINT_CATALOG`
   AND rc.`UNIQUE_CONSTRAINT_SCHEMA`  = uk.`CONSTRAINT_SCHEMA`
   AND rc.`REFERENCED_TABLE_NAME`     = uk.`TABLE_NAME`
   AND rc.`UNIQUE_CONSTRAINT_NAME`    = uk.`CONSTRAINT_NAME`
-- optional filter condition
WHERE rc.`UNIQUE_CONSTRAINT_SCHEMA` = 'database name'
  AND rc.`REFERENCED_TABLE_NAME`    = 'parent table name'
-- necessary grouping parameters
GROUP BY rc.`CONSTRAINT_CATALOG`
, rc.`CONSTRAINT_SCHEMA`
, rc.`TABLE_NAME`
, rc.`CONSTRAINT_NAME`
, rc.`UNIQUE_CONSTRAINT_CATALOG`
, rc.`UNIQUE_CONSTRAINT_SCHEMA`
, rc.`REFERENCED_TABLE_NAME`
, rc.`UNIQUE_CONSTRAINT_NAME`
-- optional ordering parameters
ORDER BY rc.`CONSTRAINT_CATALOG` ASC
, rc.`CONSTRAINT_SCHEMA` ASC
, rc.`REFERENCED_TABLE_NAME` ASC
, rc.`TABLE_NAME` ASC;


2. Query to find master and child table and column names with child table name in mysql


SELECT rc.`CONSTRAINT_CATALOG`   AS `ChildTable_Catalog`
, rc.`CONSTRAINT_SCHEMA`         AS `ChildTable_Schema`
, rc.`TABLE_NAME`                AS `ChildTable`
, rc.`CONSTRAINT_NAME`           AS `ChildTable_ForeignKey`
, GROUP_CONCAT(DISTINCT fk.`COLUMN_NAME` ORDER BY fk.`ORDINAL_POSITION` ASC) AS `ChildTable_ForeignKey_Columns`
, rc.`UNIQUE_CONSTRAINT_CATALOG` AS `ParentTable_Catalog`
, rc.`UNIQUE_CONSTRAINT_SCHEMA`  AS `ParentTable_Schema`
, rc.`REFERENCED_TABLE_NAME`     AS `Parent_Table`
, rc.`UNIQUE_CONSTRAINT_NAME`    AS `ParentTable_UniqueKey`
, GROUP_CONCAT(DISTINCT uk.`COLUMN_NAME` ORDER BY fk.`ORDINAL_POSITION` ASC) AS `ParentTable_UniqueKey_Columns`
-- constraint relation
FROM INFORMATION_SCHEMA.`REFERENTIAL_CONSTRAINTS` AS rc
-- foreign key
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS fk
    ON rc.`CONSTRAINT_CATALOG`        = fk.`CONSTRAINT_CATALOG`
   AND rc.`CONSTRAINT_SCHEMA`         = fk.`CONSTRAINT_SCHEMA`
   AND rc.`TABLE_NAME`                = fk.`TABLE_NAME`
   AND rc.`CONSTRAINT_NAME`           = fk.`CONSTRAINT_NAME`
-- unique key
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS uk
    ON rc.`UNIQUE_CONSTRAINT_CATALOG` = uk.`CONSTRAINT_CATALOG`
   AND rc.`UNIQUE_CONSTRAINT_SCHEMA`  = uk.`CONSTRAINT_SCHEMA`
   AND rc.`REFERENCED_TABLE_NAME`     = uk.`TABLE_NAME`
   AND rc.`UNIQUE_CONSTRAINT_NAME`    = uk.`CONSTRAINT_NAME`
-- optional filter condition
WHERE rc.`UNIQUE_CONSTRAINT_SCHEMA` = 'database name'
  and fk.`TABLE_NAME`='child table name'
-- necessary grouping parameters
GROUP BY rc.`CONSTRAINT_CATALOG`
, rc.`CONSTRAINT_SCHEMA`
, rc.`TABLE_NAME`
, rc.`CONSTRAINT_NAME`
, rc.`UNIQUE_CONSTRAINT_CATALOG`
, rc.`UNIQUE_CONSTRAINT_SCHEMA`
, rc.`REFERENCED_TABLE_NAME`
, rc.`UNIQUE_CONSTRAINT_NAME`
-- optional ordering parameters
ORDER BY rc.`CONSTRAINT_CATALOG` ASC
, rc.`CONSTRAINT_SCHEMA` ASC
, rc.`REFERENCED_TABLE_NAME` ASC
, rc.`TABLE_NAME` ASC;

Rebuild or compile Indexes on Mysql ndb cluster

To rebuild or verify Indexes on Mysql we can us alter table command.

this will tell you if any of index is having issues.
 i.e corrupt ,data issue related to index.

alter table table_name engine= engine_name;


1. Query to generate script of those tables whose indexes needs to be rebuild or compile.


select concat('alter table ', TABLE_SCHEMA,'.',table_name , ' engine=' , engine ,';' ) alters from information_schema.TABLES
where TABLE_SCHEMA='mydb';

2. Copy generated results and executed on either mysql command line or workbench GUI tool

i.e on mysql command line.

./mysql -u root -p -h hostname

alter table mydb.alteridentity engine=ndbcluster;

alter table mydb.audit_trail engine=ndbcluster;

Setup Docker and then Postgres on the Docker Centos6.

Follow the steps one to six under heading Install with yum given in below link to setup and start docker.

https://docs.docker.com/engine/installation/linux/centos/

Install with yum

1. Log into your machine as a user with sudo or root privileges.
2. Make sure your existing yum packages are up-to-date.

   $ sudo yum update
   

3. Add the yum repo.

   $ sudo tee /etc/yum.repos.d/docker.repo <<-'EOF'
   [dockerrepo]
   name=Docker Repository
   baseurl=https://yum.dockerproject.org/repo/main/centos/6/
   enabled=1
   gpgcheck=1
   gpgkey=https://yum.dockerproject.org/gpg
   EOF
   

4. Install the Docker package.

   $ sudo yum install docker-engine
   

5. Start the Docker daemon.

   $ sudo service docker start
   

6. Verify docker is installed correctly by running a test image in a container.

   $ sudo docker run hello-world
   Unable to find image 'hello-world:latest' locally
       latest: Pulling from hello-world
       a8219747be10: Pull complete
       91c95931e552: Already exists
       hello-world:latest: The image you are pulling has been verified. Important: image verification is a tech preview feature and should not be relied on to provide security.
       Digest: sha256:aa03e5d0d5553b4c3473e89c8619cf79df368babd1.7.1cf5daeb82aab55838d
       Status: Downloaded newer image for hello-world:latest
       Hello from Docker.
       This message shows that your installation appears to be working correctly.
   
       To generate this message, Docker took the following steps:
        1. The Docker client contacted the Docker daemon.
        2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
               (Assuming it was not already locally available.)
        3. The Docker daemon created a new container from that image which runs the
               executable that produces the output you are currently reading.
        4. The Docker daemon streamed that output to the Docker client, which sent it
               to your terminal.
   
       To try something more ambitious, you can run an Ubuntu container with:
        $ docker run -it ubuntu bash
   
       For more examples and ideas, visit:
        http://docs.docker.com/userguide/

7. If you are behind proxy then follow the below link and restart the docker as given below.
8. https://crondev.com/running-docker-behind-proxy/

For RedHat/CentOS version 6:

1 2 3 4 5 6 7

cat <<EOF | sudo tee -a /etc/sysconfig/docker export http_proxy="http://myproxy.example.com:8080" export https_proxy="https://myproxy.example.com:8080" export no_proxy=<REGISTRY_IP> EOF sudo service docker restart

Now you are ready to setup PostgreSQL.

Execute following command and it will pull latest version of PostgreSQL and follow link below to start and connect PostgreSQL with docker.

Link is given below.

https://hub.docker.com/_/postgres/

docker pull postgres

start a postgres instance

$ docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

docker run --name postgres9.5 -e POSTGRES_PASSWORD=postgres -d postgres

Start psql

$ docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres
psql (9.5.0)
Type "help" for help.

postgres=# SELECT 1;
 ?column? 
----------
        1
(1 row)

docker run -it --rm --link postgres9.5:postgres postgres psql -h postgres -U postgres

Password for user postgres:
psql (9.5.4)
Type "help" for help.

postgres=# select 1;
 ?column?
----------
        1
(1 row)


postgres=# \q

First execute following command to list running docker.

[root@centos6_pri lib]# docker ps -a

It will show you the name of the all docker images avaliable and their status in my case is  "postgres9.5"

To login to Docker

 docker exec -i -t postgres9.5 /bin/bash

How to check if Oracle Database instance is up and running?

I. Log into infrastructure instance  & login as sysdba;

\$ sqlplus /nolog

SQL> conn / as sysdba;

SQL> select status from v$instance;

STATUS

------------------------------------

OPEN

II. Log into database server & issue the following command

\$ ps -ef | grep pmon

ocsinfra 11021     1   0 09:15:15 ?           0:09 ora_pmon_orcl

ocsinfra 17960 17592   0 10:47:15 pts/2       0:00 grep pmon

If DB instance is running, then the above command returns the pmon process running as above.