Query to Check Information about Database Structure

set pages 100 lines 100;
col filename format a15;
SELECT /*+ ordered */ d.tablespace_name tablespace, d.file_name filename, d.bytes filesize, d.autoextensible autoextensible, d.increment_by * e.value increment_by, d.maxbytes maxbytes FROM sys.dba_data_files d, v$datafile v, (SELECT value FROM v$parameter WHERE name = ‘db_block_size’) e WHERE (d.file_name = v.name)
UNION
SELECT d.tablespace_name tablespace, d.file_name filename, d.bytes filesize, d.autoextensible autoextensible, d.increment_by * e.value increment_by, d.maxbytes maxbytes FROM sys.dba_temp_files d, (SELECT value FROM v$parameter WHERE name = ‘db_block_size’) e
UNION
SELECT ‘[ ONLINE REDO LOG ]’, a.member, b.bytes, null, TO_NUMBER(null), TO_NUMBER(null)
FROM v$logfile a, v$log b WHERE a.group# = b.group#
UNION
SELECT ‘[ CONTROL FILE ]’, a.name, TO_NUMBER(null), null, TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a
ORDER BY 1,2;
  • October 2, 2018 | 22 views
  • Comments