user_source/all_source有所有用户的存储过程和函数的源代码.表和索引看参考下面: set long 20000; select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u where table_name ='T1'DBMS_METADATA With DBMS_METADATA you can retrieve complete database object definitions (metadata) from the dictionary by specifying: The type of object, for example, tables, indexes, or procedures Optional selection criteria, such as owner or name Optional transformations on the output. By default the output is represented in XML, but callers can specify transformations (into SQL DDL, for example), which are implemented by XSL-T stylesheets stored in the database or externally. DBMS_METADATA provides the following retrieval interfaces: For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_xxx and CLOSE retrieve multiple objects. For browsing: GET_XML and GET_DDL return metadata for a single object and are used in SQL queries and for browsingFUNCTION get_ddl ( object_type IN VARCHAR2, name N VARCHAR2, schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB; object_type The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. In addition the following types may be specified: PACKAGE_SPEC - package specification (without body) PACKAGE_BODY - package body TYPE_SPEC - type specification (without body) TYPE_BODY - type body name An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 28-5.
schema A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.
version The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model The object model to use. This parameter takes the same values as the OPEN model parameter.
transform The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.
要将所有的过程生成创建该过程的创建脚本CREATE OR REPLACE FUNCTION aa_IDentity RETURN VARCHAR2 IS V_IDentity VARCHAR2 (200); BEGIN SELECT D.ID || ',' || D.名称 || ';' || P.ID || ',' || P.编号 || ',' || P.姓名 INTO V_IDentity FROM 上机人员表 O, 人员表 P, 部门人员 R, 部门表 D WHERE UPPER (O.用户名) = USER AND O.人员ID = P.ID AND P.ID = R.人员ID AND R.缺省 = 1 AND R.部门ID = D.ID AND ROWNUM < 2; RETURN V_IDentity; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN SELECT D.ID || ',' || D.名称 || ';' || P.ID || ',' || P.编号 || ',' || P.姓名 INTO V_IDentity FROM 上机人员表 O, 人员表 P, 部门人员 R, 部门表 D WHERE UPPER (O.用户名) = USER AND O.人员ID = P.ID AND P.ID = R.人员ID AND R.部门ID = D.ID AND ROWNUM < 2; RETURN V_IDentity; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN V_IDentity := ''; RETURN V_IDentity; END; END; END; /Create Or Replace Function zl_IncStr( strVal IN VarChar2 ) Return VarChar2 AS strTmp VarChar2(255); bytUp Number:=0; bytAdd Number; Begin If strVal Is Null Then Return(strVal); End If; strTmp:=strVal; FOR I IN Reverse 1 .. Length(strTmp) LOOP IF I = Length(strTmp) THEN bytAdd:=1; ELSE bytAdd:=0; END IF; IF Instr('0123456789',Substr(strTmp,I,1)) > 0 THEN IF To_Number(Substr(strTmp,I,1)) + bytAdd + bytUp < 10 THEN strTmp := Substr(strTmp,1,I - 1)|| (To_Number(Substr(strTmp,I,1)) + bytAdd + bytUp)|| Substr(strTmp,I + 1); bytUp:=0; ELSE strTmp:=Substr(strTmp,1,I - 1)||'0'||Substr(strTmp,I + 1); bytUp:=1; END IF; ELSE IF ASCII(Substr(strTmp,I,1)) + bytAdd + bytUp <= ASCII(Substr(strTmp,I,1)) + (ASCII('Z') - ASCII(UPPER(Substr(strTmp,I,1)))) THEN strTmp := Substr(strTmp,1,I - 1)|| CHR(ASCII(Substr(strTmp,I,1)) + bytAdd + bytUp)|| Substr(strTmp,I + 1); bytUp:=0; ELSE strTmp:=Substr(strTmp,1,I - 1)||'0'||Substr(strTmp,I + 1); bytUp:=1; END IF; END IF; IF bytUp = 0 THEN EXIT; END IF; END LOOP; Return(strTmp); END aa_IncStr; /
http://blog.csdn.net/luxuezhu/archive/2004/08/10/70326.aspx
顺便帮我一个忙,到这里把分接了这里100分哦
http://community.csdn.net/Expert/topic/3260/3260495.xml?temp=2.422512E-03然后从MSDOS中把他拷贝出来,粘贴到记事本就OK
tools->export user objects
set long 20000;
select dbms_metadata.get_ddl('TABLE',u.table_name)
from user_tables u
where table_name ='T1'DBMS_METADATA
With DBMS_METADATA you can retrieve complete database object definitions (metadata) from the dictionary by specifying: The type of object, for example, tables, indexes, or procedures Optional selection criteria, such as owner or name Optional transformations on the output. By default the output is represented in XML, but callers can specify transformations (into SQL DDL, for example), which are implemented by XSL-T stylesheets stored in the database or externally. DBMS_METADATA provides the following retrieval interfaces: For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_xxx and CLOSE retrieve multiple objects. For browsing: GET_XML and GET_DDL return metadata for a single object and are used in SQL queries and for browsingFUNCTION get_ddl (
object_type IN VARCHAR2,
name N VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
object_type
The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. In addition the following types may be specified: PACKAGE_SPEC - package specification (without body) PACKAGE_BODY - package body TYPE_SPEC - type specification (without body) TYPE_BODY - type body
name
An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 28-5.
schema
A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.
version
The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model
The object model to use. This parameter takes the same values as the OPEN model parameter.
transform
The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.
Create Table 保险类别(
序号 NUMBER(3),
名称 VARCHAR2(20),
说明 VARCHAR2(100),
医院编码 VARCHAR2(12),
是否固定 NUMBER(1),
是否禁止 NUMBER(1),
具有中心 NUMBER(1))
TABLESPACE zl9BaseItem
PCTFREE 10 PCTUSED 85 STORAGE (NEXT 255 PCTINCREASE 0 MAXEXTENTS UNLIMITED);Create Table 保险中心目录(
险类 NUMBER(3),
序号 NUMBER(5),
编码 VARCHAR2(6),
名称 VARCHAR2(20))
TABLESPACE zl9BaseItem
PCTFREE 10 PCTUSED 85 STORAGE (NEXT 255 PCTINCREASE 0 MAXEXTENTS UNLIMITED);
的脚本,这是库中的2个表,我要生成创建这两个表的脚本;
到网上搜索下载一个
RETURN VARCHAR2
IS
V_IDentity VARCHAR2 (200);
BEGIN
SELECT D.ID || ',' || D.名称 || ';' || P.ID || ',' || P.编号 || ',' || P.姓名
INTO V_IDentity
FROM 上机人员表 O, 人员表 P, 部门人员 R, 部门表 D
WHERE UPPER (O.用户名) = USER
AND O.人员ID = P.ID
AND P.ID = R.人员ID
AND R.缺省 = 1
AND R.部门ID = D.ID
AND ROWNUM < 2;
RETURN V_IDentity;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT D.ID || ',' || D.名称 || ';' || P.ID || ',' || P.编号 || ',' || P.姓名
INTO V_IDentity
FROM 上机人员表 O, 人员表 P, 部门人员 R, 部门表 D
WHERE UPPER (O.用户名) = USER
AND O.人员ID = P.ID
AND P.ID = R.人员ID
AND R.部门ID = D.ID
AND ROWNUM < 2;
RETURN V_IDentity;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
V_IDentity := '';
RETURN V_IDentity;
END;
END;
END;
/Create Or Replace Function zl_IncStr(
strVal IN VarChar2
)
Return VarChar2
AS
strTmp VarChar2(255);
bytUp Number:=0;
bytAdd Number;
Begin
If strVal Is Null Then
Return(strVal);
End If; strTmp:=strVal; FOR I IN Reverse 1 .. Length(strTmp) LOOP
IF I = Length(strTmp) THEN
bytAdd:=1;
ELSE
bytAdd:=0;
END IF; IF Instr('0123456789',Substr(strTmp,I,1)) > 0 THEN
IF To_Number(Substr(strTmp,I,1)) + bytAdd + bytUp < 10 THEN
strTmp :=
Substr(strTmp,1,I - 1)||
(To_Number(Substr(strTmp,I,1)) + bytAdd + bytUp)||
Substr(strTmp,I + 1);
bytUp:=0;
ELSE
strTmp:=Substr(strTmp,1,I - 1)||'0'||Substr(strTmp,I + 1);
bytUp:=1;
END IF;
ELSE
IF ASCII(Substr(strTmp,I,1)) + bytAdd + bytUp <=
ASCII(Substr(strTmp,I,1)) +
(ASCII('Z') - ASCII(UPPER(Substr(strTmp,I,1)))) THEN
strTmp :=
Substr(strTmp,1,I - 1)||
CHR(ASCII(Substr(strTmp,I,1)) + bytAdd + bytUp)||
Substr(strTmp,I + 1);
bytUp:=0;
ELSE
strTmp:=Substr(strTmp,1,I - 1)||'0'||Substr(strTmp,I + 1);
bytUp:=1;
END IF;
END IF; IF bytUp = 0 THEN
EXIT;
END IF;
END LOOP; Return(strTmp);
END aa_IncStr;
/
没看吗???