Category Archives: Databases

Creating Oracle Database in a Cloud

Login to Oracle Cloud console, go to Autonomous Transaction Processing page:

(more…)

Shrinking MySQL database

Calculating disk space usage per MySQL DB:

sudo du -h /var/lib/mysql/
932M    /var/lib/mysql/beauty
179M    /var/lib/mysql/slogpost
485M    /var/lib/mysql/omegauto
8.0K    /var/lib/mysql/test
676K    /var/lib/mysql/sys
22G     /var/lib/mysql/bot
79M     /var/lib/mysql/master
11M     /var/lib/mysql/mysql
176K    /var/lib/mysql/phpmyadmin
21M     /var/lib/mysql/shar
127M    /var/lib/mysql/mike
1.1M    /var/lib/mysql/performance_schema
79M     /var/lib/mysql/devnote
12M     /var/lib/mysql/mike1
48K     /var/lib/mysql/game
24G     /var/lib/mysql/
(more…)

Setting up Oracle Database for development

To set up an Oracle Database for development I install the database in a docker container and then modify password policies in the default user profile as follows:

ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED;

this prevents passwords from expiring and users from being locked.

(more…)

Oracle Database 19c auditing with Multitenant Architecture

First I installed Oracle 19c in Docker container , enabled unified auditing, and created multiple pluggable databases:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
#connect to root container
./sqlplus / as sysdba
CREATE PLUGGABLE DATABASE testpdb1 ADMIN USER admin1 IDENTIFIED BY dbpasswd1
    FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb1');
Pluggable database created.
CREATE PLUGGABLE DATABASE testpdb2 ADMIN USER admin2 IDENTIFIED BY dbpasswd2
    FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb2');
Pluggable database created.
(more…)

Installing Oracle Database 19.3 in a Docker container on Ubuntu Server 18.04

First I installed Ubuntu Server 18.04 as a Hyper-V machine on Windows 10.

While creating the virtual machine I selected Generation 1, because Generation 2 resulted in some strange effects.

Then I created a separate user with sudo permission:

sudo useradd -d /home/singh -m --shell "/bin/bash" singh
sudo passwd singh
sudo usermod -aG sudo singh
(more…)

Inegrating Oracle Database 18 with MS Active Directory

I configured DNS on my Windows Server 2016 (takes some time, probably 15 minutes):

ipconfig /registerdns
(more…)

Measuring SQLite insertion performance with C++ code

I did a quick Google search on “SQLite performance” and found the following:

Then to benchmark SQLite performance by myself I used the following C++ code that inserts 1000 batches of 1000 000 rows to a single table with an integer primary key:

(more…)

Oracle Database user and role IDs

Below I provided the results of two following queries:

SELECT USERNAME, USER_ID FROM DBA_USERS ORDER BY USER_ID DESC;
SELECT ROLE, ROLE_ID FROM DBA_ROLES ORDER BY ROLE_ID DESC;

that display Oracle Database 18 users, roles and their IDs:

(more…)

How to change MySQL 5.7 root password

First, check MySQL version:

SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.25                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.25-0ubuntu0.16.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+
(more…)

Accessing Oracle Database in C++ with MS VS2017.

Download binaries (redistributables) and C++ SDK from Oracle website. For 64bit platform they are respectively:

instantclient-basic-windows.x64-18.5.0.0.0dbru.zip
instantclient-sdk-windows.x64-18.5.0.0.0dbru.zip

Extract them, create a C++ project in VS2017 and link oraocci18.lib in Release configuration and oraocci18d.lib in Debug configuration.

(more…)