In modern database management, efficiency and optimization are pivotal, especially when dealing with large datasets in Oracle Database. This article provides a comprehensive guide on how to create, analyze, and manage a bigfile tablespace effectively, with particular emphasis on shrinking operations to reclaim unused space.
Setup: Creating a Tablespace and User
First, we need to establish a tablespace and a user for testing purposes. With Oracle Database 23c previously adopting bigfile as the default setting, we can create structures without explicitly specifying file sizes. Here’s how to do it:
-- Connect as sysdbaconn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba-- Drop existing user and tablespace if they existDROP USER reclaim_user CASCADE;DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES;-- Create a new bigfile tablespace of size 10 MBCREATE TABLESPACE reclaim_ts DATAFILE 'reclaim_ts.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;-- Create a new user associated with the new tablespaceCREATE USER reclaim_user IDENTIFIED BY reclaim_user DEFAULT TABLESPACE reclaim_ts QUOTA UNLIMITED ON reclaim_ts;-- Grant necessary permissionsGRANT CREATE SESSION, CREATE TABLE TO reclaim_user;GRANT SELECT_CATALOG_ROLE TO reclaim_user;
Populating the Tables
After successfully creating the necessary schema components, we can create two sample tables in our newly formed tablespace:
conn reclaim_user/reclaim_user@//localhost:1521/freepdb1 -- Create two tables with primary keys
CREATE TABLE t1 (id NUMBER, col1 VARCHAR2(4000),
col2 VARCHAR2(4000),
CONSTRAINT t1_pk PRIMARY KEY (id));
CREATE TABLE t2 (id NUMBER, col1 VARCHAR2(4000),
col2 VARCHAR2(4000),
CONSTRAINT t2_pk PRIMARY KEY (id)); -- Populate tables with 100,000 rows each
INSERT /+ APPEND / INTO t1SELECT ROWNUM, RPAD('x', 4000, 'x'),
RPAD('x', 4000, 'x')
FROM dual CONNECT BY LEVEL <= 100000;
COMMIT;
INSERT /+ APPEND / INTO t2SELECT ROWNUM, RPAD('x', 4000, 'x'),
RPAD('x', 4000, 'x')
FROM dual CONNECT BY LEVEL <= 100000;
COMMIT;-- Gather statistics on the tables
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 't1');
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 't2');
Checking the Size of Datafiles and Tables
To manage and optimize database performance, it is crucial to monitor the sizes of datafiles and tables. This gives insight into how much storage is utilized and how to balance the tables.
COLUMN file_name FORMAT A30;
SELECT substr(file_name, -28) AS file_name,
blocks,
bytes/1024/1024 AS size_mbFROM dba_data_files
WHERE tablespace_name = 'RECLAIM_TS';
COLUMN TABLE_NAME FORMAT A10;
SELECT TABLE_NAME,
blocks,
(blocks*8)/1024 AS size_mb
FROM user_tables
WHERE TABLE_NAME IN ('T1',
'T2')
ORDER BY 1;
This will provide an overview of the space consumed by the tables and associated datafiles.
Truncating a Table
To simulate data management and reclamation, we can truncate one of the tables, `t1`, to create a gap within the datafile:
TRUNCATE TABLE t1;
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 't1');
Analyzing and Shrinking the Tablespace
Before shrinking the tablespace, we analyze it to see how much space can potentially be reclaimed. The process involves calling the `SHRINK_SPACE` procedure from the `DBMS_SPACE` package. Here’s how to analyze shrink potential:
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdbaSET SERVEROUTPUT ON;
EXEC DBMS_SPACE.SHRINK_TABLESPACE('RECLAIM_TS', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
This step provides a report detailing the total movable objects, their sizes, and the original and suggested target sizes. This analysis helps in decision-making concerning space management.
Executing the Shrink Operation
After analysis, execute the shrink operation to reclaim unused space:
EXEC DBMS_SPACE.SHRINK_TABLESPACE('RECLAIM_TS');
The expected output indicates the number of moved objects and the new size comparison against the original size. For instance:
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2Total Moved Size (GB): 1.56
Original Datafile Size (GB): 3.26
New Datafile Size (GB): 1.63
Process Time: +00 00:00:17.782739
PL/SQL procedure successfully completed.
Additional Insights on Shrinking Tablespaces
- Moving Objects: Objects within the tablespace are compacted, allowing for efficient space reclamation.
- Online vs. Offline Operations: The `SHRINK_SPACE` operation is more flexible than traditional `ALTER TABLE … MOVE` commands, making it easier to manage data without extensive downtime.
- Failure Handling: A shrink operation can fail; however, it’s designed in a way that, even if some movements fail, it may still free up space successfully.
Conclusion
Efficient management of bigfile tablespaces in Oracle Database plays a crucial role in optimizing performance and storage usage. Using commands to create, populate, analyze, and shrink tablespaces allows database administrators to maintain control of data storage effectively. This management ensures that database systems continue running optimally without unnecessary waste of storage resources. Adopting such practices is essential for scalable and efficient database operation.
Home 