Solution : https://service.sap.com/sap/support/notes/821687 (SAP Service marketplace login required)
Summary :
This SAP Note addresses issues of space utilization and fragmentation in database management, essential for maintaining optimal database performance and cost efficiency. It explains the different types of fragmentation (internal, index, table, LOB segment, and external fragmentation) and the implications for Oracle databases. Strategies for assessing and improving the space utilization are outlined, including adapting PCTFREE and PCTUSED parameters, managing INITRANS settings, parallel query processing, and the impact of storage strategy choices like AUTOALLOCATE or UNIFORM on LMTS. Practical steps for better use of file system space and specific remedies for fragmentation types are included to optimize performance and minimize space requirements, backup, and recovery time.
Key words :
connect <sapuser>/<password>set heading offset feedback offset echo offset pagesize 50000set sqlprompt ''spool lob, customer component bc-db-ora oracle priority recommendations / additional info category faq validity, spool offcolumn table_name format a30column column_name format a25set heading, reorganization 332677 rebuilding fragmented indexes 214995 oracle locally managed tablespaces, 332677 rebuilding fragmented indexes 70513 physical block accesses resb, @lobset feedback onset sqlprompt 'sql>', ora-00060 70513 physical block accesses resb, alter database datafile '<file>' resize <new_size>, parallel query thread allocates separate extents, ora-00060 214995 oracle locally managed tablespaces
Related Notes :
| 1806750 | |
| 1295200 | Oracle 10g or higher: Space statistics based on DBMS_SPACE |
| 1010237 | Growth of DYNPSOURCE table after Unicode migration |
| 948197 | Merge fix for DBMS_STATS package on Oracle 9.2.x and 10.2.x |
| 927813 | Oracle 10g: Using Oracle Segment Advisor to optimize space |
| 910389 | FAQ: Oracle Segment Shrinking |
| 896717 | Problems with tables in ASSM tablespaces |
| 832343 | FAQ: Clustering factor |
| 771929 | FAQ: Index fragmentation |
| 745639 | FAQ: Oracle enqueues |
| 666061 | FAQ: Database objects, segments and extents |
| 620803 | Oracle 9i: Automatic Segment Space Management |
| 588668 | FAQ: Database statistics |
| 572060 | Options in the Oracle database during archiving |
| 554031 | Creating space statistics with BRCONNECT |
| 541538 | FAQ: Reorganization |
| 332677 | Rebuilding fragmented indexes |
| 214995 | Oracle locally managed tablespaces in the SAP environment |
| 84348 | Oracle deadlocks, ORA-00060 |
| 70513 | Physical block accesses RESB when accessing via MDRS |