Skip to Main Content
  • Questions

  • Get pipe name by handle address in 19c

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Roman.

Asked: November 30, 2019 - 11:32 am UTC

Last updated: December 05, 2019 - 11:30 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi, TOM!

We had an Oracle Database 11g and a procedure that read pipes which cause pipe put event that blocks a session. In Oracle Database 11g we used x$kglob to get pipe name by handle mentioned in parameters of wait event. Then we upgraded to 19c and now we don't have x$kglob (I guess it disappeared after upgrade and it's mentioned in here Doc ID 361757.1).
So the question is how can I get pipe name by its handle in Oracle 19c?

Thanks in advance!

and Chris said...

There is no supported way to do this. And, as you've found, relying on undocumented features can lead to things failing unexpectedly.

I'm not clear exactly what you're trying to achieve here.

Tell us what it is you're want to happen and why. And we'll see if there's a supported way of doing this!

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Accessing x$kglob through v$db_object_cache

Mikhail Velikikh, December 05, 2019 - 12:38 pm UTC

I am still able to access views based on X$KGLOB in 19c. They might get invalid after the upgrade because of referencing missing columns, but they can be recreated and accessed afterwards.
Without resorting to X$KGLOB (which is obviously non-documented and should not be used without Oracle Support's blessing), we can use it indirectly using V$DB_OBJECT_CACHE:

SQL> select event, p1text, p1raw from v$session where sid=42;

EVENT P1TEXT P1RAW
------------------------------ ------------------------------ ----------------
pipe get handle address 000000006EEE3120

SQL> @pt "select * from v$db_object_cache where addr=hextoraw('000000006EEE3120')"
OWNER :
NAME : TEST_PIPE
DB_LINK :
NAMESPACE : PIPE
TYPE : PIPE
SHARABLE_MEM : 4032
LOADS : 1
EXECUTIONS : 0
LOCKS : 1
PINS : 0
KEPT : NO
CHILD_LATCH : 101303
INVALIDATIONS : 0
HASH_VALUE : 2881194935
LOCK_MODE : SHARED
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 2
PINNED_TOTAL : 2
PROPERTY :
FULL_HASH_VALUE : 1ae0600e16c71ad624ca44c0abbb8bb7
CON_ID : 3
CON_NAME : PDB
ADDR : 000000006EEE3120
EDITION :
-----------------

Thanks!

Roman Shiryaev, December 09, 2019 - 10:29 am UTC

Hi!

Thank you both TOM and Mikhail for a feedback!
Our goal is to find pipes that weren't read so they're causing pipe put events. And our job gets pipe name by addr and reads it.
I see no field `addr` in Oracle 11c view v$db_object_cache (it's our current version). But it exists in Oracle 19c version of view. So this is what I wanted to get.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database