Re: dba_tab_partitions

  • From: Asif Momen <asif_oracle@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx, joe_dba@xxxxxxxxxxx
  • Date: Fri, 6 Jun 2008 13:26:44 -0700 (PDT)

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2008