Solved

Reporting of Oracle database objects

Posted on 2011-09-13
7
636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36533454
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
ID: 36533462
Thanks, I am looking for something that has nice reporting output.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36533479
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Accepted Solution

by:
Greg Clough earned 500 total points
ID: 36533544
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
ID: 36533567
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.
ID: 36534140
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
ID: 36904766
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

623 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