Problem solve Get help with specific problems with your technologies, process and projects.

Oracle questions answered

Answers to miscellaneous Oracle questions: discrete transactions, LONG RAW and image files, migration from Access, and more.

Here are answers to several Oracle questions leftover from our Ask the Experts feature. Rick is an Oracle DBA here at TechTarget.

What is a discrete transaction, and how are these done not using rollback segments?

A discrete transaction is a special transaction that can be performed very quickly, but it does have some limitations. They are generally used only for transactions where performance is critical. They must adhere to the following restrictions:

  • Modify only a few database blocks
  • Never change an individual database block more than once per transaction
  • Do not modify data likely to be requested by long-running queries
  • Do not need to see the new value of data after modifying the data
  • Do not modify tables containing any LONG values

These transcations can be performed faster if they are designated as discrete transactions using the procedure DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION.

During a discrete transaction, all changes made to any data are deferred until the transaction commits. Discrete transactions DO use rollback segments, but rollback is written out only to the rollback segments at commit time. Before it is written out it is stored in the PGA (Program Global Area). They also write out redo information to the redo log file at commit time.

I'm looking for advice and or resources about my system: Two Compaq UNIX boxes in TruCluster ASE (2-node cluster), RAID (shared storage), Oracle8 8.1.5 RDBMS and Oracle Developer v6.0 (Forms, Reports, etc.) software. Initially, RAID drives are connected to box #1. The RAID drives are configured to switch and re-mount to UNIX box#2 if failure occurs on box#1. Multiple databases in the mix--all database-related files will be placed on the RAID drives. My questions: (a) Where should the Oracle8 RDBMS software distribution be installed?--separate installs (i.e., two Oracle Homes) on local drives of each UNIX box OR once on a shared RAID drive which serves the UNIX box that is attached at the time? Is this totally optional--i.e., either will function correctly? Pros and Cons of each? (b) Same question as (a) only for the Oracle Developer v6.0 software install (which will occupy separate Oracle Home from RDBMS)? (c) Same question as (a) only for custom production database applications that run on UNIX database server side (e.g., Pro*C executables, SQL/shell scripts, etc.)? (d) Same question as (a) only for Forms, Reports, etc. executables that reside on UNIX database server? NOTE: Comment on the following if you have input: Custom production apps that utilize UNIX cron to kick off jobs at certain times I know will require special consideration with respect to how to configure shell and cluster-specific action scripts in this 2-node clustered environment; i.e., how to get the cron tasks to stay in sync and fail-over appropriately--to run only on primary node but not run on standby node, etc.

a) I have always placed the RDBMS software distribution on the failover disks. This is totally optional, and either will function correctly. This is mostly a matter of maintenance. Hopefully if you have invested in TruCluster you also have at least software mirroring for the disks installed directly in the two servers. So installing software on either the local disks or the shared disks should be just as safe. It would be frustrating to failover servers, with a small but possibly noticable downtime, just because of a disk failure. Keeping your RDBMS software distribution on the shared disks makes it much easier to maintain. Also, your shared RAID diskspace may be more expensive, but again hopefully if you feel the need to invest in two servers and TruCluster you are willing to buy plenty of diskspace.

