Scripts útiles para Oracle

Scripts sql y pl/sql para resolver problemas frecuentes, diagnosticar salud del modelo de datos y documentar la base de datos.

https://docs.oracle.com/cd/B28359_01/server.111/b28301/toc.htm

Sesiones

Diagnosticar proceso

Ejecutar en la consola el siguienye comando para ver el consumo de procesador y CPU

top
vmstat
ps -e -o pcpu,pid,user,tty,args | grep -i oracle|sort -n -k 1 -r|head

Con el siguiente script visualizar las sesiones e identificar por el PID de Sistema Operativo

SELECT p.spid SPID_CONSOLE,
       s.status,
       s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.program,
       s.type,
s.module modulo_o_pagina_apex,       
s.action action_o_proceso_apex,
       s.machine,
       
       p1text,
       s.action, event, 
       S.sql_exec_start,
       
       s.inst_id
      -- ,tracefile
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
-- WHERE  s.type != 'BACKGROUND';
-- where ACTION like '%BEFORE_BOX_BODY%';

Matar la sesión específica, tomar

ALTER SYSTEM KILL SESSION 'sid,serial#';

Otras querys útiles

select count(*),type,INST_ID from gv$session group by type,inst_id;
select SID,USERNAME,COMMAND,PROCESS,TERMINAL,PROGRAM from gv$session where type='USER';
select count(*),type,INST_ID from gv$session group by type,inst_id;
select sid, username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;
select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where sid=34;

Mostrar sesiones activas

SELECT s.sid,s.action, event, state, S.sql_exec_start, s.serial#, p.spid,
      s.username, s.osuser, s.program, s.status, s.sql_exec_start,
      s.machine,  s.type, p1text, s.program,
      p.pid, 
      p.spid, 
      tracefile
FROM   v$session s
      JOIN v$process p ON p.addr = s.paddr
   ORDER BY  2 ASC, 5,1      ;
SELECT REPLACE(q.sql_text,CHR(0)) sql_text
  FROM gv$session S,gv$sql q
 WHERE S.sql_address = q.address
   AND S.sql_hash_value = q.hash_value
   AND S.SID = :SID
select sql_id, executions, address, hash_value, first_load_time
from   v$sql
where  sql_text = 'select /* FLUSH_ME */* from dual';   

Mostrar las sesiones bloqueadas

select a.object_name, b.ORACLE_USERNAME, b.SESSION_ID
from   all_objects a, v$locked_object b
where  a.OBJECT_ID = b.OBJECT_ID
and a.object_name like '%NOMBRE_OBJ%'

Mostrar tablas bloqueadas

1.- Mostrar tablas y causa de bloqueo

SELECT SESSION_ID SID, SERIAL#  Serial
    , SUBSTR(OBJECT_NAME, 1, 25) Objecto_locked
    , SUBSTR(OS_USER_NAME, 1, 10) Terminal
    , ORACLE_USERNAME Locker
    , NVL(LOCKWAIT, 'ACTIVE') Wait
    , DECODE(LOCKED_MODE
        , 2, 'Row Share'
        , 3, 'ROW EXCLUSIVE'
        , 4, 'SHARE'
        , 5, 'SHARE ROW EXCLUSIVE'
        , 6, 'EXCLUSIVE',  'UNKNOWN') LockMode
      , OBJECT_TYPE "Type"
 FROM GV$LOCKED_OBJECT A, SYS.ALL_OBJECTS B, GV$SESSION C
WHERE A.OBJECT_ID = B.OBJECT_ID
  AND C.SID = A.SESSION_ID
ORDER BY 3

2.- Localizar el SQL, Sesion que está bloqueando

Script para matar sesiones bloqueadas

