Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','TBS_GROWTH','DBMON') from dual;
select dbms_metadata.get_ddl('VIEW','FREESPACE','DBMON') from dual;
select
dbms_metadata.get_ddl('USER', SYSADM) || usercreate
from
dba_users;
CREATE OR REPLACE FORCE VIEW "DBMON"."FREESPACE" ("NAME", "MBYTES", "USED", "FREE",
"PCT_USED", "PCT_FREE") AS
SELECT NVL(b.tablespace_name, NVL(a.tablespace_name, 'UNKOWN')) NAME,mbytes_alloc
mbytes, mbytes_alloc-NVL(mbytes_free, 0) used,NVL(mbytes_free, 0) free,
((mbytes_alloc-NVL(mbytes_free, 0))/mbytes_alloc)*100 pct_used,100-(((mbytes_alloc-
NVL(mbytes_free, 0))/mbytes_alloc)*100) pct_free FROM (SELECT SUM(BYTES)/1024/1024
mbytes_free, tablespace_name FROM SYS.dba_free_space GROUP BY tablespace_name) a,
(SELECT SUM(BYTES)/1024/1024 mbytes_alloc,tablespace_name FROM SYS.dba_data_files
GROUP BY tablespace_name) b WHERE a.tablespace_name(+) = b.tablespace_name
UNION ALL
SELECT f.tablespace_name,SUM(ROUND((f.bytes_free + f.bytes_used)/1024/1024, 2))
"total MB",SUM(ROUND(NVL(p.bytes_used, 0)/1024/1024, 2)) "Used MB",
SUM(ROUND(((f.bytes_free + f.bytes_used)-NVL(p.bytes_used, 0))/1024/1024, 2)) "Free
MB",(SUM(ROUND(NVL(p.bytes_used, 0)/1024/1024, 2)) * 100)/(SUM(ROUND(f.bytes_free +
f.bytes_used)/1024/1024, 2))),100-(SUM(ROUND(NVL(p.bytes_used, 0)/1024/1024,
2))*100)/(SUM(ROUND((f.bytes_free+f.bytes_used)/1024/1024, 2))) FROM
SYS.v_temp_space_header f, dba_temp_files d,SYS.v_$temp_extent_pool p WHERE
f.tablespace_name(+) = d.tablespace_name AND
GROUP BY f.tablespace_name
ORDER BY 5 desc;
f.file_id(+) = d.file_id AND p.file_id(+) = d.file_id
