Bugs in Tablespace Map

Toad for Oracle & DevOps Toolkit
1

There are bugs in Tablespace Map that I observed in 25.3 and again in 26.1. When I use the map on one of our tablespaces, it exhibits anomalous behavior. Here is the map:

image
image1200×819 19 KB

(I have changed the map colors in Options.)

I have a larger screenshot that demonstrates the following problems, but I don't want to post it publicly.

There are multiple problems:

  • When that tablespace's map is first shown, it does not show the last partial line as occupied. The whole line is white as being outside the map. The purple blocks on the partial last line of blocks start out white and are only purpled when I click something inside the map. Mouse hovering those blocks either when white or purple show nothing--not a segment name and not "free space". Clicking on anything inside the map colorizes those blocks, but hover still shows nothing.

  • When the map window first opens, it is maximized within the Tablespace Map tab. I then right-click the tab and "Pop-out Window" to make it smaller. I then select a tablespace and click Analyze. The map is populated, but it is larger than the size of the window, so I pull the borders to enlarge it to show the whole map. Now, when I click a block to select something, various blocks are highlighted. So far so good. But, when I click Clear Highlights, this happens:

    image
    image1202×821 19.1 KB

    Only that part of the window that was visible before resizing is cleared. The part outside the original window size keeps its highlighting. It only resets when I click Analyze again. Now, it I resize the window before selecting and analyzing a tablespace, the above does not happen. The full map is cleared by Clear highlights.

  • Finally, there is a segment occupancy mapping issue. I can provide a screenshot privately that shows the issue. What is happening is that the last block of the map, which is the bottom-right complete row, shows the wrong segment name on mouse hover. I have a query that I run that maps out datafile occupancy. That query returns different information about the last occupied blocks of the file. The segment that the map shows at the end is only near the end. According to my query, a different segment is at the very end.
    I suspect that partial row issue is in play here. The partial row that only appears when I click on something may be the real end of the map, but when I hover the mouse pointer over those blocks, no occupancy information appears. Although highlighted, they behave as if they are outside the map. It may be that the partial row is where the real end is and that is what is broken.

Cheers,
Russ

2

I noticed more wonky things about Tablespace Map.

  • If I pull up a tablespace that has a lot of free space after the last occupant, and I click somewhere in the trailing free space, only the free space prior to the last occupied block is highlighted. any trailing free space is not highlighted.
  • If I Clear Highlights in the scenario described above so that not all the enlarged window is cleared of highlighting, those uncleared highlight blocks are always highlighted until I Analyze again. Those uncleared highlights just stay highlighted until Analyze.

Now, it would be nice if was possible to type or paste a tablespace name in the Tablespace box instead of having to always use the drop down. We have LOTS of tablespaces, and it is tedious to always have to search through the long drop down list. (First-world problems, I guess.)

Cheers,
Russ

3

Hi Russ,

Sorry for the delayed response. I will log these so I don't forget about them.

In the meantime, Schema Browser -> Tablespaces -> RHS -> Objects, configured like this (and filtered by File ID if you have multiple files in your tablespace) may be a suitable workaround.

image
image1119×892 64.3 KB

I added this a few versions back to get the same info as tablespace map in a tabular format. I was trying to clean up a SYSAUX tablespace which was getting too big. You can use the toolbar to drop/truncate/rebuild/move selected objects. I found it easiest to move the objects at the end to another tablespace, then shrink SYSAUX as much as I could, then move them back. The "Rebuild" button works for all types of objects, and filters out duplicates, if you select multiple rows of the same object (due to having multiple extents). Hope that helps.

4

Yeah, I know about that. What I was working on was a query for that very thing in sqlplus. This is for an effort I have underway to expose certain info to scripts I've got for analyzing data files. It was just by accident that I ran across the Tablespace Map issue while working on that.

Here is my query:

with ext as (select owner, segment_name, segment_type, file_id, block_id, blocks from sys.dba_extents
             UNION
             select null, null, '------ free ------', file_id, block_id, blocks from sys.dba_free_space),
     map as (select /*+ MATERIALIZE */ owner, segment_name, segment_type, file_id, block_id, blocks from ext),
     blk as (select file_id, sum(blocks) "BLOCKS" from map group by file_id)
