Category Archives: Databases

Measuring SQLite 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…)

How to get all the privileges of an Oracle database user.

While investigating of how to get all the privileges of an Oracle database user I played with the following queries:

select COUNT(*) from DICTIONARY;
select USERNAME from SYS.ALL_USERS;

select * from DBA_USERS;
select * from DBA_ROLES;
select * from DBA_SYS_PRIVS where grantee = 'CONNECT';
select * from DBA_SYS_PRIVS where grantee = 'RESOURCE';
select * from DBA_SYS_PRIVS;
select * from SESSION_PRIVS;
select * from DBA_TAB_PRIVS where GRANTEE <> 'PUBLIC';
select count(*) from DBA_TAB_PRIVS;
select * from DBA_COL_PRIVS;
select * from ROLE_TAB_PRIVS;
(more…)

Installing sample Oracle Database schemas.

I installed sample Oracle database schemas with the following steps:

Cloned the repository, changed the directories in the installation script, and started my docker container with the mapped folder:

sudo su - oracle
mkdir repos
cd repos
git clone https://github.com/oracle/db-sample-schemas.git
cd db-sample-schemas
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
exit
sudo docker run -d -it --rm --name oracle18se -v /home/oracle/oradata18:/opt/oracle/oradata -v /home/oracle/repos:/home/oracle/repos -p 1521:1521 -p 5500:5500 oracle/database-se:18.3.0
sudo docker logs oracle18se
(more…)

Oracle 19c Audit changes.

In Oracle 19c the following fields of AUDIT_UNIFIED_ENABLED_POLICIES table were renamed:

  • ENABLED_OPT renamed with ENABLED_OPTION
  • USER_NAME renamed with ENTITY_NAME

So the audit options query in Oracle 19c looks like this:

SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up 
WHERE uep.ENTITY_NAME = 'ALL USERS' and uep.ENABLED_OPTION='BY USER' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION';
(more…)

Configuring Oracle Database for Auditing.

To enable Unified Auditing in Oracle Database 12 and 18 on Oracle Linux I did this:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
./sqlplus sys as sysdba
SHUTDOWN IMMEDIATE
EXIT
./lsnrctl stop
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
cd $ORACLE_HOME/bin
./lsnrctl start
./sqlplus sys as sysdba
STARTUP
(more…)

Running Oracle 18 database in a docker container on Ubuntu 18.04.

Clone the repository with the official Oracle docker images:

git clone https://github.com/oracle/docker-images

Install docker, download Oracle Database, put it to the directory docker-images/OracleDatabase/SingleInstance/dockerfiles/18.3.0 containing the Dockerfile and run the following commands to build and run Enterprise Edition:

cd docker-images/OracleDatabase/SingleInstance/dockerfiles/18.3.0
mv ~/Downloads/LINUX.X64_180000_db_home.zip .
sudo docker build -t oracle/database:18.3.0 --build-arg DB_EDITION=EE .
sudo docker run -d -it --rm --name oracle18 oracle/database:18.3.0
sudo docker logs oracle18 --tail 100
sudo docker logs oracle18 | grep -i password

and the following commands to build and run Standard Edition 2:

sudo docker build -t oracle/database-se:18.3.0 --build-arg DB_EDITION=SE2 .
sudo docker run -d -it --rm --name oracle18se oracle/database-se:18.3.0
sudo docker logs oracle18se --tail 100

the first string of the docker output contains the generated password for SYS, SYSTEM and PDBADMIN.

(more…)

Backing up and restoring all the PHP MySQL websites on a Linux server

All the following commands assumes we saved MySQL root password into MROOTPASS variable:

export MROOTPASS=<mysql root password>

The most straight forward method to backup all the MySQL databases and all the website files (PHP scripts, images, etc..) stored in the /home directory is the following:

mysqldump --all-databases -u root -p$MROOTPASS | gzip > all-databases-$(date '+%Y-%m-%d_%H-%M-%S').sql.gz
tar -cvzf home.tar.gz /home

If we backup some individual database (probably not as root) and change its user while restoring it, it might make a sense to remove DEFINER from the output script:

sed -e 's/*]*\*/\*/'

The following commands restore all the websites from the archives:

gunzip -c all-databases-2017-05-23_15-31-00.sql.gz | mysql -u root -p$MROOTPASS
cd /
sudo tar -xvzf home.tar.gz

After migration from MySQL versionĀ  14.14 Distrib 5.5.54 to 14.14 Distrib 5.7.18 (I do not know what is the difference between them) I got the following error: “ERROR 1805 (HY000) at line 1: Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted” while trying to drop some user, and fixed it by running:

mysql_upgrade -u root -p$MROOTPASS
service mysql restart