Monday, September 28, 2015

How to view a list of MySQL users and their privileges




mysql> select user,host from mysql.user;

+------------------+--------------+
| user             | host                 |
+------------------+--------------+
| root             | %                    |
| root             | 127.0.0.1         |
| honnikery   | localhost     |
| dba             | %                     |
| test              | localhost           |
+------------------+--------------+


5 rows in set (0.01 sec)


mysql> show grants for 'root'@'%';

+------------------------------------------------+
| Grants for root@%                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' .... |
+------------------------------------------------+
1 row in set (0.00 sec)

Tuesday, September 15, 2015

How to change the foreign key referential action in MYSQL

first step, drop old constraint: (reference)

alter table `honnikery`
drop foreign key `fk_name`;

notice only constraint is deleted

second step, add new constraint:

alter table `honnikery`
add constraint `fk_name`
    foreign key (`bidar`) references `honnikery1` (`bidar`) on delete cascade;

Wednesday, September 2, 2015

how to connect to mysql database from command line in windows

 I am using XAMPP software for programming and MySQL as a Database.

Go command line

C:\>d:

d:\>cd xampp\mysql\bin

d:\xampp\mysql\bin> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 007                |
| 02                 |
| assignment         |
| bidar              |
| cdcol              |
| cr_debug           |
| crm                |
| demo               |
 || mydb               |
| mysql              |
| om                 |
| orthos             |
| performance_schema |
| phpmyadmin         |
| prabha             |
| signup             |
| test               |
| webauth            |
+--------------------+
23 rows in set (0.00 sec)

Friday, August 28, 2015

MYSQL commands in linux

First to connect to MySQL command line.

# mysql -u root -p
Password:


view all available databases.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test    
  honnikery          |                    |
+--------------------+

Select a Database in MySQL

user database-name;


Show Tabels in a Database

show tables;

Show MySQL Database Fields & Field Formats
describe table-name;


Convert MyISAM to INNODB
ALTER TABLE ENGINE=INNODB;

Repair Broken Table(s) in MySQL
repair table broke_table_name;

Monday, August 24, 2015

Tuesday, August 18, 2015

Create and Drop a Job SCHEDULER (DBMS_SCHEDULER AND DBMS_JOB) IN ORACLE DATABASE 10G




create  table honnikery as select * from emp where 1=2


select * from honnikery

select job,last_date,next_date,broken from dba_jobs



CREATE OR REPLACE PROCEDURE SCHEDULER_TEST
AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE honnikery';
END;
BEGIN
INSERT INTO honnikery
SELECT * FROM emp;
COMMIT;
END;
END;



declare
  l_jobno number;

begin

 dbms_job.submit(
  l_jobno,
   what=>'Proc_Test;',
   next_date=>sysdate, -- start now
   interval=>'sysdate+5/(24*60)'
 ); 
 commit;
end;



BEGIN 
  DBMS_SCHEDULER.create_job ( 
  job_name => 'SCHEDULER_JOB',
   job_type => 'PLSQL_BLOCK', 
   job_action => 'BEGIN SCHEDULER_TEST ;  END;', 
   start_date => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=5; bysecond=0;', 
    enabled => TRUE); 
    END;

  Drop a Job SCHEDULER

begin
dbms_scheduler.drop_job(job_name => 'SCHEDULER_JOB');
end;

Enable / Disable a job

BEGIN 
  DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB');
END;



BEGIN 
  DBMS_SCHEDULER.ENABLE('SCHEDULER_JOB');
END;
 
 

Monitoring jobs 

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'SCHEDULER_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'SCHEDULER_JOB';