Oracle Create Control file manually from sqlplus

Create Control file manually 






SET PAGESIZE 0 LINESIZE 300 FEEDBACK OFF HEADING OFF TRIMSPOOL ON

SELECT 'DATAFILE' FROM dual
UNION ALL
SELECT line FROM (
  SELECT '    ''' || name || '''' ||
         CASE WHEN ROW_NUMBER() OVER (ORDER BY file#) = COUNT(*) OVER ()
              THEN '' ELSE ',' END AS line
  FROM   v$datafile
  ORDER  BY file#
);



SET PAGESIZE 0 LINESIZE 300 FEEDBACK OFF HEADING OFF TRIMSPOOL ON

SELECT line FROM (
  SELECT 0 AS ord, 'DATAFILE' AS line FROM dual
  UNION ALL
  SELECT file#,
         '    ''' || name || '''' ||
         CASE WHEN ROW_NUMBER() OVER (ORDER BY file#) = COUNT(*) OVER ()
              THEN '' ELSE ',' END
  FROM   v$datafile
)
ORDER BY ord;


===========================================

full script !!!!!!!!!!!!!!

SET PAGESIZE 0 LINESIZE 500 FEEDBACK OFF HEADING OFF TRIMSPOOL ON VERIFY OFF
SPOOL create_controlfile.sql

-- Header line
SELECT 'CREATE CONTROLFILE REUSE DATABASE "' || name || '" NORESETLOGS ' || log_mode
FROM   v$database;

-- MAX* settings
SELECT '    MAXLOGFILES '   || records_total FROM v$controlfile_record_section WHERE type = 'REDO LOG';
SELECT '    MAXLOGMEMBERS ' || MAX(members) FROM v$log;
SELECT '    MAXDATAFILES '  || records_total FROM v$controlfile_record_section WHERE type = 'DATAFILE';
SELECT '    MAXINSTANCES 1' FROM dual;
SELECT '    MAXLOGHISTORY ' || records_total FROM v$controlfile_record_section WHERE type = 'LOG HISTORY';

-- Redo log groups
SELECT 'LOGFILE' FROM dual;

SELECT line FROM (
  SELECT grp,
         '    GROUP ' || grp || ' (' || members_list || ') SIZE ' || mb || 'M' ||
         CASE WHEN ROW_NUMBER() OVER (ORDER BY grp DESC) = 1 THEN '' ELSE ',' END AS line
  FROM (
    SELECT l.group# AS grp,
           LISTAGG('''' || f.member || '''', ', ') WITHIN GROUP (ORDER BY f.member) AS members_list,
           l.bytes/1024/1024 AS mb
    FROM   v$log l JOIN v$logfile f ON f.group# = l.group#
    GROUP  BY l.group#, l.bytes
  )
)
ORDER BY grp;

-- Datafiles
SELECT 'DATAFILE' FROM dual;

SELECT line FROM (
  SELECT file#,
         '    ''' || name || '''' ||
         CASE WHEN ROW_NUMBER() OVER (ORDER BY file# DESC) = 1 THEN ';' ELSE ',' END AS line
  FROM   v$datafile
)
ORDER BY file#;

SPOOL OFF