Oracle’s UPDATE and MERGE statements can both modify data, but depending on indexing, join conditions, and query complexity their execution speed varies dramatically; this article explains when each is appropriate, outlines their drawbacks, and provides real‑world examples with execution plans and optimization techniques.
This guide explains why a numeric column turned null, compares using the volatile v$sql view with a permanent logging solution, and details step‑by‑step how to create a tablespace, logging table, stored procedure, and scheduled job to capture all Oracle SQL statements.
This article explains Oracle PL/SQL collection types—index‑by tables, nested tables, and VARRAYs—detailing their definitions, initialization rules, element access, practical code examples, and the built‑in methods available for manipulating these collections.
This article explains why an Oracle DBMS_JOB can become broken and show a next execution date of 4000‑01‑01, details the job parameters, the automatic retry logic, how to diagnose the underlying PL/SQL errors via alert logs, and provides concrete steps to restart and resolve the issue.
The article explains why Oracle bad block incidents demand scenario‑driven handling, outlines interview questions, describes the pitfalls of inspecting each alert log, and advocates promptly switching to disaster‑recovery and using backups to restore service, while sharing real‑world cases and practical DBA advice.
This article compiles a comprehensive set of Oracle SQL queries that monitor wait events, rollback segment contention, tablespace and file system I/O ratios, SGA hit rates, dictionary cache efficiency, MTS activity, fragmentation, and other critical performance metrics, often with recommended threshold values.
This article explains Oracle 12.2 sharding concepts—including table families, sharded versus duplicated tables, chunk definition and movement, shardspace usage, sharding methods, and step‑by‑step deployment commands—providing detailed guidance for DBA practitioners.
The article examines Salesforce's 2016 five‑hour data loss caused by a data‑center outage, explains why Oracle‑based backups failed, and presents Oracle GoldenGate and Active Data Guard as robust disaster‑recovery solutions for cloud databases.
Oracle’s block access mechanism involves hashing block addresses to locate hash buckets, using CBC latches and buffer pins to coordinate logical and physical reads, and following distinct step‑by‑step procedures for non‑unique index reads, unique index reads, and write operations, ensuring concurrency safety.
This article examines a real‑world Oracle query where outdated or incorrect table and index statistics caused the optimizer to choose a costly index range scan instead of a partition full‑table scan, and it outlines diagnostic steps and practical solutions to restore accurate cost estimates.
A DBA intern recounts a real‑world Oracle tablespace failure, the challenges of resizing a BIGFILE tablespace, disk‑space shortages, permission errors during data‑file migration, and the key lessons about communication, analysis, and preparation for future production incidents.
This article explains Oracle trigger fundamentals, syntax, and a series of practical examples—including preventing weekend updates, auto‑incrementing IDs, logging DML actions, aggregating department salaries, capturing deletions, view‑based inserts, salary change alerts, and tracking CREATE/DROP operations—complete with full SQL code snippets.
This guide outlines concrete strategies to reduce I/O and CPU overhead in Oracle queries, covering scope reduction, partition pruning, join simplification, hint usage, bind variables, and common pitfalls like Cartesian products and inefficient filters.
The article explains Oracle's database architecture, detailing how the server comprises an instance and a database, the structure and functions of the SGA memory components, and the roles of key background processes such as PMON, SMON, DBWn, LGWR, CKPT, and ARCn.
The article explains the internal steps Oracle uses to acquire buffers, identifies the main reasons why buffer busy wait events occur—including hot blocks, freelist limits, and specific wait codes—and provides practical tuning methods such as adjusting PCTFREE/PCTUSED, rebuilding tables, and managing undo segments.
This article explores the major enhancements in Oracle 12c's optimizer, including session‑level statistics for global temporary tables, new DBMS_STATS reporting procedures, adaptive features, advanced join elimination methods, incremental and concurrent statistics collection, and automatic column‑group detection, providing practical guidance for DBAs and developers.
This technical guide explores Oracle 12c's optimizer enhancements, covering adaptive query optimization, adaptive plans and join methods, hybrid hash distribution, dynamic and adaptive statistics, automatic re‑optimization, statistics feedback, new histogram types, and online statistics collection, with practical examples and visual illustrations.
This guide explains step‑by‑step how to offline Oracle datafiles or tablespaces, copy them to a new directory, rename them, recover if needed, bring them back online, and safely shut down and restart the database to complete the migration.
This article explains Oracle's parallel execution features—including parallel query, DML, and DDL—covers object, session, and statement level settings, provides practical SQL examples, discusses common pitfalls, and presents performance test results that demonstrate significant speed improvements for large data migration tasks.
This article explains how the Oracle oerr utility works as a shell script, shows how it maps facilities to components, locates the appropriate message files, and demonstrates using sed and awk to extract detailed error descriptions, causes, and actions for any ORA code.
This article explains Oracle's parallel query feature, covering its benefits, resource costs, required conditions, various data‑distribution methods such as broadcast, replicate and hash, how to read parallel execution plans, and practical monitoring techniques to avoid performance pitfalls.
Oracle profiles let administrators enforce password policies and resource limits such as CPU time, I/O, session count, and idle periods, with detailed parameters for both kernel resources and password rules, and the article provides a complete example of creating a custom profile using SQL.
This guide details the most effective Oracle Data Pump parameters and related database settings—such as access_method, parallel, network_link, and NLS options—to dramatically improve export and import performance while avoiding common pitfalls.
This article explains Oracle's database architecture, detailing the division between instance and database, the structure of SGA memory, and the roles of key background processes that together enable user interactions and ensure data consistency and recovery.
This comprehensive guide explains Oracle's memory‑management evolution, compares manual SGA/PGA sizing with ASMM, AMM and PGA automatic management, details key parameters and hidden settings, shows how to size SGA, PGA and large pages, and offers practical best‑practice recommendations for DBAs.
This guide explains Oracle's auditing capabilities, covering its purpose, three audit types, how to enable auditing via AUDIT_TRAIL, defining audit policies, controlling record generation, locating audit records, special considerations, and provides practical SQL examples.
The article explains how a seemingly harmless SQL statement, when executed millions of times on an IBM Power780 system with a 1 TB configuration, can overload the library cache mutex, causing the database to stall, and provides the original script, reproduction steps, and practical advice to avoid such catastrophic contention.
This guide provides Oracle SQL queries to list currently running statements, retrieve historical SQL, identify high‑CPU or high‑disk‑usage queries, find slow statements, and detect uncommitted transactions, enabling DBAs to quickly diagnose performance issues.
This article walks through two unconventional Oracle recovery techniques—restoring a mistakenly deleted datafile by leveraging Linux file descriptors and bypassing a missing archive log with BBED—detailing the scenarios, analysis, step‑by‑step commands, and practical considerations.
This article shares practical Oracle DBA experiences—including role definitions, environment considerations, monitoring strategies, performance‑tuning case studies, development‑business integration, and future‑oriented operations—offering concrete guidance and hard‑won insights for database administrators.
This article explains Oracle's idle and non‑idle wait events, shows how to query the v$event_name view, counts and classifies events, introduces related performance views, and provides detailed descriptions and parameters for 33 frequently encountered wait events.
The article examines why databases are frequent targets of security breaches, explains the most common SQL injection vulnerability, categorizes injection paths, methods, and examples—including manipulation, code, function‑call, and buffer‑overflow attacks—then outlines practical defense measures such as input encryption, database firewalls, and patching.
A colleague changed an Oracle application user's password, only to see the account lock every few minutes; the article walks through checking the database version, profile settings, listener logs, discovering a lingering dataSync.jar process that still used the old password, and outlines remediation steps.
This guide explains the different Oracle database installation methods, focuses on silent installation using response files, shows the required file templates, demonstrates the runInstaller command, and walks through typical permission and syntax errors with practical troubleshooting steps.
This guide details the complete process of moving a 2‑node Oracle 11gR2 RAC database to new servers and storage using Active Physical Standby Data Guard, covering architecture, pre‑implementation preparation, detailed implementation steps, switchover procedures, impact assessment, rollback measures, and lessons learned.
This guide introduces the free gDUL 3.0 tool for Oracle DBAs, detailing its export capabilities, directory layout, configurable parameters, platform‑specific setup tips, and step‑by‑step usage to recover data from inaccessible databases.
Discover six powerful techniques—including B‑tree and bitmap indexes, materialized views, result‑set caching, and clever query rewrites—to dramatically reduce logical reads and accelerate Oracle's SELECT COUNT(*) performance, with step‑by‑step examples and measurable improvements.
This article examines Oracle Database 12c’s key innovations—including multitenant architecture, the Database In‑Memory option, enhanced security controls, and integrated big‑data analytics—while sharing real‑world production experiences, benefits, and practical limitations for enterprises adopting cloud‑ready databases.
This guide shows how to split a delimited string into a one‑dimensional array in Oracle SQL using a custom hierarchical query, avoiding the restrictions of DBMS_UTILITY.UNCL_ARRAY and allowing any delimiter for flexible data processing.
A comprehensive collection of Oracle SQL statements helps database administrators quickly retrieve tablespace sizes, data file details, rollback segment information, control and log files, usage statistics, object metadata, version data, long‑running queries, partition parameters, locked objects, resource‑intensive sessions, lock and wait statuses, SGA metrics, cached objects, and SQL area details.
This article explains how Oracle 12c's In-Memory feature enables hybrid OLTP/OLAP workloads by storing columnar data in a dedicated memory area, covering its architecture, data loading, consistency mechanisms, query acceleration techniques, and integration with RAC for high‑availability deployments.
This guide walks through adding ASM disks, creating an ACFS filesystem, building a RAC database on it, generating read‑only and read‑write snapshots while running DML, and cloning a second database from those snapshots, including optional Data Guard usage for zero‑impact testing.
On February 10, 2016 a provincial accounting database experienced severe daily‑batch delays despite other applications running normally; a senior DBA collected alert logs, AWR snapshots, ASH dumps, and OSW metrics, uncovered log‑file‑sync and redo‑IO degradation, increased rollbacks, and a faulty SAN link, pinpointing the root cause.
This article provides a comprehensive overview of Oracle Database architecture, detailing the server components, instance memory structures such as SGA, essential background processes, and the physical and logical storage layers, helping readers understand how Oracle organizes data and manages operations.
This guide explains why the ORA-01555 "snapshot too old" error occurs in Oracle databases, where related logs are stored, the underlying UNDO and LOB mechanisms that trigger it, and step‑by‑step solutions to diagnose and eliminate the problem.
This article reveals why Oracle’s CREATE TABLE documentation spans dozens of pages and walks readers through essential Oracle database concepts such as tables, indexes, sequences, views, clusters, tablespaces, database links, and synonyms to help master the platform efficiently.
This article explains how to optimize Oracle distributed queries that involve remote tables by minimizing remote calls, reducing result set size, and improving execution plans through techniques such as collocated inline views, CBO behavior, and driving_site hints, illustrated with detailed examples and performance measurements.
This article breaks down Oracle's SQL parsing workflow into four stages—syntax check, semantic check, parse phase, and execution phase—detailing how hard and soft parses differ, why cursors may not be shared, and common reasons for parsing failures.
This guide walks through a rapid 10‑minute troubleshooting workflow for MySQL and Oracle databases, covering resource checks, log inspection, wait‑event analysis, SQL identification, and feedback best practices to quickly pinpoint performance problems without relying on external monitoring tools.
This article walks through practical Oracle SQL optimization methods—explaining execution plans, indexes, statistics, hints, and partitioning—using concrete examples and step‑by‑step case studies that show how to dramatically reduce query runtimes and CPU load.
Oracle 12c’s In‑Memory Option (IMO) lets you store table, partition, and materialized view data in a columnar format within the SGA, offering faster query performance, configurable compression, priority settings, and detailed management via INMEMORY clauses, parameters, and specialized dictionary views.
This article explains why sparse data in Oracle tables hampers continuous time‑series reporting, introduces the Partitioned Outer Join syntax introduced in Oracle 10g, and demonstrates step‑by‑step how to transform one‑dimensional and multi‑dimensional gaps into dense datasets using practical SQL examples.
This article presents two Oracle SQL challenges from the 3rd ITPUB "Shengtou Media Cup" Tic‑Tac‑Toe contest, detailing how to generate every possible terminal game sequence for insertion into a table and how to compute forced win strategies for any given board using pure SQL techniques.
This article presents the full specifications and solution approaches for a two‑part Oracle SQL challenge that requires inserting every possible terminal Tic‑Tac‑Toe game state into a table and writing a query to determine forced wins from any valid board configuration.
Presented are two Oracle‑SQL challenges from the 3rd ITPUB “Shengtou Media Cup” Tic‑Tac‑Toe contest: (1) generate every possible terminal move sequence and board state, inserting them into a TICTACTOE table without PL/SQL in WITH clauses, and (2) given any valid board, compute the forced winner and the minimal remaining moves using recursive SQL and minimax logic.
This article explains how the OraZ toolset streamlines Oracle database administration by automating routine tasks, providing intelligent hang analysis, offering comprehensive health checks, and delivering real‑time instance activity insights, ultimately reducing DBA workload and improving operational efficiency.
The article details the ITPUB "Shengtou Media Cup" SQL contest, presenting two Oracle‑based Tic‑Tac‑Toe problems—one to list every possible terminal game sequence and insert it into a table, and another to compute forced wins from any valid board—along with solution approaches and extra challenges.
This article explores the mysterious Oracle DUAL table, detailing its purpose, how it behaves during different instance startup phases, the performance‑boosting FAST DUAL feature, practical tests that reveal hidden bugs across 10g‑12c, and safe methods for recreating or modifying the table.
A practical guide introduces nine free Oracle DBA utilities—including OS Watcher, oratop, ora, SQL Developer, AWR/ASH/ADDM, ORAchk, RDA, RMAN, and OEM CC—detailing their installation, usage, and key features for effective database monitoring and maintenance.
This technical session covers Oracle RAC high‑availability best practices, installation steps, daily operational commands, detailed case studies of auto‑start checks, version‑mix issues, addNode failures, network heartbeat problems, and client connection errors, plus a concise Q&A on uninstall, SCAN vs VIP, and split‑brain detection.
This article recounts a detailed troubleshooting journey of an Oracle 11gR2 RAC cluster where a newly added node could not start with SQLPLUS due to HAIP misconfiguration and mismatched environment variables, explaining the diagnostic steps, root cause discovery, and the final resolution.
This guide explains Oracle's Orion tool, its advantages and limitations, step‑by‑step usage instructions, sample test scenarios for random read/write and sequential read, result file details, and a troubleshooting tip for asynchronous I/O errors on Linux.
This guide explains how Oracle's Trace File Analyzer (TFA) simplifies and accelerates log collection across single‑node and RAC databases, covering supported platforms, installation details, workflow, command usage, analysis features, and practical tips for permissions and configuration.
This guide explains Oracle Real‑Time SQL Monitoring, covering terminology, activation conditions, how to access it via Enterprise Manager Cloud Control, and detailed walkthroughs of its various panels, metrics, and practical examples for diagnosing and optimizing long‑running queries and index builds.
This guide explains how to transform ordinary, partitioned, or index‑organized Oracle tables to new structures online, using the DBMS_REDEFINITION package, while minimizing lock time and avoiding service interruption in 24‑7 environments.
This article consolidates Oracle ACS architect Zhou Guohong's comprehensive guide on selecting upgrade paths, outlining phase‑by‑phase tasks, highlighting key success factors, and answering common questions about migration tools such as Transportable Tablespaces, Data Pump, and GoldenGate.
This article details seven common Oracle Listener issues—including intermittent connections, process crashes, ORA-12514, SCAN registration failures, listener hangs, TNS-12535/00505 errors, and TNS-12547 lost-contact—explaining symptoms, root causes, and step-by-step remediation commands for DBA teams.
This article provides a comprehensive walkthrough of Oracle's XTTS technology, covering its evolution, traditional and incremental‑backup workflows, version constraints, detailed configuration steps, real‑world migration timing, common pitfalls, remediation tactics, and a concise Q&A for practitioners.
This article explains how to activate Oracle 12c's In‑Memory feature at the column level by first enabling it at the table level, demonstrates verification with V$IM_COLUMN_LEVEL, shows ALTER TABLE commands, and compares execution plans to illustrate performance benefits.
This article explains Oracle ASM metadata concepts, including ASM file types, allocation units, physical and virtual metadata structures, disk header composition, and practical methods for querying, validating, backing up, and restoring ASM metadata using tools like KFOD, KFED, and X$KFFXP.
This article examines Oracle's guidance on placing redo logs on SSDs, explains sector size and 4K redo log support, describes a controlled experiment comparing PCIe flash cards with SAS disks using TPCC workloads, and presents performance data showing reduced log‑file‑sync waits and higher throughput on flash storage.
This article explains why protecting database‑stored sensitive data is critical, analyzes common attack vectors and breach statistics, and provides detailed logical and physical security measures—including user and permission management, auditing, encryption, disaster‑recovery planning, and Oracle replication technologies—to help organizations achieve comprehensive database protection.
The article details how Alibaba tackled explosive data growth by migrating from a single Oracle instance to a multi‑source architecture using horizontal and vertical sharding, MySQL clusters, KV stores, and proprietary tools such as Erosa, Otter, and Cobar to achieve high availability, consistency, and performance across multiple IDC sites.
The ITPUB SQL Database Programming Contest invites participants to solve two Oracle‑SQL puzzles—generating every possible tic‑tac‑toe end‑game board with moves and determining forced win strategies—following detailed rules, evaluation criteria, and prize information.
This article walks through a real Oracle OLTP query that took 28 seconds per execution, explains how execution‑plan analysis, index hints, and correcting stale statistics reduced the runtime to sub‑millisecond levels, and shows the final combined index that eliminated the performance issue.
This guide explains how to access and use Oracle SQL Monitor—a free feature in Oracle Enterprise Edition—to capture SQL execution details, analyze performance bottlenecks, compare estimated and actual rows, and apply targeted optimizations using both the EM console and command‑line scripts.
This article explains why repeated SQL execution can cause library cache: mutex X contention in Oracle, describes the underlying hash‑bucket and handle locking mechanisms, and demonstrates how creating hotcopy objects—using hidden parameters and APIs—can alleviate hotspot mutex waits, with experimental results showing up to 50% reduction in wait time.
This article explains the internal structures and roles of Oracle undo and redo logs in transaction processing, walks through practical experiments that dissect undo segment headers, undo blocks, undo chains, redo records, fast‑commit behavior, and the true nature of DELETE operations, and shows how these insights help diagnose ORA‑600 startup failures.
This comprehensive guide explains the essence of Oracle SQL tuning, presents a step‑by‑step optimization roadmap, walks through real‑world case studies—including a high‑CPU query fixed with hints and query rewrites—and details how to capture and interpret accurate execution plans while addressing optimizer parameters and common pitfalls.
This article provides a comprehensive, illustrated walkthrough of building Oracle Database‑as‑a‑Service using OEM12c, covering component selection, installation, architecture, configuration screens, resource‑allocation policies, custom login branding, and practical tips for deploying and managing cloud databases.
The article explains how Copy Data Virtualization technology, exemplified by Actifio, consolidates Oracle and VMware data backup, disaster recovery, and development testing into a single platform, reducing production load, storage costs, and recovery time while providing incremental, always‑on data copies.
In this detailed DBA case study, the speaker explains how improper settings of Oracle's parallel execution parameters on an AIX‑based 10gR2 database led to process saturation, undo‑related wait events, and temporary connection issues, and describes the step‑by‑step diagnosis, parameter adjustments, and lessons learned for future tuning.
This article explains how Oracle wait events, especially latch contention such as cache buffers chains and LRU chain latches, reveal performance bottlenecks, and provides step‑by‑step methods, diagnostic tools, SQL examples, and tuning recommendations to locate and resolve database hotspots.
A community discussion weighs the benefits and drawbacks of upgrading Oracle 10gR2 to 11g, sharing real‑world experiences, technical pitfalls like ORA‑24247, alternative viewpoints, and guidance for DBAs deciding whether the upgrade is worth the effort.
This article presents a detailed case study of an Oracle RAC system performance bottleneck, walks through comprehensive monitoring, storage and partitioning analysis, and outlines a step‑by‑step optimization plan—including KEEP pool tuning and partition redesign—that dramatically improved I/O latency and overall throughput.
This article explains how Oracle's Cost‑Based Optimizer estimates the selectivity of range predicates without histograms, demonstrates calculations with sample data, shows PL/SQL code for converting string values to internal numbers, and discusses pitfalls such as identical internal values causing cardinality errors, offering solutions like gathering histograms or using DATE columns.
An Oracle RAC 11g installation on AIX failed intermittent queries with ORA‑04030 errors, and a deep dive revealed that missing shell limits for the grid user capped process memory at 128 MB, causing PGA allocation failures despite ample system resources.
Expert DBAs discuss critical Oracle 11.2.0.4 parameters to watch before production, covering generic, performance, deprecated, and OS‑level settings for AIX and Linux, with concrete examples such as shared pool sub‑pools, mutex tuning, cursor thresholds, and real‑world incidents like streams_pool_size misconfiguration.
This article details how to use PXE to automatically provision three‑node Oracle 11gR2 RAC clusters on bare‑metal servers, covering network planning, PXE server setup with a one‑click installer, required software download, MAC address collection, shared‑disk and node configuration, virtual tape library setup, time synchronization, and user creation.
This article presents a detailed case study of diagnosing Oracle DBtime spikes using Zabbix, custom scripts, AWR and ASH analysis, revealing how scheduler jobs, materialized view refreshes, parallel execution, and unusable indexes caused performance jitter, and demonstrates step‑by‑step remediation to stabilize DBtime.
DD Boost integrates tightly with Oracle RMAN to provide a flexible, policy‑driven backup solution that allows DBAs to manage local and disaster‑recovery sites independently, simplifies deployment via a simple plugin, and dramatically improves performance by sending only unique data blocks to Data Domain for deduplication.
During a lively Q&A session organized by the ‘Efficient Operations’ community, Oracle InnoDB principal developer Lai Zheng and Microsoft SQL Server product manager Lin Mo discuss SQL Server’s operational advantages over other databases, Microsoft’s product roadmap, common maintenance challenges, and the ideal career path for a modern DBA.
In a lively Q&A session, Oracle experts discuss how China’s “post‑IOE” shift, cloud migration, big‑data collaboration with Hadoop, and the strengths of In‑Memory, TimesTen, and Exadata shape the future direction of Oracle databases.
This article examines the technical, managerial, and cost challenges telecom operators face when migrating from Oracle‑based IOE architectures to open‑source databases, offering practical insights, case studies, and recommendations for a successful transition.
Oracle stopped publishing public security patches and updates for Java 7 in April 2015, urging users to migrate to Java 8 or obtain long‑term commercial support, which has caused compatibility challenges for tools still relying on deprecated Java 7 features and prompted consideration of alternative JVMs.
This guide walks through essential Oracle database restore steps, from checking the current directory and copying dump files to restarting the service, managing users, creating directories, and importing data with impdp commands.
This article presents a comprehensive side‑by‑side mapping of 45 common SQL Server functions to their Oracle equivalents, covering numeric, string, date, and miscellaneous operations with example queries and notes on usage differences.
