select dbms_metadata.get_ddl('TABLE',u.table_name) A_B from user_tables u
解决方案 »
- oracle10G 数据库无法启动
- 请教关于ORACLE触发器的问题
- 请问为何:在oracle10g中创建基于refresh fast on commit的单表物化视图,没有刷新,不胜感激
- 什么地方有oracle 9i for linux的patch阿?
- Oracle8i还是Oracle9i的版本选择问题????
- 常见的问题征求建议
- long 类型的大小问题?
- 关于检索数据的问题
- 如何用PLSQL Developer 修改表字段?
- 想做一个分页的程序。如何让oracle 每次只返回20条数据?
- oracle问题,请教高手,给100分
- 请问存储过程一个输入型的字符串(VARCHAR2)的参数最长是多少?
用exp system/manager tables=scott.dept file=dept.dmp
imp system/manager file=dept.dmp indexfile=c:\dept.sql full=y
到c:\dept.sql就可以查看建dept表的sql 。
有高手能用sql来实现吗?
create or replace function create_table( r_owner in
varchar2, r_table_name in varchar2 )
return varchar2
as
starting boolean :=true;
begin
dbms_output.put_line('create table '||r_owner||'.'||r_table_name||'(');
for r in (select column_name, data_type, data_length, data_precision,
data_scale, data_default, nullable
from all_tab_columns
where table_name = upper(r_table_name)
and owner=upper(r_owner)
order by column_id)
loop
if starting then
starting:=false;
else
dbms_output.put_line(',');
end if;
if r.data_type='NUMBER' then
if r.data_scale is null then
dbms_output.put(r.column_name||' NUMBER('||r.data_precision||')');
else
dbms_output.put(r.column_name||'
NUMBER('||r.data_precision||','||r.data_scale||')');
end if;
else if r.data_type = 'DATE' then
dbms_output.put_line(r.column_name||' DATE');
else if instr(r.data_type, 'CHAR') >0 then
dbms_output.put(r.column_name||' '||r.data_type||'('||r.data_length||')');
else
dbms_output.put(r.column_name||' '||r.data_type);
end if;
end if;
end if;
if r.data_default is not null then
dbms_output.put(' DEFAULT '||r.data_default);
end if;
if r.nullable = 'N' then
dbms_output.put(' NOT NULL ');
end if;
end loop;
dbms_output.put_line(' ); ');
return null;
end;
/---------------------
select create_table( user, table_name ) from user_tables;spool c:\create_table.sql
exec null;
spool off
如果是分区表怎么办?
file_handle UTL_FILE.FILE_TYPE; 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 = 'WBXSITETELEPHONYDOMAIN'
and owner= 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 =user and a.table_name ='WBXSITETELEPHONYDOMAIN' 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;cursor storage--define column
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);
--defin index
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
--open the file
file_handle := UTL_FILE.FOPEN('d:\testtmp','table.sql','w');
UTL_FILE.PUTF(file_handle, '************************* Schema table***WBXUSEROFFICE****************\n\n');
UTL_FILE.PUTF(file_handle, 'Run Date:'||To_Char(SysDate,'DD-MON-YYYY')||'\n\n');
UTL_FILE.PUTF(file_handle, '----------------------------------------------------------------\n\n');
UTL_FILE.PUTF(file_handle, 'CREATE TABLE '||user||'.'||'WBXSITE(\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');
---end snatch constraints ---begin snatch storge
---select * from user_tables;
---end snatch storge
---begin snatch trigger
---end snatch trigger
---begin snatch index
---end snatch index UTL_FILE.PUTF(file_handle, '/\n\n');
UTL_FILE.PUTF(file_handle, '----------------------------------------------------------------\n');
UTL_FILE.PUTF(file_handle, '\n********************** END **********************\n\n');
UTL_FILE.FCLOSE(file_handle);
end;
/