oracle克隆一张表,oracle复制另外一张表结构到一张新表中

toad F4

或

plsql developer   view/edit  -> view sql

要是指量的话，写个procedure也行

--create or replace directory DATA_PUMP_DIR as '/home/oracle/admin/RT/dpdump';

--grant read,write on directory DATA_PUMP_DIR to public;

procedure sp_export_ddl_table(p_table_name VARCHAR2,p_file_name VARCHAR2)

is

v_file          UTL_FILE.file_type;

v_buffer        VARCHAR2(32767);

v_amount        BINARY_INTEGER := 1024;

v_pos           INTEGER :=1;

v_clob          CLOB;

v_clob_length   INTEGER;

BEGIN

SELECT dbms_metadata.get_ddl(upper('table'),p_table_name)||';'

INTO v_clob

FROM dual;

v_clob_length := dbms_lob.getlength(v_clob);

v_file := UTL_FILE.FOPEN('DATA_PUMP_DIR',p_file_name||'.sql','a',32767);

WHILE v_pos < v_clob_length LOOP

dbms_lob.read(v_clob,v_amount,v_pos,v_buffer);

UTL_FILE.put(v_file,v_buffer);

v_pos := v_pos + v_amount;

END LOOP;

UTL_FILE.FCLOSE(v_file);

END sp_export_ddl_table;

--将一个用户下所有表的定义SQL输出到文件中

procedure sp_export_ddl_user (p_owner_name VARCHAR2 DEFAULT USER)

is

BEGIN

FOR rec in (select table_name from user_tables ) LOOP

sp_export_ddl_table(rec.table_name,p_owner_name);

END LOOP;

END;