how do see how much space is used and free in a tablespace
- 2 Answers
- 6674 Views
- Genpact, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
what is a user-defined lock?
what do you understand by flashback feature of oracle?
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
Explain materialized views and how they are used.
How can you control the amount of free space in your index pages? : sql server DBA
What are the different types of upgrades that can be performed in sql server? : sql server DBA
how can you schedule a job in database?
Why would you call update statistics? : sql server DBA
how can you get actual data change values from previous transactions in oracle?
In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
what is asm?
What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
what is the role of dip user in our database?
Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
currently iam working in mnc as a oracleappsdba,please send the EXPERIENCE RESUME those who r worked as a oracle apps dba
