Tagged articles
tablespace
31 articles · Page 1 of 1
This article walks through MySQL InnoDB's five‑level storage hierarchy—record, page, extent, segment, tablespace—explaining page internals, record chaining, page directories, headers, trailers, extent and segment management, and the system tablespace and data dictionary across MySQL 5.7 and 8.0.
InnoDBMySQLdata page
0 likes · 24 min read

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.
OCPOraclePrivileges
0 likes · 6 min read

This article provides detailed explanations of ten core MySQL topics—including InnoDB lock upgrades, redo log mechanics, MVCC version chains, tablespace types, page splitting, optimizer plan selection, crash recovery, binlog vs redo log, adaptive hash index, and memory management—along with practical tips for avoiding pitfalls and tuning performance.
Adaptive Hash IndexCrash RecoveryInnoDB
0 likes · 22 min read

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.
ConfigurationDatabase DirectoryInnoDB
0 likes · 13 min read

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.
MySQLSHOW PROFILEdatabase
0 likes · 14 min read


ITPUBJul 16, 2024 · Databases
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.
InnoDBMySQLcrash
0 likes · 4 min read

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.
MySQLRecoveryReplication
0 likes · 21 min read

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.
B+TreeIndexInnoDB
0 likes · 26 min read

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.
InnoDBMySQLibdata1
0 likes · 13 min read

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.
Database InternalsInnoDBMVCC
0 likes · 15 min read

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.
data dictionaryin-placetablespace
0 likes · 10 min read


ITPUBJul 15, 2021 · Databases
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.
DBLINKOracleRMAN
0 likes · 14 min read

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.
InnoDBMySQLdatabase troubleshooting
0 likes · 12 min read

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.
Database StorageInnoDBMySQL
0 likes · 13 min read


ITPUBApr 25, 2020 · Databases
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.
AdministrationOracleSQL
0 likes · 21 min read

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.
Database InternalsInnoDBMySQL
0 likes · 10 min read

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.
Database ArchitectureInnoDBMySQL
0 likes · 11 min read

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.
B+TreeDatabase ArchitectureFile Structure
0 likes · 22 min read

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.
ASHOraclePerformance Tuning
0 likes · 11 min read

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.
Block SizeLogical StructureOracle
0 likes · 11 min read


ITPUBOct 7, 2017 · Databases
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.
External TableOraclePartitioning
0 likes · 11 min read

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.
Database ConfigurationInnoDBMySQL
0 likes · 14 min read


ITPUBSep 19, 2017 · Databases
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.
DatafilesOracleSQL
0 likes · 13 min read


ITPUBOct 10, 2016 · Databases
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.
OracleShrink Spacedatabase administration
0 likes · 6 min read


ITPUBAug 16, 2016 · Databases
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.
OraclePerformance TuningSQL
0 likes · 18 min read


ITPUBJun 19, 2016 · Databases
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


ITPUBJun 3, 2016 · Databases
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.
DBADatabase SecurityGrant Revoke
0 likes · 4 min read


ITPUBMay 20, 2016 · Databases
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.
OraclePerformance TuningSGA
0 likes · 10 min read

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.
DBAOracledisk space
0 likes · 11 min read


ITPUBApr 25, 2016 · Databases
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.
DatafileOracleSQL
0 likes · 4 min read


ITPUBMar 11, 2016 · Databases
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.
OracleQueriesSQL
0 likes · 10 min read
