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

' ||t3.tablespace_name||chr(10)||

-- ‘ PCTFREE ‘||TO_CHAR(t3.pct_free)|| chr(10)||

-- ‘ PCTUSED ‘||TO_CHAR(t3.pct_used)|| chr(10)||

‘ STORAGE( INITIAL ‘||TO_CHAR(t3.initial_extent)|| chr(10)||

‘ NEXT ‘||TO_CHAR(t3.next_extent) ||’ );’ ||chr(10) ,

',') c

FROM user_tab_columns t1,user_tab_columns t2 ,user_tables t3

WHERE t1.table_name = t2.table_name and t2.table_name=t3.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, t3.tablespace_name ,

-- t3.pct_free , t3.pct_used,

t3.initial_extent ,t3.next_extent

order by t1.table_name,t1.column_id;

---------数据恢复 oracle数据库恢复专家 133******** QQ:9417901 网站：http://www.sosdb