Reputation
13,910
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 24 39
Newest
 Nice Answer
Impact
~746k people reached

May
14
answered Oracle paging query performance
May
11
comment How oracle syncs data across nodes in a cluster
You've posted a good answer if the original poster was using MySQL. Everything in the question, though, appears to indicate that the original poster is using the Oracle database.
May
7
comment wm_concat in a specific order
It's not clear whether you're saying that that query has an ORDER BY clause or not. If the inner query is ordered, you're probably out of luck. wm_concat is an undocumented function so you don't have a lot of options if it's not working the way you want.
May
7
comment I want to pivot a table and aggregate a column into strings
In the question I linked to, there is a link to a number of different string aggregation techniques. Pick one.
May
7
comment I want to pivot a table and aggregate a column into strings
Can you define "not working"? What output are you getting for the sample data you provided (I assume in your sample data, patient b's rank 2 row should be a 0 not a 1-- otherwise I'm not sure why the desired string would be 10 rather than 11).
May
7
comment I want to pivot a table and aggregate a column into strings
If the number of columns that you wanted isn't known at compile time, you can't use a pivot at least not in a static SQL statement. You could use dynamic SQL to construct the SQL statement at runtime but that would require running a query first to get the max(rank) and then constructing a SQL statement with that number of output columns. Returning a single string would be easier.
May
7
comment I want to pivot a table and aggregate a column into strings
select * from v$version will tell you the version. Are you guaranteed that the rank will never exceed 4?
May
7
comment I want to pivot a table and aggregate a column into strings
What version of Oracle? The pivot operator is relatively new. If you need to support older versions of Oracle, you'll need to use the old max(case trick.
May
6
comment Removing locks in Oracle without being admin
You can't get there from here. A lock will only be released when the transaction holding it terminates (either successfully committing or rolling back). The only way to force another session to immediately terminate its transaction is to kill it. You don't have privileges to kill the session so you'd need to escalate the issue to someone who does. Are you sure that you have a problem, though? I can't see the image you posted so it's not clear if the sessions are actually blocking others rather than simply holding a lock.
Apr
30
awarded  Enlightened
Apr
30
awarded  Nice Answer
Apr
25
comment How to track DML and DDL daily events
You realize that all_tab_modifications is not a real-time accurate count of the number of modifications to a table since the last time statistics were gathered, right? The counts are approximate (though generally pretty accurate) and the data is written periodically in batch. It would seem very odd to want to get information like whether the last change was an insert or an update from a view that may not reflect a change to the table that was made half an hour ago.
Apr
24
comment How to track DML and DDL daily events
If you want to know what data changed in the table, you'd need to enable auditing or create triggers to track the changes depending on what exactly you're looking for.
Apr
24
comment How to track DML and DDL daily events
What would it mean to you to report on changes to an index? An index is defined on a table so any change to an indexed column of the table would necessitate index maintenance operations. Those operations might affect the index entries of many different rows in the table (a 50/50 split of a leaf block for example). If all_tab_modifications is giving you the information you want about tables, why do you list tables again in your list of objects you're looking for more information about?
Apr
22
comment How to exceute view without DBLink in oracle server
You don't. I assume you are saying that you have a client application (i.e. SQL Developer) that can connect to each database. If there is no database link, you can't join data between the two short of writing client-side code that fetches all the data from each table and implements join-like logic on the client. That is generally very slow.
Apr
16
comment Multi Tenant Identity
Why would you want to? If you're using an auto_increment, you're generating a synthetic primary key. By definition, that means that the key isn't meaningful for anything other than identifying the row. Why would you care if one tenant sees more than the expected number of gaps in the record_id for a single tenant if the record_id isn't meaningful?
Apr
11
answered is high cpu a bad thing
Mar
19
comment SQL - Optimize table population with dynamic queries
Are you saying that it will loop 1 million times each time inserting 10,000 rows so that it inserts a total of 10 billion rows? Or are you saying that it inserts a total of 1 million rows? Is there a reason that you're looping in the first place rather than simply dynamically constructing the entire INSERT SELECT statement? Do you need the interim commits?
Mar
12
comment Continuous replication between Oracle Database and Microsoft Azure SQL
Why do you believe that GoldenGate is deprecated? Oracle purchased the company and its direction in the future is based on GoldenGate technology (plus bits from Streams).
Mar
11
comment Stored Procedure result is not showing in Datagrid pane in Toad for Oracle
p_recordset is a local variable that goes out of scope as soon as the procedure returns. If you want an object that executes a query and returns a sys_refcursor you probably want a function not a procedure. If you really want to use a procedure, you'd need to add an OUT parameter of type sys_refcursor (or types.cursor_type if that is something meaningfully different).