Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle Database Schema Size Query
SELECT /*+ ordered */
  CAST(ddf.tablespace_name  AS VARCHAR2(15))   tablespace,
  ddf.file_id                             file_id,
  CAST(ddf.file_name  AS VARCHAR2(47))    filename,
  ddf.bytes/1024/1024/1024                filesize_gb,
  NVL((ddf.bytes - qt_dfs.bytes), ddf.bytes)     used,
  TRUNC(((NVL((ddf.bytes - qt_dfs.bytes), ddf.bytes))/ddf.bytes) * 100) pct_used 
FROM
  sys.dba_data_files ddf,
  v$datafile df,
  (select file_id, SUM(bytes) bytes
   from sys.dba_free_space
   GROUP BY file_id
  ) qt_dfs
WHERE  ddf.file_id = qt_dfs.file_id (+)
  AND  ddf.file_name = df.name
UNION
SELECT
  CAST(dtf.tablespace_name  AS VARCHAR2(15))   tablespace,
  dtf.file_id                             file_id,
  CAST(dtf.file_name  AS VARCHAR2(47))    filename,
  dtf.bytes/1024/1024/1024                filesize_gb,
  NVL(tep.bytes_cached, 0)                used,
  TRUNC((tep.bytes_cached/dtf.bytes) * 100) pct_used
FROM 
  sys.dba_temp_files dtf,
  v$temp_extent_pool tep,
  v$tempfile tf
WHERE dtf.file_id = tep.file_id (+)
  AND tf.file# = dtf.file_id;

=> Reference (file size in MB) Query Output (11gXE)


Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: April 04, 2015

  39887