创建控制文件sql语句

DECLARE
  CURSOR C1 IS
    SELECT FILE_NAME FROM DBA_DATA_FILES;
  CURSOR C2 IS
    SELECT A.GROUP# GRP, A.MEMBER MEM, B.BYTES / 1024 / 1024 BYT
      FROM V$LOGFILE A, V$LOG B
     WHERE A.GROUP# = B.GROUP#
     ORDER BY A.GROUP#;
  CURSOR C3 IS
    SELECT TABLESPACE_NAME, FILE_NAME, BYTES / 1024 / 1024 AS TBYT
      FROM DBA_TEMP_FILES;
  MAX_GRP  PLS_INTEGER;
  CNT      PLS_INTEGER;
  VAL1     PLS_INTEGER;
  VAL2     PLS_INTEGER;
  VAL3     PLS_INTEGER;
  VAL4     PLS_INTEGER;
  VAL5     PLS_INTEGER;
  PVAL     VARCHAR2(100);
  NAME     VARCHAR2(50);
  LOG_MODE VARCHAR2(12);
BEGIN
  SELECT NAME, LOG_MODE INTO NAME, LOG_MODE FROM V$DATABASE;
  SELECT PROPERTY_VALUE
    INTO PVAL
    FROM DATABASE_PROPERTIES
   WHERE PROPERTY_NAME = 'NLS_CHARACTERSET';
  DBMS_OUTPUT.PUT_LINE('CREATE CONTROLFILE SET DATABASE  ' || NAME ||
                       '  RESETLOGS  ' || LOG_MODE);
  SELECT RECORDS_TOTAL
    INTO VAL1
    FROM V$CONTROLFILE_RECORD_SECTION
   WHERE TYPE = 'REDO LOG';
  SELECT DIMLM INTO VAL2 FROM X$KCCDI;
  SELECT RECORDS_TOTAL
    INTO VAL3
    FROM V$CONTROLFILE_RECORD_SECTION
   WHERE TYPE = 'DATAFILE';
  SELECT RECORDS_TOTAL
    INTO VAL4
    FROM V$CONTROLFILE_RECORD_SECTION
   WHERE TYPE = 'THREAD INSTANCE NAME MAPPING';
  SELECT RECORDS_TOTAL
    INTO VAL5
    FROM V$CONTROLFILE_RECORD_SECTION
   WHERE TYPE = 'LOG HISTORY';
  DBMS_OUTPUT.PUT_LINE('MAXLOGFILES ' || VAL1);
  DBMS_OUTPUT.PUT_LINE('MAXLOGMEMBERS ' || VAL2);
  DBMS_OUTPUT.PUT_LINE('MAXDATAFILES ' || VAL3);
  DBMS_OUTPUT.PUT_LINE('MAXINSTANCES ' || VAL4);
  DBMS_OUTPUT.PUT_LINE('MAXLOGHISTORY ' || VAL5);
  DBMS_OUTPUT.PUT_LINE('LOGFILE');
  SELECT MAX(GROUP#) INTO MAX_GRP FROM V$LOGFILE;
  SELECT COUNT(1) INTO CNT FROM DBA_DATA_FILES;
  FOR REC1 IN C2 LOOP
    IF REC1.GRP != MAX_GRP THEN
      DBMS_OUTPUT.PUT_LINE('GROUP ' || REC1.GRP || '  ''' || REC1.MEM || '''' ||
                           '   SIZE  ' || REC1.BYT || 'M,');
    ELSE
      DBMS_OUTPUT.PUT_LINE('GROUP ' || REC1.GRP || '  ''' || REC1.MEM || '''' ||
                           '   SIZE  ' || REC1.BYT || 'M');
      DBMS_OUTPUT.PUT_LINE('DATAFILE');
    END IF;
  END LOOP;
  FOR REC IN C1 LOOP
    IF C1%ROWCOUNT != CNT THEN
      DBMS_OUTPUT.PUT_LINE('''' || REC.FILE_NAME || ''',');
    ELSE
      DBMS_OUTPUT.PUT_LINE('''' || REC.FILE_NAME || '''');
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('CHARACTERSET    ' || PVAL || ';');
  FOR REC2 IN C3 LOOP
    DBMS_OUTPUT.PUT_LINE('ALTER TABLESPACE ' || REC2.TABLESPACE_NAME ||
                         ' ADD TEMPFILE ');
    DBMS_OUTPUT.PUT_LINE('''' || REC2.FILE_NAME || '''' || ' SIZE ' ||
                         REC2.TBYT || 'M REUSE AUTOEXTEND OFF;');
  END LOOP;
END;

/

 

sql语句如下：

CREATE CONTROLFILE SET DATABASE  ORCL  RESETLOGS  NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1  'D:\APP\ZHANGLI\ORADATA\ORCL\REDO01.LOG'   SIZE  50M,
GROUP 2  'D:\APP\ZHANGLI\ORADATA\ORCL\REDO02.LOG'   SIZE  50M,
GROUP 3  'D:\APP\ZHANGLI\ORADATA\ORCL\REDO03.LOG'   SIZE  50M
DATAFILE
'D:\APP\ZHANGLI\ORADATA\ORCL\USERS01.DBF',
'D:\APP\ZHANGLI\ORADATA\ORCL\UNDOTBS01.DBF',
'D:\APP\ZHANGLI\ORADATA\ORCL\SYSAUX01.DBF',
'D:\APP\ZHANGLI\ORADATA\ORCL\SYSTEM01.DBF',
'D:\APP\ZHANGLI\ORADATA\ORCL\EXAMPLE01.DBF'
CHARACTERSET    ZHS16GBK;
ALTER TABLESPACE TEMP ADD TEMPFILE
'D:\APP\ZHANGLI\ORADATA\ORCL\TEMP01.DBF' SIZE 28M REUSE AUTOEXTEND OFF;