Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Script to Generate Create Database Script from existing Database

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Script to Generate Create Database Script from existing Database

    spool createdb.sql

    set pages 1000
    set head off
    set termout off
    set feedback off
    set newpage none
    set serveroutput on
    select 'CREATE DATABASE '||name text from v$database;
    select 'LOGFILE' from dual;
    declare
    print_var varchar2(200);
    cursor c1 is select member from gv$logfile where inst_id = 1
    order by group#;
    logfile gv$logfile.member%TYPE;
    cursor c2 is select bytes from gv$log where inst_id = 1
    order by group#;
    bytes number;
    lsize varchar2(30);
    begin
    open c1;
    open c2;
    for record in (
    select group#, count(*) members from gv$logfile where inst_id = 1
    group by group#) loop
    dbms_output.put_line(print_var);
    fetch c2 into bytes;
    if mod(bytes,1024) = 0 then
    if mod(bytes,1024*1024) = 0 then
    lsize := to_char(bytes/(1024*1024))||'M';
    else
    lsize := to_char(bytes/1024)||'K';
    end if;
    else
    lsize := to_char(bytes);
    end if;
    lsize := lsize||',';
    if record.members > 1 then
    fetch c1 into logfile;
    print_var := 'GROUP '||record.group#||' (';
    dbms_output.put_line(print_var);
    print_var := ''''||logfile||''''||',';
    for i in 2..record.members loop
    fetch c1 into logfile;
    dbms_output.put_line(print_var);
    print_var := ''''||logfile||''''||',';
    end loop;
    print_var := rtrim(print_var,',');
    dbms_output.put_line(print_var);
    print_var := ') SIZE '||lsize;
    else
    fetch c1 into logfile;
    print_var := 'GROUP '||record.group#||' '''||
    logfile||''''||' SIZE '||lsize;
    end if;
    end loop;
    close c1;
    close c2;
    print_var := rtrim(print_var,',');
    dbms_output.put_line(print_var);
    end;
    /
    select 'MAXLOGFILES '||RECORDS_TOTAL from v$controlfile_record_section
    where type = 'REDO LOG';
    select 'MAXLOGMEMBERS '||dimlm from sys.x$kccdi;
    select 'MAXDATAFILES '||RECORDS_TOTAL from v$controlfile_record_section
    where type = 'DATAFILE';
    select 'MAXINSTANCES '||RECORDS_TOTAL from v$controlfile_record_section
    where type = 'DATABASE';
    select 'MAXLOGHISTORY '||RECORDS_TOTAL from v$controlfile_record_section
    where type = 'LOG HISTORY';
    select log_mode from v$database;
    select 'CHARACTER SET '||value from v$nls_parameters
    where parameter = 'NLS_CHARACTERSET';
    select 'NATIONAL CHARACTER SET '||value from v$nls_parameters
    where parameter = 'NLS_NCHAR_CHARACTERSET';
    select 'DATAFILE' from dual;
    declare
    cursor c1 is select * from dba_data_files
    where tablespace_name = 'SYSTEM' order by file_id;
    datafile dba_data_files%ROWTYPE;
    print_datafile dba_data_files.file_name%TYPE;
    begin
    open c1;
    fetch c1 into datafile;
    -- there is always 1 datafile
    print_datafile := ''''||datafile.file_name||
    ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
    loop
    fetch c1 into datafile;
    if c1%NOTFOUND then
    -- strip the comma and print the last datafile
    print_datafile := rtrim(print_datafile,',');
    dbms_output.put_line(print_datafile);
    exit;
    else
    -- print the previous datafile and prepare the next
    dbms_output.put_line(print_datafile);
    print_datafile := ''''||datafile.file_name||
    ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
    end if;
    end loop;
    end;
    /
    declare
    cursor c1 is select * from dba_data_files
    where tablespace_name NOT IN('SYSTEM') order by file_id;
    datafile dba_data_files%ROWTYPE;
    print_datafile dba_data_files.file_name%TYPE;
    print_tbsp dba_data_files.tablespace_name%TYPE;
    begin
    open c1;
    fetch c1 into datafile;
    -- there is always 1 datafile
    print_datafile := ''''||datafile.file_name||
    ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
    print_tbsp := datafile.tablespace_name||' ';
    loop
    fetch c1 into datafile;
    if c1%NOTFOUND then
    -- strip the comma and print the last datafile
    print_datafile := rtrim(print_datafile,',');
    dbms_output.put_line(print_tbsp);
    dbms_output.put_line(print_datafile);
    exit;
    else
    -- print the previous datafile and prepare the next
    dbms_output.put_line(print_tbsp);
    dbms_output.put_line(print_datafile);
    print_tbsp := datafile.tablespace_name||' ';
    print_datafile := ''''||datafile.file_name||
    ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
    end if;
    end loop;
    end;
    /


    select ';' from dual;
    spool off:

+ Reply to Thread

Similar Threads

  1. Script to Get Oracle Database info
    By shoaibansari in forum Oracle Database Administration 10g and 11g
    Replies: 1
    Last Post: 02-14-2012, 08:09 PM
  2. Replies: 0
    Last Post: 01-10-2012, 06:22 PM
  3. Replies: 0
    Last Post: 01-02-2012, 12:46 AM
  4. Create a Database Manually using Create Database Script
    By Hemant in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 12-26-2011, 08:15 PM
  5. Create Database Script
    By Hemant in forum Database Scripts
    Replies: 0
    Last Post: 12-25-2011, 03:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics