Clearly an excellent point. Even if you do create views on X$ within the limits
of subsetting v$ view references, you would need to either check the patch or
upgrade result’s possible changes to X$, then drop and (possibly) recreate any
views referencing X$ changes before the upgrade of any non-sandbox database.
Packaging the queries up in PL/SQL or SQL scripts as Tanel recommended saves
you from this administrative overhead. +1 on Jared’s note, too. Even though I’d
call it a bug that any view cannot be dropped because of a change to an
underlying object, it is still a problem you can avoid by policy.
Oracle changes or drops v$ views when underlying x$ references change. If you
make one, you have to do the same. You might only be able to do that before the
change. Enumerating your references in your specific code is a valid
alternative, sort of like Cobol copy books for data definition.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Hameed, Amir
Sent: Wednesday, August 26, 2015 9:54 PM
To: tanel@xxxxxxxxxxxxxx; Mark W. Farnham
Cc: rjoralist3@xxxxxxxxxxxxxxxxxxxxx; Oracle-L Freelists
Subject: RE: Using x$ tables in production
“One example from past: Jared Still once reported an ORA-600 caused by an user
view on a X$ that went invalid after an upgrade (as the X$ structure had
changed). This view couldn't be dropped after an upgrade (ORA-600):”
I ran into the same issue back in the 9i days after we upgraded to 10gR2. So,
creating views on top of X$ is risky.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tanel Poder
Sent: Wednesday, August 26, 2015 9:39 PM
To: Mark W. Farnham
Cc: rjoralist3@xxxxxxxxxxxxxxxxxxxxx; Oracle-L Freelists
Subject: Re: Using x$ tables in production
Agreed with what others have said ... make sure you know what an X$ does (and
what is it supposed to show you) before querying it in production. I wouldn't
build it into regular applications (only performance & troubleshooting stuff
when can't do without X$).
I would not create any "user" views on sys.X$ stuff either to not operate too
far out at the edge of library cache dependency management world.
One example from past: Jared Still once reported an ORA-600 caused by an user
view on a X$ that went invalid after an upgrade (as the X$ structure had
changed). This view couldn't be dropped after an upgrade (ORA-600):
//www.freelists.org/post/oracle-l/8174-upgrade-10g-or-11,4
I have written PL/SQL stored procedures in past for monitoring X$ for some very
specific issue (like the shared pool monitor:
http://blog.tanelpoder.com/files/scripts/tools/collectors/shared_pool_monitor/
). You'd install the procedure as SYS (so it can query X$ without any magic
views) and once enough diagnostic data is gathered (and problem solved) you'd
drop the procedure.
Tanel.
http://blog.tanelpoder.com
On Tue, Aug 25, 2015 at 11:52 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
precisely. That is why I bothered to write:
If you go beyond view contents of existing v$ view you expose (or to users that
currently don’t get v$ view access), then you have entered the “tread carefully
and it is all your own fault” zone.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Rich J
Sent: Tuesday, August 25, 2015 2:26 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Using x$ tables in production
On 2015/08/25 12:32, Mark W. Farnham wrote:
So, my advice is avoid this unless you need to for a reason, and my evidence
that it does not necessarily cause harm per se is that x$ tables are referenced
and exposed via some v$ views already.
Viewing V$ views is unlikely to cause the symptoms of an instance hang. X$
views are a different beast. Some, like X$KSMSP, have the ability to cause
severe issues (info courtesy of Tanel's blog).
Tread very carefully.
Rich
