BestHub
Sign in
Tagged articles
597 articles
Page 5 of 6
dbaplus Community
dbaplus Community
Nov 30, 2016 · Databases

Why Updating a View Can Kill Oracle Performance—and How to Fix It

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.

OraclePredicate PushdownScalar Subquery
0 likes · 13 min read
Why Updating a View Can Kill Oracle Performance—and How to Fix It
dbaplus Community
dbaplus Community
Nov 27, 2016 · Databases

How to Trace Recursive SQL Calls in Oracle Using 10046 and Orasrp

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.

10046DBAOracle
0 likes · 8 min read
How to Trace Recursive SQL Calls in Oracle Using 10046 and Orasrp
dbaplus Community
dbaplus Community
Nov 15, 2016 · Databases

How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale

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.

Driver TableOracleWITH clause
0 likes · 12 min read
How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale
ITPUB
ITPUB
Nov 15, 2016 · Databases

Mastering Oracle Character Sets and NLS_LANG: A Complete Guide

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.

NLS_LANGOraclecharset conversion
0 likes · 16 min read
Mastering Oracle Character Sets and NLS_LANG: A Complete Guide
dbaplus Community
dbaplus Community
Nov 14, 2016 · Databases

Why Oracle Log File Sync Bottlenecks Appear and How to Eliminate Them

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.

Database PerformanceLog File SyncODM
0 likes · 18 min read
Why Oracle Log File Sync Bottlenecks Appear and How to Eliminate Them
ITPUB
ITPUB
Nov 14, 2016 · Databases

Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs

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.

Database PerformanceIndex ScanOracle
0 likes · 10 min read
Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs
ITPUB
ITPUB
Nov 13, 2016 · Databases

Why Oracle DBMS_JOB Shows Next Date 4000‑01‑01 and How to Fix It

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.

DBMS_JOBJob SchedulingOracle
0 likes · 5 min read
Why Oracle DBMS_JOB Shows Next Date 4000‑01‑01 and How to Fix It
ITPUB
ITPUB
Nov 11, 2016 · Databases

Essential Oracle SQL Queries for Performance Monitoring and Troubleshooting

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.

AdministrationOracleQueries
0 likes · 26 min read
Essential Oracle SQL Queries for Performance Monitoring and Troubleshooting
ITPUB
ITPUB
Nov 9, 2016 · Databases

Step‑by‑Step Guide to Downgrade Oracle Database 12c to Earlier Releases

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.

12cOracledatabase
0 likes · 13 min read
Step‑by‑Step Guide to Downgrade Oracle Database 12c to Earlier Releases
ITPUB
ITPUB
Nov 8, 2016 · Databases

40+ Essential Oracle SQL Queries Every DBA Should Know

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.

AdministrationOracleQueries
0 likes · 16 min read
40+ Essential Oracle SQL Queries Every DBA Should Know
ITPUB
ITPUB
Nov 7, 2016 · Databases

Understanding Oracle’s Top 30 Wait Events: Causes, Parameters, and Tuning Tips

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.

Database PerformanceOracleTuning
0 likes · 26 min read
Understanding Oracle’s Top 30 Wait Events: Causes, Parameters, and Tuning Tips
ITPUB
ITPUB
Nov 4, 2016 · Databases

Mastering Oracle Statistics Collection: When, How, and Optimization Tips

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.

DBADatabase OptimizationOracle
0 likes · 16 min read
Mastering Oracle Statistics Collection: When, How, and Optimization Tips
ITPUB
ITPUB
Nov 3, 2016 · Databases

Mastering Oracle Optimizer Statistics: When and How to Collect Them

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.

DBMS_STATSDynamic SamplingESTIMATE_PERCENT
0 likes · 20 min read
Mastering Oracle Optimizer Statistics: When and How to Collect Them
ITPUB
ITPUB
Nov 1, 2016 · Databases

How to Slash Redo Log Generation and Logical Reads in Oracle UPDATE Statements

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.

Logical ReadsOracleindexing
0 likes · 8 min read
How to Slash Redo Log Generation and Logical Reads in Oracle UPDATE Statements
dbaplus Community
dbaplus Community
Nov 1, 2016 · Databases

Understanding Oracle Wait Events: Types, Causes, and Optimization Strategies

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.

Database MonitoringI/O bottlenecksOracle
0 likes · 22 min read
Understanding Oracle Wait Events: Types, Causes, and Optimization Strategies
ITPUB
ITPUB
Oct 28, 2016 · Databases

