Solved

Reporting of Oracle database objects

Posted on 2011-09-13
7
628 Views
Last Modified: 2013-12-19
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
Comment
Question by:chadd25
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Not sure of a nice reporting tool but the views are there:

select segment_type, count(*) from dba_segments group by segment_type;
0
 
LVL 1

Author Comment

by:chadd25
Comment Utility
Thanks, I am looking for something that has nice reporting output.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Accepted Solution

by:
Greg Clough earned 500 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:Greg Clough
Comment Utility
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
 
LVL 13

Expert Comment

by:George K.
Comment Utility
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
 
LVL 1

Expert Comment

by:FieldIT
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now