Learn Oracle

Where to start learning Oracle?

Step 1: Download Express Edition and SQL Developer.
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Step 2: Create Emp, Dept tables and insert seed data
https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

Step 3: Understand each statement with the help of Oracle documentation and try.
https://sites.google.com/site/instantoracle/oracle-sql-queries-practice
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
http://docs.oracle.com/cd/E11882_01/index.htm

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

————————-

Types of JOINS
https://www.techonthenet.com/oracle/joins.php
https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52331

————————-

Database SQL Language Reference
https://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm

Oracle Data Types:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm
https://www.techonthenet.com/oracle/datatypes.php

Oracle built-in functions / regular expressions
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm

Normalization
http://www.studytonight.com/dbms/database-normalization.php

SQL Query Optimization:
https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm
Also check “Explain Plan” option in SQL Developer

Table partitioning:
https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
Examples: https://livesql.oracle.com/apex/livesql/file/content_EW51YHTOHS6IXN7QQ8AFD28O.html

Indexes
https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm

Stored Procedures / Functions
https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm

Database Performance Tuning:
https://docs.oracle.com/cd/B28359_01/server.111/b28274/toc.htm

Preventing SQL Injection / Attacks
https://docs.oracle.com/cd/E58500_01/pt854pbh1/eng/pt/tpcd/task_PreventingSQLInjection-0749b7.html#topofpage
http://www.dba-oracle.com/security/sql_injection_attacks.htm

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: http://msdn.microsoft.com/en-IN/library/ms152531.aspx
Oracle GoldenGate is costly product and reliable. But consultant costs around $2000/- per day.

Hire an Architect to give complete roadmap for given problem.
-o-

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 b.name = ‘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 b.name = ‘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 b.name = ‘opened cursors current’
and p.name= ‘open_cursors’
group by p.value;

Oracle – Regular Expressions

http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm
http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm

–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));

-o-
*****
http://www.dba-oracle.com/t_converting_rows_columns.htm

http://boostips.blogspot.com/2012/05/transpose-column-value-to-row-in-oracle.html
http://www.oracle-developer.net/display.php?id=506

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

Disadvantages:

  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.

http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodelernamingstandards-167685.pdf

datamodelernamingstandards-167685

Reference:

https://wiki.kuali.org/display/KULRICE/Database+Table+and+Column+Name+Standards

https://forums.oracle.com/forums/thread.jspa?threadID=1001609

-o-