Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: August 05, 2008 - 1:51 pm UTC

Last updated: April 27, 2021 - 5:53 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I've been consulting your site for years, and its almost always a source of answers to just about all Oracle issues I encounter. On rare occasions I might encounter an issue not previously discussed, but there's an uncertainty how long I would have to wait to by chance see that "Submit a Question" button - and I'm not one who would disguise a new question in a "review" of something loosely related.

Soooo - may I offer a suggestion for your site for the benefit of your many fans who wait patiently for their turn? How about some form of "Take a Number"? If I would like to get in line to submit a question it would be nice to have a queue to get into, and thus have some idea where I am in the queue. Perhaps a "Take a Number" button would return to us a sequence number and a randomized key string. Then as your time permits you might post a range of sequence numbers that you would be willing to accept any given day. On my end if I see the open number range encompass my number, then I could reply with my number and key string, which would in turn enable the "Submit a Question" button for me.

Is something like this feasible?

Thanks for your valuable time and service to us.

John

and Tom said...

it is technically feasible, but given (some) human nature, I don't see it being practical.

Some people would hoard numbers.. So, we'd limit one ticket per email account.

Then I could see people spending an afternoon setting up email accounts, or using mailinator, or whatever and gathering as many 'tickets' as they could (eg: sort of like the people that buy up all of the concert tickets and hoard them)

Or, I'd send out an "ok, go ahead" email which would be ignored - I'd never get the question.


so, the idea is feasible (it could be implemented) but I think it would be unworkable.

