Oracle EBS 表空间模型 - Oracle Applications Tablespace Model（OAT

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

原创最新推荐文章于 2021-04-13 07:39:01 发布 · 264 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了Oracle EBS的OATM表空间模型，包括其12个表空间类型的用途，如何通过OATM有效减少表空间数量，以及查询表空间状态的SQL脚本。

部署运行你感兴趣的模型镜像

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.youkuaiyun.com/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

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 breaksSET linesize 130SET pagesize 60break ON tablespace_name skip 1col tablespace_name format a15col file_name format a50col 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_FREEFROM sys.dba_free_space fs, sys.dba_data_files ddWHERE dd.tablespace_name = fs.tablespace_nameAND dd.file_id = fs.file_idGROUP 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 a50col tablespace_name format a10SELECT file_name, tablespace_name, ROUND(bytes/1024000) MBFROM dba_data_filesORDER 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_FREEFROM (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) fsWHERE df.tablespace_name = fs.tablespace_name(+)ORDER BY fs.tablespace_name;