RE: Looking for a Script to list tables,its indexes and sizing info

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 27 Jan 2006 14:48:31 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF27068C8F45@AABO-EXCHANGE02.bos.il.pqe>

Based on your original question, I think this ought to do it:  

select owner, segment_type type, tablespace_name tablespace, segment_name, initial_extent/1024 iext_kb, extents, bytes/1048576 mb from dba_segments;  

It's pretty straightforward, really. Add where clause to filter out what you don't want, as appropriate.  

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"There are 10 types of people in the world: Those who understand binary, and those who don't."  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BN Sent: Friday, January 27, 2006 2:42 PM
To: Igor Neyman
Cc: Oracle-L Freelists
Subject: Re: Looking for a Script to list tables,its indexes and sizing info

Greetings,  

This is not a Home work Question, I am trying to group tables based on their activty(high dml, hihg read, etc.,) and put them in New LMTS tablespaces with proper sizes and settings yo avoid hot disk issues.  

I have a script to list the tables and its indexes, I couldn't figure out a way to get the sizing details, so that I can do a sum for each group to create the right Tablespaces with right size  

On 1/27/06, Igor Neyman <ineyman_at_perceptron.com> wrote:

        Look at dba_extents.

---

        From: oracle-l-bounce_at_freelists.org [mailto: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of BN

	Sent: Friday, January 27, 2006 2:04 PM
	To: Oracle-L Freelists
	Subject: Looking for a Script to list tables,its indexes and

sizing info                            

        Greetings,          

        Oracle 9iRel2:          

        I am looking for a script to report the following info for the (given) table and its indexes          

	OWNER    TYPE     TABLESPACE         SEGMENT_NAME
IEXT_KB    EXTENTS         MB
	-------- -------- ------------------ ---------------------------


---------- ---------- ---------- 

	ABC      Table     TBS1               Table1
NNNN      NNN        NNNNNNN
	         Index     TBS2               Index1
nnnn       nn        nnnnnnn
	                   TBS2               Index2
nnnn       nnnn     nnnnnnnn
	                                      ......
	 
	         Table     TBS3               Table2
nnnn      nnnn      nnnnnnn
	         Index     TBS4               Index1
nnnn      nnnn      nnnnnnnn
	..........
	 
	Thank you inadvance

	Regards & Thanks
	BN 

--

Regards & Thanks
BN

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 27 2006 - 13:48:31 CST