最好的办法是采用图形化的第三方工具,如TOAD,比PD和exp/imp都直观方便。
解决方案 »
- [求教]双表多值select问题
- 嵌套表使用方法求解
- ---------------月份跨年度查询数据------------【100分求助】
- 紧急求助oracle license的含义,license数和并发用户数有关吗?
- 如何理解oracle执行sql语句的过程!
- oracle8I中執行java class時出現method not found!
- 一个很头疼的问题,请大家帮忙看看,谢谢!
- 原来是SQL server数据库和对应的程序,现在要用oracle数据库,要做哪些工作?
- 各位大侠帮帮忙!oracle初学!建立试图
- 问题:什么是本地管理,,表空间的 local 选项??
- 又没有这么一种监视器
- oracle用exp做备份能把视图也备份出来么?应该怎么做啊?
PowerDesigner比较好用
------------------------------------------------------------------------------------------------------------
-- PACKAGE
------------------------------------------------------------------------------------------------------------
-- This package create two type flat file with the reverse engineer schema DB for a following object:
-- TABLE, VIEW, SYNONYM, PACKAGE, PROCEDURE, INDEX, TYPE, TRIGGER
-- Release Oracle 9.x.x
-- R. Procedure: 1.0
-- Data: 23/10/2002
-- Autor: Calogero Gangi
-- Parameter in input:
-- Output: UTL_FILE_DIR
--
-- Remember your set the parameter UTL_FILE_DIR in the init.ora or spfile.ora
--
-- connect as sys
--
-- Single flat file
-- execute reverse_engineer.single_file();
-- Multi flat file (one for object)
-- execute reverse_engineer.multi_file();
--
--CREATE OR REPLACE package reverse_engineer asprocedure single_file(schema_name in varchar2);
procedure multi_file(schema_name in varchar2);end reverse_engineer;
/CREATE OR REPLACE package body reverse_engineer asprocedure single_file(schema_name in varchar2) as
cursor object(schema_db varchar2) is
select object_name,object_type from dba_objects
where owner=schema_db
and object_type in ('TABLE','VIEW','SYNONYM','PACKAGE','PROCEDURE','INDEX','TYPE','TRIGGER')
order by object_type;rec_obj object%rowtype;
ddl_statement clob;
fileHandler UTL_FILE.FILE_TYPE;
begin
fileHandler := UTL_FILE.FOPEN('/usr/local/utl', 'r_e_'||upper(schema_name)||'.sql', 'a');
for rec_obj in object(upper(schema_name)) loop
begin
ddl_statement:=dbms_metadata.get_ddl(rec_obj.object_type,rec_obj.object_name,schema_name);
ddl_statement:=ddl_statement||'/';
UTL_FILE.PUT_LINE(fileHandler,ddl_statement);
exception
when others then
fileHandler := UTL_FILE.FOPEN('/usr/local/utl', 'r_e_error.log', 'a');
UTL_FILE.PUT_LINE(fileHandler,sqlcode);
UTL_FILE.PUT_LINE(fileHandler,sqlerrm);
UTL_FILE.FCLOSE(fileHandler);
end;
end loop;
UTL_FILE.FCLOSE(fileHandler);
exception
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA or SPFILE.ORA');
WHEN utl_file.invalid_mode THEN
raise_application_error(-20001, 'ERROR: Invalid mode for open file');
WHEN utl_file.invalid_operation THEN
raise_application_error(-20002, 'ERROR: Invalid operation for file');WHEN others then
UTL_FILE.FCLOSE(fileHandler);
dbms_output.put_line('Error code: '||sqlcode);
dbms_output.put_line('Error description: '||sqlerrm);
end single_file;procedure multi_file(schema_name in varchar2) as
cursor object(schema_db varchar2) is
select object_name,object_type from dba_objects
where owner=schema_db
and object_type in ('TABLE','VIEW','SYNONYM','PACKAGE','PROCEDURE','INDEX','TYPE','TRIGGER')
order by object_type;rec_obj object%rowtype;
ddl_statement clob;
fileHandler UTL_FILE.FILE_TYPE;
begin
for rec_obj in object(upper(schema_name)) loop
fileHandler := UTL_FILE.FOPEN('/usr/local/utl', 'r_e_'||upper(schema_name)||'.'||rec_obj.object_name||'.sql', 'a');
begin
ddl_statement:=dbms_metadata.get_ddl(rec_obj.object_type,rec_obj.object_name,schema_name);
ddl_statement:=ddl_statement||'/';
UTL_FILE.PUT_LINE(fileHandler,ddl_statement);
UTL_FILE.FCLOSE(fileHandler);
exception
when others then
fileHandler := UTL_FILE.FOPEN('/usr/local/utl', 'r_e_error.log', 'a');
UTL_FILE.PUT_LINE(fileHandler,sqlcode);
UTL_FILE.PUT_LINE(fileHandler,sqlerrm);
UTL_FILE.FCLOSE(fileHandler);
end;
end loop;
exception
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA or SPFILE.ORA');
WHEN utl_file.invalid_mode THEN
raise_application_error(-20001, 'ERROR: Invalid mode for open file');
WHEN utl_file.invalid_operation THEN
raise_application_error(-20002, 'ERROR: Invalid operation for file');
WHEN others then
UTL_FILE.FCLOSE(fileHandler);
dbms_output.put_line('Error code: '||sqlcode);
dbms_output.put_line('Error description: '||sqlerrm);
end multi_file;end reverse_engineer;
/