Question from Burelsons forum . Poster wanted to do a select from dba_segments and get a row count of the table without doing an analyze.
I can only fathom why.
"But who are we to ask why? Our task is to simply do, or die"
That is a quote as best I remember it, I wish I could remember it properly. Even google can't find it I have mangled it so bad.
The answer is in the link, but to have it here too:
CREATE OR REPLACE TYPE COUNTROWSTYPE AS OBJECT
( OWNER VARCHAR2(50),
CREATE OR REPLACE TYPE COUNTROWS AS TABLE OF
CREATE OR REPLACE PACKAGE COUNTROWSPKG
FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2)RETURN COUNTROWS PIPELINED;
CREATE OR REPLACE PACKAGE BODY COUNTROWSPKG
FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2) RETURN COUNTROWS
FOR X IN (SELECT OWNER, SEGMENT_NAME , SEGMENT_TYPE,PARTITION_NAME, BYTES
WHERE OWNER = UPPER( P_SCHEMANAME ) AND ROWNUM<=30
ORDER BY SEGMENT_NAME) LOOP
IF X.SEGMENT_TYPE='TABLE' THEN
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||X.OWNER||'.'||X.SEGMENT_NAME INTO O_ROWCOUNT ;
PIPE ROW ( COUNTROWSTYPE( X.OWNER,X.SEGMENT_NAME,X.SEGMENT_TYPE,X.PARTITION_NAME,X.BYTES,O_ROWCOUNT));
select * from table( COUNTROWSPKG.DOCOUNT('SYSTEM') );
You can see I limited the cursor and warned the poster a couple of times. Dangerous package. I could just imagine if some unknowning person ran this on say a schema with 300-400 million records in each table and a few hundred tables.