Oracle what is dual

This answer needs improvement as it disagrees with itself. In one place it copies from the official docs: "The dual table is useful " and in another it recommends "It shouldn't be used in production, unless If you insert one, you will see the same behavior. Nick Chammas Hendrik Brummermann Hendrik Brummermann 1 1 gold badge 3 3 silver badges 15 15 bronze badges.

This means, for example, you can describe the table. Erwin Brandstetter k 18 18 gold badges silver badges bronze badges. Gary Gary 1, 12 12 silver badges 14 14 bronze badges. Tanel Poder Tanel Poder 2 2 bronze badges. This ability to omit many spaces is not unique to Oracle. Works the same in SQL Server. I know more than you I know more than you 71 1 1 silver badge 1 1 bronze badge.

An Oracle select statement always needs a "from"-clause. For what it's worth, it works in exactly the same way in MySQL. It contains following properties: It is owned by the sys user It is available for all the users It contains only one column which name is dummy with datatype Varchar2 1 , This column can have maximum width of one character.

Vipul Vipul 19 1 1 bronze badge. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta. Now live: A fully responsive profile. Related Hot Network Questions.

There must be posting guidelines! Thanks, Meyer. December 10, - pm UTC. I think the people that wrote that code have been using Oracle for a long long long time though. If dropped Dual table? One of my friend asked me that he has dropped dual table so now how to bring it back? December 20, - am UTC. Jerry, December 20, - pm UTC. December 20, - pm UTC. The friend is not in my Domain so I can't. Thanks again. Why this fails Any ideas where I am going wrong.

The shutdown was successful but the startup failed. Database dismounted. December 21, - am UTC. Dear Raju, Here Mr. Hi Tom, actually Raju tried the magic command to query dual table after closing the database. It is great. Thanks Tom. I am learning lot of things. Any links to what's coming April 13, - pm UTC. Has to be the same length?

April 16, - am UTC. July 15, - am UTC. Considering the amount of times Dual is being hit this may result in some not so insignificant performance gains. We don't really want to replace sys. September 05, - pm UTC. We use Oracle Apps, and there would be limitations on what we can do with their modules.

September 08, - am UTC. Dear sir, well and wish the same from you. I have a question regarding dual. How can we use the dual table to print 'Hello world' for 10 times?

Do you have a trick to do it? Thanks in advance Bye! December 28, - am UTC. Dear Sir, Thanks for your response. But I don't have access to oracle 8i or above. Do you have a trick which works under oracle 7. Please do reply. Thanks in advance. December 29, - am UTC. Mary, You can do this for getting the rows in 7. Is this okey Tom? December 30, - am UTC. Since this is an Oracle trivia type of question Any idea why Oracle has never done this?

On the bright side, at least there is a dual December 30, - pm UTC. I've never "worried about dual". I've worried about people doing silly things to it, updating a dictionary table directly and breaking the system. You should use dual in queries where they will force you to use FROM as well. Dear Tom and other reviewers, I don't know why you people get Annoyed. If you can do, Do it or find a way out. December 31, - am UTC. A reader, December 31, - am UTC.

Mary, we should be grateful to Tom for providing his expertise. He is doing a favor by providing solutions which you won't find on any websites. Guess I need to work on being more clear December 31, - pm UTC.

A reader, December 31, - pm UTC. I just love your strong wit and timing. I think this is what sets you apart as a truly great author as much as your technical brilliance. Great work!! This kind of topics helps us to know a lot of the secrets behind Oracle. I've never imagine that DUAL, beeing such a little thing, can make so many people to talk about it.

Are autotrace stats on SYS. DUAL for real? Doug, February 09, - pm UTC. Unfortunately, it does this A LOT I'll leave the "wisdom" of this strategy as a discussion for another day. In other words, is there something magic going on with SYS. DUAL that makes the autotrace stats misleading? Or did I just become a hero by saving the database a whole lot of unnecessary buffer gets and latches, and cpu, and February 09, - pm UTC.

If you do it - make sure you can back it out. March 03, - am UTC. Dear TOM, Mr. That's why I asked this question. Can it be done? March 04, - am UTC. He is wrong or you are mis-quoting him. Dual is nothing special really, just a one row, one column table.

A join vc, March 04, - am UTC. March 04, - pm UTC. Hi Tom, Whatever happened to the idea of a weekly challenge? Setting everyone a tricky little SQL problem based on the new stuff like analytics would be a good way for us all to learn.

