生成创建表脚本，包括Oracle 7的数据

Question

是否有人可以帮助我查询获得一个创建表脚本，包括Oracle 7的数据?由于这是oracle 7，并且是在一个非常旧的linux盒子上，我不认为蟾蜍会工作。我必须使用putty连接到服务器，然后从它启动sqlplus。一切都是灰窗。请帮助。由于

Answer 1

如何:

create table test(id number, data varchar2(100));
insert into test values (1, 'This isn''t the data you''re looking for');

它应该在Oracle 7环境中运行，但它没有经过测试，因为我已经有十年或更长时间没有访问Oracle 7环境了。

要查找当前存在的表的结构，可以使用SQL*Plus描述命令。例如:

SQL> desc dual
Name  Null Type        
----- ---- ----------- 
DUMMY      VARCHAR2(1) 

应该为您在另一个环境中重新创建表本身提供足够的信息。此外，您可以查询[ALL|USER]_CONSTRAINTS， [ALL|USER] _con_columns， [ALL|USER]_INDEXES和[ALL|USER]_TRIGGERS以确定任何其他相关信息(假设存在这些视图，后两个视图可能不存在)。

最后，您可以构造一个查询或进程来提取数据，并对其进行假脱机处理或使用UTL_FILE将其以适合插入的格式写入文件。

Answer 2

您可以尝试导出Oracle 7数据库并将其导入到Oracle 10g XE数据库中，您应该能够从中创建迁移脚本。

请看这里http://docs.oracle.com/cd/B14117_01/server.101/b10763/expimp.htm#sthref707

Answer 3

我几年前写了一个脚本，它应该在oracle 7上工作。这不是一个简单的查询，它创建了一个包和一个表，但它可以帮助

set feedback on
set verify off
prompt Vytvářím pomocné objekty...
create table XDummy(object_name  Varchar2(30),
object_type Varchar2(13),rownm number(3),text varchar2(2000));
create or replace package SPINKYHO_OBJEKTY is
  procedure P_Indexes(PIndexName in Varchar2,Dummy in Varchar2);
  procedure P_Sequences(PSequenceName in Varchar2,Dummy in Varchar2);
  procedure P_Sources(SrcName in Varchar2,SrcType in Varchar2);
  procedure P_Tables(PTableName in Varchar2,Dummy in Varchar2);
  procedure P_Views(PViewName in Varchar2,Dummy in Varchar2);
  procedure P_Triggers(PTriggerName in Varchar2,Dummy in Varchar2);
end SPINKYHO_OBJEKTY;
/
create or replace package body SPINKYHO_OBJEKTY is
procedure Constraints(PTableName in Varchar2) is
  cursor c_constraint is
    select constraint_name,
        decode(constraint_type,'C','CHECK','P','PRIMARY KEY',
        'R','FOREIGN KEY','U','UNIQUE'),search_condition,
        r_owner,r_constraint_name
      from all_constraints
      where table_name = PTableName and
            constraint_name not like 'SYS%' and
            owner = upper('&1');
  cursor c_columns(POwner Varchar2,PConstraint Varchar2) is
    select table_name,column_name from all_cons_columns
      where owner = upper(POwner) and
            constraint_name = PConstraint;
  PConstr    Varchar2(30);
  PTyp       Varchar2(11);
  PCond      Varchar2(2000);
  PRConstr   Varchar2(30);
  PROwn      Varchar2(30);
  PText      Varchar2(2000);
begin
  open c_constraint;
  fetch c_constraint into PConstr,PTyp,PCond,PROwn,PRConstr;
  while c_constraint%FOUND loop
    insert into xdummy(object_name,object_type,rownm,text)
      values(PConstr,'CONSTRAINT',1,'alter table '||PTableName||' add (constraint "'||
             PConstr||'" '||PTyp||' (');
    if PTyp = 'CHECK' then
      insert into xdummy(object_name,object_type,rownm,text)
        values(PConstr,'CONSTRAINT',2,PCond);
      insert into xdummy(object_name,object_type,rownm,text)
        values(PConstr,'CONSTRAINT',3,'));');
    elsif PTyp in ('PRIMARY KEY','UNIQUE') then
      for r in c_columns('&1',PConstr) loop
        if c_columns%ROWCOUNT = 1 then
          PText := r.column_name;
        else
          PText := PText||','||r.column_name;
        end if;
      end loop;
      PText := PText||'));';
      insert into xdummy(object_name,object_type,rownm,text)
        values(PConstr,'CONSTRAINT',2,PText);
    elsif PTyp = 'FOREIGN KEY' then
      for r in c_columns('&1',PConstr) loop
        if c_columns%ROWCOUNT = 1 then
          PText := r.column_name;
        else
          PText := PText||','||r.column_name;
        end if;
      end loop;
      PText := PText||')';
      insert into xdummy(object_name,object_type,rownm,text)
        values(PConstr,'CONSTRAINT',2,PText);
      for r in c_columns(PROwn,PRConstr) loop
        if c_columns%ROWCOUNT = 1 then
          PText := ' REFERENCES '||r.table_name||'('||r.column_name;
        else
          PText := PText||','||r.column_name;
        end if;
      end loop;
      PText := PText||'));';
      insert into xdummy(object_name,object_type,rownm,text)
        values(PConstr,'CONSTRAINT',3,PText);
    end if;
    fetch c_constraint into PConstr,PTyp,PCond,PROwn,PRConstr;
  end loop;
  close c_constraint;
