Reader's comments on Oracle 8 RDBMS Server

Reader's Comments

A great free tool for working with oracle is Toad. In particular it makes it very easy to edit PL/SQL procedures in the database.
Get it at: http://www.toadsoft.com

-- David Tauzell, August 29, 1998

I'm not a DBA, I'm a sysadmin. But as a sysadmin, you have to work with DBAs and you learn little things about the RDBMS, like how to startup/shutdown, how you do a backup, how you find out which areas of the disk the RDBMS has glommed onto, etc.
From my perspective, Informix is a far better-designed product than Oracle. One of the DBAs at work says Informix is five years ahead of Oracle in technology, and I believe it.
Informix is also vastly easier to use and to learn; after a year of working at an Oracle site I had learned how to startup/shutdown. After a month of working at an Informix site, I had learned startup/shutdown, how to do backups, how to find out what areas of the disk the database has glommed onto, how to install the Perl DBI module for Informix, how to run it from an Intel/FreeBSD box to a HPPA/HP-UX box, etc.
Think of a computer-industry leader who is fantastically rich, fantastically driven by the art of the deal, who likes fast cars, who has near-total market share, and whose products have kinda crummy pragmatics (buggy, hard to work with, hard to troubleshoot, beloved of managerial types).
Not Bill Gates, it's Larry Ellison.

-- Daniel Boyd, September 22, 1998

Oracle documentation The Oracle documentation became available around a year ago at http://technet.oracle.com; you have to register there before you can use it.
Locking The lmode=6 clause finds all the exclusive locks. A more direct way to find the blocking session and the sessions that are blocked would use the v$lock.block column.
col type heading "Lock Type" format a15 col lmode heading "Mode Held" col request heading "Mode Requested" col block heading "Blocking?"
select sid,type,lmode,request,block from v$lock where block != 0 or request != 0 order by id1,id2,block desc
ID Lock Type Mode Held Mode Requested Blocking? ---- --------------- ---------- -------------- ---------- 16 TX 6 0 1 31 TX 0 6 0 15 TX 0 6 0
From this you know that session 16 is blocking sessions 31 and 15. You can now query v$sqltext to find out what the sessions are doing.
select s.sid,s.serial#,a.sql_text from v$sqltext a,v$session s where a.hash_value = s.sql_hash_value and s.sid in &sidstring order by s.sid,a.hash_value,a.piece
SQL> / Enter value for sidstring: (16,31,15)
SID SERIAL# SQL_TEXT ---- ---------- -------------------------------------------------- -------------- 15 30 update x set the_data = 'this is not a test' where the_key=1 16 15 update x set the_data = 'this is a test' where the_key=1 31 185 update x set the_data = 'this is also a test' where the_key=1
'' is the same as Null For the next time around... you could default the phone_number column to 'n/a' when you create the table. If you load data behind the scenes you could use insert statements that omit the phone_number column. The loaded data will have pn='n/a'; the data entered on the form will have either null, or an actual phone number...
PL/SQL You can see the text of your own packages,procedures and functions without the select any table privilege: select line,text from user_source where name = 'YOUR_PROC_NAME' order by type,line

-- Chaim Katz, January 5, 1999

I would have to agree with the Informix vs. Oracle comments. As far as 99% of the user-base is concerned, both products supply about the same level of service, cost about the same amount of money, and run about as fast.
I should mention at this point that I'm an Informix DBA by profession who was forced to become an Oracle DBA about a year ago due to interesting management decisions. This probably gives me a skewed perspective on things, so keep that in mind.
Anyway, the one place that the two systems really diverge in my opinion is in basic entry-level administration. While both systems provide a complete set of 'system tables' that an advanced DBA can write queries on, Informix is light-years ahead as far as friendly common tasks go. A good example of this would be the amount of effort to do common administrative procedures. Some examples follow.
I was assured in official Oracle training that I wanted to rebuild my b-tree indexes periodically to reclaim the space lost when rows were deleted from the main table. This surprised me, as Informix takes care of this detail automatically as data is inserted and deleted. I don't actually recall the command to do it while the index is in place, and Oracle doesn't provide a 'how to do daily maintenance' user guide (at least, not in my opinion).
To determine the amount of space used in every data file in the system, the Informix command to use would be 'onstat -d'. In Oracle, I have to write a query in PL/SQL to accomplish the same task.
In Informix, I can get a complete point-in-time binary backup of a running system by typing 'ontape -c -l0' (Create a level 0 backup). This can go to a tape, or a single file on disk. Oracle, on the other hand, requires you to:

Obtain a list of all current datafiles.
For each file:
Manually suspend activity on the file.
Copy the file to a free area.
Restore activity on the file.
Add the system configuration files to the free area.
Tar the individual files together to a tape.

Admittedly, this is not difficult to do. It requires a fairly short shell script that can be cobbled together in an hour or so. However, when running an enterprise-level system this has to be tested and validated. You'd probably want to add some form of intelligence to the script to restore access to locked files if the script is killed in mid-backup as well.
Quite simply, Informix has either automated or provided utilities to cover 99% of standard maintenance issues (consolidation, backups, etc). They both give you sufficent information that you could write your own if you wished, but Informix assumes that you'll be wanting to make hot backups, etc. and provides tested, supported tools to make your life easier.
After a couple of years of dealing with Oracle, I am beginning to see why Oracle DBAs are more in demand than Informix DBAs. I can (and do) administrate over 100 live production OLTP Informix databases with more ease and confidence than I have when working with our 2-3 internal Oracle development systems.
Aargh.

-- Richard Stanford, February 24, 1999

