This guide explains how to design and deploy a parallel execution framework for large Oracle stored procedures using DBMS_SCHEDULER, covering architecture principles, component details, development and user interfaces, database schema, view definitions, security grants, and remaining challenges such as RAC support.
This article explains the origins, structure, and practical uses of MySQL's sys schema—including its view hierarchy, comparisons with Oracle data dictionary, performance‑monitoring capabilities, and step‑by‑step examples for troubleshooting locks, analyzing schema statistics, and backing up or rebuilding the sys database.
This article recaps a DBAplus community session where a seasoned DBA shares common operational scenarios, diagnostic techniques, lock‑handling strategies, and step‑by‑step SQL optimization methods, while unveiling the DPM platform that automates inspection, data collection, and performance analysis for Oracle databases.
This article compiles the 2016 release notes and major new features of leading relational database systems—including Oracle 12c R2, MySQL 5.7, Percona, MariaDB, SQL Server 2016 SP1, PostgreSQL 9.6, DB2 LUW 11.1, and Greenplum—highlighting enhancements in multitenancy, sharding, security, performance, and cloud integration.
A detailed case study examines an Exadata X3‑2 deployment where CALIBRATE tests revealed intermittent disk performance drops linked to low temperatures on Seagate SAS drives, explains why moving disks between nodes can restore performance, and clarifies the normalcy of slight MB/s variations in slots 0 and 1.
This article explains Oracle 12c's In‑Memory Column Store feature, its compression levels, step‑by‑step testing results for full scans and joins, a real‑world production case from Zhejiang Mobile, and practical maintenance commands to keep the in‑memory area healthy.
This guide walks through Oracle's Database Configuration Assistant (DBCA), explaining silent creation commands, template types, seed vs. no‑seed databases, step‑by‑step script generation, and how DBCA ties into multitenant architecture and Data Guard features.
A detailed Oracle case study shows how a seemingly harmless view modification triggered a cascade of performance regressions, how nested UNION ALLs, missing indexes, and optimizer choices prevented predicate pushdown, and the step‑by‑step rewrites—including hints, index creation, and scalar subqueries—that finally restored fast query execution.
This article demonstrates step‑by‑step how Oracle DBAs can identify the origin of recursive SQL statements by leveraging the 10046 trace at both SQL‑ID and session levels, analyzing the trace with the Orasrp tool, and ultimately pinpointing the PL/SQL function that generated the query.
After a typhoon delayed work, the author tackled a 347‑line Oracle SQL that took over four minutes to return results, dissected its massive subqueries and views, applied three rounds of restructuring with WITH clauses and driver‑table tactics, and ultimately reduced the total runtime to just 3.5 seconds while dramatically speeding up deep data extraction.
This article explains Oracle character sets, the NLS_LANG parameter format, the differences between database, national, and client character sets, how to query and modify them, and the impact on EXP/IMP data migration, providing practical commands and step‑by‑step procedures.
During high‑concurrency flash‑sale events, Oracle’s log file sync became a performance bottleneck; the article analyzes storage, OS, and Oracle Disk Manager factors, presents AWR metrics, demonstrates tuning steps—including disabling adaptive log file sync and enabling ODM—and shows measurable latency reductions.
This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.
When an Oracle DBMS_JOB’s NEXT_DATE jumps to the far‑future date 4000‑01‑01, it indicates the job has failed repeatedly and been marked broken; this article explains the underlying reasons, examines error logs, and provides step‑by‑step commands to diagnose and restart the job.
This guide compiles a comprehensive set of Oracle SQL statements and explanations for detecting fragmented tables, index fragmentation, high clustering factor tables, session and process mapping, DML lock analysis, DDL lock inspection, active SQL tracking, resource usage statistics, and various performance‑related metrics, helping DBAs diagnose and tune database behavior efficiently.
This guide provides a comprehensive checklist and scripted procedure for safely downgrading Oracle Database 12c (Standard or Enterprise Edition) to prior releases such as 11.2.0.3, 11.2.0.2, or 11.1.0.7, covering prerequisites, command‑line steps, post‑downgrade validation, and common pitfalls.
This article compiles over forty practical Oracle SQL queries covering date manipulation, server information, data dictionary lookups, database administration, performance monitoring, and utility functions, providing developers with ready-to-use commands to streamline everyday database tasks.
This guide walks through upgrading an Oracle MAA environment from 11.2.0.4 to 12.1.0.2 with no front‑end service interruption, covering environment overview, two Data Guard switchover cycles, transient logical standby conversion, rolling patch execution with physru.sh, and post‑upgrade cleanup steps.
This article enumerates Oracle's most common wait events, explains the underlying reasons each event occurs, lists the associated parameters, provides sample queries for inspection, and offers practical guidance on diagnosing and mitigating performance issues caused by these waits.
This guide explains when and why Oracle statistics are needed, how to control automatic and manual collection, prevent out‑of‑range errors, boost efficiency with parallel and incremental methods, and handle special cases like volatile or temporary tables for optimal query performance.
This article walks through semi‑automated Oracle Data Guard deployment, when and how to use DG Broker, practical failover, switchover and archive‑recovery scenarios, Oracle 12c enhancements like Far Sync and VALIDATE, a real‑world standby query failure case, and using Data Guard for database migration.
This guide explains the role of optimizer statistics in Oracle, when to gather them, how to use automatic and manual collection jobs, tune ESTIMATE_PERCENT and METHOD_OPT, manage histograms, handle bind peeking, apply dynamic sampling, and safely test changes with pending statistics.
This article compiles the most discussed DBA questions from multiple WeChat groups in November, covering DB2 query latency, Oracle import/export challenges, MySQL monitoring, SQL performance nuances, partition table design, and practical solutions shared by experts.
A detailed Oracle performance case study shows how adding selective predicates and a supporting index to an UPDATE that runs hourly can reduce redo log generation from over 100 MB to under 1 MB and cut logical reads by more than 95%, with step‑by‑step measurements and code examples.
This article explains the evolution of Oracle performance metrics, categorizes wait events into idle and non‑idle types, details common wait events with their parameters and causes, and provides practical guidance on using Oracle views to monitor and tune these events for better database performance.
This article provides a detailed, hands‑on walkthrough of Oracle Data Guard switchover in normal operation and the subsequent steps to convert the original primary to a standby, including all necessary SQL commands, instance restarts, and verification queries.
This guide explains the most commonly used Oracle permission‑view tables, shows how to list role assignments, object privileges, system privileges, session privileges, and role memberships, and demonstrates the impact of the WITH ADMIN OPTION through practical SQL examples.
This article explains Oracle deadlock fundamentals, classifies row‑level and block‑level (ITL) deadlocks, shows how to read trace files in both single‑instance and RAC environments, and provides step‑by‑step simulations that reveal root causes and practical mitigation techniques.
This guide explains Oracle's lock mechanisms—including shared, exclusive, row‑level, pessimistic, and optimistic locks—through step‑by‑step SQL sessions that illustrate update‑lost problems, lock contention, and deadlock detection, helping developers choose the right strategy for concurrency control.
The article explains how Oracle’s default DBMS_STATS.AUTO_SAMPLE_SIZE can produce wildly inaccurate cardinality estimates on highly skewed columns, examines the NewDensity and OldDensity algorithms, demonstrates the problem with real‑world examples, and offers practical solutions such as forcing estimate_percent or disabling NewDensity.
This guide explains Oracle datafile concepts, how to query file numbers, create and add files, adjust sizes and auto‑extend settings, toggle online/offline status, rename files, and safely drop them while observing limits and recovery procedures.
This article walks through a real Oracle 11gR2 datafile loss case, explains how backup‑software snapshots caused mismatched SCNs, and details step‑by‑step recovery methods—including offline drop, header SCN editing, and forcing the database open with SCN adjustment—to successfully restore the instance.
This comprehensive guide explains Oracle's Automatic Workload Repository (AWR), how snapshots are collected and retained, and provides step‑by‑step instructions for interpreting the most common AWR report sections, from cache sizes and load profiles to SQL statistics and RAC metrics, helping DBAs pinpoint bottlenecks and improve performance.
This guide explains the three essential character sets—operating system (LANG), Oracle database (NLS_CHARACTERSET), and client (NLS_LANG)—and provides step‑by‑step instructions with screenshots to resolve SQL*Plus display garbling issues.
This guide demonstrates how to use the ass109.awk script and Oracle's oradebug tool to capture, parse, and analyze trace files, quickly identifying lock and deadlock sessions in Oracle 11gR2 without resorting to costly restarts.
This article presents a comprehensive, practice‑driven analysis of horizontal database sharding for a high‑traffic e‑commerce order system, covering sharding dimensions, strategies, quantity planning, transparent routing, pagination challenges, lookup mapping, overall architecture, deployment steps, and the measurable performance and cost benefits achieved.
This guide compiles 34 practical Oracle SQL optimization techniques—from selecting the optimal table order and rewriting WHERE clauses to leveraging indexes, avoiding costly operators, and fine‑tuning execution plans—helping developers dramatically reduce query execution time and resource consumption.
This article explores Oracle's Hint feature, reviewing its classification, demonstrating how improper use can degrade performance, presenting real‑world negative cases, and summarizing Oracle's official stance and best‑practice recommendations for when and how to apply Hints effectively.
This guide explains why backup security is critical for enterprise databases, introduces Oracle RMAN's three encryption strategies, and provides step‑by‑step commands to configure password‑based encryption, perform backups, handle decryption errors, and finally disable encryption safely.
This guide explains the concept of Oracle High Water Mark (HWM), why it grows with DML operations, and provides a step‑by‑step procedure—including pre‑check, shrink space, and post‑check—to reclaim unused blocks, reduce tablespace usage, and improve scan efficiency.
This article explains how Oracle function‑based indexes work, why deterministic keywords are required, the risks of changing function definitions without rebuilding the index, and provides concrete examples illustrating common mistakes and their impact on query results.
This guide walks through exporting Oracle PL/SQL user objects and table data with PL/SQL Developer, then restoring them by dropping the existing user, recreating it with necessary privileges, executing the exported SQL script, and importing table data while handling constraints and triggers.
This guide walks through Oracle's memory architecture, explains SGA and PGA components, details essential memory parameters with recommended values, and provides step‑by‑step SQL checks to diagnose and tune performance on both 32‑bit and 64‑bit systems.
This guide explains what Oracle bad blocks are, how they affect the database, common causes, methods to detect them, and step‑by‑step recovery techniques—including full datafile restore, RMAN block recovery, ROWID range scans, EVENT 10231, and DBMS_REPAIR—plus pre‑emptive detection tools.
This guide explains how to verify and configure Oracle's automatic optimizer statistics collection, customize collection windows, tailor statistics for large and partitioned tables, detect stale statistics, and decide when to gather histograms, providing practical SQL and PL/SQL examples.
This article explains Oracle 12c's key memory‑related enhancements—including the extended database cache (Smart Flash Cache), PGA aggregate limit, In‑Memory column store with compression, SIMD processing, temperature‑based replacement, and big‑table cache—detailing their parameters, operation, and monitoring methods for improved database performance.
This guide provides step‑by‑step commands for safely stopping listeners, locating data, log and control files, dropping the database, and removing residual files both for file‑system and ASM Oracle databases.
This guide explains why Oracle Enterprise Manager fails when listeners lack dynamic registration, demonstrates step‑by‑step configuration of two listeners (listener and lsnr2) using the local_listener parameter, and shows how to verify the setup with lsnrctl commands.
The article walks through a hands‑on experiment that shows how inserting the same Chinese string into an Oracle 10g database from clients using different character sets (ZHS16GBK vs AL32UTF8) leads to different stored encodings and results in garbled output, then explains the underlying conversion process and how to avoid it.
This article demonstrates how to enable Oracle Data Guard’s Maximum Protection mode, examines the behavior of primary and standby databases when the standby connection is broken, and walks through the resulting errors, forced shutdown, and step‑by‑step recovery back to normal operation.
This article provides a comprehensive guide to Oracle hierarchical queries, covering fundamental concepts, key clauses and operators, practical examples with the s_emp table, advanced features like SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF, NOCYCLE, and real‑world use cases such as reporting, pruning branches, and calculating aggregates in tree structures.
This article explains Oracle Data Guard's three protection modes—Maximum Performance, Maximum Availability, and Maximum Protection—detailing their underlying redo‑log mechanisms, configuration steps, and test results that illustrate how each mode behaves under various failure scenarios.
The article dissects a moderately complex Oracle SQL query from a data‑warehouse, shows how changing a parallel hint forces a full table scan that halves execution time, and then explores partitioning strategies and broader lessons on OLAP performance tuning.
This article explains why Zabbix's original table design slows down data insertion and query in large‑scale deployments, and provides a step‑by‑step code‑level optimization that creates separate "latest" tables with primary‑key indexes, modifies the server source, and updates the front‑end query to dramatically improve performance without affecting historical data ingestion.
This guide shows how to use Oracle 12c's DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to automatically identify useful column groups, create extended statistics, and verify the impact on execution plans with concrete SQL examples.
This article explains how Oracle’s Cost‑Based Optimizer can produce incorrect row estimates for queries with multiple column predicates, demonstrates the problem with a test table, and shows that gathering multi‑column statistics resolves the misestimation, improving execution plans.
This article examines a recurring ORA-00600 crash caused by Oracle MERGE statements, walks through reproducing the issue, analyzes the impact of bound variables and table size, identifies a version‑specific bug, and offers practical work‑arounds and recommendations for DBAs.
This guide explains why ORA-38706 and ORA-38707 occur when Flashback Database is turned on, shows how to switch the Oracle database to ARCHIVE mode, and provides step‑by‑step SQL commands to enable Flashback and verify the configuration.
This article explains how Oracle protects shared resources with enqueue queues, details the naming of TM queue locks per table, describes the resource structure (KSQRS) that tracks owners, waiters and converters, and shows how hash tables and related parameters manage these structures efficiently.
This guide demonstrates how to switch Oracle Database 11g between Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM), covering environment setup, parameter adjustments, spfile/pfile handling, restart procedures, and troubleshooting, with concrete SQL commands and shared‑memory observations.
The article explains Oracle 11g's VKTM background process, how time drift warnings appear in the alert log, analyzes trace files to pinpoint the drift, discusses underlying causes such as OS load, and provides the patch and event‑setting steps needed to fix the bug.
This guide explains how to restore accidentally deleted or incorrectly updated Oracle database rows using logical backups with import, physical backup with incomplete recovery, LogMiner analysis of redo logs, and the Flashback Query/DBMS_FLASHBACK features, detailing each step, required settings, and practical tips.
This guide details how to move an Oracle 11.2.0.3.0 single‑instance database from Windows to a three‑node RAC on Linux using Data Guard, covering compatibility, required patches, configuration steps, parameter settings, and post‑migration tasks to ensure a fast, low‑downtime transition.
Oracle 11g’s Flashback Data Archive lets you retain UNDO information for extended periods, enabling point-in-time queries of tables; this guide walks through creating an ASSM tablespace, setting up a flashback archive, assigning tables, and using SCN-based queries to retrieve past data.
During performance testing, an Oracle SQL using bind variables showed two child cursors with identical execution plans; the article explains this phenomenon by detailing Oracle’s rolling cursor invalidation mechanism, the role of the ROLL_INVALID_MISMATCH flag, and how to reproduce and observe it.
This article walks through Oracle’s massive SQL reference, explains essential database concepts, details the original set of commands in version 6, and then chronicles the new DDL/DML features introduced in each subsequent release up to 12c, helping readers master Oracle’s syntax evolution.
This article recounts a DBA’s personal journey preparing for and passing the Oracle OCP 051 certification, detailing study resources, hands‑on experiments, common Oracle errors, exam strategies, and lessons learned for future database learning.
An experienced DBA shares a real-world case of optimizing a nightly Oracle reporting pipeline, detailing how partitioned tables, truncate operations, table compression, disabling logging, and parallel execution reduced a 20‑hour data extraction to under one hour, and compiles community‑sourced tuning tips for large‑scale data warehouses.
This article explains how Oracle protects shared resources using enqueue queues, details the naming of queue identifiers like TM, describes the creation and composition of resource structures in the SGA, and examines the hash‑bucket mechanism that locates these structures while highlighting relevant parameters and diagnostic queries.
This guide explains why Oracle 11g's automatic optimizer statistics collection can affect 24‑hour systems, shows how to check its status, and provides step‑by‑step commands to disable, re‑enable, and verify the task, including differences from Oracle 10g.
This guide explains how to monitor Oracle UNDO tablespace usage, adjust automatic UNDO management parameters, interpret V$UNDOSTAT statistics, and safely reclaim space by creating a new UNDO tablespace and dropping the old one, helping DBAs avoid ORA‑01555 errors and space shortages.
This article explains Oracle cursor concepts, details the key V$SQL and related views, highlights crucial columns for performance tuning, and describes important parameters such as open_cursor and cursor_sharing, helping DBAs optimize SQL execution and manage shared pool memory effectively.
This article explains Oracle’s checkpoint mechanism, contrasting full and incremental checkpoints, describing the checkpoint queue structure, the roles of DBWn and CKPT processes, recovery using redo logs, and how to tune checkpoint frequency with the fast_start_mttr_target parameter while monitoring relevant performance views.
This article explains the classic Oracle batch‑processing issue caused by inaccurate statistics on tables with sharply changing row counts, reviews traditional mitigation methods and their drawbacks, and introduces Oracle 12c's online statistics gathering for bulk‑load as an automated solution while noting its current limitations.
This guide walks through preparing the primary and standby Oracle 11g databases on RH Linux, configuring listeners, tnsnames, force logging, adding standby redo logs, editing parameter files, transferring files with RMAN, verifying roles, activating Active Data Guard, using snapshot standby, switching logs, and setting an archive‑log deletion policy.
An Oracle DBA recounts how a full archive log halted business, how manual deletion uncovered a backup script failure caused by a mismatched SYS password across RAC nodes, and the steps taken to fix the issue and prevent future overflow.
This guide explains why Oracle databases hang, how to connect when sqlplus fails, and step‑by‑step commands—including ORADEBUG systemstate and hanganalyze dumps, level meanings, and RAC-specific tips—to collect diagnostic data for effective problem analysis.
This guide explains how to check the character set of an Oracle database and its client, demonstrates queries for server, instance, and session parameters, describes the risks of altering the server charset with ALTER DATABASE CHARACTER SET, provides safe conversion steps, and shows how to adjust client settings on Linux and Windows to avoid garbled Chinese text.
This guide provides comprehensive Oracle database interview questions and answers covering SQL tuning techniques such as join types, execution‑plan inspection, CBO vs RBO, heavy‑SQL identification, session tracing, index structures, binding variables, plan stability, memory settings, tablespace management, backup and recovery methods including RMAN and standby, as well as system performance diagnostics.
This article recounts a seven‑year‑old Oracle 10g database migration, detailing project background, team turmoil, topology redesign, security constraints, data‑sync strategies, custom tools, high‑fidelity testing, unexpected failures, and the lessons learned for reliable operations.
This guide outlines comprehensive Oracle database security hardening measures, covering system‑level protections, disaster‑recovery configuration, account privilege tightening, data access controls, network restrictions, password policies, audit settings, and patch management, with concrete commands and configuration examples for each step.
This article examines Oracle's Reference Partition feature, demonstrating through a series of SQL experiments how it reduces query costs, improves execution plans, and streamlines partition management, while also highlighting its limitations when indexes are involved.
When an Exadata diskgroup fails to mount, this article walks through using AMDU to extract control, data, and log files, handling OMF and custom formats, advancing SCN, and finally opening the database, while also recommending preventive tools and regular maintenance.
This article walks through Oracle 11g's Reference Partition feature, demonstrating table creation, handling ORA‑14652 errors, inserting data, and analyzing partition alignment between master and detail tables to reveal performance and management benefits.
This article provides a comprehensive guide to Oracle Data Guard, covering its disaster‑recovery concepts, RTO/RPO standards, hardware‑to‑application level design considerations, standby design options, sensitive data‑file operations, switchover/failover advice, SQL auditing with Snapshot Standby, and practical tips for building or rebuilding a Data Guard environment.
This article walks through eight frequent Oracle RAC installation errors—ranging from shared‑disk configuration and s0 partition misuse to VIP interface issues and ASM disk reuse—providing clear command‑line fixes and configuration tips for Linux administrators.
The article describes a step‑by‑step experiment that moves hourly partitioned indexes into memory on Oracle 12c to boost insert performance, then details the unexpected issue of offline index datafiles causing rebuild failures, and explains how to work around the problem by adjusting tablespaces and handling temporary segments.
This guide shares a detailed recount of preparing for the Oracle OCM certification, covering exam locations, odd/even machine handling, required software installations, crash scenarios, step‑by‑step skillset tasks, and practical review tips to help candidates succeed.
This guide walks through the complete installation of Oracle Enterprise Manager Grid Control 11g on Red Hat 6.4, detailing the required software packages, configuration parameters, and step‑by‑step solutions to more than a dozen encountered errors—from missing 32‑bit libraries to hostname validation, file‑descriptor limits, and agent cleanup.
The article explains how a company is retiring Oracle due to cost and scalability, outlines a staged move to NoSQL and cloud storage, argues that SQL remains vital, and shares practical examples of Spark SQL rewrites and JDBC‑based Hive integration.
The article examines Oracle's gradual abandonment of Java EE, detailing how funding cuts, internal re‑assignments, and legal battles have stalled development, sparked community backlash, and raised concerns about the future of enterprise Java and its ecosystem.
This guide explains why using a forced OS shutdown on an Oracle RAC cluster can leave services in an UNKNOWN state and provides a step‑by‑step, gentle procedure using crs_stat and srvctl commands to check status, shut down databases, ASM instances, and node services in the correct order, then restart them safely.
The article explains how Oracle 11g may bypass the buffer cache with direct path reads during full table scans, lists common causes such as large sorts, hash joins, and parallel queries, and shows how hidden parameters like _serial_direct_read can be adjusted to improve performance.
This guide explains why Chinese characters appear as question marks in Sqlplus on Linux, how to identify the server character set, and provides step‑by‑step instructions to set the NLS_LANG environment variable and apply it instantly using the source command.
A step‑by‑step guide shows how to reinstall Oracle 11g, adjust environment variables, copy essential files, recreate Oracle services, and verify the database connection to restore a Windows‑based Oracle database that was damaged by antivirus deletions.
This article explains Oracle's undo mechanism, detailing how it enables consistent reads, supports transaction rollback, and facilitates instance recovery by tracking SCN numbers and chaining undo blocks to reconstruct past data states.
This article explains two practical techniques for identifying Oracle hidden parameters—querying internal views like GV$PARAMETER and X$ tables, and using AUTOTRACE to inspect execution plans—so DBAs can reveal undocumented settings, their defaults, and current values.
The article describes a slow PL/SQL Developer experience on an Oracle 12.1.0.2 test database, explains how missing dictionary and fixed object statistics cause delays, shows how to gather these stats, disable optimizer_dynamic_sampling, and restart the database to significantly improve query and autocomplete performance.
The article explains how a newly created Oracle user with the RESOURCE role inherits the UNLIMITED TABLESPACE privilege, allowing table creation in any tablespace, and how the 11gR2 deferred segment creation feature creates the illusion of permission, while providing methods to disable it globally or per‑session to enforce true tablespace restrictions.
The article explains the internal steps Oracle follows to acquire buffers, identifies the conditions that trigger buffer busy waits such as hot blocks and freelist limits, and provides practical tuning methods and diagnostic SQL queries to mitigate these performance issues.
