RE: Capacity Planning Methods?

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Tue, 12 Aug 2003 07:59:23 -0800
Message-ID: <F001.005CA4C5.20030812075923@fatcity.com>

OK, I'll post it. Salt to taste or toss it in the garbage. Note that this is just collecting data and doesn't make any recommendations or such. Comments and critiques welcome, except from Mladen... (running for cover) ;)

And, of course, standard disclaimers apply!

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

--

CREATE TABLE TS_ACTIVITY
(
  TABLESPACE_NAME VARCHAR2(30),

  FREE_SPACE       NUMBER,
  USED_SPACE       NUMBER,
  MAX_FREE_SPACE   NUMBER,
  TIMESTAMP        DATE

)
TABLESPACE USERS
/

COMMENT ON TABLE TS_ACTIVITY IS 'Tablespace Activity: Records changes in physical attributes of all permanent, dictionary-managed tablespaces.' /

CREATE INDEX TS_ACTIVITY_TS_NAME ON TS_ACTIVITY (TABLESPACE_NAME)
TABLESPACE USERS
/

CREATE OR REPLACE PROCEDURE TS_CHECK IS
--

• Explicit SELECT access must be granted to this schema on the following SYS views:
  --
  
• DBA_TABLESPACES
• DBA_DATA_FILES
• DBA_FREE_SPACE
  --
  
• Modification History
• ---------------------------------------------------------------------
• 12/19/2001 REJ Created.

v_tablespace_name		ts_activity.tablespace_name%TYPE;
v_free_space			ts_activity.free_space%TYPE;
v_used_space			ts_activity.used_space%TYPE;
v_max_free_space		ts_activity.max_free_space%TYPE;
v_rowcount			NUMBER;

CURSOR C_TS IS
	SELECT d.tablespace_name, f.bytes "FREE_SPACE",
		NVL(a.bytes - NVL(f.bytes, 0), 0) "USED_SPACE",
		f.max_free_space
	FROM sys.dba_tablespaces d,
		(SELECT tablespace_name, SUM(bytes) bytes
		FROM dba_data_files
		GROUP BY tablespace_name) a,
		(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes)
max_free_space
		FROM dba_free_space
		GROUP BY tablespace_name) f
		WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = f.tablespace_name(+)
			AND d.contents = 'PERMANENT';

BEGIN FOR tsrec IN C_TS LOOP

	SELECT COUNT(*)
		INTO v_rowcount
	FROM ts_activity
	WHERE tablespace_name = tsrec.tablespace_name;

	IF v_rowcount > 0 THEN
		SELECT q.tablespace_name, q.free_space, q.used_space,
q.max_free_space
			INTO v_tablespace_name, v_free_space, v_used_space,
v_max_free_space
		FROM
			ts_activity q,
			(SELECT MAX(timestamp) timestamp
			FROM ts_activity
			WHERE tablespace_name = tsrec.tablespace_name) ts
		WHERE q.tablespace_name = tsrec.tablespace_name
			AND q.timestamp = ts.timestamp;
	END IF;

	IF tsrec.free_space != v_free_space
	OR tsrec.used_space != v_used_space
	OR tsrec.max_free_space != v_max_free_space
	OR v_rowcount = 0 THEN
		INSERT INTO ts_activity
			(tablespace_name, free_space, used_space,
max_free_space, timestamp)
			VALUES (tsrec.tablespace_name, tsrec.free_space,
tsrec.used_space, tsrec.max_free_space, SYSDATE);
	END IF;

END LOOP; END TS_CHECK;
/

-----Original Message-----

Sent: Monday, August 11, 2003 5:29 PM
To: Multiple recipients of list ORACLE-L

Rich,
I'd love to see the procedure and table that you use. Thanks for offering.

Best regards,
David B. Wagoner
Database Administrator
Arsenal Digital Solutions
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Aug 12 2003 - 10:59:23 CDT