Rating

  (33 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about E-Mail notofication

Balaji Chellappa, August 06, 2008 - 6:27 pm UTC

Tom,
How about this?
When you are not accepting the questions, still you can accept our E-mail address and when you are open for questions you can notify us. If you prefer first come first serve basis, you can delay the notification by 5 minutes after opening it for the questions (One per person per ip address to avoid duplicate email submission). You can clear the E-Mail list once the mail is out and I have to register again if I miss my turn. Another option is, pick 3 or 4 users randomly from the registered list and give them a chance to ask the question (You can impose rules something like, invitation will expire if it not used in 2 days).

I am just trying to help a person who is deliberate to post a question with out hitting your site every 10 minutes.

I am not a frequent visitor of your site ( may 3 - 4 times per week)but in my life time I saw you accepting questions twice and utilized it once.

Thanks
Balaji
Tom Kyte
August 07, 2008 - 12:26 pm UTC

... Then I could see people spending an afternoon setting up email accounts, or using mailinator, or whatever and gathering as many 'tickets' as they could (eg: sort of like the people that buy up all of the concert tickets and hoard them) ....



Re: Your first followup to John's suggestion

A reader, August 07, 2008 - 11:54 am UTC

"Some people would hoard numbers.. So, we'd limit one ticket per email account.

Then I could see people spending an afternoon setting up email accounts, or using mailinator, or whatever and gathering as many 'tickets' as they could"

Tom
I would like to add to John's suggestion. At the time of generating tickets, you can capture their IP_ADDRESS and then restrict him/her to get another 'ticket' on the same day (and from the same IP Address).



Tom Kyte
August 07, 2008 - 1:35 pm UTC

one word:

proxy


most real users of this site sit behind a proxy server at a company, they all look the same to me.

or dhcp - they all look different

or any one of the thousands of anonymous proxies out there you could use to mask your ip address


I've thought about this, there is no good way to queue them up, and do you know how frightening it would be to see that there are "N thousand requests in the queue" for me....

No tickets

A reader, August 07, 2008 - 1:11 pm UTC

I say keep the current system. Tom answers all questions "for free" so at least let him decide when he'll take new questions. Also, there are so many threads on the site I'd be surprised anyone has a totally brand new question that requires a new thread. More often than not I find a thread related to a question I have so I just write a review and Tom usually answers in a reasonable amount of time.

So, my vote goes for keeping the status quo.




Where to Submit a Question???

Ed Girard, August 07, 2008 - 1:19 pm UTC


On July 25, 2008 I responded within a "Hierarchical query" thread with a question titled "Removing Duplicates for Multiple Parents in a Hierarchical Query". While I realize technically it's a new question, it was still on topic within the thread and as we all know, it's common for other's to continue threads with new questions. In any case, my question was never answered.

On Sunday evening, August 3rd - 10 days removed from my original posting - your site (or, rather, you) allowed me to add a new question to which I reposted this same question from that previous thread. Since then, you've responded to numerous questions yet my question has not been answered and, in fact, no longer exists on Ask Tom.

What gives?

Did I break protocol somewhere? Is there something wrong with my question? Is there no answer to it? In the past 6-7 years this was my 3rd question on Ask Tom. Am I past my quota?

Thank you.

Tom Kyte
August 07, 2008 - 1:44 pm UTC

I haven't done it yet. It will require some amount of thought to get my head around, I'm not sure I can answer it in sql.

It is a hierarchy that is NOT a hierarchy - you envision it as such, but the data is not really a hierarchy at all

In fact, I'm pretty sure that I won't be able to get my head around it, that I won't have an answer.

that happens from time to time. Not every thing is doable.


I have two things sitting in there right now, out of 40 over the last few days.


limit the workload for Tom

A reader, August 07, 2008 - 2:17 pm UTC

Tome is ONLY one guy w/o pay to answer our questions although promoting techs in ORACLE world. It helps us to do our jobs in a better way, it benefits all of us in ORACLE world.
One tip: prepare your questions well (off line), then keep visit the site, and submit (copy and paste) it as long as the door is open, it works for me pretty well.

New questions submitted as "Reviews"

John, August 07, 2008 - 11:28 pm UTC

Ed from Dallas - you pretty much summed up the general problem I was attempting to address when you said "...as we all know, it's common for other's to continue threads with new questions."

Common perhaps, and Tom has tolerated it all of these years, but that doesn't make it right. A "review" ought to refer directly or indirectly to the original question that initiated the thread. Just because there exists a thread titled "SQL Query" (in fact there are at least 16 of them) doesn't invite anyone who might have a challenging SQL query of their own to dump the problem on Tom through the backdoor disguised as a "review".

This is not only disrespectful of Tom's valuable time, this practice also tends to cause many threads to wander way off topic over time. Perhaps if Tom weren't so burdened with these back-door "new questions" he might be able to put out the welcome mat at the front door more frequently.

I'll continue to wait patiently for my turn to "Submit a Question".

Scott, August 08, 2008 - 8:53 am UTC

If it gives Tom more time to post on his blog, I'll go with the current setup. That being said, his blog has been nie on dead for weeks, so maybe the status quo needs changing!

Hint, hint!

Submitting Questions

Ed, August 08, 2008 - 3:16 pm UTC

apologize for the perceived tone on the post I made to you a few days ago (in Submitting Questions). After reading it again today even I was upset - it was not at all what I had intended.

I really believed I had broken some kind of question submission protocol on your site and wanted to be sure (from you) not to do it again and to learn the proper (question asking) protocol.

You have the #1 Oracle Tech site available primarily because of your incredible Oracle skill set and unselfish attitude. Your efforts are greatly appreciated.

My sincerest apologies if I offended you in any way...

Jakub, September 26, 2008 - 7:23 am UTC

My suggestion is to limit the time a question can be repeatedly reviewed and commented by other people and also to allow Tom to "close" a question if all the importing things have been said. I think the repeated followups, which quite often have very little in common with the original question, are the biggest misuse of the site. Also they significantly decrease the usability of the site and number of new questions that can be asked.

Thanks,
Jakub

searching the missing records from a sequence of 10000 records in a table

Sunil Kumar Sahoo, September 26, 2008 - 9:03 am UTC

I have a table having more than 80,000 records in that table. Those records are created by a sequence. So all the records are consecutive in nature means 1,2,3.... so on.

But there is some records missing eg(1,2,3,5,6,7) 4th record is missing. I want to identify which records are missing from those 80,000 records.

SO please give me the solution for this type of problem

Find the record count in merge statement

Sanjairaj Ramasamy, July 28, 2011 - 12:04 am UTC

Hi Tom,
I want to find the records counts after executing the MERGE statment. How many records updated and how many records inserted. i need sepate counts.

With Advance Thanks ,
Sanjairaj Ramasamy.
Tom Kyte
July 28, 2011 - 7:15 pm UTC

you will not be able to get that. We only report rows affected.

RE: insert/update record counts in MERGE statement

Duke Ganote, July 29, 2011 - 3:20 pm UTC

Already demonstrated: Oracle doesn't provide it, nor is it easily captured...
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:25733900083512#26244765630703

imp review

suresh, September 17, 2012 - 12:38 am UTC

1. what is the new feature in srvctl .


2.what is the life cycle in srvctl.

3.what is the 11g feature that allows to shut down all 10 database in 10 node rac on 11g grid in one shot(in one command)
for example i have 20 databases in 10 node rac on 11g grid ,i want to shut down all the 20 databases in one command.what is that command?

srvctl commands for shut down

suresh, September 17, 2012 - 12:48 am UTC

3.what is the 11g feature that allows to shut down all 10 database in 10 node rac on 11g grid in one shot(in one command).
for example i have 20 databases in 10 node rac on 11g grid ,i want to shut down all the 20 databases in one command.what is that command?

I have a doubt

GG, August 29, 2013 - 12:59 pm UTC

Hi guys,

I have question which is very useful.

how to find a table name by using column value? please dont consider it as a column name it is a column value.
for example column value "smith" is feeded in database and we dont know the table name and column name for that particular column value "smith" then how to find the table name where "smith" is feeded.

help me with this.

query caused no data to be returned

karim kashef, December 22, 2013 - 1:32 pm UTC

During running Call Center Interaction History Report on Oracle Discoverer Web message appeared the query caused no data to be returned.
When we work with this user on another computer it's working normally and when we run with another users on the same computer appeared the same message.

I need how to solve this problem with this computer with Oracle Discoverer Web .

Kindly clarify my concern

A reader, January 30, 2014 - 6:09 am UTC

Hi Tom,

This site is good source of knowledge related to oracle. It is very helpful. Sorry if i am posting the question in wrong window because i am new and i dont know how to post a question.

My question is that how we should use bind variables in PL SQL function. I am confused because i have gone through many links in google that say that bind variable are defined with colon [:job ]. Please let me know my following PL SQL function is with use of bind variable or not.

FUNCTION spDoSomething(
inArg in varchar2,
outCursor out CURSOR_REF
)
return number
IS
BEGIN
open outCursor for
select * from table t1 where t1.Column1 = inArg;
return 1;
END spGetOwnIMDs;

how to estimate size of SGA and PGA

Amr Shaban, October 08, 2014 - 4:43 am UTC

hi Tom,

Can you give us an idea as to how to estimate size of SGA and PGA,it is work with Win2008 Server,system has 32GB physical memory?
that the best way to estimate size for SGA is to set aside 40%
that the best way to estimate size for PGA is to set aside 30%
can i justify this to use it

sumit sinha, July 30, 2015 - 12:35 pm UTC

Because i want to ask one question but i can't find out how to ask a question.please help me.

Mrutyunjay Nayak, December 02, 2015 - 5:43 pm UTC

Hi Tom,
Your answers of any questions are very precious to a beginner. But I am very regret that so many times I ask question.but I didn't get any answer.Could u please suggest me that any other way to contact u.
Chris Saxon
December 03, 2015 - 2:05 am UTC

Log a question. We answer them. Thats how it works.

Mrutyunjay Nayak, December 02, 2015 - 5:45 pm UTC

Hi Tom,
Your answers of any questions are very precious to a beginner. But I am very regret that so many times I ask question.but I didn't get any answer.Could u please suggest me that any other way to contact u.create table

Chris Saxon
December 03, 2015 - 2:06 am UTC

Log a question. We answer them. Thats how it works

Create table

Mrutyunjay Nayak, December 02, 2015 - 5:47 pm UTC

Hi Tom,
Your answers of any questions are very precious to a beginner. But I am very regret that so many times I ask question.but I didn't get any answer.Could u please suggest me that any other way to contact u.create table

Chris Saxon
December 03, 2015 - 2:06 am UTC

Log a question. We answer them. Thats how it works

Create table

Mrutyunjay Nayak, December 02, 2015 - 5:48 pm UTC

Hi Tom,
Your answers of any questions are very precious to a beginner. But I am very regret that so many times I ask question.but I didn't get any answer.Could u please suggest me that any other way to contact u.create table

Chris Saxon
December 03, 2015 - 2:06 am UTC

Log a question. We answer them. Thats how it works

Are you not accepting questions?

Randy Strauss, October 13, 2017 - 8:31 pm UTC

I go to asktom.oracle.com and I can search for questions, but how do I ask one?

If you're not accepting questions, it'd be nice to know that- I had to search and search and finally found this page that says you don't always accept questions.

I suggest you add an Ask button, and have it either enabled or gray, saying you're not accepting questions.
thx
Connor McDonald
October 14, 2017 - 5:10 am UTC

Thanks for the feedback. AskTOM regulars will know that we either have the "Ask" button, or a message in its place.

But yes, I'll look at making it more obvious.

Enable Ask Button

A reader, October 14, 2017 - 7:48 am UTC

Thank ASKTOM Team, To provide this service for all oracle users. Let me suggest one thing to your Team and this is my opinion to enable "ASK" Button. because sometime when problem face or some question arise in mind so when i visit your site, it doesn't accept an question. So When you enable button , you provide one message to user like your question is accepted but we are so much busy.
Connor McDonald
October 15, 2017 - 1:18 pm UTC

Thanks for your feedback, but I'm not sure how accepting more questions to the queue helps things.

We obviously want to get to everyone's questions in a timely manner, but if we just took questions constantly, we might end up with questions either taking weeks or months to get to, or simply never getting answered, ie, an always growing queue.

That doesn't help anyone.

Sql

Alok verma, March 13, 2018 - 12:07 pm UTC

Hi tom,
How to display output like this
Sno empno
1 7369
1 7902
1 7000
1 7001
1 7002
2 7003
2 7004
2 7005
2 7006
2 7007
3 7008
. .
. .
. .
. .
.continue
Connor McDonald
March 14, 2018 - 6:30 am UTC

Um....no idea what "sno" is, and what rule you are applying.

I'll take a guess at every 5 rows, ordered by (at a guess) name.

SQL> select 1+trunc((row_number() over ( order by ename)-1)/5) r, empno
  2  from scott.emp;

         R      EMPNO
---------- ----------
         1       7876
         1       7499
         1       7698
         1       7782
         1       7902
         2       7900
         2       7566
         2       7839
         2       7654
         2       7934
         3       7788
         3       7369
         3       7844
         3       7521



SGA & PGA

Dipak Pradhan, March 23, 2018 - 8:54 pm UTC

Q.if my SGA Memory size is 5GB and my PGA is 2GB and temp tablespace 3 GB my table size is 15GB, When i select the 15GB table (suppose table name sales1(select * from emp.sales1) How will data fetch and it will keep for displaying of result .How it will fit into the buffer cache.
Connor McDonald
March 25, 2018 - 11:26 am UTC

To scan a large table, most likely *none* of it will go into the buffer cache. It doesn't really make sense to because the likelihood of anyone else needing all that data is slow.

So we'll do a "direct read", ie, from disk direct to the session that is issuing the query.

Restore of database

Shararat khan, September 01, 2019 - 4:34 pm UTC

I have only archive file
So can i restore my database

pl/sql

A reader, March 24, 2020 - 1:48 am UTC

how select single record and multiple record in stored procedure without using any variable for example emp table is there in mysql
create or replace procedure foo
as
select * from emp;
as simple as this is there any simple way like this.

pl/sql

teklehaimanot, March 24, 2020 - 1:52 am UTC

in mysql we can return one or multiple records from a table like this

create procedure foo
as
select * from employee;
is there any way in oracle procedure to return one or more records without using variables like in MySQL please let me know. thanks for your assistance.

pl/sql

teklehaimanot, March 24, 2020 - 1:57 am UTC

i want to use bio metrics(fingerprint and eye) which bio metric both eye and fingerprint device best suitable for oracle and oracle support this both eye and fingerprint data ? if yes how to store them in oracle database i have thesis project to do.

Love it

Kadyn silva, November 13, 2020 - 5:32 pm UTC

OMG, I love this channel you should go on tv I remember my daughter came home from school one day and watched askTOM on "mystery science" BTW I watched it even if my daughter is not home!!
Connor McDonald
November 16, 2020 - 2:00 am UTC

write a sql query interpolation logic tenor basis ?

bisal kumar, April 21, 2021 - 4:29 pm UTC

create table temp
(
SLABEL VARCHAR2(10),
HDATE DATE,
TBPREC Number(15,5),
ORIGINAL_TENOR Number(10),
RESET_TENOR Number(10));

insert into temp (SLABEL,HDATE,TBPREC,ORIGINAL_TENOR,RESET_TENOR)
VALUES('USD','03-MAR-2021',0.128592,0,1);

insert into temp (SLABEL,HDATE,TBPREC,ORIGINAL_TENOR,RESET_TENOR)
VALUES('USD','03-MAR-2021',0.1792420,0,30);

insert into temp (SLABEL,HDATE,TBPREC,ORIGINAL_TENOR,RESET_TENOR)
VALUES('USD','03-MAR-2021',0.1803930,0,60);

output
----------------------------------------------------------------------
SLABEL |HDATE |TBPERC |ORIGINAL_TENOR |RESET_TENOR
USD 03-MAR-2021 0.128592 0 1
USD 03-MAR-2021 0.133657 0.1 2
USD 03-MAR-2021 0.138722 0.2 3
USD 03-MAR-2021 0.143787 0.3 4
USD 03-MAR-2021 0.148852 0.4 5
USD 03-MAR-2021 0.153917 0.5 6
USD 03-MAR-2021 0.158982 0.6 7
USD 03-MAR-2021 0.164047 0.7 8
USD 03-MAR-2021 0.169112 0.8 9
USD 03-MAR-2021 0.174177 0.9 10
USD 03-MAR-2021 0.1792420 1 30

Formula
=$D$2+($D$12-D2)*((E3-$E$2)/($E$12-$E$2))

write a sql query interpolation logic in orginal tenor basis and reset tenor basis.
1day=3days
10 day=30days



Connor McDonald
April 27, 2021 - 5:53 am UTC

If you can explain what D and E are in context of the data, then maybe we can help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database