Step‑by‑Step Oracle Data Guard Switchover and Failover Guide

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.

Data GuardOracleSwitchover
0 likes · 6 min read
Step‑by‑Step Oracle Data Guard Switchover and Failover Guide
ITPUB
ITPUB
Oct 27, 2016 · Databases

Mastering Oracle Permission Views: Query Roles, Privileges, and Sessions

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.

DBAOracledatabase
0 likes · 7 min read
Mastering Oracle Permission Views: Query Roles, Privileges, and Sessions
ITPUB
ITPUB
Oct 26, 2016 · Databases

Mastering Oracle Locks: Pessimistic, Optimistic, and Deadlock with Real‑World SQL demos

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.

Oracleoptimistic lockpessimistic-lock
0 likes · 10 min read
Mastering Oracle Locks: Pessimistic, Optimistic, and Deadlock with Real‑World SQL demos
dbaplus Community
dbaplus Community
Oct 24, 2016 · Databases

Why Oracle’s AUTO_SAMPLE_SIZE Misses Skewed Data and How to Fix It

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.

Auto Sample SizeDBMS_STATSDatabase Optimization
0 likes · 10 min read
Why Oracle’s AUTO_SAMPLE_SIZE Misses Skewed Data and How to Fix It
ITPUB
ITPUB
Oct 24, 2016 · Databases

Mastering Oracle Datafiles: Creation, Modification, and Management Techniques

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.

Database AdministrationDatafilesOracle
0 likes · 15 min read
Mastering Oracle Datafiles: Creation, Modification, and Management Techniques
ITPUB
ITPUB
Oct 19, 2016 · Databases

Recovering Lost Oracle Datafiles: SCN Adjustment and Open‑Database Tricks

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.

Backup FailureDatabase RecoveryDatafile Loss
0 likes · 12 min read
Recovering Lost Oracle Datafiles: SCN Adjustment and Open‑Database Tricks
dbaplus Community
dbaplus Community
Oct 18, 2016 · Databases

Mastering Oracle AWR: How to Decode and Optimize Database Performance

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.

AWRDatabase PerformanceOracle
0 likes · 32 min read
Mastering Oracle AWR: How to Decode and Optimize Database Performance
ITPUB
ITPUB
Oct 17, 2016 · Databases

Quickly Diagnose Oracle Lock Contention with AWK and ass109.awk

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.

Database LocksOracleTrace Analysis
0 likes · 9 min read
Quickly Diagnose Oracle Lock Contention with AWK and ass109.awk
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Oct 13, 2016 · Databases

How to Scale Order Systems with Horizontal Database Sharding: A Real‑World Case Study

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.

Data MigrationOracledatabase scaling
0 likes · 14 min read
How to Scale Order Systems with Horizontal Database Sharding: A Real‑World Case Study
ITPUB
ITPUB
Oct 12, 2016 · Databases

34 Proven Oracle SQL Tricks to Boost Query Performance

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.

Oracledatabaseindexes
0 likes · 19 min read
34 Proven Oracle SQL Tricks to Boost Query Performance
dbaplus Community
dbaplus Community
Oct 11, 2016 · Databases

Mastering Oracle Hints: Benefits, Risks, and Best‑Practice Guidance

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.

Database OptimizationOracleperformance tuning
0 likes · 14 min read
Mastering Oracle Hints: Benefits, Risks, and Best‑Practice Guidance
ITPUB
ITPUB
Oct 11, 2016 · Databases

How to Secure Oracle RMAN Backups with Password, Wallet, and Hybrid Encryption

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.

BackupOracleRMAN
0 likes · 9 min read
How to Secure Oracle RMAN Backups with Password, Wallet, and Hybrid Encryption
ITPUB
ITPUB
Oct 10, 2016 · Databases

How to Reclaim Oracle High Water Mark Space and Boost Query Performance

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.

Database AdministrationOracleShrink Space
0 likes · 6 min read
How to Reclaim Oracle High Water Mark Space and Boost Query Performance
dbaplus Community
dbaplus Community
Oct 9, 2016 · Databases

When Custom Function Indexes Fail: Pitfalls and Best Practices in Oracle

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.

DatabaseOptimizationFunctionBasedIndexOracle
0 likes · 7 min read
When Custom Function Indexes Fail: Pitfalls and Best Practices in Oracle
ITPUB
ITPUB
Oct 5, 2016 · Databases

