Resizing and reclaiming free space on Tablespaces

You will have to examine how the question is worded. By default 11g will create all three tablespaces. You can however create a database with only SYSTEM and SYSAUX. Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM tablespace, but you would be ill advised to do so and I really don't think they would look for that in an answer.

From the 11.2 Administration Guide:

The SYSAUX tablespace is always created at database creation.

From another section of the Administration Guide:

An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA)...

If no undo tablespace is available, then the instance starts without an undo tablespace, and undo data is written to the SYSTEM tablespace. You should avoid running in this mode.

From the 10.2 Concepts Guide:

...the SYSAUX tablespace is always created during database creation or database upgrade...

During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

Note: If the SYSAUX tablespace is unavailable, such as due to a media failure, then some database features might fail.

From the 10.1 SQL Reference:

You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode.

From the 10.1 Administrators Guide:

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

http://oradbpedia.com/wiki/The_SYSAUX_Tablespace has this take:

The SYSAUX tablespace is a new tablespace that is required in Oracle Database 10g...

When you migrate to Oracle Database 10g, the SYSAUX tablespace needs to be created as a part of that migration. This is done after mounting the database under the new Oracle Database 10g database software. Once you have mounted it, open the database in migrate mode with the startup migrate command. After the database is open, you can create the SYSAUX tablespace.

Note: The loss of the SYSAUX tablespace is not fatal to your database. In our testing it appears that the only real impact is that certain functionality related to the occupants of the SYSAUX tablespace is lost.

Earlier versions did not have a SYSAUX tablespace:

SYSAUX is the name of the compulsory tablespace, introduced in Oracle 10g.

The short answer is No. Unfortunately the way to do this in Oracle does require the "big stack of boring queries". The articles you linked to are some of the best information available on the subject. The datafile does indeed become fragmented, so that even if free space exists below the highest segment, Oracle will not automatically consolidate it when a RESIZE is done.

To "defragment" the tablespace you need to move these segments to the start of the datafile rather than at the end. For tables this is an offline process meaning the table will be unavailable while the move is taking place. Indexes can be moved either offline or with Enterprise Edition they can be moved online. Since you have an outage window I recommend you follow these steps.

A. Shrink datafiles with free space beyond the high water mark. This can be done as follows (the query is similar to Frosty Z's procedure):

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'alter database datafile '''|| file_name || ''' resize ' || 
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a 
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) 
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.

SELECT DISTINCT tablespace_name FROM
(      
    SELECT tablespace_name, block_id + blocks LastBlock,
       lead(block_id) OVER (PARTITION BY File_ID 
          ORDER BY tablespace_name, file_id, block_id) NextBlock
       FROM dba_free_space 
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;

C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)

SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
   SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
   FROM dba_free_space
   WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;

D. Move each table and rebuild the indexes and statistics.

E. Repeat step A.

I just built most of these queries, so you will want to thoroughly test them before use. I suppose you could create a procedure that would use EXECUTE IMMEDIATE to create the actual statements to run dynamically, but because queries will receive ORA-08103: Object no longer exists while the move is in progress, I think it is best to control that process manually even if it does mean a bit more time/effort.