To see the trigger code.

(This is off the top of my head..)

Define Valid_ = 'N'  -- 'Y' if you only want the valid objects
Set echo      OFF
Set embedded  ON
Set feedback  OFF
Set heading   OFF
Set linesize  2000
Set long      32760
Set newpage   0
Set pagesize  0
Set recsep    OFF
Set space     0
Set termout   OFF
Set trimout   ON
Set trimspool ON
BTitle OFF
TTitle OFF
Spool c:\crtrigger.sql
Drop table mcsql
/
Create table mcsql
   (id number, subid number,
    codesql varchar2(2000) /* LONG impossible a cause de l'affichage */,
    primary key (id, subid))
/
Declare
   /* Curseur des objets a recreer */
   Cursor ct is
      select table_owner, table_name, trigger_name, description, trigger_body
      from user_objects obj2, user_triggers trg, user_objects obj
      where obj2.status like decode ('&Valid_', 'Y', 'VALID', '%')
        and obj2.object_name = trg.trigger_name
        and trg.status like decode ('&Valid_', 'Y', '%ABLED', '%')
        and trg.table_owner = USER
        and trg.table_name = obj.object_name
        and obj.status like decode ('&Valid_', 'Y', 'VALID', '%')
        and obj.object_type = 'TABLE'
      order by table_owner, table_name, trigger_name;
   /* Curseur des objets invalides */
   Cursor ct2 is
      select table_owner, table_name, trigger_name,
             obj2.status||' - '||trg.status
      from user_objects obj2, user_triggers trg, user_objects obj
      where ( trg.status = 'ERROR' or obj2.status != 'VALID' )
        and obj2.object_name = trg.trigger_name
        and trg.table_owner = USER
        and trg.table_name = obj.object_name
        and obj.status = 'VALID'
        and obj.object_type = 'TABLE'
        and '&Valid_' = 'Y'
      order by table_owner, table_name, trigger_name;
   nomTrigger user_triggers.trigger_name%TYPE;
   nomOwner user_triggers.table_owner%TYPE;
   nomTable user_triggers.table_name%TYPE;
   description varchar2(2000);
   source varchar2(32760);
   nomTrigger2 user_triggers.trigger_name%TYPE;
   nomOwner2 user_triggers.table_owner%TYPE;
   nomTable2 user_triggers.table_name%TYPE;
   erreur varchar2(100);
   codeSql varchar2(32760);
   valeur varchar2(2000);
   id number := 0;
   subid number;
   i number;
   lg number;
   espace boolean;
Begin
   Open ct;
   Open ct2;
   Fetch ct into nomOwner, nomTable, nomTrigger, description, source;
   Fetch ct2 into nomOwner2, nomTable2, nomTrigger2, erreur;
   Loop
      Exit when ( ct%NOTFOUND and ct2%NOTFOUND );
      If ( ct%NOTFOUND
          or ( ct2%FOUND
              and nomOwner2||'#'||nomTable2||'#'||nomTrigger2
                  < nomOwner||'#'||nomTable||'#'||nomTrigger ) ) then
         valeur := '
-- '||nomTrigger2||' ('||nomOwner2||'.'||nomTable2||') -> '||erreur;
         Insert into mcsql values (id, 0, valeur);
         Fetch ct2 into nomOwner2, nomTable2, nomTrigger2, erreur;
      Else
         codeSql := '
Create or replace trigger '||description;
         codeSql := codeSql||substr(source,1,length(source)-1)||'/';
         subid := 0;
         i := 1;
         lg := 0;
         espace := False;
         While ( i <= length (codeSql) ) loop
            If ( (i+2000) <= length (codeSql) ) then
               lg := 2000;
               While ( substr(codeSql, i+lg, 1) != '
' and lg > 0 ) loop
                  lg := lg - 1;
               End loop;
               If ( lg = 0 ) then
                  lg := 2000;
                  While ( substr(codeSql, i+lg, 1) != ' ' and lg > 0 ) loop
                     lg := lg - 1;
                  End loop;
                  If ( lg = 0 ) then
                     lg := 2000;
                  Else
                     lg := lg - 1;
                     espace := True;
                  End if;
               Else
                  lg := lg - 1;
                  espace := True;
               End if;
            Else
               lg := length (codeSql) - i + 1;
            End if;
            If ( lg > 0 ) then
               valeur := substr(codeSql, i, lg);
               Insert into mcsql values (id, subid, valeur);
               subid := subid + 1;
               i := i + lg;
            End if;
            If ( espace ) then
               i := i + 1;
            End if;
         End loop;
         Fetch ct into nomOwner, nomTable, nomTrigger, description, source;
      End if;
      id := id + 1;
   End loop;
   Close ct;
   Close ct2;
   Commit;
End;
/
Select codeSql from mcsql order by id
/
Spool off

Sorry about the french I stole it from somewhere...

>Wanna to see all the dependencies of a table, all the factors.
>The problem is that i have a table having a field 'others' now i want to alter its data type to CLOB. Alter table is now working, perhaps bcoz of dependencies. Neither the data type changed nor this column is being drop so that i can add fresh one with clob data type. Now i m planning to drop the table and re-create having desired structure. For this now i need to know all the dependencies to re-create them. Also if any better method for it, pl. inform.

If I understand correctly you want to know all tables with this column..

You can use the DBA_TAB_COLUMNS view as in...
select distinct owner,table_name from DBA_TAB_COLUMNS where column_name='RECNO';

This table also lists dependencies.