Another way to handle long strings is to break them up into segments. Instead of storing the text or a message directly in a message table, you add another table, say msg_text, with columns msg_id, seg_num, and seg_text. On insert the message is split into segments limited to the smaller of the query limit and database varchar limit, minus the overhead of the sql query (the "insert..." stuff). It makes for an ugly insert operation, though.
I had to do this to work with PostgreSQL, which has an 8K limit. If you split on a word boundary (a whitespace character) then a full text indexer might be able to make sense of this.

-- Ari Halberstadt, March 4, 1999

ConText.. what ConText? Take a look at the Oracle Home Page.. Search engine licensed by Oracle is the Altavista Engine..... Or am I missing something here ???

-- Ravi Nagpal, May 20, 1999

Re: How to rename a column in Oracle

You can do something like the following, though I agree it shouldn't be necessary:

SQL> describe phone_directory
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                                     VARCHAR2(100)
 PHONE                                    VARCHAR2(100)

SQL> create table new_phone_directory (full_name varchar(100), phone varchar(100));

Table created.

SQL> insert into new_phone_directory (full_name, phone)
  2  select name, phone from phone_directory;

1 row created.

SQL> commit;

Commit complete.

SQL> drop table phone_directory;

Table dropped.

SQL> rename new_phone_directory to phone_directory;

Table renamed.

-- Larry Mulcahy, November 15, 1999

Doctor Philip Greenspun,
About the Oracle Documentation you say "I can't for the life of me figure out why this isn't simply available at www.oracle.com but it isn't.".
However, Oracle has all of their documentation on its technical site: technet.oracle.com. You have to register yourself to access the technet but it's free. Besides, nowadays, everybody asks for registering. Even to add this comment! :))
For instance, the Oracle8i Server Documentation, Release 8.1.5 may be found at http://technet.oracle.com/doc/server815.htm.
Best regards and thank for your nice site, Pedro Bizarro

-- Pedro Bizarro, December 27, 1999

This page is very helpful. Thanks.
One thing, i se that Informix is more appreciated than Oracle. I think there is one point where Oracle is however interesting, the use of SQL for all administration process of the database. This way will be the most useful, i believe, for tomorrow networked application.
Note that it's possible with Oracle to compile all database operation on SQL script, wich is very effective for migration or structural evolution of distributed architecture.
Nevertheless, Oracle suffer of this great problem, lot of important administration tasks could not be done outside SQL statements,even if the most common tasks are automated under entreprise tools.
Cedric A.

-- C A, January 4, 2000

To determine block size on Linux, use tune2fs.
According to the documentation I found, block size can be Linux distribution specific. On Red Hat 6.1, it appears to be 4096, and tune2fs can be found at: /sbin/tune2fs.

-- Jerry Asher, January 14, 2000

More Linux filesystem stuff: On my (Debian) system, tune2fs will only change stuff; to see the blocksize use 'dumpe2fs -h' (without -h, you get a dump of the entire ondisk block structure, which can be interesting, but is probably not what you want).
The blocksize is determined when the filesystem is created (mke2fs). I've got two at 4k, probably the Debian default, and one at 1k that I created it later, and intended to put lots of small files on it (C code, sorry Phil). Now that Oracle is living on it, the choice seems less wise.
You'll need to be root to use this, as it directly accesses the raw disk device.

-- Steve Greenland, January 24, 2000

I just came across a HOWTO for installing Oracle 8i on Linux that may be of help to anyone attempting such a project:
http://www.zx81.org.uk/computing/oracle/oracle-howto/oracle-howto.html
Good Luck.

-- John Milazzo, February 18, 2000

To get block size on RH Linux you can do a tune2fs -l /dev/disk1

-- Robert Bigin, March 1, 2000

If you use Oracle server, you need a copy of Embarcadero's DB Artisan. It's an amazing product that allows you to change tables, indexes tables, tablespaces without having to learn the arcane syntax and strange table names of Oracle innards. It's a DBA in a box and hugely better than the obtuse GUI that comes with Oracle. Unfortunately I think it runs only under Windoze. Be careful though, it's a powerful tool and can powerfully ruin your production DB in seconds. No affiliation, blah, blah...

-- Rob Quinn, March 22, 2000

Your page shows that Oracle REALLY IS worth the money spent on it.
In many cases single 800MHz P III 1 GB RAM with 2x2x18 GB SCSI LVD RAID is enough to run whole enterprise if developers know something about tuning PL/SQL queries AND Oracle DBA knows about setting startup parameters. You did great work showing people that sometimes changing a single line in init..ora speeds up database.
After some hard work on Oracle 8i I have some tips that help other DBAs:

When Your developer forgets to compile all changed objects, You have to do it yourself. I know that DBA Studio allows this (yeah it's easier) , but via SQL*Plus it's much faster and even automatic. You do such a script:
spool recompile.sql SELECT 'ALTER ' || object_type || ' ' || object_name || ' compile;' from user_objects where status='INVALID'; spool off -- end and then You run
@recompile.sql Nice, works fine for me :)
Guys from Oracle have prepared special procedure for compiling whole schema, but it does not work well in some cases. This script does the same in simplier way. In some circumstances I had to execute @recompile.sql twice. So I added two executions of @recompile.sql to a script.

You can do refreshing optimizer's statistics this way:
spool analyze.sql SELECT 'ANALYZE TABLE ' || object_name || ' COMPUTE STATISTICS; ' from user_objects where object_type='TABLE'; spool off -- end and then run@analyze.sql
I have installed these hints into two shell scripts that are executed by cron every night. I don't know whether it's foolproof but it works. There is also a procedure GATHER_DATABASE_STATS or so, but this script is faster.
One of the ways to back up data from a single table is to create a backup one with same data in it. You do:
CREATE TABLE BACKUP_DATA AS SELECT * FROM DATA; It's much faster than exporting table and works also when You have constrains between tables.
Yours
Marcin

-- Marcin Marciniak, June 6, 2003

Add a comment