Re: dba_tab_partitions

From: Asif Momen <asif_oracle_at_yahoo.com>
Date: Fri, 6 Jun 2008 13:26:44 -0700 (PDT)
Message-ID: <264828.33003.qm@web56611.mail.re3.yahoo.com>

Hi Joe,

It would be better if you use USER_SEGMENTS view instead of DBA_DATA_FILES as the latter will display size of each datafile where are you seems to be interested in obtaining size of each partition.

Something like this:

SQL&gt; select p.table_name, p.partition_name, num_rows, bytes
&nbsp; 2&nbsp;&nbsp;&nbsp; from user_tab_partitions p, user_segments s
&nbsp; 3&nbsp;&nbsp; where p.table_name = s.segment_name
&nbsp; 4&nbsp;&nbsp;&nbsp; and p.partition_name = s.partition_name;

TABLE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARTITION_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUM_ROWS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BYTES

------------------------------ ------------------------------ ---------- ----------
PART                           PART3                                   1      65536

PART&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PART2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65536 PART&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PART1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65536

SQL&gt;

Regards

Asif Momen
http://momendba.blogspot.com

• On Fri, 6/6/08, Joe Smith &lt;joe_dba_at_hotmail.com&gt; wrote: From: Joe Smith &lt;joe_dba_at_hotmail.com&gt; Subject: dba_tab_partitions To: oracle-l_at_freelists.org Date: Friday, June 6, 2008, 8:37 AM

#yiv1250948189 .hmmessage P
{
margin:0px;padding:0px;}
#yiv1250948189 .hmmessage
{
FONT-SIZE:10pt;FONT-FAMILY:Tahoma;}

What is the most efficient way to join dba_data_files and dba_tab_partitions to select num_rows and bytes to display both with a single query?

&nbsp;

&nbsp;

thanks.

&nbsp;

Enjoy 5 GB of free, password-protected online storage. Get Windows Live SkyDrive.       

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 06 2008 - 15:26:44 CDT