Answer originally left in comments by David Mann:
If you are unable to run FREETEMPORARY then I believe your only option is to disconnect/reconnect the session. This will cause a cleanup of any temp blob space used by that session. Had a similar issue here where we couldn't touch the app code, so we ended up tweaking their connection pool to expire and reconnect more frequently.
Oracle Metalink Doc ID 802897.1 "How to Release the Temp LOB Space and Avoid Hitting ORA-1652" covers this, for 11g your option is to invoke FREETEMPORARY or to exit session. They also mention a new event 60025 which is supposed to free temp segments when all the temp LOBs for a session are no longer used, but I didn't see any other detail on that.
Let's say you created the database with the below parameters:
NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI
And by create, I really mean create, from scratch. A DBCA custom database, or running CREATE DATABASE and dictionary scripts manually.
If this happened, these will be your database level NLS properties:
SQL> select * from nls_database_parameters
where parameter in ('NLS_COMP', 'NLS_SORT')
order by parameter;
PARAMETER VALUE
---------- ----------
NLS_COMP LINGUISTIC
NLS_SORT BINARY_CI
(By default here you should see BINARY and BINARY, and to be honest, I can not remember a single case where the database had different values - except the one I have just created in my sandbox.)
Given the above, you will get the same execution plan as in your question. You can restart the instance, or set NLS_COMP and NLS_SORT at session or system (instance) level to the same values, it will not 'fix' the execution plan.
To modify the above setting, it is technically possible (but do not ever do this in a real database) to update these values manually (re-running the dictionary scripts will not update this):
SQL> update props$ set value$ = 'BINARY' where name in ('NLS_COMP', 'NLS_SORT');
2 rows updated.
SQL> commit;
Commit complete.
After this (and a shutdown + startup), the same query used the fixed index without any implicit NLSSORT calls in the filter.
Revert the changes:
SQL> update props$ set value$ = 'BINARY_CI' where name in ('NLS_SORT');
1 row updated.
SQL> update props$ set value$ = 'LINGUISTIC' where name in ('NLS_COMP');
1 row updated.
SQL> commit;
Shutdown, startup, explain, dbms_xplan.display, and it is wrong again.
Another (troublesome, but at least supported) option would be recreating the database with the default (BINARY, BINARY) values.
Best Answer
Oracle explains the V$SQL_MONITOR view in its official documentation.
Looks like it only keeps the record there for a minute or two, or until it needs the space to show the other queries.