Version: 4.x to 2010
Transaction Code: DB02
Type of database: Oracle 8.0 and above
Use this transaction (DB02) to analyze tablespace growth. It support raw device and Oracle ASM managed device as well.
However, for raw device, the size of the tablespace is fixed, so it can only determine the % used within the space allocated.
For beginner who does not understand Oracle tablespace, it is the visible usable space from SAP and database, which contain space logically assigned (total of all physical files). Under it, it consists of 1 or multiple files with physical space allocation, with extension dbf. DBA generally assign few MB to GB during tablespace creation, and allows them to auto grow (auto extend) if they hit the pre-allocated space. It is possible NOT to allow it to grow, regardless how much space left in the drive. Raw device and ASM is more complicated, so I won't explain here.
You must navigate to Space - Tablespace folder in order to analyze the tablespace usage. SAP keeps a 30 days history in "Detailed Analysis" folder
In order to determine the growth of each tablespace, the dbf data file has to:
1. shrink (or allocate) to its minimum size with as little free space as possible
2. minimum tablespace free space fragmentation (fragmented free space within the dbf file)
3. If there a a lot of free space fragmentation, then needs to re-organize the tablespace, which is time consuming, and requires downtime. For partition table, it is less downtime, but in all cases, there will certainly be performance impact, especially for data warehouse SAP BW