2 ways to get current date time in Oracle with SQL command

You can get current date time in Oracle with this command:

SELECT CURRENT_TIMESTAMP AS CURRENTDATETIME FROM DUAL;

The result will look like this:

6/5/2019 3:22:31.375489 PM +07:00

Another way is to use SYSDATE like this command:

SELECT SYSDATE AS CURRENTDATETIME FROM DUAL;

The result will be a little different:

6/5/2019 3:23:18 PM

PL/SQL Add unique constraint into tables

This post shows you how to write SQL command to add unique constraint into a table and how to make unique constraint right at the time we create the table.

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

In Oracle, a unique constraint can not contain more than 32 columns.

Unique constraint defined in a CREATE TABLE statement

This command will be as follow

CREATE TABLE TABLENAME(
col1 INT NOT NULL,
col2 BIGINT NULL,
col3 VARCHAR2(10) NULL
CONSTRAINT constraint_name UNIQUE (col1, col2)
);

In this PL/SQL command:

  • TABLENAME is the name of defined table
  • constraint_name is the name of the unique constraint which comprise of 2 column col1 and col2

Unique constraint defined in an ALTER TABLE statement

The command to add an unique constraint to an existing table as below:

ALTER TABLE tablename
ADD (CONSTRAINT constraint_name UNIQUE(col1, col) 
USING INDEX PCTFREE 10)

in this SQL:

  • tablename is the name of the table to alter
  • constraint_name is any name for the unique constraint

PCTFREE 10 means Oracle will keep on adding new rows to a block until it is 90% full. This leaves 10% for future updates (row expansion).

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)
REFERENCES TABLEB (TABLEB_PK)

In the example above:

  • TABLEA is the name of the table to add constraint to
  • TABLEB is the name of the reference table
  • TABLEB_PK is the PK of the TABLEB, can be ID column in real
  • tableb_constraint_name is the name of the constraint, any name you want to memorize it better.

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 this command:

ALTER SYSTEM KILL SESSION '8,15';

In this case SID = 8 and SERIAL# = 15.

In order to execute the kill sql command, you need to log in with SYS account or any user with KILL SESSIONS privilege.

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 to Oracle database (and other kind of databases such as MySQL, SQLSERVER, …) they also offer many useful tool for database design and database administration like: Spotlight on Oracle, Toad Data Modeler, Toad for Oracle, …

Toad belongs to Quest Software and can be found at: www.quest.com/

Now, just log in to any session with saved password and Create Database Link (Menu Database > Create > DB Link …)

Create database link menu in Toad for Oracle
Create database link menu in Toad for Oracle

Now, create database link window displays like picture below, please fill Link Name and press dropdown button to choose your desire database to connect to. That should be the database username you want to read/recover password. In the picture below, I want to recover password for CALLCENTER user:

Create database link window in Toad for Oracle
Create database link window in Toad for Oracle

Now press on Show SQL, another window will be displayed and show you the actual SQL running behind with your desired password:

SQL Statement window in Toad Oracle
SQL Statement window in Toad Oracle

Your password for selected user was highlight as in the picture.

P/s: this trick was fixed by Quest software for all Toad version above 11.5 (e.g: 13.0) . In this example I used Toad 11.5

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 linesize 170; 
col SQL format a80; 
col SERVER heading 'SVR' format a3; 
col EVENT heading 'WAITING' format a30 fold_after; 
col OSUSER heading 'OSUSER' format a8; 
col USERNAME heading 'USERNAME' format a8; 
col PID heading 'OSPID' format 99999; 
col DISK_READS heading 'DISK I/O' format 99999999; 
col BUFFER_GETS heading 'BUFFER|GETS' format 99999999;  
--- Begin select needed criteria 
SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,      
V$SESSION.OSUSER OSUSER, 
V$SQLAREA.DISK_READS DISK_READS,      
V$SQLAREA.BUFFER_GETS BUFFER_GETS,         SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,      
V$SESSION.PROCESS PID,      
V$SESSION_WAIT.EVENT EVENT,         
V$SQLAREA.SQL_TEXT SQL FROM V$SESSION_WAIT, 
V$SQLAREA, V$SESSION 
WHERE V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND      V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND         V$SESSION.SID = V$SESSION_WAIT.SID (+) AND      
V$SESSION.STATUS = 'ACTIVE' AND      
V$SESSION_WAIT.EVENT != 'client message' 
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME; 

