Home » SQL & PL/SQL » SQL & PL/SQL » To capture the Number of rows in each partition of a table (Oracle9i)
To capture the Number of rows in each partition of a table [message #417585] Mon, 10 August 2009 06:13 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

To capture the Number of rows in each partition for the respective table using the following code but it showing error:

CODE
DECLARE
vcnt NUMBER;
BEGIN
FOR i IN (SELECT ds.partition_name FROM dba_segments ds 
         WHERE ds.SEGMENT_NAME='EMPDETAILS' 
         )
LOOP
 SELECT COUNT(*) INTO vcnt  FROM  EMPDETAILS PARTITION (i.partition_name);
 DBMS_OUTPUT.PUT_LINE (i.partition_name ||' ' ||vcnt);
END LOOP;
END;


ERROR
ORA-06550: line 8, COLUMN 65:
PL/SQL: ORA-00933: SQL command NOT properly ended
ORA-06550: line 8, COLUMN 2:
PL/SQL: SQL STATEMENT ignored


But the query is running in Toad by prompting the values
SELECT COUNT(*)   FROM  EMPDETAILS PARTITION (:partition_name);


Please suggest.




Re: To capture the Number of rows in each partition of a table [message #417589 is a reply to message #417585] Mon, 10 August 2009 06:29 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You need to use Dynamic SQL for this


execute immediate ' SELECT COUNT(*)  FROM  EMPDETAILS PARTITION ( ' || i.partition_name ||' )' into vcnt;

[Updated on: Mon, 10 August 2009 06:33]

Report message to a moderator

Re: To capture the Number of rows in each partition of a table [message #417590 is a reply to message #417589] Mon, 10 August 2009 06:39 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Bonker,

Thanks, its working fine.

Please suggest:

Query
 Table Name: EMPDETAILS
  Press F4 (Toad), it shows the Table Statistics
  even it shows the Partition with Number of rows.
  
  But when I  run query for indidual partition than the number of rows is more and does'nt matches.


Pls. suggest.
Re: To capture the Number of rows in each partition of a table [message #417591 is a reply to message #417590] Mon, 10 August 2009 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Update the statistics.

Regards
Michel
Re: To capture the Number of rows in each partition of a table [message #417593 is a reply to message #417590] Mon, 10 August 2009 06:53 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
I do not know how TOAD works. I just have to assume that TOAD is showing its statistics from DBA_TAB_PARTITIONS Or from USER_TAB_PARTITIONS or from ALL_TAB_PARTITIONS. The statistics in these system object are populated when you gather statistics on the table.

Now if statistics are gathered long time back and subsequently there were lot of inserts and delete activity occurred on this tables and you did not gather statistics after that then obviously there will be difference between rows actually counted on the partition and that shown by the system objects showing the statistics.

[Update] I did not notice Michel had already replied to this. Apologies Michel.

[Updated on: Mon, 10 August 2009 06:53]

Report message to a moderator

Previous Topic: Pl/SQL Procedure Performance Issue
Next Topic: PIVOT query
Goto Forum:
  


Current Time: Thu Feb 06 11:10:14 CST 2025