Oracle on Mac

Using docker will minimize Oracle compatibility issues with native Operating System.

Step 1: Install Docker

Step 2: Install Oracle Image
File: docker-compose.yml
version: ‘3’
image: sath89/oracle-12c
– “./docker-volume/oracle/data:/u01/app/oracle”
– “8080:8080”
– “1521:1521”
>docker-compose up

Step 3: Oracle is ready to use.
Follow notes in

Step 4: Create separate user

DATAFILE ‘tbs_perm_project1.dat’

IDENTIFIED BY “password”
DEFAULT TABLESPACE tbs_perm_project1;

GRANT create session TO user1;
GRANT create table TO user1;
GRANT create view TO user1;
GRANT create any trigger TO user1;
GRANT create any procedure TO user1;
GRANT create sequence TO user1;
GRANT create synonym TO user1;



Step 5: Manage Oracle Image in Docker container
Use Kitematic

You need to restart Oracle Container, if there are issues.


Learn Oracle

Where to start learning Oracle?

Step 1: Download Express Edition and SQL Developer.

Step 2: Create Emp, Dept tables and insert seed data

Step 3: Understand each statement with the help of Oracle documentation and try.
Big Thanks to Mr. Srinivas Aithagani for providing extensive list of queries to practice.

Here we learn by example. How business statement looks, how we write query, learning with example…i.e cause and effect.

Step 4: Oracle Reference

As backup, above content SQL Statements were downloaed and provied in the form of following files.


Types of JOINS


Database SQL Language Reference

Oracle Data Types:

Oracle built-in functions / regular expressions


SQL Query Optimization:
Also check “Explain Plan” option in SQL Developer

Table partitioning:


Stored Procedures / Functions

Database Performance Tuning:

Preventing SQL Injection / Attacks

Database Replication

Database Replication can kill production if right technique was not selected. Identify the purpose/need for database replication. Put a scope around it and identify right technique and tools to achieve the goal. Setting authorization on data is very important. Always treat data with most respect as cash. Visit compliance / audit issues.

Why we need database replication?
1. To re-produce production issues in other environment.
2. To analyze production data
3. To run reports / business intelligence
4. For failover purpose
5. For back up
6. To achieve high availability
7. To improve performance for end user by putting db servers across continents and sync them up.
And many more

Types of Replication

Transactional replication.
Merge replication.
Snapshot replication.
Good pointer from Microsoft:
Oracle GoldenGate is costly product and reliable. But consultant costs around $2000/- per day.

Hire an Architect to give complete roadmap for given problem.


ORA-01000 maximum open cursors exceeded

When we see this Oracle error

Step 1: First check database and see who is connecting and not closing connections.
Step 2: If connections were kept open for long time means, issue is in code. Check result set and statements where closed properly.
Step 3: Only DBA can alter cursor count if required

Few queries picked from net and useful for quick troubleshooting

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and = ‘opened cursors current’;

select sql_text, count(*) as “OPEN CURSORS”, user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and = ‘opened cursors current’
group by s.username, s.machine
order by 1 desc;

–Only DBA can do this
ALTER SYSTEM SET open_cursors = 500 SCOPE=BOTH;

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and = ‘opened cursors current’
and ‘open_cursors’
group by p.value;


Oracle – Regular Expressions

–Select all URLs who don’t have http as prefix
select unique url_col from table_name
WHERE not REGEXP_LIKE(url_col , ‘^http’,’i’) and url_col is not null


Transpose Columns to Rows in Oracle

WITH all_objects_data AS (
select * from table1 where id = xyz
SELECT column_name , column_value FROM all_objects_data
UNPIVOT (column_value
FOR column_name
IN (col1,col2));



Database – Naming Standardization (Oracle)

Problem Statement: In tables can we use employee_name or emp_name?

Solution: This depends on organization. Many organizations follow emp_name.

Full Name Advantages:

  1. Easy to Read
  2. No Confusion


  1. Oracle supports only 30 char length column names
  2. Difficult to write queries at command prompt

Abbreviation Usage:

  1. Needs Abbreviation map to understand all column Names.
  2. Column Comments should help in this process.
  3. Easy to write queries
  4. Looks simple and easy to write and talk

Here with attached document from Oracle Site for your reference.