The result will be something like below depends on your database status:

USERNAMEOSUSERDISK_READSBUFFER_GETSLOCKWAITPIDEVENTSQL
ORAERRAdministrator0######### 1234SQL*Net message to clientSELECT SOFTID, SERIAL FROM TBL_ITU_SOFTPIN WHERE ESTATUS = 0
REPORTERhoc142 6880SQL*Net message from dblink
REPORTERapache127 6482SQL*Net message from dblink
SYSAdministrator080 4344:3296SQL*Net message to client
Oracle show current active processes as a DBA
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 of this column, we can identify one, and no more than one row in the table.

A column satisfied those conditions might not become PK, but if the column is a PK then, it MUST satisfy those conditions.

idnamepassword
1John123456
2Jay654321
3John

In this table above, only id column can be PK since other column is not unique (name column) or is null (password column). In this case, we can asign ID column as PK.

What is FK – Foreign Key

FK is another very common definition in rational databases world. FK or foreign keys are keys which aren primary key in another table.

Take a look at this example: We have had table 1 defined above, now we have another table called table 2 holds user classes

iduser_idclass_id
136
217
328

In this table, we can tell that, ID is the PK of table 2, user_id is a PK of table 1 above, here in table 2, user_id is a FK. Also with class_id, we can asume that class_id also an FK here.

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: active (running) since Tue 2019-05-14 11:45:17 UTC; 2min 32s ago
   Process: 27540 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
   Process: 27530 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
  Main PID: 27539 (mysqld)
     Tasks: 29
    Memory: 142.1M
       CPU: 572ms
    CGroup: /system.slice/mysql.service
            └─27539 /usr/sbin/mysqld

Stop MySQL/MariaDB database

This command used to stop/shutdown MySQL database on Ubuntu:

# systemctl stop mysql.service

Restart MySQL/MariaDB database

This command used to stop/shutdown MySQL database on Ubuntu:

# systemctl restart mysql.service

Check status of MySQL/MariaDB database

This command used to check status of MySQL database on Ubuntu:

# systemctl status mysql.service

Or you can use another command

 journalctl -xe 

Location of MySQL/MariaDB ‘s configuration file on Ubuntu 16.04

By default, the configuration file’s location will be at:

/etc/mysql/mysql.conf.d/mysqld.cnf

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 set new max connection allowed by the MySQL database. A normal website could takes about 100-200 database connections. A big one could take up to 500-800 connections. Run this command as mysql root (no need to restart database but this will lost after restarting database):

mysql -u root -p
mysql> set global max_connections := 500;

Or you can set in configuration file (need to restart mysql but last permanently), by default, with Ubuntu 16.04 the location is: /etc/mysql/mysql.conf.d/mysqld.cnf in [mysqld] section:

[mysqld]
 user            = mysql
 pid-file        = /var/run/mysqld/mysqld.pid
 socket          = /var/run/mysqld/mysqld.sock
 port            = 3306
 basedir         = /usr
 datadir         = /var/lib/mysql
 tmpdir          = /tmp
 lc-messages-dir = /usr/share/mysql
 skip-external-locking
 max_connections = 500

Skip reverse DNS lookup of clients

By default, MySQL automatically lookup the incoming connection from clients. You can disable this feature to make your MySQL database faster. Adding these lines to the configuration file of MySQL at [mysqld] section:

 
[mysqld] 
# Skip reverse DNS lookup of clients 
skip-name-resolve 

You’ll need to restart you MySQL/MariaDB to make changes applied. Take a look at basic operations with MySQL/MariaDB

Enable slow queries log

By default, MySQL disabled/commented slow queries log, to enable this feature you should un-comment those lines in the config file under [mysqld] section:

# vi /etc/mysql/mysql.conf.d/mysqld.cnf
slow-query-log-file = /var/lib/mysql/mysql-slow.log 
long_query_time = 2 

You, again, need to restart the MySQL database to apply changes.

Reduce WAIT_TIMEOUT of MySQL/MariaDB

By default, the wait_timeout equals to 28800 seconds which is too long and resource consuming for weak server. Reduce it down to 120 seconds would be better. Add following lines to the configuration

# vi /etc/mysql/mysql.conf.d/mysqld.cnf
wait_timeout=120

Restart MySQL database to apply new configuration.