RE: Weirdness with dba_tab_modifications
Date: Wed, 7 Oct 2015 16:27:30 +0000
Message-ID: <c8de4c22a93048ec98cb9ea987cb9242_at_wpvl1dag02.hcrec.com>
No, all queries were run by me in same session, logged in as SYS.
Oracle 11.2.0.4 on Solaris 10
From: Ram Raman [mailto:veeeraman_at_gmail.com]
Sent: Wednesday, October 07, 2015 11:19 AM
To: Matt Adams
Cc: ORACLE-L
Subject: Re: Weirdness with dba_tab_modifications
Could you be logged in as two different users/databases for those two queries?
On Wed, Oct 7, 2015 at 9:56 AM, Matt Adams <MAdams_at_troversolutions.com<mailto:MAdams_at_troversolutions.com>> wrote: I’m investigating several foreign keys that do not appear to have corresponding indexes on them. During the course of the investigation, I started querying DBA_TAB_MODIFICATIONS.
First I ran the following:
SQL> select * from dba_tab_modifications where table_owner = 'HCSC_OWNER' and table_name = 'BILL'
/
2
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME SUBPARTITION_NAME INSERTS
------------------------------ ------------------------------ ----------
UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- ----------- --- -------------
HCSC_OWNER BILL
139434
130149 15329 06-OCT-2015 NO 0
Now, since we have (supposedly) identical schemas for various reasons, I wanted to see where else this table might exist and how busy they might be.
SQL> select * from dba_tab_modifications where table_name = 'BILL' SQL> / no rows selected
I don’t understand how the less restrictive query returns no rows.
When I switch both queries to use all_tab_modifications (rather than dba_tab_modifications), I get expected results…1 row for fist query, and 16 rows for less restrictive query. I’ve retyped the queries by hand several times, so I’m sure that I don’t have some weird invisible control character in there.
Has anybody seen this before?
Matt Adams
madams_at_troversolutions.com<mailto:madams_at_troversolutions.com>
- This communication may contain privileged and/or confidential information. If you are not the intended recipient, you are hereby notified that disclosing, copying, or distributing of the contents is strictly prohibited. If you have received this message in error, please contact the sender immediately and destroy any copies of this document. ****
--
- This communication may contain privileged and/or confidential information. If you are not the intended recipient, you are hereby notified that disclosing, copying, or distributing of the contents is strictly prohibited. If you have received this message in error, please contact the sender immediately and destroy any copies of this document. ****
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 07 2015 - 18:27:30 CEST