Re: Find Data Dictionary Size itself? Multi-tenant in this case

Debezium should be using DBMS_LOGMNR_D.BUILD <https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/LogMinerStreamingChangeEventSource.java#L496> which calls the same internal routine as in DBMS_CAPTURE_ADM. It looks like the dictionary build is indeed performed on each log switch based on this excerpt:
https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/LogMinerStreamingChangeEventSource.java#L228-L236 The call that seemingly initiates the dictionary build is: `initializeRedoLogsForMining(jdbcConnection, true, startScn);` The dictionary build part of this procedure is: https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/LogMinerStreamingChangeEventSource.java#L422-L425 Since the continuous_mine is desupported in 19c ( https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#GUID-3417B738-374C-4EE3-B15C-3A66E01AE2B5), the code should be building the LogMiner dictionary on each log switch if I got it right.

Regarding the size, you can just enable sql_trace and see what is performed when you call DBMS_LOGMNR_D.BUILD. There is a bunch of data loads done to populate LOGMNR% tables from SYS.COL$/OBJ$/CDEF$/etc. using retrospective (AS OF SCN) queries.
Therefore, 'redo size' seems to be quite a reliable statistic to estimate the LogMiner dictionary size in the redo logs: ```
SQL> select value from v$mystat natural join v$statname where name='redo size';

     VALUE

SQL> exec DBMS_LOGMNR_D.BUILD (options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS)

PL/SQL procedure successfully completed.

SQL> select value from v$mystat natural join v$statname where name='redo size';

     VALUE

SQL> select thread#, name, first_change#, next_change#, dictionary_begin, dictionary_end, blocks*block_size size_ from v$archived_log order by first_change#;

   THREAD# NAME

                                    FIRST_CHANGE# NEXT_CHANGE# DIC DIC

• ------------ --- --- ---------- 1 +DATA/RACDB/ARCHIVELOG/2024_01_04/thread_1_seq_18.301.1157375151 728618 729346 YES NO 25659392 2 +DATA/RACDB/ARCHIVELOG/2024_01_04/thread_2_seq_6.303.1157375157 728621 729367 NO NO 9216 1 +DATA/RACDB/ARCHIVELOG/2024_01_04/thread_1_seq_19.302.1157375153 729346 729353 NO YES 2048

20 rows selected.
```

The actual archive log size ~25M is close to the 'redo size' statistic (~24M) in this idle database.
Of course, when sizing the redo logs, you need to take into account other activity which might be happening while the LogMiner dictionary is being built.

Best regards,
*Mikhail Velikikh*

On Thu, 4 Jan 2024 at 12:15, Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:

> I think in this context "dictionary" means this:
>
> https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-4539AB47-A932-4A79-AFF5-5A42733B19DF
>
> No idea really, how much data it would add to redo.
>
> On Thu, 4 Jan 2024 at 12:40, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> On the surface, this question may seem odd (or silly) but I realize I
>> don't actually know how to find the size of the data dictionary itself in
>> Oracle.
>>
>> The reason this is coming up is that we're playing with Debezium and
>> Kafka replication, and this article here wants to make sure one redo log is
>> large enough to house the data dictionary size (so that on a log switch,
>> every log will have a copy of the data dictionary to compare changes).
>>
>> Which leads me to realizing, I don't know how to find the size of the DD
>> itself (or have forgotten).
>>
>> Article that leads me into this question:
>>
>> https://debezium.io/blog/2022/09/30/debezium-oracle-series-part-1/#configure-oracle-redo-logs
>>
>> Thanks,
>> Chris
>>
>>
>
> --
> Ilmar Kerm
>

--
http://www.freelists.org/webpage/oracle-l