Config:
AMM is used, SETALL for filesystemio_options, database is running in NOARCHIVELOG mode, sga_max_size is 70GB, sga_target is 41GB.

Here is my problem:
I have 200 million records in table A. I create an empty table (table B) using the NOLOGGING option. I aggregate the data from table A by a primary key to populate table B
(truncate table B; insert into table B select MIN/MAX <columns> from table A group by primary_key;) There are also a couple of left outer joins in this query.

I 'watch' the progress (table scans/joins) in the v$session_longops table.

I get to the point where all table scans and hash joins are finshed, but my query still shows in the v$session_longops table, even though it is not doing anything (that I can see).

I understand this is a bit vague, but I am really struggling with this one. The query would usually run in 90 minutes and be done. Now it runs the normal 90 minutes and sits idle another 90 minutes before finishing.

This is a test DB (that will eventaully go into production). Is there anything wrong with setting the SQL_TRACE parameter using alter system instead of session? Do you know where the log file would be placed on the system?

After I bounced the instance, I see:
ORA-19815: WARNING: db_recovery_file_dest_size of 4194304000 bytes is 99.43% used, and has 23871488 remaining bytes available.

I am not using RMAN or any other backup utility.

The recovery file dest is a setting for when you want Oracle to handle some functions for you. If it is being filled up with archived logs, you should use rman to backup so the unneeded logs get deleted. This depends on various settings. Since you are running noarchivelog mode, that is probably not what is happening, so I wonder what else is going on there, maybe you were once archivelog and then changed without cleaning up. You might want to run rman crosscheck and delete obsolete commands in case there are old things there, so Oracle can match what it thinks with reality. Look at the directories nearby (like udump and cdump), perhaps you may be getting some other trace files that need to be cleaned up. Also check for adcri filling up stuff. http://gavinsoorma.com/2010/09/purging-trace-and-dump-files-with-11g-adrci/

Whether the cannot allocate and not complete errors are meaningful depends on what surrounds them. In some cases, it can just be a minor performance annoyance. In others, your db stops (though that would normally be in archivelog mode). Post the 100 lines around the error so we can tell.

Watch your disk space when tracing.

See this for trace analyser: http://awads.net/wp/2006/07/18/oracle-trace-analyzer-is-tkprof-on-steroids/

If your database is in NOARCHIVELOG mode, (quoting your first post "database is running in NOARCHIVELOG mode") the db_recovery_file_dest wouldn't have new archivelogs being written to it. I guess you have only Consistent (aka "cold") backups written to this location. Do you happen to have other files also written here ? Query V$RECOVERY_AREA_USAGE.