how do see how much space is used and free in a tablespace
Answers were Sorted based on User's Feedback
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 |
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 |
how can you connect from 10g user to 11g user??
why Datapump is faster than traditional export and import
Explain the use of setting GLOBAL_NAMES equal to TRUE.
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?
How would you best determine why your MVIEW couldn't FAST REFRESH?
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.
What are the recovery models for a database? : sql server DBA
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?
As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
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
what are the differences of where and if in SAS?
how can you initialize log miner?