@@header

set pagesize 0

SELECT 'CREATE CONTROLFILE DATABASE "' || name || '" NORESETLOGS ' || DECODE(FORCE_LOGGING,'YES','FORCE LOGGING ',' ') || LOG_MODE
FROM v$database
UNION ALL
   select '    '
         || DECODE(TYPE
               ,'REDO LOG','MAXLOGFILES'
               ,'DATAFILE','MAXDATAFILES'
               ,'REDO THREAD','MAXINSTANCES'
               ,'LOG HISTORY','MAXLOGHISTORY'
               ,TYPE)
         || ' ' || RECORDS_TOTAL
   from v$controlfile_record_section
   where type in ('REDO LOG','DATAFILE','REDO THREAD','LOG HISTORY')
UNION ALL
   SELECT 'LOGFILE '
   FROM DUAL
UNION ALL
   SELECT DECODE(ROWNUM,1 ,' ',',') || sqltext
   FROM ( SELECT DISTINCT  '  GROUP ' || l.group#
                 || '( ' || (SELECT LISTAGG( '''' 
                                      || lf.member 
                                 || '''', ', ') WITHIN GROUP (ORDER BY group#) 
                               FROM v$logfile lf 
                              WHERE lf.group# = l.group#) 
                      || ' )'
                 || ' SIZE ' ||  l.BYTES 
                 || ' BLOCKSIZE ' || l.blocksize sqltext 
               , l.group# 
          FROM v$log l
        ORDER BY l.group#)
UNION ALL
   SELECT 'DATAFILE '
   FROM DUAL
UNION ALL
   SELECT DECODE(ROWNUM,1 ,' ',',') || sqltext
   FROM ( SELECT  '   '''
                 ||  name
                 || '''' sqltext
            FROM v$datafile
         )
UNION ALL
   SELECT 'CHARACTER SET ' || value
   FROM v$nls_parameters
   where parameter = 'NLS_CHARACTERSET'
UNION ALL
   SELECT ';'
   FROM DUAL
;