Scaling solutions for Product managers
Hi, I'm Aurelien. This blog references solutions for satisfying your next customer challenges.
From technology stacks evaluation to strategic planning, find tools for delivering the best quality apps.
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:
Set of files stored on disk
Physical data file, with tables and indexes
Tracks physical components
Track changes in data
Set of memory structures which manage database files on the database server
An user-created collection of data structures, like:
Container for segments
Set of extents
Several contiguous 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=…|
|Columns2||SELECT table_name, column_name FROM all_tab_columns;|
|Constraints||SELECT constraint_name, constraint_type, status, defferable, defered FROM user_constraints WHERE table_name=…|
|Comments||SELECT * FROM user_tab_comments WHERE table_name=…|
|Comments2||SELECT * FROM user_col_comments WHERE table_name=…|
|Sequences||SELECT * FROM user_sequences|
|Indexes||SELECT index_name, table_name, uniqueness, status FROM user_indexes WHERE table_name =… (all indexes)|
|Indexes2||SELECT index_name, table_name, column_name FROM user_ind_columns WHERE table_name= … (indexs by column)|
|Views||SELECT * FROM user_views WHERE view_name = …|
|Procedures||SELECT * FROM user_procedures (for both procedures and functions)|
|Procedures2||SELECT * FROM user_procedures WHERE object_name =‘packagename’ (for specific container package)|
|Triggers||SELECT * FROM user_triggers;|
|Search String occurences in all PL/SQL||SELECT “NAME”, “TYPE”, count(*) importance|
WHERE owner=’THE_USER_NAME’ and upper(text) like upper(‘%THE\_STRING%’)
group by “NAME”, “TYPE”
having count(*) > 7
order by importance desc;
|Search constraints referencing this table||SELECT 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’