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

1h
comment Creating Mview in Oracle is throwing error
What is the error? Why are you creating the materialized view on a prebuilt table? Where is the prebuilt table?
22h
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
18
comment Fetching data from View with more number of columns
It takes more time compared to what? What is the query plan? Are you fetching all the data from each table? Or does your actual query include predicates that cause you to select a subset of the data? Do indexes exist to support those predicates? Are those indexes being used?
May
17
comment Oracle: starter database?
What do you mean by "starter database"? Are you referring to one of the sample databases that ship with Oracle (hr, sh, etc.)? Are you asking whether a database has been created rather than just having the database software installed? Or something else?
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
25
comment how can you determine the exact time for a daily Scheduled Jobs on Oracle
In general, you can't. job_queue_processes controls the number of jobs that can be running simultaneously. It is possible that there would be too many other jobs running when your job was scheduled to run which would force your job to be postponed until some other job finished. The scheduled time and the actual start time may also differ by a few seconds simply because Oracle doesn't necessarily wake up and check for jobs to run at the exact moment you scheduled the job to run.
Apr
25
comment how can you determine the exact time for a daily Scheduled Jobs on Oracle
Are you asking how to determine when the job is scheduled to run? Or when it will actually run? Is it scheduled using dbms_job? dbms_scheduler? Something else?