Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Reporting of Oracle database objects

Are their any 3rd party tools or native oracle tools that will tell me how many tables are in an oracle database, how many views, how many stored procedures etc......
0
chadd25
Asked:
chadd25
1 Solution
 
slightwv (䄆 Netminder) Commented:
Not sure of a nice reporting tool but the views are there:

select segment_type, count(*) from dba_segments group by segment_type;
0
 
chadd25Author Commented:
Thanks, I am looking for something that has nice reporting output.
0
 
slightwv (䄆 Netminder) Commented:
The question is how to get the data not really on report formatting.

That said:

Sqlplus has very nice reporting capability.

What do you need?

Html:

Set pages 0
Set lines 100
Set feedback off

spool mylist.html
Set markup html on
Select...
Spool off
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
Greg CloughCommented:
Maybe a slight tweak on @slightvw.  Run this in either sqlplus or SQL*Developer:

set pages 30000
column owner format A30
column segment_type format A18
column count format 999,999
--
select owner, segment_type, count(*) count
  from dba_segments
  where owner not in ('SYS','SYSTEM')
  group by owner, segment_type
  order by owner, segment_type;

Open in new window


or if you know the Schema owner name (e.g. SCOTT)

set pages 30000
column segment_type format A18
column count format 999,999
--
select segment_type, count(*) count
  from dba_segments
  where owner = 'SCOTT'
  group by segment_type
  order by segment_type;

Open in new window

0
 
Greg CloughCommented:
Sorry, I just re-read the question... you probably want DBA_OBJECTS:

set pages 30000
column owner format A30
column object_type format A18
column count format 999,999
--
select owner, object_type, count(*) count
  from dba_objects
  where owner not in ('SYS','SYSTEM')
  group by owner, object_type
  order by owner, object_type;

Open in new window


or if you know the Schema owner name (e.g. SCOTT)

set pages 30000
column object_type format A18
column count format 999,999
--
select object_type, count(*) count
  from dba_objects
  where owner = 'SCOTT'
  group by object_type
  order by object_type;

Open in new window


The output from the first query will look something like:

OWNER                          OBJECT_TYPE         COUNT                  
------------------------------ ------------------- ---------------------- 
DBSNMP                         INDEX               13                     
DBSNMP                         PACKAGE             4                      
DBSNMP                         PACKAGE BODY        4                      
DBSNMP                         PROCEDURE           1                      
DBSNMP                         SEQUENCE            2                      
DBSNMP                         SYNONYM             1                      
DBSNMP                         TABLE               25                     
DBSNMP                         TYPE                8                      
DBSNMP                         VIEW                7                      
SCOTT                          INDEX               1                      
SCOTT                          PACKAGE             1                      
SCOTT                          PACKAGE BODY        1                      
SCOTT                          TABLE               1                      
SCOTT                          TRIGGER             1                      
SCOTT                          TYPE                4                      
SYSMAN                         EVALUATION CONTEXT  2                      
SYSMAN                         FUNCTION            12                     
SYSMAN                         INDEX               953                    
SYSMAN                         LOB                 75                     
SYSMAN                         MATERIALIZED VIEW   1                      
SYSMAN                         PACKAGE             193                    
SYSMAN                         PACKAGE BODY        193                    
SYSMAN                         PROCEDURE           3                      
SYSMAN                         QUEUE               12                     
SYSMAN                         RULE SET            4                      
SYSMAN                         SEQUENCE            13                     
SYSMAN                         TABLE               729                    
SYSMAN                         TRIGGER             97                     
SYSMAN                         TYPE                672                    
SYSMAN                         TYPE BODY           49                     
SYSMAN                         VIEW                471                    
GREG                           FUNCTION            24                     
GREG                           INDEX               1243                   
GREG                           LOB                 98                     
GREG                           MATERIALIZED VIEW   13                     
GREG                           PROCEDURE           50                     
GREG                           SEQUENCE            451                    
GREG                           SYNONYM             86                     
GREG                           TABLE               538                    
GREG                           TRIGGER             8                      
GREG                           VIEW                2                      

 42 rows selected 

Open in new window

0
 
George K.Commented:
What do you need this for?

Here are a couple of products with some tools that migh be helpful.

DB Tools for Oracle Version
http://www.softtreetech.com/monitor/

RPM The Remote Preemptive Monitor service for Oracle
http://www.dba-oracle.com/t_oracle_remote_preemptive_monitor_proactive.htm

Depends of course what you plan to do/use the information you are asking for.
Hope it helps.
0
 
FieldITCommented:
SQLDeveloper (free from Oracle) has an object browser to view objects accordingly.

Additionally - you can simply do:

select <insert fields> from dba_objects where object_type in <insert object types desired> and export the results to excel where you can massage the data as needed.  
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now