Schema Browser datafiles view shows usage wrongly

Toad for Oracle
1

Hi

Toad 13.3.0.181

Oracle 19.8, two node RAC on cdb$root.

I see that UNDOTBS2 is 100% used on datafile tab.
But on tablespaces view, it shows usage correctly.

UNDOTBS1 shows correctly.

image
image1493×822 72.4 KB

2

I just tried that here on 18c, I don't have a 19c RAC handy. I am seeing the correct values for UNDOTBS1 and UNDOTBS2.

image
image1865×396 53 KB

Just curious, does it make any difference if you are connected to instance1 or instance2?

Edit: I found a 19c. Connected to container db there, same result - worked.

-John

3

Nop. This picture was from instance 1. And same issue for UNDOTBS2 on instance 2 as well.

Raul

4

Try this:
Go into filter settings and change this option to DBA_FREE_SPACE.

image
image507×503 13.5 KB

DBA_LMT_FREE_SPACE is faster, but it does not take recyclebin objects into consideration.

I don't know why that would matter for UNDO, but maybe it does.

5

Did you test from noncdb or cdb..?

This seems like is something to do with the conteiner_id (con_id).
I have 3 user created pdb-s in that container. So with the cdb$root also, there are 4 of these in total.

When spooling to screen, I can see that this happens.

----------------------------------
-- Session: SYSTEM@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=ARENDUS-ETDB1)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=CDB)))
-- Timestamp: 16:29:39.776
select distinct ts#
from   v$tablespace
where  name = :name;
:name(VARCHAR[8],)='UNDOTBS2'

With this query, I get in cdb database:

   TS#
----------
     8
     4
     9
3 rows selected.

In my case these are four different containers. So I have 2 UNDOTBS2 with TS#=8.

select distinct ts#, con_id
from   v$tablespace
where  name = 'UNDOTBS2';

       TS#     CON_ID
---------- ----------
         4          1
         8          5
         8          3
         9          4

4 rows selected.

Somehow for the next query (for datafile query), I get these parameters:

:tname(VARCHAR[8],)='UNDOTBS2' 
:tid(INTEGER,)=8

I do not know how you get this 8, since this v$tablespace query gets 3 different results.

And probably this is why it goes wrong. In your query it seems like it does not count cdb architecture.

Raul

6

I am using local undo on pdb's, since it is recommended.
So in total I have 4 undo tablespaces on each instance.

The problem seems like, where does it come, that TS#=8

The TS#=4 is correct, since con_id=1 is cdb$root

TS#     CON_ID
---------- ----------
     4          1

Raul

7

Thanks for the detective work, Raul. I did test CDB but maybe I got lucky with the first result being the right one with the v$tablespace query.

8

Yeah, that might very bell be it. With my first RAC node UNDOTBS1, I also got lucky..:slight_smile:

So it should be something like this:

select distinct ts#
from   v$tablespace
where  con_id = sys_context('userenv', 'con_id') and name = :name;

Or with whatever method you get the current container_id.

Raul

1 Like
9

What is the status with this bug, have you had any progress with it..?

Regards
Raul

10

Hi Raul,

It's been fixed in beta and will be fixed in 14.0 GA which should be out in a few weeks. Beta Released (14.0.51.637)

Sorry, I forgot to announce the fix here.

-John

1 Like
11

Ok, cool, thanks.

Raul

12

Indeed, working as expected in 14.0
Thanks

Raul

1 Like