From time to time, I receive information related to Oracle that is interesting, but which requires confirmation. For example, the report may be lacking in test results, or version numbers it applies to. If this information is directly applicable to your environment, then confirm these reports against your database.
Report #1: The SGADEF file disappearance
Report #2: How Import handles commits involving LONGs and LOBs
Report #3: The events you can set in init.ora
Report #4: How Y2K testing can impact your tables
Report #5: A clever trick to spell out numbers
Report #1: The SGADEF file disappearance
This one is easily confirmable just by reading the README.doc file under $ORACLE_HOME/rdbms/doc. If your scripts depend on SGADEF existing, prepare to edit them.
Carl Roberts reported:
A note from the 8.0.5 readme (hp-ux).
1.7 SGADEF FILE
---------------
The contents of this file have been deleted, but the file remains since
its existence is needed for purposes that have never been documented.
It is targeted for obsolescence in the 8.1 release.
Go back to the top
Report #2: How Import handles commits involving LONGs and LOBs
On a listserv, Sara Wasserman reported on slow Imports of LOBs and LONGs:
The reason for the painfully slow import is that ORACLE incorrectly interprets parameter 'COMMIT = Y' when importing long (inlcuding BLOB) records and, instead of committing after each ArrayInsert with value buffer, commits after every record.
So increasing the buffer size isn't going to help the import. You are better off leaving COMMIT=N and increasing the rollback segment to the size of the largest table (drop all other rollback segments to make sure the one big enough is the one used). If you want to import in parallel, all your rollback segments will have to be big enough for the biggest table. After completing the import, you can drop and recreate your rollback segments to something more appropriate for your application.
Just another reason to enjoy LONGs.
Report #3: The events you can set in init.ora
Jeroen van Sluisdam reported seeing the event list in comp.databases.oracle.server. You should be very careful when using these, since by setting them you are telling Oracle to work in a non-standard way. The event list is most helpful when dealing with Oracle support (so you can understand the implications of the settings they may tell you to add to init.ora). Because it's 10 pages long, the event list is a separate file, click here. Use at your own risk.
Report #4: How Y2K testing can impact your tables
Jay Miller reported the following to the DBA listserv:
"If you're doing any work on a Y2K box here's a little oddity that I ran into accidently while testing a script (Oracle 8.0.4.3, Solaris 2.6):
If you create a table, then reset the date on the server to an earlier date, then login to Oracle, issue a SET TRANSACTION READ ONLY statement, and try to query the table, you get an ORA 1466 error:
01466, 00000, "unable to read data - table definition has changed"
// *Cause: Query parsed after tbl (or index) change, and executed
// w/old snapshot
// *Action: commit (or rollback) transaction, and re-execute
Not likely to be important, one should just be careful about creating tables
while you have your date set forward for Y2K testing."
As noted elsewhere, plan to rebuild from scratch after any Y2K test.
Report #5: A clever trick to spell out numbers
In response to an urgent plea for a function that translates numbers into their related words, Garry Gillies posted a response that uses the internal Oracle functions to do the trick. It's a limited trick, since it's limited by Oracle's support for Julian days, but it works well for the set of values under 5 million or so. The person who first posted the query then came back with the results of running it under Oracle6 (!) which showed that the Julian date limit changes with versions.
Garry Gillies posted:
Will this do?
It will convert numbers up to a maximum of 5373484.
SQL> select num from test order by num;
NUM
-----------
3
25
432
1234
13874
1500000
6 rows selected.
SQL> select to_char(to_date(to_char(num),'J') ,'JSP')
from test order by num;
TO_CHAR(TO_DATE(TO_CHAR(NUM),'J'),'JSP')
- ----------------------------------------------------------------------
THREE
TWENTY-FIVE
FOUR HUNDRED THIRTY-TWO
ONE THOUSAND TWO HUNDRED THIRTY-FOUR
THIRTEEN THOUSAND EIGHT HUNDRED SEVENTY-FOUR
ONE MILLION FIVE HUNDRED THOUSAND
6 rows selected.
----
I like Garry's response because it uses the Oracle internal functions, so it will work across versions well. You're limited by the scope of Oracle's support for Julian dates. He takes the number, does a to_char to change it to a character string. Then he does a to_date on that so that string is now viewed as a Julian date. Then he does a to_char on that date, using the JSP map ("sp" tells it to spell it out) to spell out the number.