Oracle DBA Script : Mapping Database

Maps out the database physically with sizes and paths of all the physical files .It will give the names and sizes of rollback segments also..

Code:
spool dbmap.rpt
start title132.sql "Database Layout " "dbmap.sql"
prompt ================================
prompt Tablespace/Datafile Listing
prompt ================================
prompt
prompt
column "Location" format A60;
column "Tablespace Name" format A15;
column "Size(M)" format 999,990;

break on "Tablespace Name" skip 1 nodup;
compute sum of "Size(M)" on "Tablespace Name";
SELECT tablespace_name "Tablespace Name",
file_name "Location", bytes/1048576"Size(M)"
FROM sys.dba_data_files
Order by tablespace_name;
Prompt
Prompt ================================
Prompt Redo Log Listing
Prompt ================================
Prompt
Prompt
column "Group" format 999;
column "File Location" format A50;
column "Bytes (M)" format 99,990;

break on "Group" skip 1 nodup;
Select a.group# "Group", b.member "File
Location", (a.bytes/1024) "Bytes (K)"
From v$log a, v$logfile b
Where a.group# = b.group#
Order by 1,2;
Prompt
Prompt ================================
Prompt Control File Listing
Prompt ================================
Prompt
Prompt
column name format A80 heading "CONTROL FILE
NAME"
column status format a10 heading "STATUS"
Select name,status
From v$controlfile;
Prompt
Prompt ================================
Prompt Rollback Listing
Prompt ================================
Prompt
Prompt
column "Segment Name" format A15;
column "Tablespace" format A15;
Column "Initial (M)" Format 99,990;
Column "Next (M)" Format 99,990;
column "Min Ext." FORMAT 9999;
column "Max Ext." FORMAT 99999999999;
column "Status" Format A7;

Select segment_name "Segment Name",
tablespace_name "Tablespace",
(initial_extent/1024)/1024 "Initial (M)",
(next_extent/1024)/1024 "Next (M)",
min_extents "Min Ext.",
max_extents "Max Ext.",
status "Status"
From sys.dba_rollback_segs
Order by tablespace_name, segment_name;
Spool Off;