select m.file_id, m.owner, m.segment_name, m.segment_type, m.block_id, m.blocks,
       b.blocks "CALC_BLOCKS", d.blocks "FILE_BLOCKS", d.bytes/power(1024,2) "FILE_MB",
       t.name "TABLESPACE", d.name "DATAFILE"
  from map m
  join blk b
    on m.file_id = b.file_id
  join v$datafile d
    on m.file_id = d.file#
  join v$tablespace t
    on d.ts# = t.ts#
 where m.file_id = &&FILE
 order by m.block_id desc;

Its output looks like this:

FILE_ID OWNER      SEGMENT_NAME    SEGMENT_TYPE         BLOCK_ID   BLOCKS CALC_BLOCKS FILE_BLOCKS  FILE_MB TABLESPACE                     DATAFILE                                                                                            
------- ---------- --------------- ------------------ ---------- -------- ----------- ----------- -------- ------------------------------ --------------------------------------------------------
    274                            ------ free ------     466816   32,384     499,072     499,200    3,900 [REDACTED]_DATA_TBL            /u02/oracle/oradata/[REDACTED]/[REDACTED]_DATA_TBL01.dbf
    274 [REDACTED]_QTY_DETAIL      INDEX                  466688      128     499,072     499,200    3,900 [REDACTED]_DATA_TBL            /u02/oracle/oradata/[REDACTED]/[REDACTED]_DATA_TBL01.dbf
    274 [REDACTED]_TRANSACTION_LOG TABLE                  466560      128     499,072     499,200    3,900 [REDACTED]_DATA_TBL            /u02/oracle/oradata/[REDACTED]/[REDACTED]_DATA_TBL01.dbf
    274                            ------ free ------     458368    8,192     499,072     499,200    3,900 [REDACTED]_DATA_TBL            /u02/oracle/oradata/[REDACTED]/[REDACTED]_DATA_TBL01.dbf
    274 [REDACTED]_CHANGE_LOG_HIST TABLE                  458240      128     499,072     499,200    3,900 [REDACTED]_DATA_TBL            /u02/oracle/oradata/[REDACTED]/[REDACTED]_DATA_TBL01.dbf
    274 [REDACTED]_CHANGE_LOG_HIST TABLE                  458112      128     499,072     499,200    3,900 [REDACTED]_DATA_TBL            /u02/oracle/oradata/[REDACTED]/[REDACTED]_DATA_TBL01.dbf

Cheers,
Russ

5

Hi, I didn't know about that "show free space" checkbox on the extents list, so thanks for pointing it out, it looks useful.

Another thing with that Tablespace Map is the scaling. Regardless the size of the window, it only draws data in a certain area from the top left corner. If the window is bigger/maximised, it leaves a lot of unused space, with everything squished into the corner. I wonder whether it might be possible, when calculating the "blocks per cell" scaling, to look at the number of cells being displayed in the window (or at least look at the visible space in the window) and scale accordingly?

(And thanks to the @russell.cannon for the tip on changing colours - setting "free space" to something not-white helps differentiating free space from the superfluous form space :slight_smile: )

image
image1921×879 25.1 KB

6

I also don't like the fact that it isn't possible to change all that superfluous "outside" space to a different color. I am "hard of seeing", and one of my problems is that bright screens are blinding to me. So, I put everything into dark mode and dark colors, etc. I have to undock the TS Map so to get rid of all that whiteness.

It's just me, and since I can undock the map, it's fine.

Cheers,
Russ

7

I see no reason why that area can't be darker. Maybe not inky blackness, but a dark gray, at least.

8

Maybe give us the ability to set its color?

Cheers,
Russ

9

I added incremental filtering and ability to paste into the dropdowns for next beta.

Paste will only accept tablespaces that actually appear in the dropdown.

The incremental filtering is nice. You can type in any portion of the tablespace name, it doesn't have to be the first few letters. Anything that doesn't match falls off the list. If your match is the first item in the list, you can just hit ENTER (as shown in the video below). If it's further down, you can arrow down to it, then hit ENTER. Over the coming months, I'll be adding this kind of filtering to every dropdown that can have a long list of items.

tablespace-selection

I also made the background color follow the color theme. Seemed like a better choice than another option.

The coloring problems are still on my to-do list, but those are more work, so I'm saving them for another day.

10

This is great! Thanks so much, John.

Cheers,
Russ