end Constraints;
--
procedure P_Indexes(PIndexName in Varchar2,Dummy in Varchar2) is
  cursor c_columns is
    select table_name,column_name
      from all_ind_columns
    where index_name = PIndexName and
          index_owner = upper('&1')
    order by column_position;
  cursor c_unique is
    select uniqueness from all_indexes
      where index_name = PIndexName and
            owner = upper('&1');
  PTable       Varchar2(30);
  PColumn      Varchar2(30);
  PText        Varchar2(1000);
  PRownm       Number;
  PUniqueness  Varchar2(20);
begin
  open c_unique;
  fetch c_unique into PUniqueness;
  close c_unique;
  open c_columns;
  fetch c_columns into PTable,PColumn;
  PRownm := 1;
  while c_columns%FOUND loop
    if PRownm = 1 then
      if PUniqueness = 'UNIQUE' then
        PText := 'create unique index '||PIndexName||' on '||PTable||' ('||PColumn;
      else
        PText := 'create index '||PIndexName||' on '||PTable||' ('||PColumn;
      end if;
    else
      PText := ','||PColumn;
    end if;
    insert into XDummy(object_name,object_type,rownm,text)
      values(PIndexName,'INDEX',PRownm,PText);
    fetch c_columns into PTable,PColumn;
    PRownm := PRownm + 1;
  end loop;
  insert into XDummy(object_name,object_type,rownm,text)
    values(PIndexName,'INDEX',PRownm+1,')'||chr(10)||';'||chr(10));
  close c_columns;
  commit;
end P_Indexes;
--
procedure P_Sequences(PSequenceName in Varchar2,Dummy in Varchar2) is
begin
  insert into xdummy(object_name,object_type,rownm,text)
    select PSequenceName,'SEQUENCE',1,
           'create sequence '||PSEquenceName||chr(10)||'start with '||to_char(last_number)||
            chr(10)||'increment by '||to_char(increment_by)||';'||chr(10)
      from all_sequences
      where sequence_name = PSequenceName and
            sequence_owner = upper('&1');
  commit;
end P_Sequences;
--
procedure P_Sources(SrcName in Varchar2,SrcType in Varchar2) is
  cursor c_text is
    select text from all_source
      where name = SrcName and
            type = SrcType and
            owner = upper('&1')
      order by line;
  PText        Varchar2(1000);
  PRownm       Number;
begin
  for r_text in c_text loop
    if c_text%ROWCOUNT = 1 then
      PText := 'create '||r_text.text;
    else
      PText := r_text.text;
    end if;
    PRownm := c_text%ROWCOUNT;
    insert into XDummy(object_name,object_type,rownm,text)
      values(SrcName,SrcType,PRownm,PText);
  end loop;
  insert into XDummy(object_name,object_type,rownm,text)
    values(SrcName,SrcType,PRownm+1,'/'||chr(10));
  commit;
end P_Sources;
--
procedure P_Tables(PTableName in Varchar2,Dummy in Varchar2) is
  cursor c_columns is
    select column_name,data_type,data_length,data_precision,data_scale,
        nullable,default_length,data_default
      from all_tab_columns
    where table_name = PTableName and
            owner = upper('&1');
  PColumn      Varchar2(30);
  PDataType    Varchar2(9);
  PDataLength  Number;
  PDataPrec    Number;
  PDataScale   Number;
  PNull        Varchar2(1);
  PDefLength   Number;
  PDefault     Varchar2(2000);
  PText        Varchar2(1000);
  PRownm       Number;
begin
  open c_columns;
  fetch c_columns into PColumn,PDataType,PDataLength,PDataPrec,PDataScale,
              PNull,PDefLength,PDefault;
  PRownm := 1;
  while c_columns%FOUND loop
    if PRownm = 1 then
      PText := 'create table '||PTableName||chr(10)||'('||chr(10)||PColumn;
    else
      PText := ','||PColumn;
    end if;
    PText := PText||' '||lower(PDataType);
    if PDataType in ('VARCHAR2','CHAR','NUMBER') then
      if PDataPrec is not null then
        PText := PText||'('||PDataPrec;
        if nvl(PDataScale,0) <> 0 then
          PText := PText||','||PDataScale||')';
        else
          PText := PText||')';
        end if;
      else
        if PDataLength > 0 then
          PText := PText||'('||PDataLength||')';
        end if;
      end if;
    end if;
    if PDefault is not null then
      PText := PText||' DEFAULT '||substr(PDefault,1,PDefLength);
    end if;
    if PNull = 'Y' then
      PText := PText||' NULL';
    else
      PText := PText||' NOT NULL';
    end if;
    insert into XDummy(object_name,object_type,rownm,text)
      values(PTableName,'TABLE',PRownm,PText);
    fetch c_columns into PColumn,PDataType,PDataLength,PDataPrec,PDataScale,
                PNull,PDefLength,PDefault;
    PRownm := PRownm + 1;
  end loop;
  insert into XDummy(object_name,object_type,rownm,text)
    values(PTableName,'TABLE',PRownm+1,')'||chr(10)||';'||chr(10));
  close c_columns;
  Constraints(PTableName);
  commit;
