Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Splitting a string using Regular Expressions and table cast multiset

767233Aug 17 2010 — edited Aug 18 2010
Hi there,

I asked a similar question in the past, but can somebody help me with this?

I have a a couple of fields that look like this:

col A
-------
dfd223/aaa333
fffs233/aaa2d2-dfa343/dfe3f3
asdf234/sdafd1234-dfadf234/dfa12 - asdf34/daf233

and I want to make it into something like this:

col a | col b
------------------
dfd223 | aaa333
fffs233| aaa2d2
dfa343 | dfe3f3
asdf234 | sdafd1234
dfadf234 | dfa12
asdf23 | daf233

Can somebody give me a solution and small tutorial on how to do this via regular expressions and hierarchical queries. I tried browsing through tutorials online but it's really really confusing me so if somebody can use this as an example to help me but would be REALLY appreciated. Running Oracle 11gr1 as well, if that matters...

Thanks! =)

Comments

Aman....
888442 wrote:
Hi,

How can find about the queries which are currently running in my database (11.1.0.7)

I was using v$session_longops, but I learnt that some of the activities are not recorded in this.

v$sql and v$sqlarea list the queries which are not running as well.
Important question is that why you want to know this? There would be many queries that you may have fired but there would MANY that oracle internally would have fired so what you are going to do by looking at such a massive info?

Aman....
Ranjith
Hi,

select sesion.sid,
sql_text
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sqltext.piece;

Use this query to find the running query in all active sessions.

Thanks,
Ranjith
Lubiez Jean-Valentin
Hello,


The following query may give you the list of running SQL ( SQL_ID ) from Active Sessions: 
select inst_id, program, module, SQL_ID, machine
from gv$session
where type!='BACKGROUND'
and status='ACTIVE' 
and sql_id is not null;
Then, from other Views like v$sqltext you may have the SQL statement for the SQL_ID you got with the above query.


Hope this help.
Best regards,
Jean-Valentin
VenkatB
>
How can find about the queries which are currently running in my database (11.1.0.7)

I was using v$session_longops, but I learnt that some of the activities are not recorded in this.

v$sql and v$sqlarea list the queries which are not running as well.
>

What version are you on? That's important to know before you can join V$session with v$sql. SQL_ID has come to v$session only from 10gR1, I think. Otherwise you may have to use sql_address.

Try this for 10g and above
select a.sid, a.username,b.sql_id, b.sql_fulltext from v$session a, v$sql b
where a.sql_id = b.sql_id and a.status = 'ACTIVE' and a.username != 'SYS';
Regards
Venkat

Edited by: VenkatB on Feb 28, 2012 9:31 AM
Nikolay Savvinov
Hi,

V$SESSION_LONGOPS contains information about long operations (as the name suggests), not about all running SQL. An operation needs to take 6s or more to make it into V$SESSION_LONGOPS.

Just query V$SESSION, making sure that you only look at active sessions.

Best regards,
Nikolay
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 15 2010
Added on Aug 17 2010
4 comments
1,359 views