Questions

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),
SEGMENT_NAME VARCHAR2(100),
SEGMENT_TYPE VARCHAR2(100),
PARTITION_NAME VARCHAR2(100),
BYTES NUMBER,
NUMROWS NUMBER
)
/

CREATE OR REPLACE TYPE COUNTROWS AS TABLE OF
COUNTROWSTYPE
/

CREATE OR REPLACE PACKAGE COUNTROWSPKG
AS
FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2)RETURN COUNTROWS PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY COUNTROWSPKG
AS
FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2) RETURN COUNTROWS
PIPELINED
IS
O_ROWCOUNT NUMBER;
BEGIN
FOR X IN (SELECT OWNER, SEGMENT_NAME , SEGMENT_TYPE,PARTITION_NAME, BYTES
FROM DBA_SEGMENTS
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 ;
END IF;
PIPE ROW ( COUNTROWSTYPE( X.OWNER,X.SEGMENT_NAME,X.SEGMENT_TYPE,X.PARTITION_NAME,X.BYTES,O_ROWCOUNT));
END LOOP;
RETURN;
END;
END;
/

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.