Solution : https://service.sap.com/sap/support/notes/541538 (SAP Service marketplace login required)
Summary :
This SAP Note provides guidance on system reorganization involving database objects such as tables, tablespaces, and indexes in both offline and online modes. Offline reorganizations require system downtime and involve data exportation into a dump file, reconfiguration, or ALTER TABLE MOVE for tables without LONG and LONG RAW columns as per Note 1080376. Online reorganizations, facilitated by tools like BRSPACE, allow system operation during reconfiguration using Oracle’s DBMS_REDEFINITION package to prevent access locks. Reorganizations are beneficial in various scenarios, such as improving tablespaces, disk usage optimization, and performance enhancement by redistributing table data or modifying storage parameters. However, certain scenarios don’t necessitate reorganization.
Key words :
harmless parallel processing variants parallel query, avoid unforeseen st enqueue problems, avoid long begin backup runtimes, oracle parallel data manipulation language, materialized view-related errors refer, acquire lock -- table locks disabled, optimal full table scan performance, modify default attributes tablespace <tablespace>, frequently asked questions solution 1, existing key field combinations
Related Notes :
| 1565421 | Oracle 11g: dbms_redefinition may flush shared pool |
| 1292525 | Ora-600 [Kdlm_merge_lobs:1] during table reorganization |
| 1290097 | ORA-01792 while running online reorganization of the table |
| 1129347 | DBMS_METADATA.GET_DDL, HIDDEN/UNUSED columns and ORA-947 |
| 1080376 | Enhancements for reorganization and rebuild |
| 1016172 | Sorting table records during reorganization |
| 910389 | FAQ: Oracle Segment Shrinking |
| 875477 | Avoiding long runtimes with BEGIN BACKUP |
| 832343 | FAQ: Clustering factor |
| 821687 | FAQ: Space utilization and fragmentation in Oracle |
| 806554 | FAQ: I/O-intensive database operations |
| 793113 | FAQ: Oracle I/O configuration |
| 787533 | Bad performance/ system lockup due to enqueue waits |
| 778784 | Inconsistencies between data class and database container |
| 771191 | Copying BW objects to new tablespaces |
| 741478 | FAQ: Materialized views |
| 737810 | ora-00069/ora-08116 with DDL operation for table or index |
| 722188 | FAQ: Oracle partitioning |
| 666061 | FAQ: Database objects, segments and extents |
| 659946 | FAQ: Temporary tablespaces |
| 647697 | BRSPACE - New tool for Oracle database administration |
| 646681 | Reorganization of tables with BRSPACE |
| 620803 | Oracle 9i: Automatic Segment Space Management |
| 619188 | FAQ: Oracle wait events |
| 535675 | EXP can produce dump file with corrupted data |
| 355771 | Oracle: Explanation of the new tablespace layout |
| 354293 | DBVerify reports corrupt block in freespace area |
| 332677 | Rebuilding fragmented indexes |
| 214995 | Oracle locally managed tablespaces in the SAP environment |
| 85558 | Tables with data sorted by index |
| 84348 | Oracle deadlocks, ORA-00060 |
| 23237 | duplicate keys, duplicate rows, duplicate records |
| 11369 | ORA-1452 during import after export from other system |
| 391 | Archiver stuck |