Profiling an Oracle 9i DB

How should it be used in these case?

you can also enable TRACE for that user or session to see what all is happening. The trace file generated will be a raw trace and you need to convert it to readable format by using tkprof utility.

For more details on the above, please refer to 12 chapter Using Oracle Trace in the below link

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm

Thanks

Is this something that be run from SQLplus, and output to a dump file?  If so what would the syntax be?

ALTER SESSION SET SQL_TRACE = TRUE;
 
-- you can do that with just a single statement but this has some performance overhead because a lot of data is written to the trace file on what all sql statements are being issued in the session.

And if I want to trace the session of another user?

if you want to know what is happening in a particular session or what are all the operatins done by a user without his knowledge, then either we have to enable sql trace at the init.ora level meaning a lot of trace files will be generated for each session/user and a lot of file system space is also required if you db is being used by many users/programs.

why don't you look at the below link to get an understadning of the log miner facility :

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm 

search for "Using LogMiner to Analyze Redo ogs"