怎样写语句导出某个table的 CREATE TABLE 的语句啊？
怎样写语句导出某个table的 CREATE TABLE 的语句啊？
[解决办法]
你用toad等工具可以很方便地查看。
自己写语句，麻烦：
shiqiguo 网友的一篇帖子:
create or replace procedure get_table_ddl
AUTHID CURRENT_USER
as
/**************************************************************
函数名称：get_table_ddl
函数功能：得到所有表建表的DDL语句
**************************************************************/
file_handle UTL_FILE.FILE_TYPE;
l_user varchar2(30); --表的schema的名称
l_tablename varchar2(50); --表的名称
--得到用户的所有的表
cursor table_cur is
select table_name
from user_tables;
--得到表的列的名称的游标
cursor snatch_columns is select
owner,
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
data_default,
nullable
from all_tab_columns
where table_name = l_tablename
and owner= l_user
order by column_id;
--得到表的索引的信息的游标
CURSOR constraint_index is select
a.table_owner,
a.index_name,
a.table_owner,
a.table_name,
a.uniqueness,
a.tablespace_name,
a.ini_trans,
a.max_trans,
a.initial_extent,
a.next_extent,
a.min_extents,
a.max_extents,
a.pct_increase,
a.pct_free
from user_indexes a ,user_constraints b
where a.table_owner =l_user and a.table_name =l_tablename and a.table_name=b.table_name
and a.index_name=b.constraint_name and b.constraint_type = 'P ';
--得到列信息
CURSOR col_cursor (i_own VARCHAR2, c_ind VARCHAR2, c_tab VARCHAR2) IS
select column_name
from dba_ind_columns
where index_owner = i_own
and index_name = c_ind
and table_name = c_tab
order by column_position;
--定义 列变量
v_table_owner varchar2(30);
v_table_name varchar2(30);
v_column_name varchar2(50);
v_data_type varchar2(30);
v_data_length varchar2(30);
v_data_precision varchar2(30);
v_data_scale varchar2(30);
v_data_default varchar2(30);
v_nullable varchar2(30);
v_tmp_str varchar2(2000);
--定义 索引变量
lv_index_owner dba_indexes.owner%TYPE;
lv_index_name dba_indexes.index_name%TYPE;
lv_table_owner dba_indexes.table_owner%TYPE;
lv_table_name dba_indexes.table_name%TYPE;
lv_uniqueness dba_indexes.uniqueness%TYPE;
lv_tablespace_name dba_indexes.tablespace_name%TYPE;
lv_ini_trans dba_indexes.ini_trans%TYPE;
lv_max_trans dba_indexes.max_trans%TYPE;
lv_initial_extent dba_indexes.initial_extent%TYPE;
lv_next_extent dba_indexes.next_extent%TYPE;
lv_min_extents dba_indexes.min_extents%TYPE;
lv_max_extents dba_indexes.max_extents%TYPE;
lv_pct_increase dba_indexes.pct_increase%TYPE;
lv_pct_free dba_indexes.pct_free%TYPE;
lv_column_name dba_ind_columns.column_name%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(2000);
begin
select user into l_user from dual;
--open the file
--下面目录必须在参数文件中设置过
file_handle := UTL_FILE.FOPEN( 'D:\oracle\file ', 'table.sql ', 'w ');
UTL_FILE.PUTF(file_handle, '************************* 得到 '||l_user|| '用户所有建表的DDL语句****************\n\n ');
UTL_FILE.PUTF(file_handle, '现在时间是: '||To_Char(SysDate, 'DD-MON-YYYY ')|| '\n\n ');
open table_cur;
loop
fetch table_cur into l_tablename;
exit when table_cur%NOTFOUND;
UTL_FILE.PUTF(file_handle, '----------------------------\n\n ');
UTL_FILE.PUTF(file_handle, 'CREATE TABLE '||user|| '. '||l_tablename|| '(\n ');
---begin snatch the column
lv_first_rec := TRUE;
open snatch_columns;
loop
v_tmp_str := ' ';
fetch snatch_columns into v_table_owner,v_table_name,v_column_name,v_data_type,v_data_length,
v_data_precision,v_data_scale,v_data_default,v_nullable;
exit when snatch_columns%NOTFOUND;
if (lv_first_rec) then
v_tmp_str := v_column_name|| ' ';
lv_first_rec := FALSE;
else
--v_tmp_str := v_tmp_str|| ',\n ';
v_tmp_str := ',\n '||v_column_name|| ' ';
end if;
if v_data_type = 'NUMBER ' then
if v_data_scale is null then
v_tmp_str := v_tmp_str|| 'NUMBER( '||v_data_precision|| ') ';
else
v_tmp_str := v_tmp_str|| 'NUMBER( '||v_data_precision|| ', '||v_data_scale|| ') ';
end if;
else if v_data_type = 'DATE ' then
v_tmp_str := v_tmp_str|| 'DATE ';
else if instr(v_data_type, 'CHAR ') > 0 then
v_tmp_str := v_tmp_str||v_data_type|| '( '||v_data_length|| ') ';
else
v_tmp_str := v_tmp_str||v_data_type|| ' ';
end if;
end if;
end if;
if v_data_default is not null then
v_tmp_str := v_tmp_str|| 'DEFAULT '||v_data_default|| ' ';
end if;
if v_nullable = 'N ' then
v_tmp_str := v_tmp_str|| ' NOT NULL ';
end if;
--v_tmp_str := v_tmp_str|| ', ';
UTL_FILE.PUTF(file_handle,v_tmp_str);
end loop;
close snatch_columns;
--end snatch the columns.
--begin snatch constraints
open constraint_index;
LOOP
FETCH constraint_index INTO lv_index_owner,
lv_index_name,
lv_table_owner,
lv_table_name,
lv_uniqueness,
lv_tablespace_name,
lv_ini_trans,
lv_max_trans,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_pct_free;
EXIT WHEN constraint_index%NOTFOUND;
lv_first_rec := TRUE;
lv_string:= ',\nCONSTRAINT '||(lv_index_name);
--- get CONSTRAINT_index_column
OPEN col_cursor(lv_index_owner,lv_index_name,lv_table_name);
LOOP
FETCH col_cursor INTO lv_column_name;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_string := lv_string|| ' PRIMARY KEY '|| ' ( ';
lv_first_rec := FALSE;
else
lv_string := lv_string || ', ';
end if;
lv_string := lv_string ||(lv_column_name);
END LOOP;
CLOSE col_cursor;
lv_string := lv_string|| ') USING INDEX ';
lv_string := lv_string|| ' TABLESPACE ' || lv_tablespace_name || ' \nSTORAGE ( ';
lv_string := lv_string|| ' \nINITIAL ' || to_char(lv_initial_extent/1024)|| 'K ';
lv_string := lv_string|| ' \nNEXT ' || to_char(lv_next_extent/1024)|| 'K ';
lv_string := lv_string|| ' \nMINEXTENTS ' || to_char(lv_min_extents);
lv_string := lv_string|| ' \nMAXEXTENTS ' || to_char(lv_max_extents) ;
select decode(lv_pct_increase,null,0) into lv_pct_increase from dual;
lv_string := lv_string|| ' \nPCTINCREASE ' || to_char(lv_pct_increase) || ') ';
lv_string := lv_string|| '\n ';
UTL_FILE.PUTF(file_handle,lv_string);
END LOOP;
CLOSE constraint_index;
UTL_FILE.PUTF(file_handle, ')\n ');
UTL_FILE.PUTF(file_handle, '/\n\n ');
---end snatch constraints
END LOOP;
CLOSE table_cur;
--得到表名称结束
UTL_FILE.PUTF(file_handle, '----------------------------\n ');
UTL_FILE.PUTF(file_handle, '\n********************** 完毕 **********************\n\n ');
UTL_FILE.FCLOSE(file_handle);
end get_table_ddl;
/
-- grant execute on get_table_ddl to public;