DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Received:X-Mailer:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Message-ID;
  b=USL441h/4q6mqmwCxU7VzMOpB2CULZzB9wg6+GEUy/LIdMbpLZXuhh/79EXgfKlT8myONwkExJNcxQUlDGY6z9XJ/fRlWXen38Z5/1THHRwSNScd9ByBNXSyDXZNPquNp7rnJ4jLNjegZkU5f3YGJyHx/KitxOfMx2F8Qunsvlo=;
Date: Fri, 6 Jun 2008 13:26:44 -0700 (PDT)
From: Asif Momen <asif_oracle@yahoo.com>
Subject: Re: dba_tab_partitions
To: oracle-l@freelists.org, joe_dba@hotmail.com
In-Reply-To: <BLU108-W3432E441BFC25867E4EE1097B70@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1105779088-1212784004=:33003"
Message-ID: <264828.33003.qm@web56611.mail.re3.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: asif_oracle@yahoo.com
--0-1105779088-1212784004=:33003
Content-Type: text/plain; charset=us-ascii

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&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; PART3&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; 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@hotmail.com&gt; wrote:
From: Joe Smith &lt;joe_dba@hotmail.com&gt;
Subject: dba_tab_partitions
To: oracle-l@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. 


--0-1105779088-1212784004=:33003
Content-Type: text/html; charset=us-ascii

<table cellspacing='0' cellpadding='0' border='0' background='none' style='font-family:arial;font-size:10pt;color:rgb(51, 51, 51);background-color:rgb(255, 255, 255);width:100%;'><tr><td valign='top' style='font: inherit;'>Hi Joe,<br><br>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.<br><br>Something like this:<br><br>SQL&gt; select p.table_name, p.partition_name, num_rows, bytes<br>&nbsp; 2&nbsp;&nbsp;&nbsp; from user_tab_partitions p, user_segments s<br>&nbsp; 3&nbsp;&nbsp; where p.table_name = s.segment_name<br>&nbsp; 4&nbsp;&nbsp;&nbsp; and p.partition_name = s.partition_name;<br><br>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<br>------------------------------ ------------------------------ ---------- ----------<br>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; PART3&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<br>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<br>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<br><br>SQL&gt;<br><br>Regards<br><br>Asif Momen<br>http://momendba.blogspot.com<br><br><br>--- On <b>Fri, 6/6/08, Joe Smith <i>&lt;joe_dba@hotmail.com&gt;</i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;">From: Joe Smith
 &lt;joe_dba@hotmail.com&gt;<br>Subject: dba_tab_partitions<br>To: oracle-l@freelists.org<br>Date: Friday, June 6, 2008, 8:37 AM<br><br><div id="yiv1250948189">

<style>
#yiv1250948189 .hmmessage P
{
margin:0px;padding:0px;}
#yiv1250948189  .hmmessage
{
FONT-SIZE:10pt;FONT-FAMILY:Tahoma;}
</style>
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?<br>
&nbsp;<br>
&nbsp;<br>
thanks.<br>
&nbsp;<br><br><hr>Enjoy 5 GB of free, password-protected online storage. <a rel="nofollow" target="_blank" href="http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skydrive_062008">Get Windows Live SkyDrive.</a> 
</div></blockquote></td></tr></table><br>

      
--0-1105779088-1212784004=:33003--
--
http://www.freelists.org/webpage/oracle-l