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.
How to reorganize the inventory schema in the Oracle Applications?
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
Find over 114 triggers defined in other schemas that depend on tables on INV schema.
-- Example Tables Involved
Example Environment / Versions
-- Operating System / Database
Operating System: HP-UX 11.11
-- Data dictionary version
COMP_ID SCHEMA STATUS VERSION COMP_NAME
CATALOG SYS VALID 188.8.131.52.0 Oracle9i Catalog Views
CATJAVA SYS VALID 184.108.40.206.0 Oracle9i Java Packages
CATPROC SYS VALID 220.127.116.11.0 Oracle9i Packages and Types
CONTEXT SYS VALID 18.104.22.168.0 Oracle Text
JAVAVM SYS VALID 22.214.171.124.0 JServer JAVA Virtual Machine
ORDIM SYS VALID 126.96.36.199.0 Oracle interMedia
RAC SYS INVALID 188.8.131.52.0 Oracle9i Real Application Clusters
SDO MDSYS VALID 184.108.40.206.0 Spatial
XML SYS VALID 220.127.116.11.0 Oracle XDK for Java
-- Version of the export utility
Export: Release 18.104.22.168.0 - Production on Thu Feb 21 16:28:06 2008
-- Database characterset
-- NLS_LANG setting
-- 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
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
|How to reorganize the inventory schema in the Oracle Applications?|
|Example Environment / Versions|