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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