How to Backup and Restore Oracle PL/SQL User Objects Using Export/Import Tools

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.

BackupExportImport
0 likes · 5 min read
How to Backup and Restore Oracle PL/SQL User Objects Using Export/Import Tools
ITPUB
ITPUB
Sep 30, 2016 · Databases

Master Oracle Memory Tuning: Key SGA & PGA Parameters Explained

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.

Database PerformanceMemory TuningOracle
0 likes · 14 min read
Master Oracle Memory Tuning: Key SGA & PGA Parameters Explained
ITPUB
ITPUB
Sep 28, 2016 · Databases

How to Diagnose and Repair Bad Blocks in Oracle Databases

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.

Bad BlocksDBMS_REPAIROracle
0 likes · 10 min read
How to Diagnose and Repair Bad Blocks in Oracle Databases
ITPUB
ITPUB
Sep 27, 2016 · Databases

Mastering Oracle Statistics Collection: When, How, and What to Gather

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.

DBMS_STATSHistogramsOracle
0 likes · 10 min read
Mastering Oracle Statistics Collection: When, How, and What to Gather
dbaplus Community
dbaplus Community
Sep 26, 2016 · Databases

Oracle 12c Memory Innovations: Smart Flash Cache, PGA Limits & In‑Memory Column Store

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.

12cDatabase PerformanceIn-Memory
0 likes · 13 min read
Oracle 12c Memory Innovations: Smart Flash Cache, PGA Limits & In‑Memory Column Store
ITPUB
ITPUB
Sep 26, 2016 · Databases

How to Manually Delete an Oracle Database and Clean Up ASM Files

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.

ASMOracleSQL*Plus
0 likes · 6 min read
How to Manually Delete an Oracle Database and Clean Up ASM Files
ITPUB
ITPUB
Sep 22, 2016 · Databases

How to Resolve Oracle EM Errors by Properly Configuring Dynamic Listeners

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.

Database AdministrationDynamic RegistrationEnterprise Manager
0 likes · 9 min read
How to Resolve Oracle EM Errors by Properly Configuring Dynamic Listeners
ITPUB
ITPUB
Sep 21, 2016 · Databases

Why the Same Chinese Text Becomes Garbled in Different Oracle Sessions – A Character Set Deep Dive

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.

Character SetOracleencoding
0 likes · 13 min read
Why the Same Chinese Text Becomes Garbled in Different Oracle Sessions – A Character Set Deep Dive
ITPUB
ITPUB
Sep 19, 2016 · Databases

How Oracle Data Guard’s Maximum Protection Mode Handles Failures and Recovery

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.

Data GuardDatabase ReplicationMaximum Protection
0 likes · 12 min read
How Oracle Data Guard’s Maximum Protection Mode Handles Failures and Recovery
dbaplus Community
dbaplus Community
Sep 18, 2016 · Databases

Master Oracle Hierarchical Queries: From Basics to Advanced Techniques

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.

CONNECT BYHierarchical QueryOracle
0 likes · 25 min read
Master Oracle Hierarchical Queries: From Basics to Advanced Techniques
dbaplus Community
dbaplus Community
Sep 13, 2016 · Databases

How to Speed Up Zabbix Latest Data Display by Adding Dedicated Tables and Indexes

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.

Database OptimizationOracleZabbix
0 likes · 11 min read
How to Speed Up Zabbix Latest Data Display by Adding Dedicated Tables and Indexes
ITPUB
ITPUB
Sep 13, 2016 · Backend Development

Auto‑Detect Useful Column Groups in Oracle 12c to Improve Query Plans

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.

DBMS_STATSExtended StatsOracle
0 likes · 8 min read
Auto‑Detect Useful Column Groups in Oracle 12c to Improve Query Plans
ITPUB
ITPUB
Sep 12, 2016 · Databases

Why Oracle’s Optimizer Misestimates Multi‑Column Filters and How to Fix It

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.

Multi-Column StatisticsOracleQuery Optimizer
0 likes · 8 min read
Why Oracle’s Optimizer Misestimates Multi‑Column Filters and How to Fix It
dbaplus Community
dbaplus Community
Sep 7, 2016 · Databases

Why Oracle MERGE Triggers ORA-00600 Errors and How to Diagnose Them

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.

Database BugORA-00600Oracle
0 likes · 12 min read
Why Oracle MERGE Triggers ORA-00600 Errors and How to Diagnose Them
ITPUB
ITPUB
Sep 7, 2016 · Databases

