| 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:
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  :
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 #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
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:
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:
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 #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:36Andrey_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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
