My Oracle Support Banner

How to Reorganize INV Schema / Reclaim the High Watermark (Doc ID 555058.1)

Last updated on JANUARY 21, 2021

Applies to:

Oracle Inventory Management - Version 11.5.5 and later
Information in this document applies to any platform.

Goal

How to reorganize the inventory schema in the Oracle Applications?


-- Summary:
Question on import/export to reduce high watermark. Want to confirm that exporting data from table, then importing the same data would not result in any issues. Also want to know if triggers should be disabled first.

-- Example Business Scenario #1:
Purged 40% of the data out of tables (APPS schemas) including Inventory Tables listed below.
Want to reduce space used by file system related to these tables.
The Oracle Support DBA team suggested using import/export following <Note 1029252.6>.
The note suggests if extents are in the middle of a datafile as in this case, one should export
the table, drop the table, then import the table.

How does Oracle Inventory Development recommend lowering the high watermark on these tables that have been purged? Would the following steps be recommended?
1. Run purge (ie., transaction purge, customer purge, costing purge, etc.)
2. Disable triggers on the related tables
3. Export data from tables
4. Drop table
5. Recreate table.
6. Import data into tables

-- Note
Find over 114 triggers defined in other schemas that depend on tables on INV schema.

-- Example Tables Involved
MTL_CST_ACTUAL_COST_DETAILS
MTL_CST_TXN_COST_DETAILS
MTL_ACTUAL_COST_SUBELEMENT
MTL_MATERIAL_TRANSACTIONS
MTL_TRANSACTION_ACCOUNTS
MTL_MATERIAL_TXN_ALLOCATIONS


Example Environment / Versions

-- Operating System / Database
Operating System: HP-UX 11.11
Database: 9.2.0.8

-- Data dictionary version
COMP_ID SCHEMA STATUS VERSION COMP_NAME
CATALOG SYS VALID 9.2.0.8.0 Oracle9i Catalog Views
CATJAVA SYS VALID 9.2.0.8.0 Oracle9i Java Packages
CATPROC SYS VALID 9.2.0.8.0 Oracle9i Packages and Types
CONTEXT SYS VALID 9.2.0.8.0 Oracle Text
JAVAVM SYS VALID 9.2.0.8.0 JServer JAVA Virtual Machine
ORDIM SYS VALID 9.2.0.8.0 Oracle interMedia
RAC SYS INVALID 9.2.0.8.0 Oracle9i Real Application Clusters
SDO MDSYS VALID 9.2.0.8.0 Spatial
XML SYS VALID 9.2.0.10.0 Oracle XDK for Java

-- Version of the export utility
Export: Release 9.2.0.8.0 - Production on Thu Feb 21 16:28:06 2008

-- Database characterset
PARAMETER VALUE
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

-- NLS_LANG setting
American_America.UTF8

-- Export parameters
exp "'/ as sysdba'" FEEDBACK=100000 FILE=$t_dump_pipe owner=INV BUFFER=300000000 COMPRESS=N
INDEXES=N DIRECT=Y LOG=$t_logN CONSISTENT=Y RECORDLENGTH=300000000 >$t_log1 2>$t_log2

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
 How to reorganize the inventory schema in the Oracle Applications?
 Example Environment / Versions
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.