How can one see who is using a temporary segment?

For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.

If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT

From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:

select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.saddr = u.session_addr
/

select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value
/

How do I find used/free space in a TEMPORARY tablespace?

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;

How does one see the uptime for a database?

Look at the following SQL query:

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
or
SELECT to_char(logon_time,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
or
Check on operating system level when the PMON process was stated (UNIX/ LINUX only):
ps -ef grep pmon

Users still running on Oracle 7 can try one of the following queries:

column STARTED format a18 head 'STARTUP TIME'
select C.INSTANCE,
to_date(JUL.VALUE, 'J')
to_char(floor(SEC.VALUE/3600), '09' )
':'
-- substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
substr (to_char(floor(mod(SEC.VALUE/60, 60)), '09'), 2, 2)
'.'
substr (to_char(mod(SEC.VALUE, 60), '09'), 2, 2) STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC,
SYS.V_$THREAD C
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';

select to_date(JUL.VALUE, 'J')
to_char(to_date(SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_char(to_date(JUL.VALUE, 'J') + (SEC.VALUE/86400), -- Return a DATE
'DD-MON-YY HH24:MI:SS') STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';

How does one give developers access to trace files (required as input to tkprof)?

The "alter session set sql_trace=true" command generates trace files in USER_DUMP_DEST that can be used by developers as input to tkprof. On Unix the default file mask for these files are "rwx r-- ---".

There is an undocumented INIT.ORA parameter that will allow everyone to read (rwx r-- r--) these trace files:
_trace_files_public = true

Include this in your INIT.ORA file and bounce your database for it to take effect.

Can one resize tablespaces and data files?

Add more files to tablespaces
To add more space to a tablespace, one can simply add another file to it.
Example:
ALTER TABLESPACE USERS ADD DATAFILE '/oradata/orcl/users1.dbf' SIZE 100M;

Resize datafiles
One can manually increase or decrease the size of a datafile from Oracle 7.2 using the following command:
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Extend datafiles

Also, datafiles can be allowed to automatically extend if more space is required. Look at the following commands:

CREATE TABLESPACE pcs_data_ts DATAFILE 'c:ora_appspcspcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;

ALTER DATABASE DATAFILE 1 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Can one rename a tablespace?

From Oracle 10g Release 1, users can rename tablespaces.
Example:
ALTER TABLESPACE ts1 RENAME TO ts2;
However, you must adhere to the following restrictions:

• COMPATIBILITY must be set to at least 10.0.1
• Cannot rename SYSTEM or SYSAUX
• Cannot rename an offline tablespace
• Cannot rename a tablespace that contains offline datafiles

For older releases, use the following workaround:

• Export all of the objects from the tablespace
• Drop the tablespace including contents
• Recreate the tablespace
• Import the objects

How are extents allocated to a segment?

Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.

Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. Clearly Oracle doesn't always round extents to a multiple of 5 blocks.

The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.