end P_Tables;
--
procedure P_Views(PViewName in Varchar2,Dummy in Varchar2) is
  cursor c_columns is
    select column_name
      from all_tab_columns
    where table_name = PViewName and
            owner = upper('&1')
    order by column_id;
  cursor c_view is
    select text from all_views
      where view_name = PViewName and
            owner = upper('&1');
  PColumn      Varchar2(30);
  PText        Varchar2(5000);
  PRownm       Number;
begin
  open c_columns;
  fetch c_columns into PColumn;
  PRownm := 1;
  while c_columns%FOUND loop
    if PRownm = 1 then
      PText := 'create view '||PViewName||chr(10)||'('||chr(10)||PColumn;
    else
      PText := ','||PColumn;
    end if;
    insert into XDummy(object_name,object_type,rownm,text)
      values(PViewName,'VIEW',PRownm,PText);
    fetch c_columns into PColumn;
    PRownm := PRownm + 1;
  end loop;
  insert into XDummy(object_name,object_type,rownm,text)
    values(PViewName,'VIEW',PRownm+1,')'||chr(10)||'AS');
  close c_columns;
  open c_view;
  fetch c_view into PText;
  close c_view;
  insert into XDummy(object_name,object_type,rownm,text)
    values(PViewName,'VIEW',PRownm+1,PText||chr(10)||';'||chr(10));
  commit;
end P_Views;
--
procedure P_Triggers(PTriggerName in Varchar2,Dummy in Varchar2) is
  cursor c_triggers is
    select description,trigger_body
      from all_triggers
    where trigger_name = PTriggerName and
            owner = upper('&1');
  PDescr       Varchar2(2000);
  PBody        Varchar2(32000);
  PRownm       Number := 2;
  PText        Varchar2(2000);
begin
  open c_triggers;
  fetch c_triggers into PDescr,PBody;
  insert into XDummy(object_name,object_type,rownm,text)
    values(PTriggerName,'TRIGGER',1,'create trigger '||PDescr);
  while instr(PBody,chr(10)) > 0 loop
    PText := substr(PBody,1,instr(PBody,chr(10)));
    insert into XDummy(object_name,object_type,rownm,text)
      values(PTriggerName,'TRIGGER',PRownm,PText);
    PBody := substr(PBody,instr(PBody,chr(10))+1);
    PRownm := PRownm + 1;
  end loop;
  insert into XDummy(object_name,object_type,rownm,text)
    values(PTriggerName,'TRIGGER',PRownm+1,PBody||'/'||chr(10));
  close c_triggers;
end P_Triggers;
end SPINKYHO_OBJEKTY;
/
set echo off
set termout off
set feedback off
set heading off
prompt Sestavuji seznam dB objektů...
spool main.sql
select 'prompt '||object_type||' '||object_name||'...'||chr(10)||
       'execute spinkyho_objekty.'||decode(object_type,'TABLE','P_Tables',
       'VIEW','P_Views','SEQUENCE','P_Sequences','INDEX','P_Indexes',
       'TRIGGER','P_Triggers','P_Sources')||
       ' ('''||object_name||''','''||object_type||''');'
  from /*all_objects*/ sys.dba_objects
  where object_name != 'XDUMMY' and
        object_name != 'SPINKYHO_OBJEKTY' and
        object_name like upper('&2') and
        owner like upper('&1')
  order by decode(object_type,'TABLE',1,'INDEX',2,'SEQUENCE',3,
            'VIEW',4,'TRIGGER','5','6');
spool off;
set termout on
@main.sql
prompt Vytvarim vysledny soubor...
set termout off
set recsep off
set pages 999
spool dict.sql
select text from xdummy
  order by decode(object_type,'TABLE',1,'INDEX',2,'SEQUENCE',3,
            'VIEW',4,'TRIGGER','5','6'),object_name,rownm;
spool off;
set termout on
set recsep wrapped
set verify on
set heading on
set feedback on
set pages 60
prompt Ruším pomocné objekty...
drop table XDummy;
drop package spinkyho_objekty;

如果需要导出数据，请使用

set feedback off
rem set trimspool on /* 8.1 feature */
set lines 10000
set pages 9999 /* set pages 0 works since Oracle 8 */
spool data.sql
select 'insert into your_table(column1, ...) values ('''||column1||''','''...''');)'
  from your table;
spool off