SQL Add foreign key constraint example

This example will show you SQL to add foreign key constraint in to a table. This SQL tested with Oracle database but you can apply into any other relational database such as: MariaDB, MySQL, MSSQLSERVER.

The SQL command looks like this:

ALTER TABLE TABLEA 
ADD CONSTRAINT tableb_constraint_name FOREIGN KEY (TABLEB_PK)
Read the rest “SQL Add foreign key constraint example”

How to select and kill sessions win Oracle database

To kill sessions from a user, we first find all sessions belongs to this user. We can do it with table V$SESSION.

To select all sessions belong to an user:

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'ABC';

Then, with SID and SERIAL#, we can delete/kill a session with … Read the rest “How to select and kill sessions win Oracle database”

How to read remembered passwords in Toad for Oracle

I will show you how to read/find remembered passwords saved in Toad. It’s a little tricky but a very handful tips for your DBA job.

First of all your new session window looks like this:

New session window in Oracle Toad software
New session window in Oracle Toad software

Toad is a software allow us to connect … Read the rest “How to read remembered passwords in Toad for Oracle”

Oracle show current active processes as a DBA

This SQL will help you list all current active processes in Oracle as a DBA. You will need SYS account or an account with granted privilege to run this command.

Just copy and paste to run on SQL Developer or Toad with SYS account logged in.

SET pagesize 55; 
SET 
Read the rest “Oracle show current active processes as a DBA”

What is PK, FK in rational databases like Oracle, MySQL?

This post will show you what is PK, FK in rational databases such as Oracle, MySQL, Postgre, MSSQLServer. They are basics but they are also important.

What is PK – Primary Key

PK or Primary Key is the unique, not null column in the table. Unique means with one value … Read the rest “What is PK, FK in rational databases like Oracle, MySQL?”

How to start/stop/restart MySQL/MariaDB on Ubuntu

This post show you how to start/stop/restart and check status of MySQL/MariaDB database on Ubuntu.

Start MySQL/MariaDB database

This command used to startup MySQL database on Ubuntu:

# systemctl start mysql.service

Your output will look like this

mysql.service - MySQL Community Server
    Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Active: 
Read the rest “How to start/stop/restart MySQL/MariaDB on Ubuntu”

4 critical MySQL/MariaDB optimization for WordPress on 512MB Ram server

Tuning and optimizing MySQL/MariaDB to run on a weak box with 512MB RAM for 1-5 WordPress websites running on is a big challenge. Since the cloud providers need you to pay more money on their products, they rarely have tutorial like this.

Set MariaDB/MySQL max connection

Using this command to … Read the rest “4 critical MySQL/MariaDB optimization for WordPress on 512MB Ram server”

Oracle- How to find all duplicated records in the table

This post will guide you how to find/list all duplicated records in a table in the fastest way. This command also not make your database slower since its a lightweight SQL.

Basic syntax

SELECT * FROM TBLA WHERE ID IN (
 SELECT ID FROM (SELECT ID, COUNT(*) AS NUM FROM 
Read the rest “Oracle- How to find all duplicated records in the table”

MSSQLServer – How to insert from select to an existing table

In this tutorial, we’ll use “INSERT INTO SELECT FROM” structure to build an SQL to insert data from a select query to an existing table in MS SQLSERVER (tested with version 2008)

General syntax of “INSERT INTO SELECT FROM”

INSERT INTO TableA SELECT * FROM TableB WHERE col1 Read the rest “MSSQLServer – How to insert from select to an existing table”