Oracle EBS 表空间模型 - “Oracle Applications Tablespace Model（OATM）”

最新推荐文章于 2021-04-13 07:39:01 发布

转载最新推荐文章于 2021-04-13 07:39:01 发布 · 179 阅读

文章标签：

本文深入探讨了Oracle Applications Tablespace Model（OATM），解释了其如何在Oracle EBS中管理本地表空间，减少表空间数量，并提供了一系列查询脚本来帮助管理表空间。文章详细介绍了OATM的好处及附带的查询脚本，适用于数据库管理和优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

OATM即Oracle Applications Tablespace Model，是Oracle EBS可选的表空间模型。

OATM提供12个表空间模型用于管理本地表空间，" APPS_"开头的属于Oracle EBS的业务表空间，此外的三个为Oracle数据库的系统表空间，包括Temp空间，System表空间，Undo表空间。

OATM是11i10发布的，11i之前，每个模块都有两个表空间，一个是数据表空间，另外一个索引表空间，造成了非常多的表空间，通过OATM可以有效的减少表空间数量，把同一类的表空间合并成一个。

Oracle Applications Tablespace Model

Tablespace Type	Tablespace Name	Content
Transaction Tables	APPS_TS_TX_DATA	Tables that contain transactional data.
Transaction Indexes	APPS_TS_TX_IDX	Indexes on transaction tables.
Reference	APPS_TS_SEED	Reference and setup data and indexes.
Interface	APPS_TS_INTERFACE	Interface and temporary data and indexes.
Summary	APPS_TS_SUMMARY	Summary management objects, such as materialized views, fact tables, and other objects that record summary information.
Nologging	APPS_TS_NOLOGGING	Materialized views not used for summary management and temporary objects.
Advanced Queuing/AQ	APPS_TS_QUEUES	Advanced Queuing and dependent tables and indexes.
Media	APPS_TS_MEDIA	Multimedia objects, such as text, video, sound, graphics, and spatial data.
Archive	APPS_TS_ARCHIVE	Tables that contain archived purge-related data.
Undo	UNDO	Automatic Undo Management (AUM) tablespace. UNDO segments are identical to ROLLBACK segments when AUM is enabled.
Temp	TEMP	Temporary tablespace for global temporary table, sorts, and hash joins.
System	SYSTEM	System tablespace used by the Oracle Database

通过OATM管理表空间的好处：

Fewer and more consolidated tablespaces
Locally Managed Tablespaces
Accounts for the I/O characteristics of an object
Reclaims space after migration
Real Application Cluster (RAC) Support

附表空间查询的脚本：SQL Query for Tablespace

-- list all tablespaces with their associated files, the

-- tablespace\'s allocated space, free space, and the

-- next free extent:
clear breaks
SET linesize 130SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading \'TABLESPACE|TOTAL KB\'
col kbytes_free heading \'TOTAL FREE|KBYTES\'
 
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024ORDER BY dd.tablespace_name, dd.file_name;
 
 
-- list datafiles, tablespace names, and size in MB:
 
col file_name format a50
col tablespace_name format a10

SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
  
 
-- list tablespaces, size, free space, and percent free

-- query originally developed by Michael Lehmann SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)ORDER BY fs.tablespace_name;