How to change CHARACTER SET parameter not to a superset

This is a method to change CHARACTER SET not to a superset as UTF-8, but to one at the same level,
e.g. from WE8ISO8859P1 to EL8ISO8859P7 (Greek).

For Oracle 9 and up, make sure you are connected "AS SYSDBA" in sqlplus.
For Oracle 8/8i, make sure you are connected as INTERNAL in svrmgrl.
Then follow these steps:

SHUTDOWN IMMEDIATE;

Make sure there is a database backup you can rely on, or create one.

STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE EL8ISO8859P7;

An alter database takes typically only a few minutes or less.
It depends on the number of columns in the database, not the amount of data.

SHUTDOWN;

If you use Oracle 8, then also do:

STARTUP RESTRICT;
SHUTDOWN;

The extra restart/shutdown is necessary in Oracle8(i) because of a SGA initialization bug which is fixed in Oracle9i.
Restore the parallel_server parameter in INIT.ORA, if necessary.
Restart the database:

STARTUP;

Forms fail to generate after 10g RDBMS upgrade [cannot pass cursor variables to a procedure that is called through a database link]

After 10g RDBMS upgrade some forms fail to generate. An example is shown in the image, where you can see the generation is stuck compiling a trigger.
The relevant Metalink note is 300990.1:
Compiling Form Fails or FRM-10760 When Using TYPE Declarations In Procedure Called Via DB Link.

Although the note refers to Forms 9.0 to 10.1, the problem also occurs in Forms 6i, since it is a RDBMS functionality change:

In Oracle Server 10g and higher restrictions have been placed on cursor variables. To quote from the Oracle Server 10g documentation, Chapter 6 Performing SQL Operations from PL/SQL:
Restrictions on Cursor Variables
You cannot pass cursor variables to a procedure that is called through a database link.
The restriction therefore applies to TYPE declarations, %TYPE and %ROWTYPE used in a remote database procedure.

In the trigger, in which the compilation hungs, there is a call to a procedure:

XXE_F36_LLU_WCRM.prc_Get_LocationInfo

In this package there are declarations, like:

prec_cli IN w_llu_ll_order@datallu%ROWTYPE

p_wcrm_order_id IN w_llu_ll_order.order_id@datallu%TYPE

w_llu_ll_order is a table in a remote database, accessed via a DB link.
These declarations have to be replaced.
I tried 2 workarounds, which both work.
The first is to create local views that point to the remote table, like:

create view apps.v_w_llu_ll_order as select * from w_llu_ll_order@datallu;

and then replace the above declarations:

prec_cli IN v_w_llu_ll_order%ROWTYPE

p_wcrm_order_id IN v_w_llu_ll_order.order_id%TYPE

The second is to check the column types in the remote table and explicitly set the variable types to match them:

p_wcrm_order_id IN number(10)

Now, the form generation will not hung.

Find datafiles' High Watermark per tablespace

This is an easy way to automatically create the commands to resize datafiles to their HWM and reclaim space.
Just, declare your tablespace name in V_TBLSPC variable:

SET SERVEROUTPUT ON

DECLARE
   V_STMT     VARCHAR2 (500);
   V_TBLSPC   VARCHAR2 (30) := 'CZD';

   CURSOR C1
   IS
      SELECT FILE_ID
        FROM DBA_DATA_FILES
       WHERE TABLESPACE_NAME = V_TBLSPC;
BEGIN
   FOR LINE IN C1
   LOOP
      SELECT    'ALTER DATABASE DATAFILE '
             || ''''
             || D.FILE_NAME
             || ''''
             || ' RESIZE '
             || NVL (CEIL (D.BYTES / 1024 / 1024 - TAKE_BACK.TAKE_BACK_MB),
                     D.BYTES / 1024 / 1024)
             || 'M;'
                SQL
        INTO V_STMT
        FROM DBA_DATA_FILES D,
             (SELECT SUM (BYTES) / 1024 / 1024 TAKE_BACK_MB
                FROM DBA_FREE_SPACE
               WHERE TABLESPACE_NAME = V_TBLSPC AND FILE_ID = LINE.FILE_ID
                     AND BLOCK_ID >=
                            NVL (
                               (SELECT (A.BLOCK_ID + (A.BYTES / B.BLOCK_SIZE))
                                  FROM DBA_EXTENTS A, DBA_TABLESPACES B
                                 WHERE A.BLOCK_ID =
                                          (SELECT MAX (BLOCK_ID)
                                             FROM DBA_EXTENTS
                                            WHERE FILE_ID = LINE.FILE_ID
                                                  AND TABLESPACE_NAME =
                                                         V_TBLSPC)
                                       AND A.FILE_ID = LINE.FILE_ID
                                       AND A.TABLESPACE_NAME = V_TBLSPC
                                       AND B.TABLESPACE_NAME = V_TBLSPC),
                               0)) TAKE_BACK
       WHERE D.FILE_ID = LINE.FILE_ID;

      DBMS_OUTPUT.PUT_LINE (V_STMT);
   END LOOP;
END;
/

A sample output of the above script is:

ALTER DATABASE DATAFILE '/oracle67/oradata/czd20.dbf' RESIZE 4994M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd05.dbf' RESIZE 3091M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd06.dbf' RESIZE 3092M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd07.dbf' RESIZE 3094M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd08.dbf' RESIZE 3093M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd09.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd01.dbf' RESIZE 3095M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd10.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd02.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd03.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd04.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle40/oradata/czd11.dbf' RESIZE 4995M;
ALTER DATABASE DATAFILE '/oracle43/oradata/czd12.dbf' RESIZE 9998M;
ALTER DATABASE DATAFILE '/oracle48/oradata/czd13.dbf' RESIZE 8093M;
ALTER DATABASE DATAFILE '/oracle52/oradata/czd14.dbf' RESIZE 3996M;
ALTER DATABASE DATAFILE '/oracle54/oradata/czd15.dbf' RESIZE 14996M;
ALTER DATABASE DATAFILE '/oracle58/oradata/czd16.dbf' RESIZE 4000M;
ALTER DATABASE DATAFILE '/oracle59/oradata/czd17.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle62/oradata/czd18.dbf' RESIZE 13497M;
ALTER DATABASE DATAFILE '/oracle66/oradata/czd19.dbf' RESIZE 15497M;
ALTER DATABASE DATAFILE '/oracle69/oradata/czd21.dbf' RESIZE 11993M;
ALTER DATABASE DATAFILE '/oracle71/oradata/czd22.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle75/oradata/czd23.dbf' RESIZE 6996M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd24.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle76/oradata/czd25.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd26.dbf' RESIZE 3995M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd27.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd28.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle87/oradata/czd29.dbf' RESIZE 24985M;
ALTER DATABASE DATAFILE '/oracle88/oradata/czd30.dbf' RESIZE 24641M;
ALTER DATABASE DATAFILE '/oracle89/oradata/czd31.dbf' RESIZE 13641M;
ALTER DATABASE DATAFILE '/oracle90/oradata/czd32.dbf' RESIZE 8961M;