To capture the Number of rows in each partition of a table [message #417585] |
Mon, 10 August 2009 06:13  |
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 #417593 is a reply to message #417590] |
Mon, 10 August 2009 06:53  |
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
|
|
|