10g: Use V$SQL_BIND_CAPTURE to find bind variables values

In your database, you probably see a lot of queries running using bind variables,
like :B1 in the following script, and you don't know its value:

DELETE FROM ASO_PRICE_ADJ_ATTRIBS WHERE PRICE_ADJUSTMENT_ID = :B1

If you need to find the value of this variable, first, query V$SQL to find the SQL_ID:

SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'DELETE FROM ASO_PRICE_ADJ_ATTRIBS%';

SQL_ID	SQL_TEXT
5tya0jzb74tu9	DELETE FROM ASO_PRICE_ADJ_ATTRIBS WHERE PRICE_ADJUSTMENT_ID = :B1

Then, use the SQL_ID in V$SQL_BIND_CAPTURE to find the value:

SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE
WHERE SQL_ID='5tya0jzb74tu9';

NAME	VALUE_STRING	DATATYPE_STRING	LAST_CAPTURED
:B1	15499478	NUMBER	03/12/2010 03:15:39 πμ
:B1	15075864	NUMBER	19/11/2010 02:31:16 πμ
:B1	15148907	NUMBER	20/11/2010 02:31:50 πμ
:B1	14251241	NUMBER	15/10/2010 03:45:30 μμ

You can see that at various points in time the query is used with different :B1 values.
From the LAST_CAPTURED column you can decide which value is the one you are interested.

Of course, you may combine these queries:

SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE A, V$SQL B
WHERE A.SQL_ID = B.SQL_ID
AND SQL_TEXT LIKE 'DELETE FROM ASO_PRICE_ADJ_ATTRIBS%'
ORDER BY LAST_CAPTURED;

You may use DBA_HIST_SQLBIND instead of V$SQL_BIND_CAPTURE, if you want to base your conclusions on AWR historical data.

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;

Find a schema's dependencies

Create the following, under SYSTEM or any other schema you may use for logging purposes:

CREATE TABLE HELPDESK.DEPSCHEMA
(
   OBJECT_ID              NUMBER,
   REFERENCED_OBJECT_ID   NUMBER,
   NEST_LEVEL             NUMBER,
   SEQ#                   NUMBER
)
TABLESPACE HELPDESK
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

GRANT ALL ON HELPDESK.DEPSCHEMA TO SYSTEM;

CREATE SEQUENCE HELPDESK.DEPSCHEMASEQ
   START WITH 0
   INCREMENT BY 1
   MINVALUE 0
   MAXVALUE 100000000
   NOCACHE
   CYCLE
   NOORDER;

GRANT ALL ON HELPDESK.DEPSCHEMASEQ TO SYSTEM;

CREATE OR REPLACE FORCE VIEW HELPDESK.DEPSCHEMA_V
(
   SEQ#,
   NEST_LEVEL,
   SCHEMA,
   OBJECT_TYPE,
   OBJECT_NAME,
   STATUS;
)
AS
     SELECT D.SEQ#,
            D.NEST_LEVEL,
            O.OWNER,
            O.OBJECT_TYPE,
            O.OBJECT_NAME,
            O.STATUS
       FROM HELPDESK.DEPSCHEMA D, ALL_OBJECTS O
      WHERE D.OBJECT_ID = O.OBJECT_ID(+)
   ORDER BY SEQ#;

CREATE OR REPLACE PROCEDURE SYSTEM.DEPSCHEMA_FILL (SCHEMA CHAR)
IS
   TYPE T_CURSOR IS REF CURSOR;
   V_CURSOR   T_CURSOR;
   OBJ_ID     NUMBER;

BEGIN
   DELETE FROM HELPDESK.DEPSCHEMA;
   COMMIT;

   OPEN V_CURSOR FOR
      SELECT OBJECT_ID
        FROM ALL_OBJECTS
       WHERE OWNER = UPPER (DEPSCHEMA_FILL.SCHEMA) AND OBJECT_TYPE != 'INDEX';

   LOOP
      FETCH V_CURSOR INTO OBJ_ID;
      EXIT WHEN V_CURSOR%NOTFOUND;

      INSERT INTO HELPDESK.DEPSCHEMA
           VALUES (OBJ_ID,
                   0,
                   0,
                   HELPDESK.DEPSCHEMASEQ.NEXTVAL);

      INSERT INTO HELPDESK.DEPSCHEMA
             SELECT OBJECT_ID,
                    REFERENCED_OBJECT_ID,
                    LEVEL,
                    HELPDESK.DEPSCHEMASEQ.NEXTVAL
               FROM PUBLIC_DEPENDENCY A
         CONNECT BY PRIOR A.OBJECT_ID = A.REFERENCED_OBJECT_ID
         START WITH REFERENCED_OBJECT_ID = DEPSCHEMA_FILL.OBJ_ID;
   END LOOP;
   COMMIT;
END;
/

Run the procedure:

SQL> EXEC SYSTEM.DEPSCHEMA_FILL('xxe');
PL/SQL procedure successfully completed.

And finally querying the HELPDESK.DEPSCHEMA_V, you will get:

SEQ#	NEST_LEVEL	OWNER	OBJECT_TYPE	OBJECT_NAME	STATUS
132	0	XXE	TABLE	XXLL_ERROR_DEBUG_LOG	VALID
133	1	APPS	SYNONYM	XXLL_ERROR_DEBUG_LOG	VALID
134	2	APPS	PACKAGE BODY	XXLL_UTILS	VALID
135	0	XXE	TRIGGER	XXLL_PARAGRAPH_ID_T	VALID
136	0	XXE	TABLE	XXLL_ADJUSTMENT_TYPE_CONF	VALID
137	0	XXE	TABLE	XXE_IPTV_SYMPTOMS	VALID
138	1	APPS	SYNONYM	XXE_IPTV_SYMPTOMS	VALID
139	1	APPS	PACKAGE BODY	XXNTT_SERVICEREQUEST_PVT	VALID
140	1	APPS	VIEW	XXSLAM_TICKETS_V	VALID
141	0	XXE	TABLE	XXE_CS_CALC_INACTIVE	VALID
142	1	APPS	PACKAGE BODY	XXE_ISUPPORT_EXTRA_DATA	VALID

So, for instance, table XXE.XXLL_ERROR_DEBUG_LOG is used by synonym APPS.XXLL_ERROR_DEBUG_LOG, which is used by package body APPS.XXLL_UTILS.

Here, I describe a method to find common dependencies between objects in the same schema or not.