Category Archives: Oracle

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

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

Oracle Database Cold Backup and Restore Script

In 2008 when I worked with some Oracle databases under Solaris and AIX, I spent some time to figure out how to make the database backup and restore and decided to use cold backup as the most straightforward method. As far as I remember, to backup the database I shutted down Oracle and then simply archive the database files using “zip” command. To restore the database I used the following shell script that extracts archived files and adjust some Oracle settings:

(more…)