Scripts útiles para Oracle

/ Oracle / Por

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.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\';

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%\'

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;

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;

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