Software

Ask something
Discussion Locked

Oracle Forum

0 helpful
20 replies

by maryweilage

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Welcome to the Oracle Forum. This is the place to post your Oracle questions, share your best techniques, and express your opinion on topics that impact IT development.

We also encourage you to post your comments about the Oracle TechMail.

If you haven't subscribed to the Oracle TechMail, there is no better time to take advantage of our free e-newsletter. Visit the e-newsletter center to sign up for the Oracle TechMail:
http://builder.com.com/techmails.jhtml?repID=u001

20 replies

by maryweilage

Oracle TechMail
Speed database access with the Bequeath protocol adapter:

The Feb. 28, Oracle TechMail examines the Bequeath protocol adapter and discusses its various advantages. Have you used Bequeath to speed up database access? If so, whatimprovements did you see after using Bequeath?

by mark.powell

I would just like to say that the quality of the Oracle tip has improved since Scott Stephens took over.

-- Mark D Powell --
remove .x for email

by Mark W. Kaelin

Use Java for regular expressions in an Oracle database:

The March 5, Oracle TechMail discusses the manipulation of regular expressions. Have you used the pure PL/SQL pattern matching utility, OWA_PATTERN, outside of modplsql applications? Have you used Oracle's Java stored procedures to bind regular expression functionality into PL/SQL?

by maryweilage

Manage stored procedures with DBA_OBJECTS

The Mar. 7, 2003, Oracle TechMail discussed how to manage stored procedures with the DBA_OBJECTS view. Have you had trouble managing stored procedures with traditional tools? If you've DBA_OBJECTS, have you been pleased with the results? Share your feedback here.

by Mark W. Kaelin

Understand the new NULLIF and COALESCE SQL functions:

The March 12, Oracle TechMail discusses the NULLIF and COALESCE SQL functions. Do you think the new SQL functions will make it easier to manipulate NULL values in your Oracle applications? Tell us how.

by fawzia71

Even though my organization is still in version 8 and planning on migratoin to 9i in a few months, I would like to comment that the article/tip didnot reveal a particularly 'fabulous' usage of the new functions in 9i. Infact I would go as far as to say that NVL was superior since it would not return an error due to sheer incompatibility!

by peterkryszak

The biggest gain in using these functions is compatibility with other SQL implementations. I agree that NVL, etc. are better (along with Oracle's join syntax, and so forth).

Oracle has always done things better, now they are improving standards compliance.

by Mark W. Kaelin

Simulate Java object state persistence in SQL and PL/SQL:

The March 14, Oracle TechMail discusses how to simulate Java object state persistence in SQL and PL/SQL. Have you used the RAW data type to give more robust, Java-like functionality to your database applications? Have you considered using Java Serialization to overcome limits to persistence? Share your techniques.

by swstephe

I suppose I should clarify:

The purpose of the article was to show how you could use RAW columns as a buffer for Java serialization and persistence rather than as a replacement for or simulation of Java serialization. It doesn't really overcome limits of persistence, it simply provides an alternative storage area.

by Mark W. Kaelin

Use Oracle hashing for big, static, uniform tables:

The March 19, Oracle TechMail discusses how to get faster table reads with hash clusters. Do you have to access a big, static uniform table in your applications? Do you use this hashing technique? Why or why not?

If you haven't subscribed to the Oracle TechMail, there is no better time to take advantage of our free e-newsletter. Visit our e-newsletter subscription center to subscribe to this valuable TechMail today:

http://builder.com.com/techmails.jhtml?repID=u001

by swstephe

I try to throw in about 1 DBA tip for every 4 developer tips. Mostly because there is so much more to write about for developers than DBA's. I guess some of the tips sound developer-ish toward the end. I can certainly try to weigh more toward theDBA side if that is what most readers would like to see. I seem to get more feedback from DBA-ish articles.

by duss

More DBA-ish articles would be a great thing! I believe even developers would benefit from more of these as they'd have more of an insight into what their applications can cause in databases with regards to performance issues.

by glsc

It took a while to tune into the different style of the "column", but I enjoy the subjects and have learned something from quite a few of them.

Keep up the good work.

by Mark W. Kaelin

A Builder.com member asked this question:

I would like to know that after the "drop table"
command is there any command that can recover back the table as well as the data that had stored in the table in Oracle 8i?

Any suggestions?

by swstephe

The "drop table" command is DDL, so there is no rollback information stored. That means the canned answer is that the table is gone and you should restore from backup, (imp ... table=name).

There might be another alternative which involves recovering your database to a point in time before the table was dropped, exporting the table, and then bringing it back up to the present and re-import the table. I've done this a number of times on really critical tables, but it is really risky and requires you have a really sound hot backup strategy. If you miss one of the critical backups, you may have to recover the entire database from a backup you should take before you start the procedure.

by dustymichaels

No offense, but this writer is not a DBA, and it is clear when he tries to write DBA tips.

Can we get an experienced DBA to submit some tips?

by mark.powell

I almost never write any code spending almost all my time on pure DBA activities; however, a DBA has to know almost everything a developer knows in order to help them when the screw up or need to know how to do something. So I see no problem with the recent series.

-- Mark D Powell --

by maryweilage

This comment is from member Ryan Murray:
"A technique I have used in the past (table of alarms generated by a telecom hardware monitoring application) that is very effecient when viewed fro mte perspective of the application as a whole is to maintain a table which contains tha available "hole" values. A stored proc can be used to retrieve the "next" id which consults first this table, and if there are no hole values left, then the driving sequence object (or other new id generator) for the table. The "hole" value table can be maintained by either the procedure which is responsible for creating the holes (ie deleting records) or a programmed scan of the table can be done at regular intervals. In the case that the sequence (or other new id generator) runs out of values, the scan procedure can be activated immediately or an algorithm like your can be used. By doing a periodic scan instead of finding the "holes" every time, the performance on a table with millions of rows is notably better."

by maryweilage

Here is Oracle e-newsletter author Scott Stephens' response to this reader's comment:
"Yes, maintaining a table of "holes" is much better performance where contiguous sequence numbers is important, (and re-use is allowed), but requires some advanced planning. If you have the foresight to realize this, it is the better way to go. Even if you are using a sequence generator, if the database is shutdown before all the values in the sequence cache are used up, those values are lost resulting in a "hole" in the next sequence, even with triggers. The technique I outlined in my article was written as a response to the numerous FAQs out there which recommend full table scans to look for missing values, or some tricks using MAX or MINUS."

This discussion is closed to new replies.