TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) Oracle Professional 4) PL/SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the V$SYSAUX_OCCUPANTS dictionary view used for? --------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- ... that The 2004 JavaOne Conference was recently held in San Francisco, June 28th – July 1. Oracle attended the conference and was displaying its new approach to J2EE and service-oriented development. For more information of Oracle’s involvement with this Conference, visit: http://otn.oracle.com/tech/java/events/javaone04/index.html --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the upcoming issue of Oracle Professional, Oracle expert Steven Feuerstein explores Oracle10g Multisets, a.k.a. Nested Tables. Darryl Hurley completes his 2 part series on how to use DDL triggers to save changes to PL/SQL source code. And, IOUG Executive VP, Bill Burke introduces us to the Oracle Enterprise Manager 10g. Here’s how you can access Oracle Professional: http://www.pinpub.com/op/ --------------------------------------------------------- 4) PL/SQL Tip --------------------------------------------------------- This tip came from Dan Clamage (http://www.clamage.com/) and is useful for those of you who need to work with CLOBs in Oracle 9i. It also describes a new feature added in 9i for supporting CLOBs. Subject: Playing with CLOBs in 9i: a Quick Study First, I’ll start by creating a simple table with 1 CLOB column. SQL> CREATE TABLE t_clob (t CLOB); Table created. SQL> -- easy to insert strings or literals into a clob SQL> INSERT INTO t_clob (t) VALUES ('hello, world!'); 1 row created. SQL> -- returns a clob, but just the substr you asked for SQL> SELECT SUBSTR(t, 1, 5) h 2 FROM t_clob; H --------------------------------------------------- hello SQL> -- grab the one clob, SQL> -- do some string manipulation on it, SQL> -- update the clob SQL> DECLARE 2 v_clob CLOB := EMPTY_CLOB(); 3 v_rowid ROWID; 4 BEGIN 5 SELECT t, ROWID row_id 6 INTO v_clob, v_rowid 7 FROM t_clob; 8 v_clob := REPLACE(v_clob, 'o', '~'); -- this is new functionality 9 dbms_output.put_line(v_clob); -- wow! new support for clob! 10 UPDATE t_clob 11 SET t=v_clob 12 WHERE ROWID = v_rowid; -- don't need to lock (for update) row anymore 13 END; 14 / hell~, w~rld! PL/SQL procedure successfully completed. SQL> -- it's in there! SQL> SELECT t FROM t_clob; T --------------------------------------------------- hell~, w~rld! -------------------------------------------------------- 5) In the News -------------------------------------------------------- Well, you’ve probably heard the details of Oracle’s challenge to the Department of Justice’s lawsuit. The Justice department is trying to block the PeopleSoft take-over bid by Oracle on anti-trust grounds. Oracle is arguing that the software market is more competitive than the government would have us believe. The court case ended July 1 after more than 100 hours of testimony and at least 40 witnesses. Closing arguments in the case are scheduled for July 20th with a Judge ruling expected sometime late August. For more information about the Oracle take-over bid, go to: http://www.oracle.com/peoplesoft/ * * * * PeopleSoft Inc. recently announced the extension of their Refund Program until September 30, 2004. This program was introduced in June, 2003 as a hindrance to Oracle’s takeover bid of the company. The program promises customers two to five times their software license fees if the company is acquired and its products are discontinued. Liabilities from the program have grown to $2 billion dollars as of June 30, 2004. --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The V$SYSAUX_OCCUPANTS dictionary view was introduced in Oracle Database 10g to describe the set of application tables within the new SYSAUX tablespace. This tablespace holds tables that otherwise were assigned to the SYSTEM tablespace in earlier versions of Oracle. It is a locally-managed tablespace using automatic segment-space management. Oracle stores its own utilities in there and you can run the following SELECT statement to see the contents: SQL> select occupant_name name, occupant_desc description, 2 schema_name owner 3 from v$sysaux_occupants; NAME DESCRIPTION OWNER --------------- ----------------------------------------------------- ------------------ LOGMNR LogMiner SYSTEM LOGSTDBY Logical Standby SYSTEM STREAMS Oracle Streams SYS AO Analytical Workspace Object Table SYS XSOQHIST OLAP API History Tables SYS SM/AWR Server Manageability - Automatic Workload Repository SYS SM/ADVISOR Server Manageability - Advisor Framework SYS SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS SM/OTHER Server Manageability - Other Components SYS STATSPACK Statspack Repository PERFSTAT ODM Oracle Data Mining DMSYS SDO Oracle Spatial MDSYS WM Workspace Manager WMSYS ORDIM Oracle interMedia ORDSYS Components ORDSYS ORDIM/PLUGINS Oracle interMedia ORDPLUGINS Components ORDPLUGINS ORDIM/SQLMM Oracle interMedia SI_INFORMTN_SCHEMA Components SI_INFORMTN_SCHEMA EM Enterprise Manager Repository SYSMAN TEXT Oracle Text CTXSYS ULTRASEARCH Oracle Ultra Search WKSYS JOB_SCHEDULER Unified Job Scheduler SYS For more information about Oracle 10g, stay tuned to this newsletter and be sure to visit http://www.pinpub.com/op/ to get subscription information on our Monthly publication.