A guide to Oracle DB core concepts and SQL queries

If you want to get started or quickly refresh your Oracle DB skills, here is a compilation of core concepts and queries. By the end of this article, you will have an overview of materials that average Java developers know when working with oracle DB.


Many of these tips come from the excellent book Oracle SQL 11g. Some others are queries I regularly use when analyzing legacy databases for listing dependencies.

1- Core concepts
2- Analysis requests
3- DCL – Accessing
4- DDL – Creating structures
5- DML – Insert / remove data
6- Basic PL / SQL

1- Core concepts

Oracle DB is a powerful Relational Database Management System (RDBMS), designed for handling Terabytes of data, and mainly on mainframe systems. Since 10g, Oracle introduced distributed workloads and distributed transactions management.

Official doc emphasizes these main concepts:
– Oracle is a relational database with ACID transactions
– Its RDBMS governs the database by kernel code, metadata & queries
– It contains physical and logical units which are independent:

Physical units

Set of files stored on disk

Data files
Physical data file, with tables and indexes

Control files
Tracks physical components

Redo logs
Track changes in data

Logical units

Database instance
Set of memory structures which manage database files on the database server

An user-created collection of data structures, like:
– Tables
– Indexes
– Sequences

Container for segments

Set of extents

Several contiguous data blocks

Data blocks
The smallest unit of data

User / Schema
In Oracle DB, “user” and “schema” are equivalent concepts. When created, a user gives is assimilated to its schema (set of tables, etc.. to which it can access). The “usernae” and “password” are used to connect to the DB instance can then access only the items shared for its schema. Also, the user/schema can be granted several privileges for these data items. (read/write/update/delete/modify-structure)

Table, Columns, Constraints, Synonyms
The schema contains tables, which are logical sets of data shaped by “columns” and “constraints” (defined with specific syntax). Tables can be linked in another schema under a different name “synonym”

SQL Queries, Indexes, Sequences
“SQL queries” are a specific language for searching data within these tables. Indexes are the fact of optimizing one or more columns of a table to make this search faster, to the detriment of writing. Some columns can be filled by sequences (logical suites of numbers)

PL / SQL, Packages, Functions, Triggers
Finally, Oracle defines a specific procedural language in addition to simple SQL queries: the PL/SQL. It regroups common concepts of loops, data structures (“Objects”), conditional structures and else with SQL to provide advance Programmation on DB side.

PL/SQL blocks can be found within “functions” (if it returns some value, or “procedures” (if it returns nothing). When PL / SQL blocks can be triggered by external events – such as DB writing, reading, etc.., it is called “Trigger”.

If the PL/SQL part grows much larger, functions and procedures can be stored into “packages”, to help general comprehension for developers / DBA. Like packages for java.

2- Analysis requests

Here is a listing of common requests, when describing the structure of the database. They can be made on SQL clients like SQL Developer. Evolved DB analysis software like DBeaver or Toad can show an equivalent result more transparently.

Table info DESCRIBE tablename
Columns SELECT * FROM user_tables WHERE table_name=…
Columns2SELECT table_name, column_name FROM all_tab_columns;
ConstraintsSELECT constraint_name, constraint_type, status, defferable, defered FROM user_constraints WHERE table_name=…
CommentsSELECT * FROM user_tab_comments WHERE table_name=…
Comments2SELECT * FROM user_col_comments WHERE table_name=…
SequencesSELECT * FROM user_sequences
IndexesSELECT index_name, table_name, uniqueness, status FROM user_indexes WHERE table_name =… (all indexes)
Indexes2SELECT index_name, table_name, column_name FROM user_ind_columns WHERE table_name= … (indexs by column)
ViewsSELECT * FROM user_views WHERE view_name = …
ProceduresSELECT * FROM user_procedures    (for both procedures and functions)
Procedures2SELECT * FROM user_procedures WHERE object_name =‘packagename’ (for specific container package)
TriggersSELECT * FROM user_triggers;
Search String occurences in all PL/SQLSELECT “NAME”, “TYPE”, count(*) importance
  FROM all_source
WHERE owner=’THE_USER_NAME’ and upper(text) like upper(‘%THE\_STRING%’)
 escape ‘\’
group  by  “NAME”, “TYPE”
having count(*) > 7
order by importance desc;
Search constraints referencing this tableSELECT a.table_name, a.column_name, a.constraint_name, c.owner,
       — referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = ‘R’
and a.owner = ‘THE_USER_NAME’
   AND c_pk.table_name = ‘THE_TABLE_NAME’

3- DCL – Accessing

4- DDL – Creating structures

5- DML – Insert / remove data

6- Basic PL / SQL

Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!

Popular posts

Contact Info


Jakutská 422/6, Prague

Copyright 2020 SCALEFINE ©  All Rights Reserved