Remove Table Extents

Oracle provides a mechanism for a table to extend when new rows are added to the table. Once the size specified by the INITIAL parameter is exhausted, Oracle will allocate a new extent of NEXT size to hold the new rows. Tables will continue to extend in this fashion until the MAXEXTENTS value is reached.

Yes, the tablespace manager still exists in Oracle 19c, though its features and the way it is used have evolved over time. In Oracle 19c, the tablespace manager is primarily responsible for managing storage allocations within tablespaces, but much of the manual management has been replaced or automated by newer features like Automatic Segment Space Management (ASSM) and Automatic Storage Management (ASM).
Key Points About the Tablespace Manager in Oracle 19c:

1. Still Present:
   • The tablespace manager continues to exist and plays a role in managing free space, segment allocation, and extent allocation within tablespaces.
2. Automatic Segment Space Management (ASSM):
   • Oracle 19c defaults to ASSM for managing segment space within tablespaces. ASSM uses bitmaps instead of freelists to track free space, reducing contention and improving performance.
   • Tablespaces with ASSM eliminate the need for manual tuning of freelists or freelist groups, simplifying space management.
3. Manual Space Management:
   • For legacy or specific use cases, Manual Segment Space Management (MSSM) is still supported but is not recommended unless required for compatibility or special circumstances.
4. Automatic Storage Management (ASM):
   • ASM, introduced in earlier versions of Oracle, is widely used in Oracle 19c to manage storage across multiple disks. It operates at the file system level and complements the tablespace manager by abstracting the physical storage.
5. Enhancements in Oracle 19c:
   • Oracle 19c includes enhancements like online tablespace encryption, improved compression options, and automatic data optimization that work in conjunction with the tablespace manager to simplify database administration.

When to Use the Tablespace Manager:

• Legacy Systems: If you are managing older applications or databases that use MSSM, you might still interact with the tablespace manager more directly.
• Specific Administration Tasks: Tasks such as resizing tablespaces, monitoring tablespace usage, or managing data files involve the tablespace manager.
• Compatibility: If you're migrating from older Oracle versions, the tablespace manager ensures backward compatibility.

Tools for Tablespace Management:

• SQL Queries: You can query the DBA_TABLESPACES, DBA_FREE_SPACE, and DBA_SEGMENTS views to monitor and manage tablespaces.

  SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb
  FROM dba_data_files;
      

• Oracle Enterprise Manager (OEM): The OEM graphical interface simplifies tablespace management tasks such as adding, resizing, or monitoring tablespaces.
• Command-Line Interface: Using SQL*Plus or other Oracle tools, you can perform operations like creating or altering tablespaces.

Conclusion: The Oracle tablespace manager exists in Oracle 19c, but much of its functionality has been enhanced or automated through modern features like ASSM and ASM. While it plays a foundational role in storage management, administrators rely more on these automated features for efficiency in contemporary Oracle environments.
Multiple Extents: Once a table has extended numerous times, it is not uncommon to see table extents spread randomly across the tablespace. This may (or may not) be a problem. Some DBAs say that multiple extents are good for a table since the data blocks are spread more randomly across the tablespace. Other DBAs argue that multiple extents are bad because Oracle may have to access additional data blocks to get the table rows.

To see this type of fragmentation, you can go to the Tablespace manager within OEM and look at the table extents. Click the View Image button to see an example. The credit detail table is spread in many extents across the tablespace. However, Oracle has allocated most of the extents adjacent to each other.

Create table 
  new_customer
STORAGE (INITIAL 10m NEXT 2m) <== 
where 10m = num_rows * avg_row_len 
from dba_tables As
Select * 
from  customer
Order by

1. Run the Oracle export utility with the COMPRESS=Y option. This will direct the Oracle import utility to create the table with a large enough INITIAL extent to hold all of the data in one extent.
2. Use CTAS to reorganize the table. This involves manually computing the new INITIAL extent size (based on avg_row_len times num_rows) and running the CTAS with a STORAGE clause as shown in the example below.

Both of these methods will serve to coalesce extents, but the CTAS method almost always runs faster. The next lesson explores chained rows.