53 lines
1.5 KiB
SQL
53 lines
1.5 KiB
SQL
@@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
|
|
;
|