-- 日期:2013-02-13 -- 调用: -- EXEC COMPARE_DBTODB '','OPENVONE',2,'MYSQL1','OPENVONE',3 -- EXEC COMPARE_DBTODB 'ORACLE2','OPENVONE',3,'','OPENVONE',2 -- . ( (((( ) ( 4000 ), @MSSQLTEXT VARCHAR ( 4000 ), @MYSQLTEXT VARCHAR ( 4000 ), @SOUR
--日期:2013-02-13
--调用:
--EXEC COMPARE_DBTODB '','OPENVONE',2,'MYSQL1','OPENVONE',3
--EXEC COMPARE_DBTODB 'ORACLE2','OPENVONE',3,'','OPENVONE',2
--.
(
(((()
(4000),
@MSSQLTEXT VARCHAR(4000),
@MYSQLTEXT VARCHAR(4000),
@SOURCETEXT VARCHAR(4000),
@TARGETTEXT VARCHAR(4000),
@SQLTEXT VARCHAR(4000)
N'SELECT A.TABLE_NAME AS TABLENAME,B.COLUMN_NAME AS COLUMNNAME,
CASE DATA_TYPE
WHEN THEN
CASE NVL(TO_CHAR(B.DATA_SCALE),)
WHEN
ELSE
END
WHEN
WHEN
WHEN
WHEN
ELSE B.DATA_TYPE
END AS SQLTYPE,
CASE DATA_TYPE
WHEN )
WHEN
ELSE B.DATA_TYPE
END AS ALLTYPE,
CASE B.NULLABLE WHEN END AS ISNULLABLE,
COLUMN_ID AS COLUMN_ID
from dba_tables A JOIN DBA_TAB_COLUMNS B ON (A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER)
WHERE A.OWNER=N'SELECT TABLENAME,COLUMNNAME,SQLTYPE,SQLTYPE AS ALLTYPE,ISNULLABLE,COLUMN_ID FROM(
SELECT A.NAME AS TABLENAME,b.name AS COLUMNNAME,UPPER(c.NAME)+
replace(CASE c.name
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
WHEN
ELSE ) AS SQLTYPE,
case b.ISNULLABLE
when 0 then
ELSE END AS ISNULLABLE,B.COLORDER AS COLUMN_ID
FROM .DBO.sysobjects a
JOIN .DBO.syscolumns b ON a.id=b.id
JOIN .DBO.systypes c ON (b.xtype=c.xtype AND B.XUSERTYPE=C.XUSERTYPE)
WHERE A.XTYPE=N'SELECT A.TABLE_NAME AS TABLENAME,B.COLUMN_NAME AS COLUMNNAME,
CASE B.COLUMN_TYPE
WHEN
WHEN
WHEN
ELSE UPPER(B.COLUMN_TYPE) END AS SQLTYPE,
B.COLUMN_TYPE AS ALLTYPE,
CASE B.IS_NULLABLE
WHEN
WHEN
END AS ISNULLABLE,B.ORDINAL_POSITION AS COLUMN_ID
from information_schema.`TABLES` A
JOIN information_schema.`COLUMNS` B
ON (A.TABLE_NAME=B.TABLE_NAME AND A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_CATALOG=B.TABLE_CATALOG)
WHERE A.TABLE_SCHEMA=(,,@SOURCEDB)
(,,@TARGETDB)
(((((tempdb.dbo.sysobjects name)
DROP TABLE ##TEMPSOURCE
tempdb.dbo.sysobjects name)
DROP TABLE ##TEMPTARGET
)
)
SELECT A.TABLENAME,A.COLUMNNAME,A.SQLTYPE AS SQLTYPE1,B.SQLTYPE AS SQLTYPE2,A.ALLTYPE AS ALLTYPE1,B.ALLTYPE AS ALLTYPE2,A.ISNULLABLE AS ISNULLABLE1,B.ISNULLABLE AS ISNULLABLE2,A.COLUMN_ID
FROM ##TEMPSOURCE A JOIN ##TEMPTARGET B ON (A.TABLENAME=B.TABLENAME AND A.COLUMNNAME=B.COLUMNNAME AND (CONVERT(VARCHAR,A.SQLTYPE)<>CONVERT(VARCHAR,B.SQLTYPE) OR A.ISNULLABLE<>B.ISNULLABLE))
ORDER BY A.TABLENAME,A.COLUMN_ID
DROP TABLE ##TEMPSOURCE
DROP TABLE ##TEMPTARGET
二:以下为ORACLE数据库断自动生成存储过程和获取数据库DDL语句的过程,最终通过调用CLOB_READ保存入文件中,目前暂未修正超过32767字节的写文本问题。超过32767字节文本会被自动截断。
ADD_ALL_TABLES
--功能:执行添加表记录的存储过程,返回大字段,需要保存到文件进行读取
--参数:DBUSER-数据库用户,TBNAME-模糊匹配的表名,如果为空或''则生成全库的过程,SQLTEXT-返回的存储过程大文本字段
--调用:
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
DBA_TABLES WHERE OWNER=DBUSER
TBNAMEtable_name ;
R_COL(TABLENAME dba_tab_columns WHERE owner=DBUSER
AND table_name=tablename
ORDER BY column_id;
r_tb_list dba_tables%rowtype;
r_col_list dba_tab_columns%rowtype;
addcol1 VARCHAR2(4000):='';
addcol2 VARCHAR2(4000):='';
BEGIN
OPEN r_tb;
LOOPr_tb INTO r_tb_list;
EXIT WHEN r_tb%notfound;
sqltext:CHR(10);
sqltext:substr(r_tb_list.table_name,r_tb_list.table_nameCHR(10);
sqltext:CHR(10);
sqltext:substr(r_tb_list.table_name,CHR(10);
addcol1:='';
addcol2:='';
OPEN r_col(r_tb_list.table_name);
LOOP
FETCH R_COL INTO R_COL_LIST;
EXIT when R_COL%NOTFOUND;
sqltext:r_col_list.column_namer_col_list.data_typeCHR(10); --参数名称默认以小写'p'开头,可以修改该处的规则为想要的命名方式
ADDCOL1:;
addcol2:r_col_list.column_name;
LOOP;
CLOSE r_col;
addcol1:= substr(addcol1, 0, LENGTH(addcol1)-1);
addcol2:= substr(addcol2, 0, LENGTH(addcol2)-1);
sqltext:CHR(CHR(CHR(r_tb_list.table_nameaddcol1CHR(addcol2CHR(CHR(substr(r_tb_list.table_name,CHR(CHR(10);
END LOOP;
CLOSE r_tb;
END;
DEL_ALL_TABLES
--功能:执行删除的存储过程(根据主键列删除,注意标识符不能超过30个字符),返回大字段,需要保存到文件进行读取
--参数:DBUSER-数据库用户,TBNAME-模糊匹配的表名,如果为空或''则生成全库的过程,SQLTEXT-返回的存储过程大文本字段
--调用:
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
dba_tables WHERE owner=DBUSER
TBNAMEtable_name ;
r_pk(tablename dba_tab_columns WHERE owner=DBUSER
AND table_name=tablename AND column_name IN
(
SELECT col.column_name FROM user_constraints con, user_cons_columns col
col.table_name=tablename
)
ORDER BY column_id;
r_tb_list dba_tables%rowtype;
r_pk_list dba_tab_columns%rowtype;
wherecol VARCHAR2(4000):='';
BEGIN
OPEN r_tb;
LOOPr_tb INTO r_tb_list;
EXIT WHEN r_tb%notfound;
sqltext:CHR(10);
sqltext:substr(r_tb_list.table_name,r_tb_list.table_nameCHR(10);
sqltext:CHR(10);
sqltext:substr(r_tb_list.table_name,CHR(10);
wherecol:='';
OPEN r_pk(r_tb_list.table_name);
LOOP
FETCH r_pk INTO r_pk_list;
EXIT WHEN r_pk%notfound;
wherecol:r_pk_list.column_name;
--参数名称默认以小写'p'开头,可以修改该处的规则为想要的命名方式
sqltext:r_pk_list.column_namer_pk_list.data_typeCHR( LOOP;
CLOSE r_pk;
sqltext:CHR(CHR(CHR(r_tb_list.table_namesubstr(wherecol, CHR(CHR(substr(r_tb_list.table_name,CHR(CHR(10);
ELSE
sqltext:CHR(CHR(r_tb_list.table_nameCHR(CHR(substr(r_tb_list.table_name,CHR(CHR(10);
END IF;
END LOOP;
CLOSE r_tb;
END;
UPDATE_ALL_TABLES
--功能:执行修改的存储过程(根据主键列修改,注意标识符不能超过30个字符),返回大字段,需要保存到文件进行读取
--参数:DBUSER-数据库用户,TBNAME-模糊匹配的表名,如果为空或''则生成全库的过程,SQLTEXT-返回的存储过程大文本字段
--调用:
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
dba_tables WHERE owner=DBUSER
TBNAMEtable_name ;
r_col(TABLENAME dba_tab_columns WHERE owner=DBUSER
AND table_name=TABLENAME AND column_name NOT IN
(
SELECT col.column_name FROM user_constraints con, user_cons_columns col
col.table_name=TABLENAME
)
ORDER BY column_id;
dba_tab_columns table_name=TABLENAME AND column_name IN
(
SELECT col.column_name FROM user_constraints con, user_cons_columns col
col.table_name=TABLENAME
)
ORDER BY column_id;
r_tb_list dba_tables%rowtype;
r_col_list dba_tab_columns%rowtype;
r_pk_list dba_tab_columns%rowtype;
SETCOL VARCHAR2(4000):='';
WHERECOL VARCHAR2(4000):='';
BEGIN
OPEN r_tb;
LOOPr_tb INTO r_tb_list;
EXIT WHEN r_tb%notfound;
sqltext:CHR(10);
sqltext:substr(r_tb_list.table_name,r_tb_list.table_nameCHR(10);
sqltext:CHR(10);
sqltext:substr(r_tb_list.table_name,CHR(10);
SETCOL:='';
WHERECOL:='';
OPEN r_pk(r_tb_list.table_name);
LOOP
FETCH r_pk INTO r_pk_list;
EXIT WHEN r_pk%notfound;
sqltext:r_pk_list.column_namer_pk_list.data_typeCHR(10);
WHERECOL:r_pk_list.column_name;
END LOOP;
CLOSE r_pk;
OPEN r_col(r_tb_list.table_name);
LOOP
FETCH r_col INTO r_col_list;
EXIT WHEN r_col%notfound;
sqltext:r_col_list.column_namer_col_list.data_typeCHR(10);
SETCOL:r_col_list.column_name;
END LOOP;
CLOSE r_col;
SETCOL:= substr(SETCOL, 0, LENGTH(SETCOL)-1);
substr(WHERECOL, 0, LENGTH(WHERECOL)-5)) INTO WHERECOL FROM DUAL;
sqltext:CHR(CHR(CHR(r_tb_list.table_nameSETCOLCHR(CHR(substr(r_tb_list.table_name,CHR(CHR(10);
END LOOP;
CLOSE r_tb;
END;
DROP_ALL_OBJECTS
--功能:删除用户下所有的结构关系
--参数:DBUSER-数据库用户,TAG-结构类型,见下方说明
--调用:EXECUTE DROP_ALL_OBJECTS('OPENVONE',0)
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
TAG IN NUMBER
)
--操作均基于指定用户下进行,其他用户不做处理
--0.删除全部
--1.删除视图
--2.删除函数
--3.删除过程
--4.删除类型
--5.删除包
--6.删除触发器
--7.删除表(解除主键关系再删除)
--8.删除序列
--9.清空作业
--CUR_OBJECTS DBA_OBJECTS WHERE OWNER=DBUSER
OBJECT_TYPE ,,,,,,,)
,,,,,,,)
,,,,,,,,8);
DBA_JOBS TAG=9);
OBJ_LIST DBA_OBJECTS%ROWTYPE;
JOB_LIST DBA_JOBS%ROWTYPE;
SQLTEXT VARCHAR2(2000);
LOOP
FETCH CUR_OBJECTS INTO OBJ_LIST;
EXIT WHEN CUR_OBJECTS%NOTFOUND;
SQLTEXT:OBJ_LIST.OBJECT_TYPEOBJ_LIST.;
) THEN
SQLTEXT:;
END IF;
EXECUTE IMMEDIATE SQLTEXT;
END LOOP;
CLOSE CUR_OBJECTS;
CUR_JOBS;
--循环删除作业
LOOP
FETCH CUR_JOBS INTO JOB_LIST;
EXIT WHEN CUR_JOBS%NOTFOUND;
DBMS_JOB.REMOVE(JOB_LIST.JOB);
LOOP;
CLOSE CUR_JOBS;
(TAGTAGIMMEDIATE ;;
END DROP_ALL_OBJECTS;
DROP_SINGLE_OBJECT
--功能:基于指定用户下删除单个结构(表、视图、函数、类型、包、触发器、序列),但不能删除该过程本身
--参数:DBUSER-数据库用户,pOBJECTNAME-结构名
--调用:EXECUTE DROP_SINGLE_OBJECT('OPENVONE','TB_USERS')
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
pOBJECTNAME IN VARCHAR2
)
AS
VCOUNT NUMBER;
OBJ_TYPE DBA_OBJECTS.OBJECT_TYPE%TYPE;
SQLTEXT VARCHAR2(2000);
(,,,,,,,)
pOBJECTNAME; VCOUNTOBJECT_TYPE pOBJECTNAME;
SQLTEXT:OBJ_TYPEpOBJECTNAME;
) THEN
SQLTEXT:;
END IF;
EXECUTE IMMEDIATE SQLTEXT;
END IF;
COMMIT;
END;
GET_ALL_OBJECTS
--功能:查询用户下所有的结构并返回大字段文段,美国服务器,需要保存到文件进行读取
--参数:DBUSER-数据库用户,TAG-结构类型,见下方说明,TBNAME-模糊匹配的结构名,如果为空或''则生成全库的DDL语句,SQLTEXT-返回生成结构的DDL语句大文本字段
--调用:
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
TAG IN NUMBER,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
--操作均基于指定用户下进行,其他用户不做处理
--0.全部
--1.序列
--2.表(未解决键/索引对应关系以及创建先后顺序问题,需注意)
--3.索引
--4.触发器
--5.视图
--6.类型
--7.函数
--8.过程
--R_OBJ DBA_OBJECTS WHERE OWNER=DBUSER
TBNAMEOBJECT_TYPE ,,,,,,,,)
,,,,,,,,)
,,,,,,,,,9);
R_OBJ_LIST DBA_OBJECTS%ROWTYPE;
DDL_TEXT CLOB;
BEGIN
OPEN R_OBJ;
LOOP
FETCH R_OBJ INTO R_OBJ_LIST;
EXIT WHEN R_OBJ%NOTFOUND;
SELECT dbms_metadata.get_ddl(R_OBJ_LIST.OBJECT_TYPE,R_OBJ_LIST.OBJECT_NAME,R_OBJ_LIST.OWNER) INTO DDL_TEXT FROM DUAL;
SQLTEXT:=SQLTEXT||DDL_TEXT;
END LOOP;
CLOSE R_OBJ;
END;
CLOB_READ
--功能:读取指定用户下某一存储过程返回的大对象文本,并保存到指定目下下的文本文件中
--参数:DBUSER-数据库用户名,香港服务器,PROCNAME-该用户下的存储过程名,PARAS-该用户下的存储过程参数列表(以','分隔),DIR-指定目录,FILENAME-文件名,TITLE-文件标题说明,AUTHOR-文件作者说明
--调用:EXECUTE clob_read('OPENVONE','GET_ALL_OBJECTS','''OPENVONE'',0,''''','D:\backdb','添加所有结构脚本','执行添加用户添加所有结构脚本','郭君')
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2,
PROCNAME IN VARCHAR2,
PARAS IN VARCHAR2,
DIR IN VARCHAR2,
FILENAME IN VARCHAR2,
TITLE IN VARCHAR2,
AUTHOR IN VARCHAR2
)
--步骤:分三步走
--一:设置文件目录
--alter system set utl_file_dir='D:\backdb'scope=spfile;
--二:赋予用户操作文件的权限
--grant EXECUTE ON UTL_FILE TO openVone;
--三:调用示例:EXECUTE clob_read('OPENVONE','GET_ALL_OBJECTS','''OPENVONE'',0,''''','D:\backdb','添加所有结构脚本','执行添加用户添加所有结构脚本','郭君')
AS
l_file utl_file.file_type;
l_buffer VARCHAR2(4000);
l_amount BINARY_INTEGER:=2000;
l_pos INTEGER:=1;
l_clob CLOB;
l_clob_len INTEGER;
t_sql VARCHAR2(2000);
col_list VARCHAR2(300);
out_count NUMBER; --输出参数个数,有且只能为一个
in_count (procname ;
(procname ;
t_sql:PROCNAME;
FOR i IN 1..in_count LOOP
col_list:,; LOOP;
t_sql:;
IMMEDIATE t_sql USING OUT l_clob;
--此处为输出参数
l_clob_len:= dbms_lob.getlength(l_clob);
l_file:,,32767);
--文件目录/名称
utl_file.put_line(l_file,);
utl_file.put_line(l_file,TITLE);
utl_file.put_line(l_file,AUTHOR);
utl_file.put_line(l_file,to_char(SYSDATE,));
utl_file.put_line(l_file,);
WHILE l_pos<l_clob_len LOOP
dbms_lob.READ(l_clob,l_amount, l_pos, l_buffer);
utl_file.put(l_file,l_buffer);
l_pos:=l_pos+l_amount;
END LOOP;
utl_file.fclose(l_file);
END IF;
END CLOB_READ;
RECOMPILE_ALL_PROJECTS
--功能:编译失效的结构,一般用的很少,服务器空间,查看失效过程可通过SELECT * FROM DBA_OBJECTS WHERE STATUS='INVALID'实现
--参数:DBUSER-数据库用户名
--调用:
--作者:郭君
--日期:2013-02-10
(
DBUSER IN VARCHAR2
)
CUR_OBJECTS DBA_OBJECTS OBJECT_TYPE ,,,,,)
DECODE(OBJECT_TYPE,,,,,,,6);
OBJ_LIST DBA_OBJECTS%ROWTYPE;
SQLTEXT VARCHAR2(2000);
BEGIN
OPEN CUR_OBJECTS;
LOOP
FETCH CUR_OBJECTS INTO OBJ_LIST;
EXIT WHEN CUR_OBJECTS%NOTFOUND;
SQLTEXT:OBJ_LIST.OBJECT_TYPEOBJ_LIST.;
EXECUTE IMMEDIATE SQLTEXT;
END LOOP;
CLOSE CUR_OBJECTS;
END RECOMPILE_ALL_PROJECTS;
/