Is there any way ?
Ravi, April 04, 2002 - 1:00 am UTC
Hi Tom,
I have a procedure that opens a cursor and loops through it and do some processing. It is taking too long to finish. Can we findout from any of the dynamic views, how many rows the cursor has selected and which row it is currently processing.
Thanks,
Ravi.
April 04, 2002 - 11:43 am UTC
You can call dbms_application_info (a supplied package, if you have my book -- i have a chapter on it, if not see the supplied packages guide)
Using that you can
o set client_info, module, action in v$session easily
o use session_longops to do a more detailed progress report.
Sagi, October 02, 2002 - 8:22 am UTC
Hi Tom!
I think you forgot about v$system_parameter.
I saw that the different between V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2. You explanation for V$PARAMETER & V$PARAMETER2 stands good here too.
But what is the difference between V$PARAMETER and V$SYSTEM_PARAMETER?
Thanx in advance.
REgards,
Sagi
October 02, 2002 - 10:36 am UTC
check out that nifty reference guide I refered to and quoted heaving from.
Amazingly -- it describes each and every single one of these magic v$ view (he says totally tongue in cheek)
dbms_application_info and parallel
David, February 11, 2003 - 10:51 am UTC
Tom,
I'm using dbms_application_info.set_module and I see this when I query from v$session in the module_name column. However, if a process is running in parallel, I don't see the module_name filled in for the parallel sessions. For example, we have 4 cpu's - I see my main session with the module name filled in, but the four sessions spawned off for parallel operations have null in the module_name column. Is there any way to have the module_name filled in for these other four sessions?
Thanks!
February 11, 2003 - 4:55 pm UTC
Nope, but you can use v$px_session to see who goes with who so you know the QC/slave relationship
How does Oracle do this ?
Adrian, May 04, 2005 - 4:19 am UTC
Tom,
There's a strange recursive relationship going on between V_$ views and their respective synonyms. An exercise in obfuscation by Oracle no doubt, but I'd be interested to know how this is being done...
SYS@ORA10 as SYSDBA> SELECT name, type, referenced_name, referenced_type FROM dba_dependencies WHERE name = 'V$RESERVED_WORDS';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
-------------------- ----------------- -------------------- -----------------
V$RESERVED_WORDS SYNONYM V_$RESERVED_WORDS VIEW
SYS@ORA10 as SYSDBA> SELECT name, type, referenced_name, referenced_type FROM dba_dependencies WHERE name = 'V_$RESERVED_WORDS';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
-------------------- ----------------- -------------------- -----------------
V_$RESERVED_WORDS VIEW V$RESERVED_WORDS VIEW
SYS@ORA10 as SYSDBA> SELECT object_type FROM dba_objects WHERE object_name = 'V$RESERVED_WORDS';
OBJECT_TYPE
-------------------
SYNONYM
SYS@ORA10 as SYSDBA> SELECT text FROM dba_views WHERE view_name = 'V_$RESERVED_WORDS';
TEXT
--------------------------------------------------------------------------------
select "KEYWORD","LENGTH" from v$reserved_words
There's something sneaky going on with the V$ synonyms - the second query against dependencies tells me it's a view...
Regards
Adrian
May 04, 2005 - 9:05 am UTC
v$ and x$ views are magic, they are somewhat "burned into" the definition of the database.
just consider them magic and done in a fashion you cannot replicate.
Just like SYS is magic (and not subject to the same rules as you and I.... things work different for SYS)
Just like DBA is magic (that role name is special, things happen just because you are in the DBA role)
some more dollars
Matthias Rogel, May 04, 2005 - 9:42 am UTC
$ strings $ORACLE_HOME/bin/oracle | grep -i reserved_words
GV$RESERVED_WORDS
V$RESERVED_WORDS
select KEYWORD, LENGTH from GV$RESERVED_WORDS where inst_id = USERENV('Instance')
question: if I would use a hex-editor and would replace
all occurrencies of RESERVED_WORDS in $ORACLE_HOME/bin/oracle
by RESERVED_WORMS
would my database still do its job after restart ?
(just for the sake of understanding how magic v$ and x$ views are)
May 04, 2005 - 10:15 am UTC
I would not expect it to work correctly, no.
You could have no reasonable expectations that it would work.
What is FLOB ?
Raghu, November 22, 2005 - 8:31 am UTC
Thomas, Greetings to you.
what is FLOB ?
it is available as part of the reserved words under v$reserved_words .
one ACE has posted this question in the forum.
you would be the right person to answer.
November 22, 2005 - 8:50 am UTC
it is nothing.
Not really used you just esquive a question
Yoann Mainguy, November 22, 2005 - 9:28 am UTC
November 22, 2005 - 10:25 am UTC
I'll clarify:
it is nothing -- to you and me it is nothing, it is at best something internal or something not yet implemented.
;)