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)
user_tab_modifications not showing data for new table [message #665879] Thu, 28 September 2017 08:03 Go to next message
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:

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:


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

10 rows created.

SQL> commit;

Commit complete.

SQL>


3. Gather Statistics:


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

PL/SQL procedure successfully completed.

4. Run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO on it:

SQL>   exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

And it won't appear Sad :

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
Re: user_tab_modifications not showing data for new table [message #665880 is a reply to message #665879] Thu, 28 September 2017 08:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
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:

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 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Roachcoach,
I verified it has the attribute:

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

MON
---
YES

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

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!

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 !
Re: user_tab_modifications not showing data for new table [message #665885 is a reply to message #665883] Thu, 28 September 2017 08:28 Go to previous messageGo to next message
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
Re: user_tab_modifications not showing data for new table [message #665886 is a reply to message #665883] Thu, 28 September 2017 08:36 Go to previous messageGo to next message
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?
Re: user_tab_modifications not showing data for new table [message #665888 is a reply to message #665886] Thu, 28 September 2017 11:12 Go to previous message
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

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:
  


Current Time: Thu Mar 28 06:45:04 CDT 2024