Reputation
13,350
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 23 37
Newest
 Enlightened
Impact
~690k people reached

May
24
comment When Should You Shrink Your Database
Specifying the database you're using, whether these staging tables are going to fill up again in the future, and exactly what you mean by "shrink" (i.e. the command you'd want to run) would be helpful.
May
18
comment Use of temporary table variable inside a stored procedure
I don't understand what you mean by "@ symbol works". An Oracle nonquoted identifier cannot begin with or contain an @ sign (well a database link name can contain but not begin with an @ though I can't imagine why you'd want to do so) docs.oracle.com/cd/B19306_01/server.102/b14200/…
May
18
comment Use of temporary table variable inside a stored procedure
If you are really using Oracle rather than SQL Server, you wouldn't prefix variable names with @, you wouldn't have a declare there, there is no such thing as a table variable, and you can't have a procedure that just does a SELECT without either selecting data into a scalar variable (or variables) or into a collection or opening a cursor that can be used or returned to the caller. Since you're not manipulating data, it would also not make sense to create a procedure rather than a function. Possibly, you want a function that returns a sys_refcursor but that's a wild guess.
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