This article walks through three common Oracle OCP exam questions—distinguishing permanent, temporary, and UNDO tablespaces, choosing the correct object versus system privilege syntax, and selecting the safest RMAN command for a hot backup—providing detailed explanations and the correct answers.
This article explains the main directory layout of MySQL 8, the locations of data files, configuration files, system databases, and how InnoDB and MyISAM storage engines represent tables and indexes on the file system, including the use of system and file‑per‑table tablespaces.
This article examines a MySQL case where importing a 4 GB tablespace takes 13 minutes, explains why SHOW PROFILE reports most of the time in the System lock stage, and dives into the internal implementation of SHOW PROFILE, data collection, calculation, and the tablespace import code path.
Upgrading to MySQL 8.0.38 (or later) causes crashes when an instance contains more than ten thousand tables, even with validate_tablespace_paths disabled, but using a shared or general tablespace configuration can reliably avoid the failure.
This article explains how DBA engineers can speed up MySQL data recovery using Xtrabackup by covering three scenarios—full backup with binlog, single-database restore, and single-table restore—detailing steps such as SQL thread replay, parallel replication, and transportable tablespace techniques with practical command examples.
This article explains how InnoDB stores data on disk, describing tablespaces, segments, extents, pages, row formats, record headers, page directories, B+‑tree indexes, and the differences between clustered and secondary indexes, with examples and diagrams to illustrate each component.
This article explains the different types of InnoDB tablespaces, why the ibdata1 file can grow uncontrollably, and provides a step‑by‑step procedure—including full backup, instance recreation, and configuration of UNDO and file‑per‑table settings—to safely reduce its size in MySQL environments.
This article explains the MySQL InnoDB undo log—its definition, role in transaction rollback and MVCC, when it is created, where and how it is stored across different MySQL versions, the internal tablespace and rollback‑segment structures, log types, and real‑world examples of its usage.
This article details the background, error analysis, and step‑by‑step solutions—including logical dump/restore and tablespace transfer—for fixing a MySQL 5.6 to 8.0 in‑place upgrade failure caused by missing #sql‑ib orphan files and data‑dictionary mismatches in a large Zabbix deployment.
This guide walks through restoring a mistakenly updated Oracle 11.2.0.1 table to a specific point in time using RMAN tablespace recovery on a new host, handling Windows-specific issues, and finally retrieving the data via a database link, with full scripts and step‑by‑step instructions.
This article demonstrates how to simulate accidental deletion of a MySQL InnoDB .ibd file, observe the impact on queries, recover the file using the file descriptor in /proc, restart MySQL, and verify that various DDL operations can detect the missing tablespace.
This article explains the three types of MySQL InnoDB tablespaces—system, single, and general—detailing their structures, configuration options, advantages, disadvantages, and practical procedures for creation, migration, optimization, and removal, with concrete command‑line examples.
This comprehensive guide walks you through essential Oracle database administration tasks—including starting and stopping the database, managing listeners, creating and altering tablespaces, handling users and privileges, controlling transactions, building indexes, defining views and materialized views, working with sequences, synonyms, and partitioned tables—complete with commands, examples, and best‑practice tips.
This article explains how InnoDB stores user tables and indexes in .ibd files, describes the structure of tablespaces, pages, extents, header pages, XDES entries, INODE pages, and file segments, and shows how indexes allocate and free these internal structures as they grow and shrink.
This article explains MySQL’s fundamental concepts, distinguishing databases from instances, outlines the three‑layer MySQL architecture, and dives deep into InnoDB’s storage hierarchy—from tablespaces, pages, and row formats to .frm/.ibd files, record overflow handling, and the B+‑tree index structures that power fast queries.
This article provides a comprehensive overview of InnoDB's file structure, covering physical organization from pages to tablespaces, logical structure including segments and indexes, and file management processes.
The article walks through two common Oracle performance problems—slow tablespace‑information queries and sluggish ASH data collection—explaining root causes, showing diagnostic steps, and providing exact SQL and PL/SQL scripts to gather statistics, clean up partitions, and restore fast query response.
This article explains how Oracle's logical structures—blocks, segments, tablespaces, and rowids—affect SQL performance, presents detailed experiments on block capacity, row migration, segment handling, tablespace behavior, and provides practical optimization guidelines based on observed logical read reductions.
This guide provides a comprehensive collection of Oracle SQL commands covering connection, query execution, variable handling, formatting, tablespace creation, modification, and external table operations, presented step‑by‑step for database administrators and developers.
This article explains the structure and components of MySQL 5.7 InnoDB tablespaces—including system, undo, temporary, and general tablespaces—covers double‑write buffering, redo logs, encryption with keyring, and provides practical configuration examples and command‑line demonstrations.
This guide explains Oracle datafile concepts, how to query absolute and relative file numbers, create and add files to tablespaces, modify file size with autoextend or resize, change file availability, rename or move files, and safely delete files while observing key restrictions.
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 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.
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.
This article explains how granting and revoking Oracle roles, especially the DBA role, affect permission activation timing, the immediate loss of UNLIMITED TABLESPACE rights, and practical steps to restore necessary tablespace access for application users.
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.
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 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.
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.
