Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.1 September 6, 2000 TABLE OF CONTENTS 1) Pop Quiz 2) Did you know... 3) URLs of the Week 4) SQL Tip: Database Dictionary Views 5) DBA Tip: Fast Import 6) People: After Ray Lane 7) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ --------------------------------------------------------- What is the name of Oracle's Web server product? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- * that if you're running Oracle on Unix, you can use the "oerr" utility to display an Oracle error message? For example, if you're trying to insert a duplicate value into a table in SQL*Plus, you'll receive an Oracle error, as shown below: SQL> create table tmp (n1 number primary key); Table created. SQL> insert into tmp values (1); 1 row created. SQL> insert into tmp values (1); insert into tmp values (1) * ERROR at line 1: ORA-00001: unique constraint violated When this happens, you can exit from SQL*Plus and enter the following command at the Unix prompt: $ oerr facility error where 'facility' refers to the three-character prefix in the error message, and 'error' corresponds to the numeric value in the error. In this example, you enter the following: $ oerr ORA 1 The following text will be displayed: 00001, 00000, "unique constraint (%s.%s) violated" // *Cause: An UPDATE or INSERT statement attempted to // insert a duplicate key. // For Trusted Oracle configured in DBMS MAC mode, you // may see this message if a duplicate entry exists at // a different level. // *Action: Either remove the unique restriction or do // not insert the key. Remember, you can always refer to the Oracle documentation (Oracle Error Messages guide) for the same information. * that you can join an active mailing list ORACLE-L by e-mailing a message like this: SUBSCRIBE ORACLE-L Joe Smith to LISTSERV@lists.sunysb.edu? Once you've joined the list, you can opt for the daily digest format. --------------------------------------------------------- 3) URLs OF THE WEEK --------------------------------------------------------- * Did you realize that Oracle documentation is available at the Oracle Technology Network (OTN) Web site---once you've filled out the free online registration form? http://technet.oracle.com/docs/ * Less than two weeks ago, Oracle rolled out OracleSalesOnline.com, an SFA (sales force automation) application with free entry-level functionality. The goal? Take market share from Siebel Systems. http://www.oraclesalesonline.com. Related: Q&A with Marc Benioff, salesforce.com's CEO. http://www.mercurycenter.com/svtech/news/indepth/docs/qa082800.htm * Both IBM and Microsoft have research sites that can give you an idea of priorities and directions in their database research. For several years in the 1990s, Oracle and Olivetti funded a UK-based research lab, but AT&T acquired it last year: http://www.uk.research.att.com/. If you know of any Oracle research sites, send me an e-mail, and I'll spread the word. In the meantime, keep an eye on the competition. http://research.Microsoft.com, http://www.research.ibm.com/compsci/datamgmt/index.html. --------------------------------------------------------- 4) SQL TIP: Database Dictionary Views --------------------------------------------------------- Data dictionary views are available for you to query the internal catalog of an Oracle database. You can use these views to find out the list of valid database account names, the list of valid tables, the state of your stored procedures, and more. Let's try out a few of these. One way of verifying the name of the current account is to run the following query: SQL> select username, default_tablespace from user_users; USERNAME DEFAULT_TABLESPACE -------------- -------------------------- SCOTT APP_DATA This simple query actually provides two important pieces of information. Prior to executing any critical command (for example, dropping a table or deleting rows), I often run this query to ensure I'm logged into the correct database account. The default_tablespace column tells me the default location of the tables I create. In this example, if I don't specify a tablespace when creating a table, it will be stored in a tablespace called APP_DATA. I run this command to ensure that the default tablespace isn't set to SYSTEM, because the SYSTEM tablespace should be reserved for use by Oracle only. ALL application tables should be created in separate tablespaces. To find out the tables owned by the current account, you can run the following query: SQL> select table_name, tablespace_name from user_tables; Note that there are typically three versions of a given view, denoted by the "user", "dba", and "all" prefixes. For example, instead of querying the user_tables view, you could run the query on all_tables or dba_tables. By definition, the "user" version of the view refers to objects owned by the current user; "dba" refers to objects that exist in the entire database; and "all" refers to all objects owned by the current user and the ones to which you have access. There are literally hundreds of views available, and it's difficult for novices and experienced users alike to remember the names---let alone the usage of each and every one of these views. Fortunately, you only need to remember the name of one database view called "dict". This view is a catalog of all the views in the database, as shown here: SQL> select * from dict; TABLE_NAME COMMENTS -------------- ---------------------------------------- ALL_ALL_TABLES Description of all object and relational tables accessible to the user ALL_ARGUMENTS Arguments in object accessible to the user ALL_CATALOG All tables, views, synonyms, sequences accessible to the user ... If you vaguely recall that the name of a view contains the characters "TABLE", then run the following: SQL> select * from dict where table_name like '%TABLE%'; --------------------------------------------------------- 5) DBA TIP: Fast Import --------------------------------------------------------- Oracle's export/import utility can be used to transfer the entire database, all objects belonging to a database account, and individual tables between databases. The export/import can take a long time and consume a large amount of disk space if you're trying to transfer a lot of large tables. One option to work around this constraint is to take advantage of Unix pipes for export/import, as shown here: $ mknod imp.pipe $ exp file=imp.pipe userid=scott/tiger tables=emp & >> exp.log $ imp file=imp.pipe userid=scott2/xxxx & >> imp.log The "mknod" Unix command creates a special file that's really a pipe. It's then used by the export/import to transfer the data. --------------------------------------------------------- 6) PEOPLE: After Ray Lane --------------------------------------------------------- Ray Lane, who resigned his position as president and chief operating officer of Oracle in July, has joined the legendary venture capital firm Kleiner Perkins Caulfield & Byers---the firm that had funded famous dot-coms such as Netscape, Amazon.com, and AOL. Lane was credited with turning Oracle around in the early 1990s, and he was also instrumental in increasing the company's revenue from $1 billion to $10 billion. With Lane gone, Larry Ellison has assumed responsibility of the day-to-day operation of the organization. There's speculation about who will be the heir to Ellison's empire: Gary Bloom, executive vice president of the system products division; Safra Catz, executive vice president of business practices; or Jeff Henley, Oracle's chief financial officer. Who do _you_ think will get the nod? --------------------------------------------------------- 7) ANSWER TO THE POP QUIZ: It's a branding thing --------------------------------------------------------- Oracle's Web server product is called Oracle iAS (Oracle Internet Application Server). Prior to iAS, Oracle called the product OAS (Oracle Application Server), and before that, WAS (Web Application Server), and before that, WebServer. Indeed, the constantly changing naming conventions are confusing if not downright annoying, but the renaming (and rebranding) often reveal Oracle's direction for the product. The latest product offering is more than just a simple Web server displaying HTML pages; it's an integrated environment for serving PL/SQL and Java pages, EJBs (Enterprise JavaBeans), and even Oracle forms and reports. In addition, iAS is also responsible for performing advanced caching of pages and load balancing of Web traffic. http://www.oracle.com/ip/deploy/ias/index.html --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This weekly eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2000 http://www.pinnaclepublishing.com All rights reserved.