The SQL*Plus trace feature can be turned on for a running
session if the SID and SERIAL# are known. This information is
available from the v$session dynamic performance table (view).
There are several different practical methods of querying
v$session such as by the Oracle user id (username), by the OS
user id (osuser), or by the OS process id (process) being the
most common.

Once
the sid and serial# have been obtained the Oracle provided
package dbms_system procedure set_sql_trace_in_session
can be called: execute
sys.dbms_system.set_sql_trace_in_session(14,32293,TRUE);

The
trace will automatically terminate when the session is exited,
but it can be stopped earlier if desired by changing TRUE to
FALSE in the set_sql_trace_in_session parameter list invocation
call.

The
trace file is written to the init.ora user_dump_dest parameter
value, which can be found in either the init.ora file for the
database or by querying v$parameter:
The trace file name is normally ora_OSPID.trc, where OSPID is the
Operating System process id, which is the value for the spid
column of v$process for the Oracle
process associated with session. See the SQL above for this
information.

On
UNIX systems the command ls ltr can be used to list the
files with the most recent appearing last. To be usable tkprof
needs to be executed against the trace file.

Personally
I do not like to give developers direct access to the dbms_system
package since developers should not generally execute some of the
procedures so I place a cover package on my system. As user sys
grant execute on dbms_system to the package_owner, which will be
system in my example:

The ordebug utility can also be used
to trace an already running system. The example that follows was
executed from SQL*Plus instead of the obsolete with version 9 svrmgrl
utility. Comments denoted by dashes were inserted into copy/paste
of screen output.

You can set the session using either the OS process id as in
my example or using the Oracle pid: setorapid
N where N is the value of the column pid
in v$process for the session to be
traced. In the example above that pid would have been 16
according to Oracle. The available trace levels are:

As of version 8.1.7 the dbms_system package
is not covered in the Oracle8i Supplied PL/SQL
Packages manual.

The following resources are available on Metalink.
Note 100883.1 "How to Create a SQL Trace from Another
Session"
Note 1056567.6 "Using SQL_TRACE_IN_SESSION in ORACLE
Versions 7 and 8"
Note 1058210.6 HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING
ORADEBUG