OraFAQ Forum: Server Administration » user_tab_modifications not showing data for new table

Home » RDBMS Server » Server Administration » user_tab_modifications not showing data for new table (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

user_tab_modifications not showing data for new table [message #665879]

Thu, 28 September 2017 08:03

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi all,

I've read in Oracle documentation about the possibility to see changes done on tables in USER_TAB_MODIFICATIONS.

I tried to test it by
1. adding a new user, connect and add table:

Code: [Select all] [Show/ hide]

SQL> create user temp1 identified by 1;

User created.

SQL> grant dba to temp1;

Grant succeeded.

SQL> conn temp1/1@MY_DB
Connected.
SQL>
SQL>
SQL> create table t as select * from dba_objects;

Table created.

2. running some DML on it:

Code: [Select all] [Show/ hide]

SQL> insert into t select * from dba_objects where rownum < 11;

10 rows created.

SQL> commit;

Commit complete.

SQL>

3. Gather Statistics:

Code: [Select all] [Show/ hide]

SQL> exec DBMS_STATS.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

4. Run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO on it:

Code: [Select all] [Show/ hide]

SQL>   exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

And it won't appear Sad

Code: [Select all] [Show/ hide]

SQL> select * from user_tab_modifications where table_name ='T';

no rows selected

SQL>

I've also tried to see how a table that *DOES exist* in DBA_TAB_MODIFICATIONS is defined, by dbms_metadata.get_ddl and I don't see any attribute that is called "monitoring".
Therefore, I am a little confused. How can I get my newly created table to appear in this view and have information about it on demand ?

Thanks in advance,
Andrey

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665880 is a reply to message #665879]

Thu, 28 September 2017 08:06

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Does the DBA_ version show you anything?

Edit: Never mind, I see the issue.

The *_tab_modifications view shows you data which has happened SINCE the last stat gather. Take your stat gather out.

[Updated on: Thu, 28 September 2017 08:07]

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665881 is a reply to message #665880]

Thu, 28 September 2017 08:10

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Thanks Roachcoach for the quick reply!

But I don't see it happening as you described, or I misunderstood what you've meant...
I gathered stats and then right away selected from the view - no luck Sad

Code: [Select all] [Show/ hide]

SQL> exec DBMS_STATS.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

SQL>  select * from user_tab_modifications where table_name ='T';

no rows selected

SQL>

[Updated on: Thu, 28 September 2017 08:11]

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665882 is a reply to message #665881]

Thu, 28 September 2017 08:13

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The act of gathering table stats wipes out the data in *_tab_modifications.

That view holds the data which has occurred since the last statistic gather, in your case, no DML.

You can see the attribute in here:

Code: [Select all] [Show/ hide]

select monitoring from dba_tables where table_name ='&TABNAME'

I have not checked but I understand that this can no longer be turned off anyway, although the command to do so still fires ok. This would explain why the attribute is is not in metadata or the create table pages of the docs.

[Updated on: Thu, 28 September 2017 08:21]

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665883 is a reply to message #665882]

Thu, 28 September 2017 08:25

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Roachcoach,
I verified it has the attribute:

Code: [Select all] [Show/ hide]

SQL> select monitoring from user_tables where table_name ='T';

MON
---
YES

Still, I cannot see changes done since the last dbms_stats...

Code: [Select all] [Show/ hide]

SQL> insert into t select * from dba_objects where rownum < 11;

10 rows created.

SQL> commit;

Commit complete.

SQL> insert into t select * from dba_objects where rownum < 100;

99 rows created.

SQL> commit;

Commit complete.

SQL> delete from t;

109 rows deleted.

SQL> select * from user_tab_modifications where table_name ='T';

no rows selected

SQL>

Then, I tried to perform the flush_monitoring_info thing and it worked!

Code: [Select all] [Show/ hide]

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where table_name ='T';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- --------- ---
DROP_SEGMENTS
-------------
T
                                      119          0     150312 28-SEP-17 NO
            0

MANY MANY THANKS !

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665885 is a reply to message #665883]

Thu, 28 September 2017 08:28

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

No worries.

If you don't flush the monitoring, it will get there, just on its own timescale. They delay the population deliberately for performance reasons Smile

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665886 is a reply to message #665883]

Thu, 28 September 2017 08:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Andrey_R wrote on Thu, 28 September 2017 06:25

MANY MANY THANKS !

So what will you do differently with your new knowledge?

Report message to a moderator

Re: user_tab_modifications not showing data for new table [message #665888 is a reply to message #665886]

Thu, 28 September 2017 11:12

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

BlackSwan wrote on Thu, 28 September 2017 16:36

Andrey_R wrote on Thu, 28 September 2017 06:25

MANY MANY THANKS !
So what will you do differently with your new knowledge?

I don't know yet. Happy to have another tool in my arsenal.

As for the immediate case - I helped a programmer to be able to monitor transactions in a place where the application failed. I warned him to use with cautious and not to do it on production environments (also needs permissions for it anyway ).

Regards,
Andrey

Report message to a moderator

Previous Topic:	Is there a way to run Oracle Universal Installer in english without resetting the system locale
Next Topic:	I need to create 2 tables for below data how to map between the data values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 03:49:39 CDT 2025