MySQL Settings

Configuration File Location:
for Windows 7:  C:\ProgramData\MySQL\MySQL Server 5.6\my.ini


How to move database to different directory

1. Stop the MySQL instance service.
2. Copy the data directory to the location where the new data directory should be.
3. Giving a full control privileges to user NetworkService on the new data directory, otherwise the service won't start.

How to use TIMESTAMP for Oracle

CURRENT_TIMESTAMP

 

SELECT CURRENT_TIMESTAMP FROM dual;

 

SELECT DUMP(SYSTIMESTAMP) FROM dual;

 

SELECT EXTRACT(YEAR FROM DATE '2007-04-01') FROM dual;

 

SELECT FROM_TZ(TIMESTAMP '2007-11-20 08:00:00', '3:00') FROM dual;

SELECT FROM_TZ(TIMESTAMP '2007-11-20 19:30:00', '3:00') FROM dual;

 

ALTER SESSION SET TIME_ZONE = '-5:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

ALTER SESSION SET TIME_ZONE = '-8:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

-- The following statement fails because the mask does not include
-- the TIME ZONE portion of the return type of the function:
INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));

-- The following statement uses the correct format mask
-- to match the return type of LOCALTIMESTAMP:
INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

SELECT * FROM local_test;

 

Create Sequence Numbers for Oracle

Full Create Sequence Syntax
CREATE SEQUENCE <sequence_name> INCREMENT BY <integer> START WITH <integer> MAXVALUE <integer> / NOMAXVALUEMINVALUE <integer> / NOMINVALUECYCLE / NOCYCLECACHE <#> / NOCACHEORDER / NOORDER;

Create Sequence Simplest Form
CREATE SEQUENCE seq_campus_site_id;
SELECT seq_campus_site_id.NEXTVAL FROM dual;

Simple Autonumber With Sequence
INSERT INTO <table_name>(<column_name>)VALUES(<sequence_name>.NEXTVAL);
INSERT INTO campus_site(site_id, organization_name, campus_name)VALUES(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Main Seattle');
SELECT * FROM campus_site;
INSERT INTO campus_site(site_id, organization_name, campus_name)VALUES(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Bothell');
SELECT * FROM campus_site;

Simple Autonumber With Sequence Into Two Tables
INSERT INTO <table_name>(<column_name>)VALUES(<sequence_name>.CURRVAL);
CREATE SEQUENCE seq_division_id;
INSERT INTO campus_site(site_id, organization_name, campus_name)VALUES(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Tacoma');
INSERT INTO division (division_id, site_id, division_name) VALUES(seq_division_id.NEXTVAL, seq_campus_site_id.CURRVAL, 'Engineering');
SELECT * FROM campus_site;
SELECT * FROM division;

Create Schema in Oracle

Purpose

Create multiple tables and views and perform multiple grants in your own schema in a single transaction. If all statements execute successfully, then the database commits them as a single transaction. If any statement results in an error, then the database rolls back all of the statements.

 

conn / as sysdba

CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;

GRANT create session TO uwclass;
GRANT create table TO uwclass;
GRANT create view TO uwclass;

conn uwclass/uwclass

-- first one that doesn't work (t3 does not exist)
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t3 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;

-- then one that does
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t2 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;