Home » Other » Suggestions & Feedback » OraFAQ Forum Guide
( ) 15 Votes
| OraFAQ Forum Guide [message #262537] |
Mon, 27 August 2007 07:34 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
|
OraFAQ Forum Guide
Welcome to the OraFAQ forums! Please read and follow these guidelines:
- SEARCH both this forum & GOOGLE BEFORE posting!
- Post in only one forum. Moderators may move a post to a more appropriate forum. Duplicate posts will be removed.
- Choose a topic title that clearly states the problem to be solved. A good title might be "ORA-1555 during pl/sql loop".
Avoid vague, general, non-specific, titles like "Query", "export", "SQL", etc.
- Post your Oracle DB version to 4 decimal places (SELECT * FROM V$VERSION;).
- Post your Operating System (OS) name & version for your database (DB) server system.
- Write a clear explanation of the problem in proper English, with proper grammar and punctuation.
Do not post in all capitals or use IM-speak like "U", "Ur", "U r", "Plz", "prb", "qry" or any acronym that is not well-known by the community.
Include all relevant information such as SQL Client name & version and include OS name & version if different from DB Server.
- SHOW us using COPY & PASTE from SQL*Plus, the whole session, exactly what you did & how Oracle responded as in the example below.
(If using a tool, such as SQL Developer, that doesn't display line numbers, identify precisely any lines that are flagged in error messages.)
Make sure you post some effort of your own. We are not here to do your work or homework for you.
SQL> set term on echo on
SQL> @pp.sql
SQL> DECLARE
2 STR VARCHAR2(4);
3 BEGIN
4 STR := 'FAILS';
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
- Post FORMATTED and properly INDENTED code as demonstrated above.
- Post TESTED DDL (CREATE TABLE) for referenced tables & INSERT statements for test data to reproduce problem test case.
- Provide your expected result set and explain the rules/reasons that lead to it.
- Plain text file attachments are acceptable, while Excel, *.pdf or similar files are not.
- If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
- Pay attention to what you post as this is a public forum and everyone can see it. Confidential information will be masked upon request, but the whole topic won't be deleted for any reason.
- Commercial posts are admitted only in the Marketplace Forum. OraFAQ Forum policy is to accept only Oracle-related training and marketing adverts; the other ones might be removed.
Useful sites
Useful sites
(bookmark them!)
Following sites require free (no spam!) Oracle Technology Network account. Subscribe now, it's well worth it.
A few more links:
OraFAQ Forum Guide History
OraFAQ Forum Guide History
- 2007-08-27 - Moderator staff - First version, made up as a collection of stickies spread through different OraFAQ Forums
- 2011-05-07 - BlackSwan - SIGNIFICANT rewrite & reduction
- 2011-05-08 - Barbara Boehmer - minor reorganization of BlackSwan's revisions
- 2011-05-26 - BlackSwan updated < code tag> page URL
- 2011-08-25 - Michel Cadot - added previous forum guide as "Detailed Forum Guide"
- 2011-09-05 - BlackSwan updated < code tag> page URL
- 2011-11-14 - BlackSwan expanded text for #7
- 2011-12-19 - Littlefoot - added #13: will your topic ever be deleted?
- 2013-02-20 - Michel Cadot - added links to general forum rules
- 2013-12-04 - Littlefoot - all links merged into the "Useful sites" section
- 2014-02-07 - Littlefoot - Training & Marketplace forums accept only Oracle-related adverts
- 2014-06-14 - Michel Cadot - fixed some links
- 2018-11-01 - Michel Cadot - fixed some links
What's the best way to ask a technology question? T. Kyte's answer
Step one should be to go ahead and look to see if you can't solve it yourself really quickly by taking a look at the documentation or actually using the search features on the sites where you're going to post the question. Because 9 times out of 10, you'll find that your question has been asked and answered by someone else.
Once you think you have a unique question that you can't answer yourself, when you post the question--and this goes for Ask Tom as well as any other forum--ask the question as if you were asking your mom the question. Give that level of detail. The people you're asking haven't been sitting staring at the problem for a day or a week, like you have. We don't have all the information you have. So rather than being very terse and saying, "How can I get this output from this data?" explain what the output is. Explain the logic behind getting that output.
And then when you have the data that we're supposed to get this output from, phrase it in a series of CREATE TABLEs and INSERT INTOs. If you want us to help you develop a SQL query, for example, it would be really useful if we had your tables and some of your data. If you just post the output from a SQL*Plus SELECT statement, it's going to take us 5 or 10 minutes to reverse-engineer that, create a CREATE TABLE statement, and insert all the data into it. Save us that time, and make it easy for us to answer you. Give a lot of detailed information, and provide a reasonable test case.
http://www.flickr.com/photos/tkyte/4033155188/sizes/o/
Detailed OraFAQ Forum Guide
OraFAQ Forum Guide
Introduction
Welcome to OraFAQ Forum! Please, read this document before you start to actively participate. Here are guidelines of how to behave on the Forum in order to make it a cozy place to stay.
Some of the most important instructions are:
- Be polite!
- Never belittle anyone for asking beginner-level questions or for their English skills.
- Use English language in the main forum (non-English forums are also provided - see bottom of list) and DO NOT use IM-speak!
- Provide all relevant information about your problem, including Oracle software version (4 decimal places) and operating system version.
- Format your code and make sure that lines do not exceed 80 characters. Use the "Preview Message" button to check it.
Click on a section title to expand its text.
How to get a quick answer to your question?
How to get a quick answer to your question?
Quote (a Chinese proverb) :"He who asks a question may appear to be a fool, he who does not ask will remain a fool forever"
Before you decide to open a new topic, you should ask yourself:
- First of all: did I try myself? Or am I just hoping that someone else is willing to do my work?
- Have I checked the Wiki page (this site's knowledge base)?
- Did I search the board properly? Have I also tried the Site Search Engine?
A large group of questions posted here are just a déjà-répondu of the past.
- Did I read the documentation? A lot of questions are answered there. Really.
- Did I use Google? Even if your question hasn't been answered here, chances are that on other websites someone already has posted an answer.
- Is my question specific to the Oracle database? Now, that may seem obvious since this site is called Oracle FAQs, but we get quite a few questions about Access and MS SQL Server here - and SQL between databases is not always compatible - so please ask any non-Oracle questions elsewhere. It will be to your advantage.
Ok, so you didn't find an answer yet. No sweat, you're welcome to post it here and a lot of people are happy to answer. They can, however, answer only if enough info is provided.
Useful sites
Useful sites
(bookmark them!)
Following sites require free (no spam!) Oracle Technology Network account. Subscribe now, it's well worth it.
A few more links:
Posting guidelines
Posting guidelines
Theory
- Have the courtesy to write in plain English; it is not the first language of most of the participants to this forum, but that is no excuse for poor or non-existent punctuation.
- If everyone used standard punctuation, like spaces between words and sentences, capitals for the first word of a sentence, a full-stop (period) to mark the end of a statement or a question mark to terminate a question then your requests will be more easily understood and your questions answered more clearly and quickly. Got it? Try it, it isn't that hard.
- Also, stop putting every phrase in a new paragraph, it makes entries deep and hard to read.
- People don't feel encouraged when they have to decipher some gobbledygook.
Abbreviations like "U", "Ur", "U r", "Plz", "prb", "qry" especially tend to get on people's nerves.
IM speak
Instant Messager language, like the (non-exhaustive) list below
- u = you
- your = you are
- any one = anyone
- grt = great
- pls/plz = please (especially avoid this in topic titles)
- ugt = urgent (especially avoid this in topic titles)
- wrt = write
- i = I
- im = I am
is not appreciated:
- It is hard to read.
- It is unprofessional.
- It doesn't show much respect towards your fellow forum members.
If you don't take time to write in proper English, how can you expect that others would answer properly?
- Always spell check and proof read your messages before posting them.
- As a rule of thumb: be polite. No one is paid for answering your question. The least you can do is show some respect to those who are willing to dedicate some of their spare time to look at your problem.
- If you feel that a message is not admissible for some reason (offending language/opinions, personal attacks, ...), you can report it to the moderator staff. But if you want extra attention for your topic, reporting will act counter-productive.
- Don't use uppercase only. It's harder to read and gives the impression you're shouting.
- Choose a topic title that makes sense & clearly states problem to be solved.
A good title might be "ORA-1555 during pl/sql loop"
Do NOT use a single word title like "Query", "export", "SQL", etc.
Do NOT use a two word title like "Slow Query", "Need Help", "Got Error", "SQL Plus", etc.
A less fortunate choice might be "URGNT PLZ!!!!!!!!!!!!"
Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)
- Choose the appropriate forum. If in doubt, don't worry - moderators can always move it
- Don't cross-post. Duplicates will be removed.
- Commercial posts are admitted only in the Marketplace Forum.
- As a corollary, do not post a link to your site or blog unless it answers the question.
Practice
- Post Operating System (OS) name & version for DB server system.
- Post results for Oracle RDBMS by invoking following SQL
SELECT * from v$version
- By doing so, you'll avoid reactions using features you can't use.
- Post details about virtualization; Base OS name & version, guest OS name & version, and name & version Virtual Machine s/w
- Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
Do NOT post "procedure does not work" or "results is not correct".
While statement above is undoubtedly true for you, it is 100% devoid of actionable details.
Post using COPY & PASTE what you do & complete results.
Explain why the posted results are not as desired.
Post expected & desired results & explain why it is correct.
- Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
- Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
- Provide your expected result set and explain the rules/reasons that lead to it.
- If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:
SQL> insert into t2 values (t2_s.nextval, x1) returning id into :x2;
insert into t2 values (t2_s.nextval, x1) returning id into :x2
*
ERROR at line 1:
ORA-00984: column not allowed here - Do not describe your problem and/or solution in MS Office Word, Excel, *.pdf, *jpg or similar files and attach them to your message. Some people can not, some do not want to download them. Plain TXT files are acceptable.
- Be patient. A lot of people here are just browsing the board once a day.
- Don't think you have more chance if you PM individual members. Just post your question in public: more people can see/answer them that way. Most PM'ed questions are posted to the forum anyway.
- Again, forum members are not here to do your work for you nor to do your homework assignment for you. If you are stuck, we will gladly help you out.
- Don't "wild post", i.e. post a reply that has nothing to do with the original thread.
- If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
How to format your post?
How to format your post?
If you are providing code snippets such as SQL*Plus copy/paste, please be sure to use formatting tags: [code] Enter your code here.[/code] It makes a huge difference to the forum readers. Unreadable code makes a question harder to understand and will delay answers. A lot of people don't spend that much time on the forum and unclear posts will be skipped more often because they take a lot more time to decipher.
The difference between the code blocks below is clear:
First a block without proper tags, you'll notice that it is not that readable:
DECLARE
CURSOR yourcursor
IS
SELECT yourcolumn
, another_column
FROM yourtable
WHERE some_column BETWEEN A AND B
AND some_other > SYSDATE;
BEGIN
FOR a_record IN yourcursor
LOOP
do_something_here;
IF a_record.another_column = 1
THEN
do_extra_stuff;
ELSE
do_other_stuff;
END IF;
END LOOP;
END;
Now, the same PL/SQL block this time with use of the code tags. It clearly delineates the code from the other text and preserves text indenting.
DECLARE
CURSOR yourcursor
IS
SELECT yourcolumn
, another_column
FROM yourtable
WHERE some_column BETWEEN A AND B
AND some_other > SYSDATE;
BEGIN
FOR a_record IN yourcursor
LOOP
do_something_here;
IF a_record.another_column = 1
THEN
do_extra_stuff;
ELSE
do_other_stuff;
END IF;
END LOOP;
END;
You can also paste the code in your message, select it and hit this button  .
What if the code is not formatted itself? Code tags will do little about that. So you first have to format the code
Make sure that lines of code do not exceed 80 characters. Use the "Preview Message" button to check it.
Responding to Posts
Responding to Posts
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
Performance Tuning
Performance Tuning
Please consider the following when tuning your query:
- The first step is to read the Oracle Performance Tuning Guide in its entirety. It describes the different access paths and teaches you how to use explain plan and tkprof to tune your query. (In Oracle 10g, it also covers the new 10g automated tuning features that may be at your disposal.) While you are reading, you can proceed with the remaining steps below.
- The second step to tuning your query is to gather comprehensive statistics, and re-gather them every time your data undergoes significant change. This means using dbms_stats with cascade => true and a method_opt of at least 'for all indexed columns size 250'.
- The third step is to make sure that all of the columns that should not / will not contain null values are set to not null, that all columns that are used as primary and foreign keys have a primary or foreign key constraint on them, and seriously consider putting indexes on all foreign key columns.
- The fourth step is to re-evaluate your physical database design. Ideally, this step was conducted in development, before the system went live. But regardless, consider all of the available oracle data structures (heap tables, index organized tables, hash clusters, btree clusters, btree indexes, bitmap indexes, partitions, materialized views, and compression).
- The fifth step is to maintain your system at a reasonably up to date patch and version level. Each new version eliminates many older bugs, provides added functionality that you can use for performance, and typically has inherent optimizations at the internal level that cause your existing code to run faster. At the time of this writing, you should be at, or currently upgrading to, at least version 10gR2.
During the tuning process, keep in mind the following:
- Full Table Scans are not always bad, and Index Scans are not always good.
- Routinely rebuilding btree indexes often does more harm than good. Only take action for a reason, and measure/evaluate whether your goal was achieved by the action you took.
- Unless you have a particularly strange circumstance with a single query, and have exhausted all other options, it is generally best to only provide informational hints (such as cardinality, first_rows, and all_rows), rather than to provide specific access plan hints (like full and index).
All about braindumps
All about braindumps
Introduction
Braindumps are a list of questions and answers from an actual certification exam. Although it might seem to be a good starting point to study, you should take the following points into consideration:
- The answers in the list might be wrong. You don't know whether you are studying/memorizing the correct answers since you are relying on the word of a complete stranger. He could have made a mistake in either the question or the answer.
- It is unethical to just memorize answers in order to get a certification. You should be certified for your knowledge and insight, not for having a good memory.
- It breaks down the value of certifications. Imagine the following situation: I, as an OCP, apply for a job. The guy I have to replace was a "braindump OCP" that wasn't up for the job because he had no actual knowledge. My OCP would be worthless in the eyes of my possible employer just because the employer had lost faith in certifications.
- It is illegal. Full stop. Read the next paragraph if you're not sure about this.
What does Oracle say?
Oracle states in its Oracle certification program candidate agreement the following:
OracleYou agree that all work submitted by You in completing the Certification Exam and in satisfaction of the Certification Requirements, including Certification Exam answers, assignments, resolutions and personal registration information is entirely Your own. You will neither: (i) provide nor accept improper assistance; nor (ii) use unauthorized materials in attempting to satisfy Certification Requirements.
OracleYou agree that You will not copy, publish, offer to sell, sell, publicly perform or display, distribute in any way or otherwise transfer, modify, make derivative works thereof, reverse engineer, decompile, disassemble or translate any Certification Exam or part thereof.
OracleThe Certification Exam (including without limitation, questions, answers, worksheets, computations, drawings, diagrams, length and number of exam segments and/or questions, or any communication related to the Certification Exam) is the confidential property of Oracle ("Confidential Information") and is made available to You for the sole purpose of testing your knowledge in the technical area referenced in the title of the applicable Certification Exam.
OracleYou agree (i) to hold Confidential Information in confidence and take all reasonable precautions to protect it; (ii) not to use Confidential Information at any time during the term or after the termination of this Agreement; except as provided herein; and (iii) that You shall not disclose, publish, reproduce or transmit any Confidential Information to any third party, in any form, including without limitation, verbal, written, electronic or any other means for any purpose.
Oracle has even contacted OCP candidates:
[url=http://forums.oracle.com/forums/thread.jspa?messageID=1682045�] Oracle Certification Program Candidate [/url]
What about OraFAQ?
- We don't host braindump papers. They will be deleted on sight.
- Threads demanding for dumps will be closed or deleted on sight.
Here's the background: there once was a forum called OraFAQ. It was an open forum, no subscription needed. You just needed to make up your nickname and posted on the forum. On that forum, there were a lot of threads concerning people asking for free download sites of books. Those threads quickly moved from single post threads to threads with a lot of replies with no extra information but the desire of getting the same information. Those threads were known as "metoo" threads: Quote:member_a: I want to download ebook for free
member_b: metoo
member_c: metoo, pls send to me@my_email.com[/email][/email][/email][/email][/email][/email][/email]
member_d: metoo, pls send to metoo@another_mail.com[/email][/email][/email][/email][/email][/email][/email]
... OraFAQ has moved from this open forum to a forum that needed membership. This made the situation better but "metoo" threads still existed. Moderators were appointed and one of them decided that the "metoo" situation needed to end. He cleaned out all those threads from the forum. No more asking for dumps and all other "metoo" threads were closed on sight. This moderator still looks at threads and he is very strict about the "metoo" policy.
There is another side effect of the popularity of the OraFAQ forum. It became very visible. Even at Redwood Shores, Oracle HQ, there were people aware of its existence. This is not a bad thing, but as a forum with such a high profile OraFAQ cannot tolerate nor permit that links to illegal sites are posted here. Oracle and other companies that publish Oracle books tend to scan forums for illegal content. We, as moderators, try to make life easier for Frank Naude.
Certification Links
Marketplace
Marketplace
General info
It is suitable for the following subjects:
- Career advice
- Vacancies
- Job applications
- Product reviews/recommendations
Bear in mind the following:
- Only Oracle related posts are accepted.
- If applicable, include location information:
- City
- State
- Country (OraFAQ is an international forum with members from all over the world.)
- If applicable, include the job description in the topic title.
- Don't disclose personal information (especially, but not exclusive, email addresses).
- Apply proper casing and proper punctuation.
- Use professional English and no IM speak.
Mind you: there is a zero tolerance concerning spam.
To people declaring their availability for a position
The poster of every entry in a thread is written as a hyper-link. If you follow that link, it will take you to the profile page of the poster.
Most posters in the Marketplace forum have their email address visible in their profile. Therefore, by using that link you will find it easier, more professional, and probable have a far higher chance of successfully informing that person of your availability than posting a reply to a thread in this forum.
Acknowledgments
Acknowledgments
Thanks were given to (alphabetically) David (djmartin), Frank, Frank Naude, Maarten Hereijgers (Maaher), Mahesh Rajendran, Scot (smartin), Scott Mackey (scottwmackey), and Todd Barry for their additions, along with Hobbes, Joy Division, Michel Cadot and Ross Leishman (rleishman) for proofreading.
OraFAQ Forum Guide History
* 27.08.2007 - Moderator stuff - First version, made up as a collection of stickies spread through different OraFAQ Forums
* 30.08.2007 - Littlefoot - changed 'Acknowledgments' section title. Added 'History' section.
* 13.11.2007 - Michel Cadot - added link to Metalink FAQ
* 02.02.2008 - Frank Naude - added & updated some links
* 02.09.2008 - Barbara Boehmer - added section "Responding to Posts"
* 06.09.2008 - Littlefoot - minor changes to the "Practice" subsection of the "Posting guidelines" section
* 18.11.2008 - Littlefoot - history of "metoo" dumps requests in the "All about braindumps" section
* 19.02.2009 - Littlefoot - added screenshot of how to properly use [code] tags; certain sections updated in order to reflect current Forum state
* 28.04.2009 - Littlefoot - removed a note that regards the "Homework" forum from the "Responding to Posts" section, as it no longer exists
* 26.02.2010 - Littlefoot - fixed link to Metalink (now My Oracle Support) registration FAQ
* 19.03.2010 - Littlefoot - removed links to pages that do not exist any more (mostly in the braindumps section)
-
Attachment: code_tags.png
(Size: 14.48KB, Downloaded 191624 times)
[Updated on: Thu, 01 November 2018 14:12] by Moderator Report message to a moderator |
|
|
|
| |
  |
Goto Forum:
Current Time: Sat Dec 21 06:44:29 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