DECLARE
    CURSOR c IS
    SELECT c.owner,
          c.object_name,
          c.object_type,
          b.SID,
          b.serial#,
          b.status,
          b.osuser,
        b.machine
    FROM v$locked_object a, v$session b, dba_objects c
    WHERE b.SID = a.session_id AND a.object_id = c.object_id
    and c.object_name in ('MTL_SYSTEM_ITEMS_INTERFACE');
   c_row c%ROWTYPE;
   l_sql VARCHAR2(100);
   BEGIN
   OPEN c;
   LOOP
   FETCH c INTO c_row;
   EXIT WHEN c%NOTFOUND;
   l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||'''';
   EXECUTE IMMEDIATE l_sql;
   END LOOP;
   CLOSE c;
   END;

Mostrar las consultas SQL en ejecución

SELECT sess.sid, 
       sess.serial#,sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text 
   FROM v$session sess, v$sql sql
  WHERE sql.sql_id(+) = sess.sql_id 
    AND sess.type = 'USER'
    AND STATUS = 'ACTIVE';

Monitoreo del estado de los JOBs

USER_SCHEDULER_JOB_RUN_DETAILS o DBA_SCHEDULER_JOB_RUN_DETAILS muestra el estado de un JOB.

SELECT * FROM       USER_SCHEDULER_JOB_RUN_DETAILS 
order by REQ_START_DATE DESC;
select 
   job_name, 
   job_name,
   avg(extract( day from run_duration )*24*60*60 + extract( hour from run_duration )*60*60 + extract( minute from run_duration )*60 + extract( second from 
   run_duration ))
from 
   dba_scheduler_job_run_details
group by 
   job_name, 
   job_name
having avg(extract( day from run_duration )*24*60*60 + extract( hour from run_duration )*60*60 + extract( minute from run_duration )*60 + extract( second from run_duration )) > 0
order by 3 desc;

Diagnosticar consumo de memoria

Tomado de: http://www.dba-oracle.com/t_high_cpu.htm

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc

Versión compacta de la ejecución de los jobs

SELECT  log_id,
        to_char(log_date, 'YYYY-MM-DD') fecha,
        to_char(log_date, 'HH24')       hora,
        CASE
            WHEN REGEXP_LIKE(substr(job_name, -4), '^\d+(\.\d+)?$') 
                THEN substr(job_name, 1, LENGTH(job_name) -5 ) 
            ELSE
                job_name
        END AS job_name,
        CASE
            WHEN REGEXP_LIKE(substr(job_name, -4), '^\d+(\.\d+)?$') THEN to_number(substr(job_name, -4)) 
            ELSE
                NULL
            END AS id_fly_job,
        status,
        req_start_date,
        actual_start_date,
        log_date actual_end_date,
          EXTRACT( DAY FROM     run_duration )*24*60*60 
        + EXTRACT( HOUR FROM    run_duration )*60*60 
        + EXTRACT( MINUTE FROM  run_duration )*60 
        + EXTRACT( SECOND FROM  run_duration ) duracion_secs,

          EXTRACT( DAY FROM     cpu_used )*24*60*60 
        + EXTRACT( HOUR FROM    cpu_used )*60*60 
        + EXTRACT( MINUTE FROM  cpu_used )*60 
        + EXTRACT( SECOND FROM  cpu_used ) cpu_secs,
        LENGTH(output) size_output,
        dbms_lob.getlength (binary_output) size_binary,
        ERRORS,
        output
   FROM user_scheduler_job_run_details
ORDER BY log_id DESC;

Procedimientos en línea

Habilitar la salida de mensajes de DBMS_OUTPUT
set serveroutput on;
DECLARE
   PROCEDURE myProc (dt IN VARCHAR2) IS
   BEGIN
          DBMS_OUTPUT.PUT_LINE (dt );
   END;
   
   BEGIN
       myProc ('30-JAN-99');
       myProc ('27-FEB-99');
   END;

Diccionario de Datos

Fuente: https://docs.oracle.com/cd/B28359_01/nav/catalog_views.htm

Las vistas del diccionario de datos, también conocidas como vistas de catálogo, permiten supervisar el estado de la base de datos en tiempo real:

  • Las vistas USER, ALL y DBA muestran información sobre los objetos de esquema a los que puede acceder, en diferentes niveles de privilegio.
  • Las vistas V$ muestran información relacionada con el rendimiento.
  • Las vistas _PRIVS muestran información de privilegios para diferentes combinaciones de usuarios, roles y objetos.
Código de funciones y procedimientos: dba_source, user_source, all_source
Usuarios: dba_users, user_users, all_users
Roles: dba_roles
Roles asignados a roles o usuarios: dba_role_privs, user_role_privs
Privilegios asignados a roles o usuarios: dba_sys_privs
Permisos sobre tablas asignados a roles o usuarios: dba_tab_privs
Límites de recursos: user_resource_limits
Perfiles y sus límites de recursos asociados: dba_profiles
Límites de recursos en cuanto a restricciones en claves: user_password_limits
Límites de recursos en cuanto a espacio máximo en tablespaces:dba_ts_quotas, user_ts_quotas
Tablespaces: dba_tablespaces, user_tablespaces
Ficheros que componen los datafiles: dba_data_files
Segmentos: dba_segments, user_segments, all_segments
Segmentos de Rollback: dba_rollback_segs
Extensiones que forman los segmentos: dba_extents, user_extents
Bloques libres: dba_free_space, user_free_space
Bloques libres que podrían unirse: dba_free_space_coalesced
Secuencias: dba_sequences, user_sequences, all_sequences
Tablas, vistas, sinónimos y secuencias: dba_catalog, user_catalog, all_catalog
Tablas : dba_tables, user_tables, all_tables
Campos de tablas: dba_cons_columns, user_cons_columns, all_cons_columns
Columnas de las tablas: dba_tab_columns, user_tab_columns, all_tab_columns
Vistas: dba_views, user_views, all_views
Sinónimos: dba_synonyms, user_synonyms, all_synonyms
Restricciones de clave primaria, externa, not null, integridad referencial:dba_constraints, user_constraints, all_constraints
Índices: dba_indexes, user_indexes, all_indexes
Columnas de los índices: dba_ind_columns, user_ind_columns, all_ind_columns 

Mostrar todas las tablas

Vista del usuario (lo que está en el esquema; lo que es de su propiedad)

  SELECT tablespace_name, table_name, num_rows, table_lock, last_analyzed
    FROM user_tables 
ORDER BY num_rows DESC, 
         table_name;

Vista ampliada ALL, a lo que puede acceder

  SELECT tablespace_name, table_name, num_rows, table_lock, last_analyzed
    FROM all_tables 
ORDER BY num_rows DESC, 
         table_name;

Vista a nivel de DBA

  SELECT tablespace_name, table_name, num_rows, table_lock, last_analyzed
    FROM dba_tables 
ORDER BY num_rows DESC, 
         table_name;

Mostrar todos los objetos

  SELECT  object_type
         , object_name
         , created
         , last_ddl_time
         , to_char(TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           +(last_ddl_time - created),'hh24:mi:ss') AS ddl_time 
         , TIMESTAMP
         , status
         , NAMESPACE
    FROM  user_objects
ORDER BY  created DESC
         , object_type;
SELECT * 
  FROM all_catalog
 WHERE owner = 'SCHEMA';

Mostrar las columnas de las tablas

  SELECT  owner
         , table_name
         , column_name
         , nullable AS "Null?"
         , data_type|| '(' || data_length ||')' AS "Type"
         , identity_column
    FROM   all_tab_columns
   WHERE  OWNER ='NOMBRE_SCHEMA'
ORDER BY  table_name,column_id 
;

Mostrar las restricciones

  • USER_CONS_COLUMNS describe las restricciones de cada columna
  • USER_CONSTRAINTS describe las definiciones de cada restricción
 SELECT  cc.table_name
        ,cc.column_name
        ,CASE C.constraint_type
              WHEN 'C' THEN 'CKECK'
              WHEN 'P' THEN 'PRIMARY KEY'
              WHEN 'U' THEN 'UNIQUE'
              WHEN 'R' THEN 'FOREIGN KEY'
              WHEN 'V' THEN 'COMPROBAR VISTA'
              WHEN 'O' THEN 'SOLO LECTURA/VISTA'
              ELSE C.constraint_type
         END condicion
        ,search_condition  
        ,cc.constraint_name
        ,c.R_CONSTRAINT_NAME
        ,C.delete_rule
        ,C.status
        ,C.last_change
        ,index_owner
    FROM user_cons_columns cc, user_constraints C
   WHERE cc.constraint_name = C.constraint_name
ORDER BY C.last_change DESC, cc.table_name, constraint_type
;

Listar todas las vistas

SELECT view_name, text_length
  FROM user_views ;

Mostrar las secuencias

  SELECT sequence_name
        , min_value
        , max_value
        , increment_by
        , last_number 
    FROM user_sequences;

Mostrar los sinónimos

SELECT *
  FROM all_synonyms;     

Contar/Mostrar el código fuente de los objetos

  SELECT   type
         , name
         , count(*) LINEAS
    FROM user_source  
GROUP BY type, name
ORDER BY type, count(*) DESC

Mostrar los comentarios por tabla

SELECT *
  FROM user_tab_comments
 WHERE comments IS NOT NULL

Mostrar los índices

  SELECT table_owner, table_name, column_name, index_name
    FROM all_ind_columns
   WHERE index_owner = 'SCHEMA'
ORDER BY table_owner, table_name, column_name

Calidad del modelo de datos

Tablas sin llave primaria

SELECT table_name 
     FROM user_tables A 
    WHERE NOT EXISTS (
        SELECT NULL 
          FROM user_constraints 
         WHERE constraint_type ='P' 
           AND table_name = A.table_name
    )

Referencias entre objetos

SELECT     owner
         , type
         , name
         , referenced_owner
         , referenced_type
         ,  referenced_name 
    FROM all_dependencies
   WHERE owner = 'SCHEMA'
ORDER BY owner
        , name
        , type
        , referenced_name
SELECT     type
         , owner || '.' || name object
         , '+--<>' REL
         , referenced_type
         , referenced_owner || '.' || referenced_name AS dependencies
   FROM  all_dependencies
  WHERE  owner = 'XXSCHEMA_CONTRATOS'
    AND  (     referenced_owner <> 'SYS'
           AND referenced_owner <> 'SYSTEM'
           AND referenced_owner <> 'PUBLIC'
         )
    AND  (     OWNER <> 'SYS'
          AND OWNER <> 'SYSTEM'
          AND OWNER <> 'PUBLIC'
         )
ORDER BY owner
        , name
        , referenced_type 
        , referenced_owner 
        , referenced_name;  

Consultas para analizar APEX

Aplicaciones de APEX

  SELECT  workspace
         , application_id
         , application_name
         , owner
         , PAGES
         , last_updated_on last_updated
         , VERSION
         , authentication_scheme
         , theme_number
    FROM apex_applications
ORDER BY workspace, application_id

Tipo de componente para la aplicación

  SELECT  page_function
        , application_id
        , COUNT(*) page_count 	
    FROM apex_application_pages 	
   WHERE application_id = :application_id 	
GROUP BY application_id
        , page_function 	
ORDER BY page_function;

Páginas de aplicaciones APEX

SELECT  page_id
          , page_group
          , nvl(PAGE_title,page_name) page_title
          , created_by
          , created_on
          , last_updated_by
          , last_updated_on 
          , page_function
          , regions
          , items
          , buttons
          , computations
          , validations
          , processes
          , branches
          , page_requires_authentication
    FROM  apex_application_pages P
   WHERE  P.APPLICATION_ID = :APPLICATION_ID  
ORDER BY  last_updated_on desc;

Listas de valores de APEX

  SELECT   list_of_values_name
         ,lov_type
         , list_of_values_query
         , lov_entries
    FROM apex_application_lovs
   WHERE application_id = :application_id
ORDER BY 1,2,3


SELECT   list_of_values_query
         , COUNT (*)
    FROM apex_application_lovs
   WHERE application_id = :application_id
GROUP BY  list_of_values_query  
ORDER BY 1,2 DESC

Actividad por usuario

SELECT      apex_user,
            applications,
            page_views,
            distinct_pages,
            total_elapsed_time,
            average_elapsed_time,
            total_rows_queried,
            distinct_ip_addresses,
            page_views_with_errors,
            dynamic_page_views,
            cached_page_views
    FROM    apex_workspace_log_summary_usr
   WHERE    workspace = :workspace
ORDER BY    apex_user
  SELECT application_id
        , application_name
        , user_name
        , authentication_method
        , application_schema_owner
        , access_date
        , ip_address
        , authentication_result
        , custom_status_text status_text
    FROM apex_workspace_access_log
   WHERE application_id = :application_id
ORDER BY access_date DESC
SELECT  application_id
        , application_name
        , page_id
        , page_name
        , view_date
        , think_time
        , elapsed_time
        , rows_queried
        , log_context
        , TRUNC(sysdate - view_date) days_ago
        , seconds_ago
        , page_view_mode
        , regions_from_cache
FROM
        apex_workspace_activity_log
WHERE  application_id = :application_id
ORDER BY view_date DESC

Uso de la aplicación por pantalla

 SELECT    a.application_name || '('||l.application_id||')'  "Application"
        ,  p.page_name|| '('||l.page_id||')'  "Page"
        ,  count(*) "Page Views"    
   FROM    APEX_WORKSPACE_ACTIVITY_LOG l
        ,  apex_applications a          
        ,  apex_application_pages p   
   WHERE l.APPLICATION_ID = :APPLICATION_ID  
     AND  a.application_id = l.APPLICATION_ID 
     AND p.application_id     = a.application_id 
     AND p.page_id            = l.PAGE_ID 
GROUP BY a.application_name || '('||l.APPLICATION_ID||')', p.page_name|| '('||l.PAGE_ID||')' 
ORDER BY 1,3 desc

Rendimiento por página

SELECT 
  p.page_name|| '('||l.page_id||')' "Page",
  count(*) "Visits",
  MIN(l.ELAPSED_TIME) "Min(e)"        ,
  MAX(l.ELAPSED_TIME) "Max(e)"        ,
  ROUND(AVG(l.ELAPSED_TIME),2) "Avg(e)",
  ROUND(AVG(l.ELAPSED_TIME),2)* count(*) "Weighted(e)",
  'SQLDEV:GAUGE:0:100:0:0:'||100*(ROUND(AVG(l.ELAPSED_TIME),2)*count(*))/nvl( (select sum(ROUND(AVG(l1.ELAPSED_TIME),2)*count(*)) PCT from apex_workspace_activity_log l1 where l1.APPLICATION_ID = :APPLICATION_ID group by l1.application_id),1) "Performance Factor", 
  MIN(l.rows_queried)    "Min(rows)" ,
  MAX(l.rows_queried)     "Max(rows)",
  ROUND(AVG(l.rows_queried), 2) "Avg(rows)"
   FROM apex_workspace_activity_log l,
  apex_applications a          ,
  apex_application_pages p
  WHERE (l.APPLICATION_ID = :APPLICATION_ID )
AND  a.application_id = l.APPLICATION_ID
AND p.application_id     = a.application_id
AND p.page_id            = l.PAGE_ID
and view_date > view_date - 30
-- Últimos 15 minutos:  and view_date > SYSDATE - (15*60) / (60 * 60 * 24)
GROUP BY a.application_name || '('||l.APPLICATION_ID||')', p.page_name|| '('||l.PAGE_ID||')'
order by 6 desc

Código fuente de las páginas

Buscar en PL/SQL page processes

SELECT  application_id,  
        application_name,  
        page_id,  
        page_name,  
        process_name,  
        process_point,  
        DBMS_LOB.SUBSTR (PROCESS_SOURCE, 3000, 1) source  
 FROM   APEX_APPLICATION_PAGE_PROC  
WHERE   DBMS_LOB.SUBSTR (PROCESS_SOURCE, 3000, 1) LIKE '%cadena%'
SELECT  application_id,  
        page_id,  
        process_name, 
        dbms_lob.getlength(PROCESS_SOURCE) LENGTH,
        DBMS_LOB.INSTR (UPPER(PROCESS_SOURCE), 'TEXTO' , 1) POS1,
        page_name
 FROM   APEX_APPLICATION_PAGE_PROC  
WHERE  DBMS_LOB.INSTR (UPPER(PROCESS_SOURCE), 'TEXTO' , 1) >0

Buscar las dependencias de la páginas a los objetos de la Base de Datos

set serveroutput on;
DECLARE    
    CURSOR objetos IS
        SELECT  object_type, object_name
          FROM  user_objects
          WHERE ( OBJECT_TYPE <>'SEQUENCE' 
                    AND OBJECT_TYPE <>'INDEX'
                    AND OBJECT_TYPE <>'TRIGGER'
                    AND OBJECT_TYPE <>'LOB'
                    AND OBJECT_TYPE <>'PACKAGE'
                );
          --and rownum < 10;
    CURSOR dependencias( objeto user_objects.object_name%TYPE) IS
        SELECT DISTINCT 
                page_id, 
                process_id,
                page_name,
                process_name
                --dbms_lob.getlength(PROCESS_SOURCE) LENGTH,
                --DBMS_LOB.INSTR (lower(PROCESS_SOURCE), objeto , 1) POS1,
         FROM  APEX_APPLICATION_PAGE_PROC  
        WHERE  DBMS_LOB.INSTR (upper(PROCESS_SOURCE), upper(objeto) , 1) >0
                AND application_id = 104;
    lv_object_type  user_objects.object_type%type;    
    lv_objeto       user_objects.object_name%type;    
    
    lv_page_id      APEX_APPLICATION_PAGE_PROC.page_id%type;
    lv_process_id   APEX_APPLICATION_PAGE_PROC.page_id%type;
    lv_page_name    APEX_APPLICATION_PAGE_PROC.page_name%type;
    lv_process_name APEX_APPLICATION_PAGE_PROC.process_name%type;
    BEGIN
        OPEN objetos;
        LOOP 
            FETCH objetos INTO lv_object_type,lv_objeto;
            EXIT WHEN objetos%NOTFOUND;
            -- dbms_output.put_line(lv_object_type||lv_objeto);            
            OPEN dependencias(lv_objeto);
            LOOP
                FETCH dependencias INTO lv_page_id,lv_process_id, lv_page_name, lv_process_name;
                EXIT WHEN dependencias%NOTFOUND;
                dbms_output.put_line(104||','||lv_object_type||','||lv_objeto||','||lv_page_id||','||lv_page_name||','||lv_process_id||','|| lv_process_name);
            END LOOP;
            CLOSE dependencias;
        END LOOP;
        CLOSE objetos;
    END;

Buscar en PL/SQL shared application processes

  SELECT  application_id,  
          application_name,  
          process_name,  
          DBMS_LOB.SUBSTR (PROCESS, 3000, 1)  
  FROM    APEX_APPLICATION_PROCESSES  
  WHERE   DBMS_LOB.SUBSTR (PROCESS, 3000, 1) LIKE '%cadena%' 

Buscar en el código Javascript

   SELECT   application_id,  
            application_name,  
            page_id,  
            page_name,  
            NVL (DBMS_LOB.SUBSTR (JAVASCRIPT_CODE, 4000, 1),  
                DBMS_LOB.SUBSTR (PAGE_HTML_HEADER, 4000, 1))  
            js  
     FROM  APEX_APPLICATION_PAGES  
    WHERE ( JAVASCRIPT_CODE IS NOT NULL OR PAGE_HTML_HEADER IS NOT NULL )
  --WHERE  NVL (DBMS_LOB.SUBSTR (JAVASCRIPT_CODE, 4000, 1),  
  --      DBMS_LOB.SUBSTR (PAGE_HTML_HEADER, 4000, 1)) LIKE  
  --     '%getElementById%';

Características de las tablas y los objetos dependientes

SELECT TU.tablespace_name, TU.table_name, TU.num_rows, TU.last_analyzed,
OB.IS_NULL, OB.PK,OB.FK,OB.LECTURA,
NVL(DEP.TOTAL,0) DEPENDENCIAS, NVL(DEP.PAGINA,0) PAGINA, NVL(DEP.PAQUETE,0) PAQUETE, NVL(DEP.PROCEDIMIENTO,0) PROCEDIMIENTO, NVL(DEP.FUNCION,0) FUNCION , NVL(DEP.VISTA,0) VISTA

FROM user_tables TU 

LEFT JOIN (
            select OBJS.TABLE_NAME, OBJS.IS_NULL, OBJS.PK,OBJS.FK,OBJS.VISTA AS LECTURA 
            from
            (
                        WITH 
                           T 
                        AS 
                        (
                            SELECT  cc.table_name, CASE C.constraint_type
                                      WHEN 'C' THEN 'CKECK'
                                      WHEN 'P' THEN 'PRIMARY KEY'
                                      WHEN 'U' THEN 'UNIQUE'
                                      WHEN 'R' THEN 'FOREIGN KEY'
                                      WHEN 'V' THEN 'COMPROBAR VISTA'
                                      WHEN 'O' THEN 'SOLO LECTURA/VISTA'
                                      ELSE C.constraint_type
                                 END CONSTRAINT_TYPE
                            FROM user_cons_columns cc, user_constraints C
                           WHERE cc.constraint_name = C.constraint_name
                           GROUP BY cc.table_name, CASE C.constraint_type
                                      WHEN 'C' THEN 'CKECK'
                                      WHEN 'P' THEN 'PRIMARY KEY'
                                      WHEN 'U' THEN 'UNIQUE'
                                      WHEN 'R' THEN 'FOREIGN KEY'
                                      WHEN 'V' THEN 'COMPROBAR VISTA'
                                      WHEN 'O' THEN 'SOLO LECTURA/VISTA'
                                      ELSE C.constraint_type
                                 END
                        )
                        SELECT 
                            *
                        FROM 
                           T
                        PIVOT 
                        (
                           COUNT(*) FOR  (CONSTRAINT_TYPE) IN  (
                                     'CKECK' AS IS_NULL,
                                     'PRIMARY KEY' AS PK,
                                     'UNIQUE' AS UNIQUE$,
                                     'FOREIGN KEY' AS FK,
                                      'COMPROBAR VISTA' AS VISTA,
                                      'SOLO LECTURA/VISTA' AS LECTURA)
                        
                        
                        )
                        
            ) OBJS
) OB ON (TU.table_name = OB.TABLE_NAME)            
LEFT JOIN 
(
            WITH
            Q
            AS 
            (
                SELECT * FROM (
                    select OBJECT_TYPE, OBJECT_NAME, 'PAGINA' AS DEPENDENCIA, count(*) TOTAL
                    from XXAPE_GS_RELATION_APEX_BD_OBJECTS_TMP
                    where object_type = 'TABLE'
                    GROUP BY OBJECT_TYPE, OBJECT_NAME
                    UNION 
                    SELECT   referenced_type, referenced_name, type, count(*)
                       FROM  all_dependencies
                      WHERE  owner = 'XXSCHEMA_CONTRATOS' and referenced_type= 'TABLE'
                        AND  (     referenced_owner <> 'SYS'
                               AND referenced_owner <> 'SYSTEM'
                               AND referenced_owner <> 'PUBLIC'
                             )
                        AND  (     OWNER <> 'SYS'
                              AND OWNER <> 'SYSTEM'
                              AND OWNER <> 'PUBLIC'
                             )
                        AND (
                        TYPE <> 'TRIGGER' AND TYPE <> 'PACKAGE'
                        )
                    group by  referenced_type, referenced_name, type    
                 )   
                )
                SELECT OBJECT_TYPE, OBJECT_NAME, PAGINA, PAQUETE, PROCEDIMIENTO, FUNCION, VISTA, (NVL(PAGINA,0) + NVL(PAQUETE ,0) + NVL( PROCEDIMIENTO ,0) + NVL(FUNCION ,0) + NVL( VISTA,0)  ) TOTAL FROM Q 
                PIVOT (
                
                    SUM(TOTAL) FOR  (DEPENDENCIA) IN  (
                        'PAGINA' PAGINA,
                        'PACKAGE BODY' PAQUETE,
                        'PROCEDURE' PROCEDIMIENTO,
                        'FUNCTION' FUNCION,
                        'VIEW' VISTA
                    )
                )
) DEP 
ON (TU.TABLE_NAME = DEP.OBJECT_NAME)
WHERE TU.TABLESPACE_NAME IS NOT NULL          
    
ORDER BY num_rows DESC, 
         table_name;

Listado de estructura de tipos, funciones y procedimientos

SELECT  MAX(NAME) NAME, MAX(TYPE), MAX(LINE) LINE, MAX (TIPO) TIPO , MAX(RUTINA) KEEP (  DENSE_RANK LAST ORDER BY TYPE )
FROM (
    select NAME, TYPE, LINE, text 
    , REGEXP_SUBSTR(text,'[A-Z0-9_]+', 1, 1) TIPO
    , REGEXP_SUBSTR(text,'[A-Z0-9_]+', 1, 2) RUTINA
    from    user_source 
    WHERE 
    REGEXP_SUBSTR( upper(TEXT),'([[:space:]*](TYPE|FUNCTION|PROCEDURE)[[:space:]+])') IS NOT NULL
    AND 
     (  
        INSTR(upper(substr(trim(REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')),1,5)), 'TYPE', 1, 1) > 0
        OR INSTR(upper(substr(trim(REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')),1,9)), 'PROCEDURE', 1, 1) > 0
        OR INSTR(upper(substr(trim(
            REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')
            ),1,8)), 'FUNCTION', 1, 1) > 0
        
        )

)
GROUP BY RUTINA

Páginas/Objetos/Dependencias/Webservices

SELECT
    page_id,
    page_name,
    object_type,
    object_name,
    referenced_owner_child,
    type_child,
    name_child,
    ws,
    lineas,
    tipo,
    metodo
FROM
    xxape_gs_relation_apex_bd_objects_tmp pages
    FULL OUTER JOIN (
        SELECT
            all_dependencies.type   type_parent,
            all_dependencies.name   name_parent,
            referenced_owner        referenced_owner_child,
            referenced_type         type_child,
            referenced_name         name_child,
            ws,
            lineas,
            metodos.tipo,
            metodos.metodo
        FROM
            all_dependencies left
            JOIN (
                SELECT
                    type,
                    name,
                    COUNT(line) ws,
                    LISTAGG(line, ',') WITHIN GROUP(
                        ORDER BY
                            line
                    ) AS lineas
                FROM
                    user_source
                WHERE
                    regexp_substr(upper(text), 'APEX_WEB_SERVICE.MAKE_REQUEST') IS NOT NULL
                GROUP BY
                    type,
                    name
            ) ws ON ( ws.name = all_dependencies.referenced_name )
            LEFT JOIN (
                SELECT
                    MAX(name) name,
                    MAX(type) type,
                    MAX(line) line,
                    MAX(tipo) tipo,
                    MAX(rutina) KEEP(DENSE_RANK LAST ORDER BY type) metodo
                FROM
                    (
                        SELECT
                            name,
                            type,
                            line,
                            text,
                            regexp_substr(text, '[A-Z0-9_]+', 1, 1) tipo,
                            regexp_substr(text, '[A-Z0-9_]+', 1, 2) rutina
                        FROM
                            user_source
                        WHERE
                            regexp_substr(upper(text), '([[:space:]*](TYPE|FUNCTION|PROCEDURE)[[:space:]+])') IS NOT NULL
                            AND ( instr(upper(substr(TRIM(regexp_replace(text, '(^[[:space:]]*|[[:space:]]*$)')), 1, 5)), 'TYPE',
                            1, 1) > 0
                                  OR instr(upper(substr(TRIM(regexp_replace(text, '(^[[:space:]]*|[[:space:]]*$)')), 1, 9)), 'PROCEDURE'
                                  , 1, 1) > 0
                                  OR instr(upper(substr(TRIM(regexp_replace(text, '(^[[:space:]]*|[[:space:]]*$)')), 1, 8)), 'FUNCTION'
                                  , 1, 1) > 0 )
                    )
                GROUP BY
                    rutina
            ) metodos ON ( metodos.name = all_dependencies.referenced_name )
        WHERE
            owner = 'XXSCHEMA_CONTRATOS' /*and referenced_type= 'TABLE'*/
            AND ( referenced_owner <> 'SYS'
                  AND referenced_owner <> 'SYSTEM'
                  AND referenced_owner <> 'PUBLIC' )
            AND ( owner <> 'SYS'
                  AND owner <> 'SYSTEM'
                  AND owner <> 'PUBLIC' )
            AND ( all_dependencies.type <> 'TRIGGER'
                  AND all_dependencies.type <> 'PACKAGE' )
            AND (
                all_dependencies.name NOT IN (
                    'PK_JRXML2PDF_REPGEN',
                    'PK_JRXML2PDF_APEX2JRXML',
                    'PK_JRXML2PDF_CHARTS',
                    'PK_JRXML2PDF_LOADER',
                    'PK_JRXML2PDF_LOG',
                    'PK_JRXML2PDF_TYPES',
                    'PK_JRXML2PDF_UTIL'
)
            )
            AND (
                referenced_name NOT IN ( 
                    'HTMLDB_UTIL',
                    'XLIB_HTTP',
                    'XLIB_JASPERREPORTS',
                    'PK_JRXML2PDF_REPGEN',
                    'AS_PDF3_MOD',
                    'JRXML_REPORT_IMAGES',
                    'XLIB_LOG',
                    'XLIB_COMPONENT',
                    'XLIB_LOGS',
                    'JRXML_REPORT_DEFINITIONS',
                    'JRXML_FONTS',
                    'XLIB_COMPONENTS',
                    'JRXML_NLS_PARAMETERS',
                    'JRXML_SEQ',
                    'XLIB_SEQ',
                    'JRXML_RESOURCE_FILES',
                    'JRXML_RESOURCE_ENTRIES','V'

                )        
            )
    ) objs ON ( pages.object_name = objs.name_parent
                AND pages.object_type = objs.type_parent )
GROUP BY
    page_id,
    page_name,
    object_type,
    object_name,
    referenced_owner_child,
    type_child,
    name_child,
    ws,
    lineas,
    tipo,
    metodo
ORDER BY
    1,
    3,
    4

https://community.oracle.com/docs/DOC-1007838

http://stevenfeuersteinonplsql.blogspot.com/2016/10/naming-conventions-for-oracle-database.html

http://ruter8.blogspot.com/2011/03/searching-through-source-code-in-oracle.html