Regards, Dave. March 26, - am UTC. Fair Enough! A reader, March 26, - am UTC. No problem, I often wonder how you fit all this stuff into 24 hours! The name! RE: The Name? Williams, March 26, - am UTC. Well, there are two of them Maybe Bruce Scott would know? Since this table has lost one row but kept its name. Dual Table Mark A. Williams, March 26, - pm UTC. Well, there you go Bob's your uncle and all that P, April 25, - pm UTC. Can you tell us more about Fast dual in 10g.

Is it an IOT? How can it manage with 0 LIO's? May 30, - pm UTC. If you don't select dummy that is. If you select sysdate from dual -- fast dual is used.

If you select dummy, sysdate from dual -- dual the table is used. Hi Tom i know the importance of the dual table could you give me some ideas on how to secure dual table? Regards Hatem. June 16, - pm UTC. Hatem, June 16, - pm UTC. Hi Tom are there a way to prvent dml,ddl on the dual table without affecting the application? Locking down dual. Bill, June 16, - pm UTC. The user asked if the dual table could be locked down and I told him, however I agree totally with you.

No one else, without exception should be allowed to touch it. I have followed this advise for 15 years as a DBA and I have never had a problem with the data directory being trashed. Also never give out the password to the system DBA account.

Normally Oracle. I have seen users do many silly things over the years and you have to make a database not only fool proof, but damn fool proof. Hi Tom my problem that i'm using oracle application Please Advice. June 17, - am UTC. Please help on this reader, June 17, - am UTC. I haven't read this before any where and I am not sure of it so will you please give your opinion on this and e. June 17, - pm UTC. I'm hoping that after reading this site for a while, people will be able to "show how approach A is better than approach B" spent quite a while on that topic, how to do that, in my book effective oracle by design as well.

It is the most important thing you can learn. It would be "not smart" if that was the way things actually worked. Dual is just a one row, one column physical table. I find it very odd that DUAL is supposedly named because it had "two rows" - any "oracle old timer" I've spoken to can not remember DUAL with anything except a single row. If memory serves, and no, I've no intention of revealing how many years have passed since I did a maths degree :- , the dual of the rows of a matrix was the columns of a matrix, and thus the special case of where the dual of matrix was equal to the matrix itself was a 1x1 matrix Conner, that sounds suspicious to me.

The dual or transpose of any diagonal matrix is equal to the original. Of course, it's only in the 1x1 case that the order of storage of the rows is irrelevant Dave, June 17, - pm UTC.

Ok, you guys are far too math-geeky. Stop it immediately. A reader, June 21, - pm UTC. Magic command. June 21, - pm UTC. Tom, I have created a sequence and am generating sequence numbers using dual table I am using this sequence to generate ids in an online application.

I have created a function that would return a sequence number. I have used tkprof and checked the values but am unable to come to a conclusion on this matter. July 18, - pm UTC. I would definitely a NOT use dynamic sql when static sql could be used. Thanks tom your advice was very helpful thanks once again. Hi tom, Thanks for the help You have mentioned to avoid the dual table Can you please explain what happens if we use the dual table.

Can you explain using tkprof or autotrace. July 19, - pm UTC. Why don't I get it in the first query? July 21, - am UTC. I'll hypothesize If i have 2 rows in dual table sql returns only 1 thru select query. August 06, - am UTC. Why is the cardinality so off? If dual is a special, magic table, shouldnt the CBO know that it always has 1 row? I guess if I gather system stats, it will get it right, is this recommended?

I am on 9iR2 Thanks. August 16, - pm UTC. In fact you can create your own table dual. Thanks a lot. August 17, - am UTC. A reader, August 17, - am UTC. So, would it make sense to gather stats only on SYS. Yes, I meant 'stats on SYS schema', not 'system stats', sorry for the confusion. Thats what I am trying to get from you. In your experience and knowledge, do you know that gathering stats on SYS has such-and-such benefits?

Of course, YMMV thats why we test. But what can I hope to get? In your queries - dual would never be used unless you yourself used dual. I would not get too uptight about this really. I mean really -- how often do you use dual in a complex multi-table query? Hi, From Java 1. When I replace it with some other table its working.

Im not familar with Java. Im trying to run an java application. Also when we tried on Oracle 8i it would. Nope, dual is there, you can log into sqlplus and see for yourself. Now I understand what is Dual table!! This is a very good explanation of Dual table. It was never meant to be seen itself, but instead used inside a view that was expected to be queried.

The name, DUAL, apt for the process of creating a pair of rows from just one. I have searched many others vedios none of the vedios talk about adding freshly HR database schema.

Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email.