Fix ORA-38706/ORA-38707 Errors and Enable Oracle Flashback Database

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.

Archive ModeDatabase RecoveryFlashback Database
0 likes · 5 min read
Fix ORA-38706/ORA-38707 Errors and Enable Oracle Flashback Database
ITPUB
ITPUB
Sep 5, 2016 · Databases

Understanding Oracle Enqueue Locks, Resource Structures, and Hash Tables

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.

Database InternalsEnqueueLocks
0 likes · 11 min read
Understanding Oracle Enqueue Locks, Resource Structures, and Hash Tables
ITPUB
ITPUB
Sep 2, 2016 · Databases

How to Switch Oracle 11g Between ASMM and AMM – Step-by-Step

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.

AMMASMMDatabase Administration
0 likes · 8 min read
How to Switch Oracle 11g Between ASMM and AMM – Step-by-Step
ITPUB
ITPUB
Sep 1, 2016 · Databases

Why Oracle VKTM Time Drift Happens and How to Resolve It

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.

Alert LogOracleTime Drift
0 likes · 8 min read
Why Oracle VKTM Time Drift Happens and How to Resolve It
ITPUB
ITPUB
Aug 30, 2016 · Databases

Recover Mistaken Oracle Data Deletions with Backup, LogMiner, and Flashback

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.

Database AdministrationFlashbackLogMiner
0 likes · 14 min read
Recover Mistaken Oracle Data Deletions with Backup, LogMiner, and Flashback
ITPUB
ITPUB
Aug 25, 2016 · Databases

Unlock Historical Table Snapshots with Oracle Flashback Data Archive

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.

Database AdministrationFlashback Data ArchiveOracle
0 likes · 9 min read
Unlock Historical Table Snapshots with Oracle Flashback Data Archive
ITPUB
ITPUB
Aug 23, 2016 · Databases

Uncover Oracle’s Evolving SQL Commands: From Version 6 to 12c

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.

DDLDMLOracle
0 likes · 13 min read
Uncover Oracle’s Evolving SQL Commands: From Version 6 to 12c
ITPUB
ITPUB
Aug 22, 2016 · Databases

How I Passed the Oracle OCP 051 Exam: A DBA’s Journey and Practical Tips

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.

BackupRestoreDBAExamPreparation
0 likes · 12 min read
How I Passed the Oracle OCP 051 Exam: A DBA’s Journey and Practical Tips
ITPUB
ITPUB
Aug 18, 2016 · Databases

Cut Oracle Data Extraction from 20 Hours to 1 Hour: Proven DBA Tuning Tricks

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.

Data WarehousingOracleSQL Optimization
0 likes · 14 min read
Cut Oracle Data Extraction from 20 Hours to 1 Hour: Proven DBA Tuning Tricks
ITPUB
ITPUB
Aug 17, 2016 · Databases

Decoding Oracle Enqueue Locks: Queues, Resource Structures, and Hash Buckets

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.

Database InternalsEnqueue LocksOracle
0 likes · 13 min read
Decoding Oracle Enqueue Locks: Queues, Resource Structures, and Hash Buckets
ITPUB
ITPUB
Aug 17, 2016 · Databases

How to Disable and Re‑Enable Oracle 11g Automatic Statistics Collection

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.

DBMS_AUTO_TASK_ADMINDatabase AdministrationOracle
0 likes · 5 min read
How to Disable and Re‑Enable Oracle 11g Automatic Statistics Collection
ITPUB
ITPUB
Aug 16, 2016 · Databases

Mastering Oracle UNDO Tablespace: Monitoring, Tuning, and Space Reclamation

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.

Database AdministrationOracleTablespace
0 likes · 18 min read
Mastering Oracle UNDO Tablespace: Monitoring, Tuning, and Space Reclamation
ITPUB
ITPUB
Aug 12, 2016 · Databases

Unlock Oracle Cursor Insights: Master V$SQL, V$SQLAREA, and Tuning Parameters

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.

CursorDatabase PerformanceOracle
0 likes · 13 min read
Unlock Oracle Cursor Insights: Master V$SQL, V$SQLAREA, and Tuning Parameters
ITPUB
ITPUB
Aug 11, 2016 · Databases

How Oracle’s Incremental Checkpoints Reduce I/O Overhead and Speed Recovery

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.

