用select生成建库语句

有群友在问，“alter database backup controlfile to trace，产生的控制文件内容，有没有办法通过SQL去查询”

好吧。语句如下

SELECT 'CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE ' ||
        (SELECT wmsys.wm_concat(chr(10) || 'GROUP ' || f.group# || f.member ||
                                ' SIZE ' || round(l.bytes / 1024 / 1024) ||
                                'M BLOCKSIZE ' || l.blocksize)
           FROM v$log l
          INNER JOIN (SELECT f.group#,
                            CASE
                              WHEN COUNT(*) > 1 THEN
                               '(' || CAST(wmsys.wm_concat(chr(10) || ' ''' ||
                                                           f.member || '''') AS
                                           VARCHAR2(4000)) || ')'
                              ELSE
                               ' ''' || MAX(f.member) || ''''
                            END AS MEMBER
                       FROM v$logfile f
                      GROUP BY group#) f
             ON f.group# = l.group#) || chr(10) || 'DATAFILE' ||
        (SELECT wmsys.wm_concat(chr(10) || '''' || file_name || '''')
           FROM dba_data_files) || chr(10) || 'CHARACTER SET ' ||
        (SELECT VALUE
           FROM nls_database_parameters
          WHERE parameter = 'NLS_CHARACTERSET')
  FROM dual

开始用的listagg，但他文件较多超出了长度。于是改为wmsys.wm_concat，为哈？clob啊。当然 限11.2