SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 355
SET FEEDBACK OFF

SELECT df.AUTOEXTENSIBLE "AutoExtent",Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,50) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(df.maxbytes/1024/1024,2) "Size (MaxMBytes)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE df.tablespace_name='SHIPPING_INDX' and e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

Oracle 10G checking data file space

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 355
SET FEEDBACK OFF

SELECT df.AUTOEXTENSIBLE "AutoExtent",Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,50) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(df.maxbytes/1024/1024,2) "Size (MaxMBytes)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE df.tablespace_name='SHIPPING_INDX' and e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

Create standby controlfile

oracle@anuj> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 6 13:22:27 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@anuj>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/controlfile.ctl';

Database altered