how do see how much space is used and free in a tablespace

Answers were Sorted based on User's Feedback



how do see how much space is used and free in a tablespace..

Answer / mohapatra.gouranga@gmail.com

Best ans is:
SELECT df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "%
Free",
Round((df.bytes - SUM(fs.bytes)) * 100 /
df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 /
fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 /
fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY
df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Is This Answer Correct ?    0 Yes 0 No

how do see how much space is used and free in a tablespace..

Answer / anil kumar prajapati

olumn "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More DB Administration Interview Questions

how can you connect from 10g user to 11g user??

0 Answers   HP,


why Datapump is faster than traditional export and import

4 Answers   Mphasis,


Explain the use of setting GLOBAL_NAMES equal to TRUE.

1 Answers  


if monday take full backup and tuesday it was cumulative backup and wednesday we taken incremental backup, thursday some disaster happen then what type of recovery and how it will take?

1 Answers  


How would you best determine why your MVIEW couldn't FAST REFRESH?

0 Answers  






Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle's. What database recovery options are available? Database is in archive log mode.

0 Answers   Infosys,


What are the recovery models for a database? : sql server DBA

0 Answers  


mssqlserver 2005 Backup Advantages & Disadvantages? mssqlserver 2005 Restore Advantages & Disadvantages? mssqlserver 2005 Recovery mode Advantages & Disadvantages? mssqlserver 2005 Mirroring Advantages & Disadvantages? mssqlserver 2005 Replication Advantages & Disadvantages? mssqlserver 2005 Logshpping Advantages & Disadvantages? mssqlserver 2005 Job Advantages & Disadvantages?

1 Answers   TCS, Wipro,


As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

1 Answers   IBM,


Does any body has ORACLE Certification Dumps or Materials on 9i DBA, 10G DBA, Internet Application Developer, OCP 8i to 10g DBA Upgrade, Oracle 11i if any body have it, please kindly drop an email to: taruni_2k8@yahoo.com

0 Answers   Oracle,


what are the differences of where and if in SAS?

0 Answers  


how can you initialize log miner?

0 Answers   Oracle,


Categories