oracle建表脚本导出,oracle中导出表结构,产生建表得脚本跟存储参数

oracle中导出表结构，产生建表得脚本跟存储参数 ----------------------------

/************************************************************/

/* 功能：自动产生创建表的脚本 */

/* 文件名：gen_cre_tab1.sql */

/************************************************************/

set linesize 500

set pagesize 1000

set arraysize 8

set feedback off

set heading off

select decode(t1.column_id,1,'CREATE TABLE '||t1.table_name|| ' (', ' ') a,

t1.column_name b,

t1.data_type||decode(t1.data_type,'DATE',

decode(t1.NULLABLE,'N',' not null'),

'VARCHAR2','('||to_char(t1.data_length)||')'||

decode(t1.NULLABLE,'N',' not null'),

'NUMBER',decode(t1.data_precision,null,‘ ‘ ,

'('||to_char(t1.data_precision)|| ','||to_char(t1.data_scale)||')' )

||decode(t1.NULLABLE,'N',' not null'), 'CHAR','('||to_char(t1.data_length)||')'||

decode(t1.NULLABLE,'N',' not null'))|| decode(t1.column_id,max(t2.column_id),');',',') c FROM user_tab_columns t1,user_tab_columns t2 WHERE t1.table_name = t2.table_name and

t1.table_name in (select distinct object_name from user_objects

where object_type='TABLE')

group by t1.column_id,t1.table_name,t1.data_type,t1.nullable,

t1.data_length,t1.data_scale,t1.column_name,

t1.data_precision

order by t1.table_name,t1.column_id;

例2．从USER_TABLES和USER_TAB_COLUMNS来产生创建表的脚本：

/************************************************************/

/* 功能：自动产生创建表的脚本和相应的存储参数 */

/* 文件名：gen_cre_tab2.sql */

/************************************************************/

set linesize 500

set pagesize 1000

set arraysize 8

set feedback off

set heading off

select decode(t1.column_id,1,'CREATE TABLE '||t1.table_name|| ' (', ' ') a, t1.column_name b, t1.data_type||decode(t1.data_type,'DATE',

decode(t1.NULLABLE,'N',' not null'),

'VARCHAR2','('||to_char(t1.data_length)||')'||

decode(t1.NULLABLE,'N',' not null'), 'NUMBER',decode(t1.data_precision,null,‘ ‘ ,

'('||to_char(t1.data_precision)|| ','||to_char(t1.data_scale)||')' )

||decode(t1.NULLABLE,'N',' not null'), 'CHAR','('||to_char(t1.data_length)||')'||

decode(t1.NULLABLE,'N',' not null'))|| decode(t1.column_id,max(t2.column_id),

')'||chr(10)|| 'TABLESPACE