CheckpointOracleRecovery
0 likes · 14 min read
How Oracle’s Incremental Checkpoints Reduce I/O Overhead and Speed Recovery
dbaplus Community
dbaplus Community
Aug 10, 2016 · Databases

How Oracle 12c Solves Batch‑Load Statistics Problems with Online Gathering

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.

12cBatch ProcessingDatabase Optimization
0 likes · 9 min read
How Oracle 12c Solves Batch‑Load Statistics Problems with Online Gathering
ITPUB
ITPUB
Aug 10, 2016 · Databases

Step‑by‑Step Manual Setup of Oracle Data Guard on RH Linux

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.

Active Data GuardData GuardDatabase Replication
0 likes · 20 min read
Step‑by‑Step Manual Setup of Oracle Data Guard on RH Linux
ITPUB
ITPUB
Aug 3, 2016 · Databases

How to View and Change Oracle Server and Client Character Sets Safely

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.

Character SetLinuxNLS_LANG
0 likes · 11 min read
How to View and Change Oracle Server and Client Character Sets Safely
ITPUB
ITPUB
Jul 28, 2016 · Databases

Oracle SQL Tuning Essentials: Joins, Execution Plans, CBO vs RBO, Backup Tips

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.

CBODatabase BackupOracle
0 likes · 19 min read
Oracle SQL Tuning Essentials: Joins, Execution Plans, CBO vs RBO, Backup Tips
ITPUB
ITPUB
Jul 21, 2016 · Information Security

How to Harden Oracle Database Security: Practical Hardening Steps

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.

DataGuardDatabase SecurityHardening
0 likes · 19 min read
How to Harden Oracle Database Security: Practical Hardening Steps
ITPUB
ITPUB
Jul 18, 2016 · Databases

How Reference Partition Boosts Oracle Query Performance and Simplifies Management

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.

Database ManagementOracleReference Partition
0 likes · 10 min read
How Reference Partition Boosts Oracle Query Performance and Simplifies Management
dbaplus Community
dbaplus Community
Jul 14, 2016 · Databases

Mastering Oracle Data Guard: Design, Tips, and Common Pitfalls

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.

DG BrokerData GuardOracle
0 likes · 20 min read
Mastering Oracle Data Guard: Design, Tips, and Common Pitfalls
ITPUB
ITPUB
Jul 14, 2016 · Databases

How to Resolve Common Oracle RAC Installation Errors: A Step‑by‑Step Guide

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.

InstallationLinuxOracle
0 likes · 9 min read
How to Resolve Common Oracle RAC Installation Errors: A Step‑by‑Step Guide
ITPUB
ITPUB
Jul 8, 2016 · Databases

Boost Oracle Insert Speed by Moving Hourly Indexes to Memory and Fixing Offline Datafile Issues

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.

OracleTablespacesindexes
0 likes · 10 min read
Boost Oracle Insert Speed by Moving Hourly Indexes to Memory and Fixing Offline Datafile Issues
ITPUB
ITPUB
Jul 7, 2016 · Databases

Ace the Oracle OCM Exam: Insider Tips, Environment Setup, and Common Pitfalls

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.

Data GuardExam PreparationOCM
0 likes · 11 min read
Ace the Oracle OCM Exam: Insider Tips, Environment Setup, and Common Pitfalls
ITPUB
ITPUB
Jul 5, 2016 · Databases

How to Overcome 18 Common Pitfalls When Installing Oracle Enterprise Manager Grid Control 11g

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.

Enterprise ManagerGrid ControlInstallation
0 likes · 18 min read
How to Overcome 18 Common Pitfalls When Installing Oracle Enterprise Manager Grid Control 11g
ITPUB
ITPUB
Jun 30, 2016 · Databases

Safely Shut Down and Restart an Oracle RAC Cluster Without Service Disruption

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.

ClusterOracleRAC
0 likes · 3 min read
Safely Shut Down and Restart an Oracle RAC Cluster Without Service Disruption
ITPUB
ITPUB
Jun 29, 2016 · Databases

Why Oracle 11g Uses Direct Path Reads for Full Table Scans and How to Tune Them

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.

10g11gDirect Path Read
0 likes · 4 min read
Why Oracle 11g Uses Direct Path Reads for Full Table Scans and How to Tune Them
ITPUB
ITPUB
Jun 28, 2016 · Databases

Fix Chinese Characters in Oracle Sqlplus on Linux Without Reboot

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.

