PostgreSQL DBA Part 9: Tablespaces and Where Data Is Stored
Learn PostgreSQL tablespaces on Ubuntu: create one on another disk, move tables and indexes, set temp_tablespaces and drop it safely, with real output.
PT Ganesh
PostgreSQL DBA Part 8: Databases, Schemas and search_path
PostgreSQL schema vs database explained for DBAs: create databases, use template0, organise schemas and set search_path permanently, with real output.
Oracle Identity Column: Examples, Options and ORA-00001 Fix
Create an Oracle identity column with GENERATED ALWAYS or BY DEFAULT ON NULL, get the new ID in PL/SQL, and fix ORA-00001 with START WITH LIMIT VALUE.
PRAGMA AUTONOMOUS_TRANSACTION in Oracle: Examples and Pitfalls
Learn PRAGMA AUTONOMOUS_TRANSACTION in Oracle with examples: error logging that survives rollback, audit triggers, and fixes for ORA-06519.
PostgreSQL DBA Part 7: Roles, Users and Privileges (GRANT)
Create users and group roles in PostgreSQL 18, grant database, schema and table privileges, set default privileges and drop roles safely. Tested steps.
Oracle Global Temporary Table: Examples, Indexes and Stats
Learn how an Oracle global temporary table works, ON COMMIT DELETE vs PRESERVE ROWS, indexes, ORA-14452, statistics and private temporary tables.
PostgreSQL DBA Part 6: pg_hba.conf and Client Authentication
Learn pg_hba.conf on PostgreSQL 18 for Ubuntu: rule order, peer vs scram-sha-256, allowing remote connections, testing rules and fixing login errors.
Oracle Delete Duplicate Rows but Keep One (4 Methods)
Delete duplicate rows in Oracle but keep one, using MIN(ROWID), ROW_NUMBER and GROUP BY, with expected output, NULL traps and how to prevent repeats.
ORA-01555 Snapshot Too Old in Oracle: Causes and Fixes
Fix ORA-01555 snapshot too old in Oracle: find the failing query, read V$UNDOSTAT, size undo correctly and stop committing inside cursor loops.
ORA-00060 Deadlock Detected in Oracle: Causes and Fixes
Why Oracle raises ORA-00060 deadlock detected, what it rolls back, how to read the trace file, and code fixes: lock order, FK indexes and retries.
Oracle Pagination Query: OFFSET FETCH vs ROWNUM (Examples)
Write an Oracle pagination query with OFFSET FETCH (12c+) or ROWNUM (11g), see each page's output, and learn keyset paging for fast deep pages.
PostgreSQL DISTINCT ON: One Row per Group, With Examples
Learn PostgreSQL DISTINCT ON with real examples: latest row per group, multiple columns, NULLs, ties, row_number() comparison and indexing for speed.
Mutating Table Error in Oracle (ORA-04091): How to Fix It
Fix the mutating table error in Oracle (ORA-04091): see why row-level triggers fail, then solve it with a statement-level or compound trigger.
PostgreSQL DBA Part 5: postgresql.conf, Reload vs Restart
Find postgresql.conf on Ubuntu, read settings with SHOW and pg_settings, and learn which changes need a reload and which need a restart in PostgreSQL 18.
PostgreSQL DBA Part 4: psql Commands Every DBA Needs
Learn the psql commands a PostgreSQL DBA uses daily: connecting, meta-commands, output formats and safe scripts with ON_ERROR_STOP, on Ubuntu.
How to Read Explain Plan in Oracle (With Examples)
Learn how to read an explain plan in Oracle: execution order, Rows and Cost, access vs filter predicates, and real row counts with DBMS_XPLAN.
PostgreSQL DBA Part 3: The Data Directory (PGDATA) Explained
What every file and folder in the PostgreSQL 18 data directory on Ubuntu does, how tables map to files, and how to move PGDATA to a new disk safely.
Oracle Blocking Sessions Query: Find and Fix Lock Waits
Find blocking sessions in Oracle with V$SESSION and V$LOCK, trace the root blocker and locked row, and resolve the block safely. With example output.
PostgreSQL DBA Part 2: Install PostgreSQL 18 on Ubuntu
Install PostgreSQL 18 on Ubuntu from the official PGDG repository, then find its files and start, stop and reload it with systemctl and pg_ctlcluster.
PostgreSQL DBA Part 1: What a DBA Does and How PostgreSQL Works
Start the PostgreSQL DBA series: what a DBA does, and PostgreSQL architecture explained with real processes, memory settings and files on Ubuntu.
Oracle LISTAGG DISTINCT: Remove Duplicates (With Examples)
Remove duplicate values from Oracle LISTAGG with DISTINCT in 19c, or a subquery on 11g to 18c. Includes output, ordering tips and an ORA-01489 fix.
PostgreSQL Deadlock Detected: Causes, Examples and Fixes
Fix PostgreSQL ERROR: deadlock detected (40P01). Reproduce it, read the log, find blocking sessions and lock rows in a safe order, with real output.
PostgreSQL Upsert with ON CONFLICT: Examples and Pitfalls
Learn PostgreSQL upsert with INSERT ON CONFLICT DO UPDATE and DO NOTHING: multi-column keys, EXCLUDED, partial indexes, real errors and MERGE.
Oracle BULK COLLECT LIMIT Example with FORALL (Step by Step)
Learn Oracle BULK COLLECT with LIMIT step by step: the safe fetch loop, the %NOTFOUND trap, FORALL inserts and SAVE EXCEPTIONS, with expected output.
PostgreSQL FOR UPDATE SKIP LOCKED: Build a Safe Job Queue
Learn how FOR UPDATE SKIP LOCKED lets many workers take jobs from one PostgreSQL table without waiting or double work, with real examples and output.
Oracle MERGE Statement Example: Upsert, Update and Delete
Learn the Oracle MERGE statement with examples: upsert, conditional update, DELETE WHERE, single-row upsert, and fixes for ORA-30926 and ORA-38104.
Mastering Oracle SQL Joins: The Ultimate Guide to Data Retrieval and Performance
In the world of relational database management systems (RDBMS), data is rarely stored in a single, massive table. Instead, it is normalized into multiple tables to reduce redundancy and improve data integrity. Consequently, the ability to combine these tables effectively is the hallmark of a skilled database developer. In Oracle SQL, Joins are the primary
Mastering Oracle Database Architecture: A Comprehensive Overview
Ever wondered what happens behind the scenes when you hit “Execute” on a SQL query? It’s not just magic; it’s a sophisticated symphony of memory structures, specialized background processes, and a meticulously organized physical storage hierarchy. This intricate system is what allows Oracle to handle petabytes of data, ensure high concurrency, and guarantee rock-solid data
Mastering PostgreSQL Architecture: 6 Powerful Steps to Optimize Performance & Scalability
PostgreSQL, often referred to as Postgres, is a powerful open-source relational database management system (RDBMS) known for its robustness, extensibility, and performance. Its architecture is designed to handle complex workloads while ensuring reliability and scalability. In this blog, we’ll briefly explore the PostgreSQL architecture, its key components, and how it supports high-performance applications. This guide
Understanding Oracle Triggers – A Comprehensive Guide with Examples
In the world of database management, ensuring data consistency, integrity, and automation is crucial. Oracle triggers are a powerful tool to accomplish these objectives. They allow the automatic execution of specified actions when certain database events occur.
SQL Query Design Patterns: Writing Efficient Queries
In the world of databases, writing efficient and maintainable SQL queries can often feel like solving a complex puzzle.
Master Oracle Interview Questions: Key Concepts & Examples for 2024
Preparing for an Oracle database interview can be challenging, especially with the range of advanced concepts you need to cover.
Ace Your PL/SQL Interview Questions for Success! Part-4
Are you preparing for a PL/SQL developer interview and want to ensure you’re fully equipped to tackle the most frequently asked questions? You’re not alone.
Forward and Backward Navigation in Oracle PL/SQL- Expert Examples
Navigating through rows of data in Oracle PL/SQL is a common requirement when working with large datasets or developing applications that involve data traversal.
Top 10 Common Oracle Errors and Proven Ways to Fix Them
Oracle is one of the most powerful and widely used databases, but even the best systems aren’t immune to errors.
Query Optimization in SQL- Best Practices and Examples
To improve the performance of SQL queries, developers and DBAs need to understand the query optimizer and the techniques it uses to select an access path and prepare a query execution plan.