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......
LVL 1
chadd25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Greg CloughSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Greg CloughSenior Oracle DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.