Character SetDatabase ConfigurationLinux
0 likes · 4 min read
Fix Chinese Characters in Oracle Sqlplus on Linux Without Reboot
ITPUB
ITPUB
Jun 27, 2016 · Databases

How to Quickly Recover a Corrupted Oracle 11g Database on Windows

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.

Database RecoveryOracleOracle11g
0 likes · 5 min read
How to Quickly Recover a Corrupted Oracle 11g Database on Windows
ITPUB
ITPUB
Jun 24, 2016 · Databases

How Oracle Uses Undo to Provide Consistent Reads and Recover Transactions

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.

Consistent ReadDatabase RecoveryOracle
0 likes · 10 min read
How Oracle Uses Undo to Provide Consistent Reads and Recover Transactions
ITPUB
ITPUB
Jun 23, 2016 · Databases

How to Uncover Oracle Hidden Parameters with Views and Trace

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.

Database InternalsHidden ParametersOracle
0 likes · 5 min read
How to Uncover Oracle Hidden Parameters with Views and Trace
ITPUB
ITPUB
Jun 21, 2016 · Databases

Speed Up PL/SQL Developer on Oracle 12c by Disabling Dynamic Sampling

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.

Dynamic SamplingOraclePL/SQL Developer
0 likes · 3 min read
Speed Up PL/SQL Developer on Oracle 12c by Disabling Dynamic Sampling
ITPUB
ITPUB
Jun 19, 2016 · Databases

Why Oracle User Can Create Tables in Unauthorized Tablespaces

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.

Database_SecurityDeferred_Segment_CreationOracle
0 likes · 8 min read
Why Oracle User Can Create Tables in Unauthorized Tablespaces
ITPUB
ITPUB
Jun 17, 2016 · Databases

Why Do Oracle Buffer Busy Waits Occur and How to Resolve Them?

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.

Database TuningOraclebuffer busy waits
0 likes · 10 min read
Why Do Oracle Buffer Busy Waits Occur and How to Resolve Them?
ITPUB
ITPUB
Jun 16, 2016 · Databases

Forcefully Kill Oracle Processes and Release Shared Memory on Linux

This guide explains two powerful Linux commands that instantly terminate all Oracle-related processes and clean up shared memory, providing a quick recovery method for stuck Oracle databases while warning of the risks involved.

Database AdministrationLinuxOracle
0 likes · 5 min read
Forcefully Kill Oracle Processes and Release Shared Memory on Linux
ITPUB
ITPUB
Jun 13, 2016 · Databases

How to Connect to Oracle When Your Password Contains an @ Symbol

When an Oracle user password includes the '@' character, standard sqlplus login fails with a connection string parsing error; escaping the '@' by using backslashes or enclosing the password in double quotes restores successful connection.

Database ConnectionEscape CharacterOracle
0 likes · 3 min read
How to Connect to Oracle When Your Password Contains an @ Symbol
ITPUB
ITPUB
Jun 9, 2016 · Databases

Fix Chinese Garbled Text in Oracle SQL*Plus on Windows and Linux

This guide explains why Chinese characters appear garbled in PL/SQL and SQL*Plus on Windows and Linux, shows how to verify the Oracle server character set, configure the NLS_LANG environment variable, adjust terminal encoding, and ensure client‑server encoding consistency.

LinuxOraclePL/SQL
0 likes · 4 min read
Fix Chinese Garbled Text in Oracle SQL*Plus on Windows and Linux
ITPUB
ITPUB
Jun 2, 2016 · Databases

Essential MySQL & Oracle Development Best Practices for Robust Databases

This guide presents practical MySQL and Oracle development recommendations, covering table design, indexing, and SQL optimization techniques such as using appropriate data types, avoiding redundant indexes, employing efficient query patterns, and leveraging specific Oracle hints to improve performance and maintainability.

Database designOracleSQL Optimization
0 likes · 8 min read
Essential MySQL & Oracle Development Best Practices for Robust Databases
ITPUB
ITPUB
Jun 2, 2016 · Databases

How to Efficiently Delete Massive Data Sets with a PL/SQL Procedure

This article explains a practical PL/SQL stored‑procedure technique for bulk‑deleting large tables by batching rows, using rowid partitioning, off‑peak execution, and short pauses to minimize impact on Oracle database performance.

OraclePL/SQLStored Procedure
0 likes · 3 min read
How to Efficiently Delete Massive Data Sets with a PL/SQL Procedure