Message-ID: <F001.005CA4C5.20030812075923@fatcity.com>
Date: Tue, 12 Aug 2003 07:59:23 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Jesse, Rich" <Rich.Jesse@qtiworld.com>
Subject: RE: Capacity Planning Methods?
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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@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@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@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).