1
SELECT tablespace_name, sum(bytes)/1024/1024 "MB Free"
  FROM dba_free_space 
 WHERE tablespace_name = 'USERS'
 GROUP BY tablespace_name;

Hi everyone, the above query is what i use for showing the free space in user tablespace but how do i write a separate PL/SQL script to store it in separate table with tablespace name and time stamp.

Kindly help me as i need this run on job scheduler for every hour.

Thanks in advance

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
peedee
  • 1,941
  • 2
  • 14
  • 14

1 Answers1

2

Assuming you've already created the table you want to store the data in, simply

CREATE OR REPLACE PROCEDURE snap_free_space
AS
BEGIN
  INSERT INTO new_table( tablespace_name, free_bytes, collection_time )
    SELECT tablespace_name, sum(bytes), sysdate
      FROM dba_free_space
     GROUP BY tablespace_name;
END;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • thank you so much, do u have any idea how do i run it on job scheduler every hour ? – peedee Oct 07 '11 at 21:55
  • DBMS_JOB is the internal database scheduling tool. – Thomas Jones-Low Oct 08 '11 at 03:41
  • DBMS_SCHEDULER was introduced with 10g. http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25788/d_sched.htm#CIHHBGGI "The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package." http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25788/d_job.htm#BABHCBFD – Shannon Severance Oct 08 '11 at 06:44