Apéndice: Ejemplos de consultas para el recuento de objetos PL/SQL

Utilice las consultas de ejemplo de esta sección para obtener el recuento de objetos PL/SQL para sus bases de datos de Oracle y SQL Server. Puede agrupar estas consultas en un script para recopilar los datos necesarios. Para optimizar, cree un usuario común en todas las bases de datos.

Bases de datos Oracle

Descargar consultas

SELECT *
FROM
    (
      select
         host_name as SERVERNAME,
         instance_name as DATABASENAME,
         'ORACLE' as DATABASEENGINE,
         (
            select
               case
                  when
                     (
                        select
                           substr(value, - 7, 5)
                        from
                           v $ listener_network
                        where
                           type = 'LOCAL LISTENER'
                     )
                     like '%=%'
                  then
(
                     select
                        substr(value, - 6, 4)
                     from
                        v $ listener_network
                     where
                        type = 'LOCAL LISTENER')
                     else
(
                        select
                           substr(value, - 7, 5)
                        from
                           v $ listener_network
                        where
                           type = 'LOCAL LISTENER')
               end
                        from
                           dual
         )
         as DATABASEPORT,
         version as DATABASEVERSION,
         edition
      from
         v $ instance
    )
    q,
    (
      select
         obj.owner "APPSCHEMA",
          obj_cnt "TOTALOBJECTS",
         decode(seg_size, NULL, 0, seg_size) "SIZEINGB"
      from
         (
            select
               owner,
               count(*) obj_cnt
            from
               dba_objects
            where
               owner not in
               (
                  'RDSADMIN',
                  'SYS',
                  'SYSTEM',
                  'XS$NULL',
                  'OJVMSYS',
                  'LBACSYS',
                  'OUTLN',
                  'SYS$UMF',
                  'DBSNMP',
                  'SI_INFORMTN_SCHEMA',
                  'DVF',
                  'DVSYS',
                  'ORDPLUGINS',
                  'MDSYS',
                  'OLAPSYS',
                  'ORDDATA',
                  'XDB',
                  'WMSYS',
                  'ORDSYS',
                  'GSMCATUSER',
                  'MDDATA',
                  'REMOTE_SCHEDULER_AGENT',
                  'SYSBACKUP',
                  'ORACLE_OCM',
                  'SPATIAL_CSW_ADMIN_USR',
                  'PUBLIC',
                  'SYSBACKUP',
                  'SYSRAC',
                  'SYSKM',
                  'OUTLN',
                  'SYS$UMF',
                  'SYSDG',
                  'SYS',
                  'APPQOSSYS',
                  'DBSFWUSER',
                  'GGSYS',
                  'ANONYMOUS',
                  'CTXSYS',
                  'GSMADMIN_INTERNAL',
                  'XDB',
                   'DBSNMP',
                  'GSMCATUSER',
                  'REMOTE_SCHEDULER_AGENT',
                  'AUDSYS',
                  'DIP',
                  'GSMUSER',
                  'SVCSAMLMSRO',
                  'REMOTE_SCHEDULER_AGENT',
                  'PERFSTAT'
               )
            group by
               owner
         )
         obj,
         (
            select
               owner,
               ceil(sum(bytes) / 1024 / 1024 / 1024) seg_size
            from
               dba_segments
            where
               owner not in
               (
                  'RDSADMIN',
                  'SYS',
                  'SYSTEM',
                  'XS$NULL',
                  'OJVMSYS',
                  'LBACSYS',
                  'OUTLN',
                  'SYS$UMF',
                  'DBSNMP',
                  'SI_INFORMTN_SCHEMA',
                  'DVF',
                  'DVSYS',
                  'ORDPLUGINS',
                  'MDSYS',
                  'OLAPSYS',
                  'ORDDATA',
                  'XDB',
                  'WMSYS',
                  'ORDSYS',
                  'GSMCATUSER',
                  'MDDATA',
                  'REMOTE_SCHEDULER_AGENT',
                  'SYSBACKUP',
                  'ORACLE_OCM',
                  'SPATIAL_CSW_ADMIN_USR',
                  'PUBLIC',
                  'SYSBACKUP',
                  'SYSRAC',
                  'SYSKM',
                  'OUTLN',
                  'SYS$UMF',
                  'SYSDG',
                  'SYS',
                  'APPQOSSYS',
                  'DBSFWUSER',
                  'GGSYS',
                  'ANONYMOUS',
                  'CTXSYS',
                  'GSMADMIN_INTERNAL',
                  'XDB',
                  'DBSNMP',
                  'GSMCATUSER',
                  'REMOTE_SCHEDULER_AGENT',
                  'AUDSYS',
                  'DIP',
                  'GSMUSER',
                  'SVCSAMLMSRO',
                  'REMOTE_SCHEDULER_AGENT',
                  'PERFSTAT'
               )
            group by
               owner
         )
         seg
      where
         obj.owner = seg.owner( + )
      order by
         3 desc,
         2 desc,
         1
    )
    b;
   
  

Bases de datos de SQL Server

Descargar consultas

SELECT (SELECT
         @@ServerName)
       AS SERVERNAME,
       DB_NAME() AS DATABASENAME,
       'MSSQL' AS DATABASEENGINE,
       SERVERPROPERTY('PRODUCTVERSION') AS DATABASEVERSION,
       (SELECT
         -- '$($db.DatabasePort)'
         '1433')
       AS DATABASEPORT,
       SERVERPROPERTY('edition') AS EDITION,
       SCHEMA_NAME(so.schema_id) AS APPSCHEMA,
       (SELECT
         COUNT(*) cnt
       FROM sys.objects oo
       WHERE oo.schema_id = so.schema_id
       AND oo.[is_ms_shipped] = 0
       GROUP BY SCHEMA_NAME(schema_id))
       AS TOTALOBJECTS,
       CAST((SUM(ps.reserved_page_count) * 8.0 / 1024 / 1024) AS decimal(10, 2)) AS SIZEINGB
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i
  ON i.object_id = ps.object_id
  AND i.index_id = ps.index_id
JOIN sys.objects so
  ON i.object_id = so.object_id
WHERE so.type = 'U'
GROUP BY so.schema_id
ORDER BY OBJECT_SCHEMA_NAME(so.schema_id),
SIZEINGB DESC;