I think the best reason for placing your RDBMS software distribution on the failover disks is for maintainability. This way you can be assured of the consistency of your binaries and config files. You are definitely going to want to make sure that your init.ora file and all of your $ORACLE_HOME/*/admin config files are identical. But the trickier bit will be making sure that you have also had identical binaries installed even after patches and upgrades. Most patches and all upgrades will require you to manipulate something inside of the database. Often at least one system table will need to be updated to match a patch. You should make every effort to ensure that the version of the software matches the datafiles. I have found that the easiest way to do this is to make sure there is only one version by placing it all on the shared disks. The alternative is to use policy and procedure to ensure that you always always always make the same changes on both boxes. This introduces a human error factor that you could have avoided.

b) I do not have direct experience with Oracle Developer, but I would be willing to make many of the same install decisions. Is it safe to assume that you will not want to run this from your idle failover server? I cannot speak about TruCluster specifically, but many cluster solutions allow you to failover separate applications in different directions. Server A could be the primary for the database, using server B as a failover. While Server B could be the primary for the application, using server A as a failover. This allows you to use your investment in two servers to get better performance normally with reduced performance during a failover situation.

c) I can only imagine what you have in mind. But you do mention that they run on the UNIX database server side. If they need to be on the database server, and they are critical enough to be failed over, I would again recommend placing them on shared disks, for the same reason.

d) Again, if they are cricital and need to be failed over, you will want to make sure that they are always in sync. Place as much as you can on the shared disks.

Keep in mind there will be some things you cannot place on the shared disks. Much of the OS will must be on local disks. Work closely with your sysadmin to ensure that these two systems are as identical as possble.

Crontabs are another one of those tricky bits. You will need to handle this at least partly by policy and procedure. Whenever one system is changed you will need to update the other system. Perhaps TruCluster has some facility for synchronizing crontabs, but I doubt it. I suggest you put as much of the meat as possible into scripts on the shared disks. Then place small scripts on the local disks that look something like this:

if [ -x /shared_mount/script_dir/real_script ]

This script will test if real_script exists and is executable. You should then be able to keep identical crontabs on either machine.

Oracle has the datatype "Long Raw" which can store image files. However, how do you enter the data/file into the database? Is it via "DBA Studio"?

It would be great if Oracle had made a simple interface in DBA Studio, but they didn't. Also, are you sure you want to use LONG RAW? If you are using Oracle 8i, the BLOB field will make your life a lot easier. Unless you have an existing database and application that is already dependent upon LONG RAW, you should avoid it. To quote Oracle's Documention, it is "Provided for backward compatibility". If you must use the LONG RAW, you will need to develop an application, most likely in C, that can insert the data for you. You should consult the Oracle Call Interface (OCI) and the Oracle Programmers Reference. If you can use a BLOB, you will have many options available to you, including the OCI, InterMedia using PL/SQL, and the SQL Loader.

A very quick and simple way to load images into BLOBs would be to use the SQL Loader. You can use this method to load the images to the destination table, or you can load them into an intermediate table and then use SELECT statements to move the image to its final destination. If you have an ongoing need for users to continually load images, I highly recommend investing the time to develop a custom application.

Using SQL Loader

Given the following table:
CREATE TABLE image_table (image_id NUMBER(5), ext_filename varchar2(40),
image_data blob);

Control File:

INFILE 'loadimages.dat'
INTO TABLE image_table
image_id INTEGER External (5),
ext_filename char(40),
image_data LOBFILE (ext_filename) TERMINATED BY EOF

Data File (loadimages.dat):


Currently all indexes are in the users permanent tablespace. What is the most efficient way to move these indexes in their own tablespace? There are hundreds of indexes, so manually rebuilding them is not really a consideration.

It is tempting to think there is a way to move indexes from one tablespace to another without rebuilding them. After all, one must figure all the data necessary is stored in the datablocks. We just want to move the appropriate blocks from one tablespace to another. Well, if there is a way, I have not found it, yet. It appears you cannot avoid the CPU time it will take to rebuild the indexes. The obvious way, which you understandably don't want to do, is to drop them and recreate them with the storage clause you want. But, there are ways to script the work that needs to be done. The easiest of which I think is to use the ALTER INDEX command. To alter an index, your schema must contain the index, or you must have the ALTER ANY INDEX system privilege. To quickly build a script to rebuild all of the offending indexes, use the output of the following command:

select 'alter index '|| index_name ||' rebuild tablespace ;' from all_indexes where tablespace_name ='USER';

Keep in mind that running this for hundreds of indexes will take a long time. You may want to break this up in order to spread this out over multiple sessions. The good thing about this method is that you will have each indexed in the old tablespace and usable right up until the moment that it becomes usable in the new tablespace. Keep in mind that you will need to have enough space in the new tablespace to hold the index you are rebuilding without freeing up any space in the old tablespace during a rebuild.

What's the best way to migrate data from Access 97 to Oracle 8.1.7 on Windows NT?

There are a two good options for migrating between Access97 and Oracle8. If you need to move only a few tables of data, you can move them one at a time manually using the export function. If you need to move an entire database, I recommend using the Migration Workbench. The export method is faster and easier if you need to move only a single table, or even just a few tables. I have had difficulty exporting tables with a lot of data in Memo Fields. If you need to export a table with Memo Fields, I recommend giving the Migration Workbench a try; it works consistently.

Single Table Export:

Using this method you will be able to export your tables one at a time. You will need to have the Oracle8 Client and make sure you have the latest ODBC drivers installed. Be sure to setup a Net8 Service Name using the Net8 Assistant. Once you have a working Net8 Service Name you need to have a Datasource (DSN) setup in the ODBC Adminstrator. In Access97 go to the Tables Tab. Right click on the table you wish to export. Select "Save as/Export". Choose "To an External File or Database". This should present you with a Save Table Dialog. Change the "Save as type" to "ODBC Databases". This should immediately change to an Export Dialog. Enter the name you want this table to have in Oracle8; it should default to the same name it had in Access97. When it prompts for the DSN Name you should be able to select the DSN you set up for your Oracle database. Now you should see a login dialog; use your Oracle username/password that you want to own the tables. Be sure the Service Name matches the Service Name you setup in Net8 Assistant.

Migration Workbench:

Using this method you will be able to move an entire database, or select just the tables you choose. This method is especially useful if you need to move a lot of tables. I found it to be a little slow at times, but it always worked eventually. You will need to get a free Oracle Technology Network account, to download it. There are far too many options and possiblities to go through in this format, but there is documentation available. For complete details see:

For More Information

  • What do you think about this tip? E-mail the Editor at with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle DBA jobs, training and certification

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.