In the following example you do not have to use double quotation marks because <TT>TODAY</TT>is not a reserved word. To be sure, check your specific implementation.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate TODAY</B>  2  <B>from dual;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">TODAY--------15-MAY-97SQL&gt;</FONT></PRE><H3><FONT COLOR="#000077">The Use of DISTINCT When Selecting Multiple Columns</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>select distinct(city), distinct(zip)</B>  2  <B>from address_tbl;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">select distinct(city), distinct(zip)                         *ERROR at line 1:ORA-00936: missing expressionSQL&gt;</FONT></PRE><H5>ANALYSIS:</H5><P>A city can have more than one ZIP code. As a rule, you should use the <TT>DISTINCT</TT>command on only one selected column.<H3><FONT COLOR="#000077">Dropping an Unqualified Table</FONT></H3><P>Whenever dropping a table, <I>always</I> use the owner or schema. You can haveduplicate table names in the database. If you don't use the owner/schema name, thenthe wrong table could be dropped.</P><P>The <I>risky</I> syntax for dropping a table:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; drop table people_tbl;</FONT></PRE><P>The next statement is much safer because it specifies the owner of the table youwant to drop.</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; drop table ron.people_tbl;</FONT></PRE><BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Qualifying the table when dropping	it is always a safe practice, although sometimes this step may be unnecessary. Never	issue the <TT>DROP TABLE</TT> command without first verifying the user id by which	you are connected to the database. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">The Use of Public Synonyms in a Multischema Database</FONT></H3><P>Synonyms make life easier for users; however, public synonyms open tables thatyou might not want all users to see. Use caution when granting public synonyms especiallyin a multischema environment.<H3><FONT COLOR="#000077">The Dreaded Cartesian Product</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>select a.ssn, p.last_n</B>  2  <B>from address_tbl a,</B>  3       <B>people_tbl p;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SSN       LAST_NAME--------- ---------------303785523 SMITH313507927 SMITH490552223 SMITH312667771 SMITH420001690 SMITH303785523 JONES313507927 JONES490552223 JONES312667771 JONES420001690 JONES303785523 OSBORN313507927 OSBORN490552223 OSBORN312667771 OSBORN420001690 OSBORN303785523 JONES313507927 JONES490552223 JONES312667771 JONES420001690 JONES16 rows selected.</FONT></PRE><P>This error is caused when you do not join the tables in the <TT>WHERE</TT> clause.Notice how many rows were selected. Both of the preceding tables have 4 rows; therefore,we wanted 4 rows returned instead of the 16 rows that we received. Without the useof a join in the <TT>WHERE</TT> clause, each row in the first table is matched upwith each row in the second. To calculate the total number of rows returned, youwould multiple 4 rows by 4 rows, which yields 16. Unfortunately, most of your tableswill contain more than 4 rows of data, with some possibly exceeding thousands ormillions of rows. In these cases don't bother doing the multiplication, for yourquery is sure to become a run-away query.<H3><FONT COLOR="#000077">Failure to Enforce Input Standards</FONT></H3><P>Assuring that input standards are adhered to is commonly known as quality assurance(QA). Without frequent checks on the data entered by data entry clerks, you run avery high risk of hosting trash in your database. A good way to keep a handle onquality assurance is to create several QA reports using SQL, run then on a timelybasis, and present their output to the data entry manager for appropriate actionto correct errors or data inconsistencies.<H3><FONT COLOR="#000077">Failure to Enforce File System Structure Conventions</FONT></H3><P>You can waste a lot of time when you work with file systems that are not standardized.Check your implementation for recommended file system structures.<H3><FONT COLOR="#000077">Allowing Large Tables to Take Default Storage Parameters</FONT></H3><P>Default storage parameters will vary with implementations, but they are usuallyrather small. When a large or dynamic table is created and forced to take the defaultstorage, serious table fragmentation can occur, which can severely hinder databaseperformance. Good planning before table creation will help to avoid this. The followingexample uses Oracle's storage parameter options.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>create table test_tbl</B>  2  <B>(ssn   number(9) not null,</B>  3  <B>name  varchar2(30) not null)</B>  4 <B> storage</B>  5<B>  (initial extent 100M</B>  6  <B> next extent     20M</B>  7  <B> minextents 1</B>  8  <B> maxextents 121</B>  9  <B> pctincrease 0};</B></FONT></PRE><H3><FONT COLOR="#000077">Placing Objects in the System Tablespace</FONT></H3><P>The following statement shows a table being created in the <TT>SYSTEM</TT> tablespace.Although this statement will not return an error, it is likely to cause future problems.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>create table test_tbl</B>  2  <B>(ssn   number(9) not null,</B>  3  <B>name  varchar2(30) not null)</B>  4  <B>tablespace SYSTEM</B>  5  <B>storage</B>  6 <B> (initial extent 100M</B>  7  <B>next extent     20M</B>  8  <B>minextents 1</B>  9  <B>maxextents 121</B>  10 <B>pctincrease 0};</B></FONT></PRE><P>The next example corrects this so-called problem:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>create table test_tbl</B>  2  <B>(ssn   number(9) not null,</B>  3  <B>name  varchar2(30) not null)</B>  4 <B> tablespace linda_ts</B>  5 <B> (initial extent 100M</B>  6   <B>next extent     20M</B>  7 <B> minextents 1</B>  8  <B>maxextents 121</B>  9  <B>pctincrease 0};</B></FONT></PRE><H5>ANALYSIS:</H5><P>In Oracle, the <TT>SYSTEM</TT> tablespace is typically used to store <TT>SYSTEM</TT>owned objects, such as those composing the data dictionary. If you happen to placedynamic tables in this tablespace and they grow, you run the risk of corrupting orat least filling up the free space, which in turn will probably cause the databaseto crash. In this event the database may be forced into an unrecoverable state. Alwaysstore application and user tables in separately designated tablespaces.<H3><FONT COLOR="#000077">Failure to Compress Large Backup Files</FONT></H3><P>If you do large exports and do not compress the files, you will probably run outof disk space to store the files. Always compress the export files. If you are storingarchived log files on hard disk instead of on tape, these files can be and probablyshould be compressed to save space.<H3><FONT COLOR="#000077">Failure to Budget System Resources</FONT></H3><P>You should always budget your system resources before you create your database.The result of not budgeting system resources could be a poorly performing database.You should always know whether the database is going to be used for transactions,warehousing, or queries only. The database's function will affect the number andsize of rollback segments. The number of database users will inevitably affect thesizing of the <TT>USERS</TT> and <TT>TEMP</TT> tablespaces. Do you have enough spaceto stripe your larger tables? Tables and indexes should be stored on separate devicesto reduce disk contention. You should keep the redo logs and the data tablespaceson separate devices to alleviate disk contention. These are just a few of the issuesto address when considering system resources.<H2><FONT COLOR="#000077">Preventing Problems with Your Data</FONT></H2><P>Your data processing center should have a backup system set up. If your databaseis small to medium, you can take the extra precaution of using <TT>EXPORT</TT> toensure that your data is backed up. You should make a backup of the export file andkeep it in another location for further safety. Remember that these files can belarge and will require a great deal of space.<H3><FONT COLOR="#000077">Searching for Duplicate Records in Your Database</FONT></H3><P>If your database is perfectly planned, you should not have a problem with duplicaterecords. You can avoid duplicate records by using constraints, foreign keys, andunique indexes.<H2><FONT COLOR="#000077">Summary</FONT></H2><P>Many different types of errors--literally hundreds--can stand in the way of youand your data. Luckily, most errors/mistakes are not disasters and are easy to remedy.However, some errors/mistakes that happen are very serious. You need to be carefulwhenever you try to correct an error/mistake, as the error can multiply if you donot dig out the root of the problem. When you do make mistakes, as you definitelywill, use them as learning experiences.<BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>We prefer to document everything	related to database errors, especially uncommon errors that we happen to stumble	upon. A file of errors is an invaluable <FONT COLOR="#000000">Troubleshooting</FONT>	reference. <HR></P>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Day 21 provides you with a sample	of some of the most common Personal Oracle7 errors. For a complete list of errors	and suggested resolutions, remember to refer to your database documentation. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Q&amp;A</FONT></H2><DL>	<DD><B>Q You make it sound as if every error has a remedy, so why worry?</B>	<P><B>A </B>Yes, most errors/mistakes are easy to remedy; but suppose you drop a	table in a production environment. You might need hours or days to do a database	recovery. The database will be done during this time, and your company will be paying	overtime to several people to complete the fix. The boss will not be happy.</P>	<P><B>Q Any advice on how to avoid errors/mistakes?</B></P>	<P><B>A </B>Being human, you will never avoid all errors/mistakes; however, you can	avoid many of them through training, concentration, self-confidence, good attitude,	and a stress-free work environment.</DL><H2><FONT COLOR="#000077">Workshop</FONT></H2><P>The Workshop provides quiz questions to help solidify your understanding of thematerial covered, as well as exercises to provide you with experience in using whatyou have learned. Try to answer the quiz and exercise questions before checking theanswers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;<H3><FONT COLOR="#000077">Quiz</FONT></H3><DL>	<DD><B>1.</B> A user calls and says, &quot;I can't sign on to the database. But everything	was working fine yesterday. The error says invalid user/password. Can you help me?&quot;	What steps should you take?	<P><B>2. </B>Why should tables have storage clauses and a tablespace destination?</DL><H3><FONT COLOR="#000077">Exercises</FONT></H3><DL>	<DD><B>1. </B>Suppose you are logged on to the database as <TT>SYSTEM</TT>, and you	wish to drop a table called <TT>HISTORY</TT> in your schema. Your regular user id	is <TT>JSMITH</TT>. What is the correct syntax to drop this table?	<P><B>2. </B>Correct the following error:</P>	<H5>INPUT:</H5></DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate DATE</B>  2  <B>from dual;</B></FONT></PRE>	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>	<PRE><FONT COLOR="#0066FF">select sysdate DATE               *ERROR at line 1:ORA-00923: FROM keyword not found where expected</FONT></PRE></BLOCKQUOTE><H1><FONT COLOR="#0066FF"></FONT></H1><CENTER><P><HR><A HREF="../ch20/ch20.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../wk3rev/wk3rev.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR><BR><BR><IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"BORDER="0"></P><P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. Allrights reserved.</CENTER></BODY></HTML>