- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Provide a way for DBA to prevent use of the /* +APPEND */ hint
Vendor software is rebuilding changed rows first by a) deleting the rows from a table, b) then re-inserting them using the /* +APPEND */ hint. Which inserts the data above the high water mark (HWM), making the space freed up by the delete unusable. This causes the table to grow each event, even if row counts stay same. In some cases we have a 30G table, that only contains 1.5G of data. The other 28G is not usuable. We can't control if the vendor will change their code. So I would like to see a way to prevent/override the append hint from doing a direct path load.
- DB level: I don't know if an init parameter at the DB level would work, or if that could impact vanilla Oracle processing.
- Session level: I don't know if we could configure the vendor process to set a session level parameter.
- Schema level: Maybe a schema level permission to force it to use existing free space, i.e. "grant disable_append_hint to vendor_schema".
I know implementing this at a more generic level, might prevent legitimate usage by the schema, say if they are pairing it with a truncate instead of a delete. But having this option would allow us to control misuse by a schema, if we deemed any added impact to be acceptable.