Home » SQL & PL/SQL » SQL & PL/SQL » sql (10.2.0.1)
sql [message #407334] Tue, 09 June 2009 15:21 Go to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hello,

I need to collect total number of object (object should be table and index).

When I try to write sql.
select owner,tablespace_name,count(segment_type) tables  
from dba_segments 
where segment_type in ('TABLE') 
GROUP BY OWNER,tablespace_name

I only total number of tables list; Please let me know; how to find total numebr of indexes using the same query; (I hope need some mofification; Please let me know modified sql)

thanks

[Edit MC: format a little bit the query]

[Updated on: Wed, 10 June 2009 00:40] by Moderator

Report message to a moderator

Re: sql [message #407335 is a reply to message #407334] Tue, 09 June 2009 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
select owner,tablespace_name,count(segment_type) tables from dba_segments where segment_type in ('TABLE','INDEX') GROUP BY OWNER,tablespace_name
Re: sql [message #407336 is a reply to message #407335] Tue, 09 June 2009 15:37 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

thanks.

but I want to know total number of tables and index details as per tablesapce and owner.

As per your query it's not correct way..

ps; pls use code tage for sql query posting.

thanks
Re: sql [message #407338 is a reply to message #407336] Tue, 09 June 2009 15:44 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
gentlebabu
ps; pls use code tage for sql query posting.

Well, in my opinion, [code] tags don't mean much when the code itself is not formatted.
Re: sql [message #407344 is a reply to message #407336] Tue, 09 June 2009 16:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>As per your query it's not correct way..
Why would you say so?
>>but I want to know total number of tables and index details
Do you want the sum of tables and indexes grouped by owner and tablespace?


Re: sql [message #407346 is a reply to message #407334] Tue, 09 June 2009 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>it's not correct way..
If you say so.

Posting Guideline direct you to provide expected/desired results.

How are we to know what you actually want, when you don't SHOW us?
Re: sql [message #407349 is a reply to message #407346] Tue, 09 June 2009 19:04 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

I apologize my incorrect question; Sorry for that.

Per user; I have more then few tablespaces assigned; So that I want to find how many tables and indexes available (I mean total number of tables and indexes) as per tablespace and owner.

Thanks
Re: sql [message #407351 is a reply to message #407334] Tue, 09 June 2009 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Posting Guideline direct you to provide expected/desired results.

How are we to know what you actually want, when you don't SHOW us?

Do not use words to describe what you think you want.
SHOW us example of what expected/desired report will contain.
Re: sql [message #407357 is a reply to message #407334] Tue, 09 June 2009 20:50 Go to previous messageGo to next message
alamtapash@gmail.com
Messages: 8
Registered: June 2009
Location: DHAKA
Junior Member
HI,

could pls. try with the following query:

select owner, TABLESPACE_NAME, count(decode(segment_type, 'TABLE',segment_type )) "TABLE", count(decode(segment_type, 'INDEX',segment_type )) "INDEX"
from dba_segments where segment_type in ('TABLE', 'INDEX') GROUP BY OWNER, TABLESPACE_NAME
Re: sql [message #407358 is a reply to message #407334] Tue, 09 June 2009 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
alamtapash@gmail.com


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

SELECT   owner,
         tablespace_name,
         Count(Decode(segment_type,'TABLE',segment_type)) "TABLE",
         Count(Decode(segment_type,'INDEX',segment_type)) "INDEX"
FROM     dba_segments
WHERE    segment_type IN ('TABLE','INDEX')
GROUP BY owner,
         tablespace_name 


Please realize that generally speaking a table or an index is comprised of multiple segments.
So counting segments will be greatly higher than actual table or index counts.

A table or an index can & will exist in a single tablespace ALWAYS.

I can not think of anything useful to be done with such a report.

[Updated on: Tue, 09 June 2009 21:23]

Report message to a moderator

Re: sql [message #407362 is a reply to message #407349] Tue, 09 June 2009 22:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As usual, there are many fancy methods to do this.
To a give a basic few,
using sql*plus reporting.

  1  select     owner,tablespace_name,count(segment_type) tables
  2  from       dba_segments
  3  where      segment_type in ('TABLE','INDEX') and
  4        owner in ('DBADMIN','OE','SYS','SYSTEM')
  5        GROUP BY OWNER,tablespace_name
  6*       ORDER BY owner,tablespace_name
dbadmin@chum > /

OWNER                          TABLESPACE_NAME                    TABLES
------------------------------ ------------------------------ ----------
DBADMIN                        USERS                                   2
OE                             EXAMPLE                                30
OE                             USERS                                  11
SYS                            SYSAUX                                605
SYS                            SYSTEM                               1002
SYSTEM                         SYSAUX                                 36
SYSTEM                         SYSTEM                                218

7 rows selected.

dbadmin@chum > break on owner
dbadmin@chum > compute sum of tables on tablespace_name
dbadmin@chum > /

OWNER                          TABLESPACE_NAME                    TABLES
------------------------------ ------------------------------ ----------
DBADMIN                        USERS                                   2
******************************                                ----------
sum                                                                    2
OE                             EXAMPLE                                30
                               USERS                                  11
******************************                                ----------
sum                                                                   41
SYS                            SYSAUX                                605
                               SYSTEM                               1002
******************************                                ----------
sum                                                                 1607
SYSTEM                         SYSAUX                                 36
                               SYSTEM                                218
******************************                                ----------
sum                                                                  254

7 rows selected.




--using rollup


  1  select     owner,tablespace_name,count(segment_type) objects
  2  from    dba_segments
  3  where   segment_type in ('TABLE','INDEX')  and
  4     owner in   ('DBADMIN','OE','SYS','SYSTEM')
  5     group by rollup (owner,tablespace_name)
  6*    order by owner,tablespace_name

dbadmin@chum > break on owner
dbadmin@chum > /

OWNER                          TABLESPACE_NAME                   OBJECTS
------------------------------ ------------------------------ ----------
DBADMIN                        USERS                                   2
                                                                       2
OE                             EXAMPLE                                30
                               USERS                                  11
                                                                      41
SYS                            SYSAUX                                605
                               SYSTEM                               1002
                                                                    1607
SYSTEM                         SYSAUX                                 36
                               SYSTEM                                218
                                                                     254
                                                                    1904

12 rows selected.                                   

[Updated on: Tue, 09 June 2009 22:15]

Report message to a moderator

Re: sql [message #407599 is a reply to message #407362] Wed, 10 June 2009 11:32 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Thanks Mr Mahesh & alamtapash@gmail.com

Babu
Re: sql [message #407605 is a reply to message #407599] Wed, 10 June 2009 11:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
985 posts and all you could think of as a title is "sql" ?
How descriptive is that in a SQL forum?!
Previous Topic: Data Migration
Next Topic: Duplicating two tables
Goto Forum:
  


Current Time: Sun Dec 04 04:26:30 CST 2016

Total time taken to generate the page: 0.09650 seconds