| < Day Day Up > |
|
–database size SELECT 'Database Size' "*****" ,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round (df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free" ,round(sum(round(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum (nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/ 1024 – sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Used" ,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free" ,sum(round(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used" ,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round (df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) "Size" FROM dba_free_space fs, dba_data_files df WHERE fs.file_id(+) = df.file_id GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible ORDER BY df.file_id; –tablespace size COL Tablespace FORMAT a16; SELECT df.tablespace_name "Tablespace" ,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free" ,round(((df.bytes – sum(nvl(fs.bytes,0))) / (df.bytes) ) * 100) "%Used" ,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free" ,round(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0))) "Mb Used" FROM dba_free_space fs, dba_data_files df WHERE fs.file_id(+) = df.file_id GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible ORDER BY df.file_id; –extent size COL Object FORMAT a24; COL Type FORMAT a5; SELECT segment_name "Object", segment_type "Type" ,ROUND(SUM(bytes)/1024/1024) "Mb", ROUND (SUM(bytes)/1024) "Kb" ,SUM(bytes) "Bytes", SUM(blocks) "Blocks" FROM dba_extents WHERE owner = 'ACCOUNTS' AND segment_type IN ('TABLE','INDEX') GROUP BY segment_name, segment_type ORDER BY segment_name, segment_type DESC; –segment size COL Object FORMAT a24; COL Type FORMAT a5; SELECT segment_name "Object", segment_type "Type" ,ROUND(bytes/1024/1024) "Mb", ROUND(bytes/1024) "Kb" ,bytes "Bytes", blocks "Blocks" FROM dba_segments WHERE owner = 'ACCOUNTS' AND segment_type IN ('TABLE','INDEX') ORDER BY segment_name, segment_type DESC;
| < Day Day Up > |
|