Tuesday, July 25, 2006

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.

4 comments:

Anonymous said...

The original is something like

"Ours is not to question why, ours is but to do or die.”


which is a popular misquote of The Charge of the Light Brigade by
Alfred, Lord Tennyson.

"Forward, the Light Brigade!
Was there a man dismay’d?
Not tho’ the soldier knew
Someone had blunder’d:
Their’s not to make reply,
Their’s not to reason why,
Their’s but to do and die:
Into the valley of Death
Rode the six hundred."

Regards
Chris

uiyui said...

EVEN by wow gold the standards gold in wow of the worst financial buy wow gold crisis for at least wow gold cheap a generation, the events of Sunday September 14th and the day before were extraordinary. The weekend began with hopes that a deal could be struck,maplestory mesos with or without government backing, to save Lehman Brothers, America''s fourth-largest investment bank.sell wow gold Early Monday buy maplestory mesos morning Lehman maplestory money filed for Chapter 11 bankruptcy protection. It has more than maplestory power leveling $613 billion of debt.Other vulnerable financial giants scrambled maple money to sell themselves or raise enough capital to stave off a similar fate. billig wow gold Merrill Lynch, the third-biggest investment bank, sold itself to Bank of America (BofA), an erstwhile Lehman suitor,wow power leveling in a $50 billion all-stock deal.wow power leveling American International Group (AIG) brought forward a potentially life-saving overhaul and went maple story powerleveling cap-in-hand to the Federal Reserve. But its shares also slumped on Monday.

Adi said...

Find Internet Marketing resource hare Online Marketing Strategy Internet Marketing Tools Online Marketing Campaign Online Marketing Business Online Marketing System Online Business Online Home Business Online Business Tips Internet Marketing Online Online Marketing Affiliate Marketing Online Marketing Success Selling Online Free Online Marketing Online Marketing Tools Online Marketing Blog

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa