时间：2021-07-01 10:21:17 帮助过：2人阅读
一：基于sqlserver数据库段，连接mysql，oracle数据库采用数据库链接方式
GO
--功能：对比多数据库之间的结构关系异同，方便DBA统一管理
--调用:
--EXEC COMPARE_DBTODB ‘‘,‘OPENVONE‘,2,‘MYSQL1‘,‘OPENVONE‘,3
--EXEC COMPARE_DBTODB ‘ORACLE2‘,‘OPENVONE‘,3,‘‘,‘OPENVONE‘,2
--EXEC COMPARE_DBTODB ‘ORACLE2‘,‘OPENVONE‘,1,‘MYSQL1‘,‘OPENVONE‘,3
CREATE PROCEDURE [dbo].[COMPARE_DBTODB]
(
@SOURCELINK VARCHAR(50), --源链接服务器名称,为空则表示为本机,由于采用SQLSERVER中转，只有SQLSERVER才能在本机查询，其他均为链接查询
@SOURCEDB VARCHAR(50), --源数据库名/用户名
@SOURCETYPE VARCHAR(50), --源数据库类型 1-ORACLE, 2-MSSQL,3-MYSQL
@TARGETLINK VARCHAR(50), --目标链接服务器名称,为空则表示为本机
@TARGETDB VARCHAR(50), --目标数据库名/用户名
@TARGETTYPE INT --目标数据库类型 1-ORACLE, 2-MSSQL,3-MYSQL
)
AS
BEGIN
DECLARE
@ORACLETEXT VARCHAR(4000),
@MSSQLTEXT VARCHAR(4000),
@MYSQLTEXT VARCHAR(4000),
@SOURCETEXT VARCHAR(4000),
@TARGETTEXT VARCHAR(4000),
@SQLTEXT VARCHAR(4000)
--ORACLE结构查询语句
SET @ORACLETEXT=N‘SELECT A.TABLE_NAME AS TABLENAME,B.COLUMN_NAME AS COLUMNNAME,
CASE DATA_TYPE
WHEN ‘‘‘‘NUMBER‘‘‘‘ THEN
CASE NVL(TO_CHAR(B.DATA_SCALE),‘‘‘‘0‘‘‘‘)
WHEN ‘‘‘‘0‘‘‘‘ THEN ‘‘‘‘INT‘‘‘‘
ELSE ‘‘‘‘DECIMAL(‘‘‘‘||NVL(TO_CHAR(B.DATA_PRECISION),‘‘‘‘18‘‘‘‘)||‘‘‘‘,‘‘‘‘||B.DATA_SCALE||‘‘‘‘)‘‘‘‘
END
WHEN ‘‘‘‘VARCHAR2‘‘‘‘ THEN ‘‘‘‘VARCHAR(‘‘‘‘||B.CHAR_LENGTH||‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘DATE‘‘‘‘ THEN ‘‘‘‘DATETIME‘‘‘‘
WHEN ‘‘‘‘BLOB‘‘‘‘ THEN ‘‘‘‘VARBINARY(4000)‘‘‘‘
WHEN ‘‘‘‘LONG‘‘‘‘ THEN ‘‘‘‘VARCHAR(4000)‘‘‘‘
ELSE B.DATA_TYPE
END AS SQLTYPE,
CASE DATA_TYPE
WHEN ‘‘‘‘NUMBER‘‘‘‘ THEN REPLACE(B.DATA_TYPE||‘‘‘‘(‘‘‘‘||NVL(TO_CHAR(B.DATA_PRECISION),‘‘‘‘*‘‘‘‘)||‘‘‘‘,‘‘‘‘||NVL(TO_CHAR(B.DATA_SCALE),‘‘‘‘*‘‘‘‘)||‘‘‘‘)‘‘‘‘,‘‘‘‘(*,*)‘‘‘‘,‘‘‘‘‘‘‘‘)
WHEN ‘‘‘‘VARCHAR2‘‘‘‘ THEN B.DATA_TYPE||‘‘‘‘(‘‘‘‘||B.CHAR_LENGTH||‘‘‘‘ BYTE)‘‘‘‘
ELSE B.DATA_TYPE
END AS ALLTYPE,
CASE B.NULLABLE WHEN ‘‘‘‘Y‘‘‘‘ THEN ‘‘‘‘NULL‘‘‘‘ ELSE ‘‘‘‘NOT NULL‘‘‘‘ 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=‘‘‘‘‘‘@DBOWNER‘‘‘‘‘‘‘‘‘
--SQLSERVER结构查询语句
SET @MSSQLTEXT=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 ‘‘‘‘bigint‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘binary‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘bit‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘char‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘datetime‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘decimal‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘,‘‘‘‘+CONVERT(VARCHAR,B.SCALE)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘float‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘image‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘int‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘money‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘nchar‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘ntext‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘numeric‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘,‘‘‘‘+CONVERT(VARCHAR,B.SCALE)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘nvarchar‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘real‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘smalldatetime‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘smallint‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘smallmoney‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘sql_variant‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘sysname‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘text‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘timestamp‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘tinyint‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘uniqueidentifier‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
WHEN ‘‘‘‘varbinary‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘varchar‘‘‘‘ THEN ‘‘‘‘(‘‘‘‘+CONVERT(VARCHAR,B.PREC)+‘‘‘‘)‘‘‘‘
WHEN ‘‘‘‘xml‘‘‘‘ THEN ‘‘‘‘‘‘‘‘
ELSE ‘‘‘‘‘‘‘‘END,‘‘‘‘-1‘‘‘‘,‘‘‘‘MAX‘‘‘‘) AS SQLTYPE,
case b.ISNULLABLE
when 0 then ‘‘‘‘NOT NULL‘‘‘‘
ELSE ‘‘‘‘NULL‘‘‘‘ END AS ISNULLABLE,B.COLORDER AS COLUMN_ID
FROM ‘‘‘‘@DBOWNER‘‘‘‘.DBO.sysobjects a
JOIN ‘‘‘‘@DBOWNER‘‘‘‘.DBO.syscolumns b ON a.id=b.id
JOIN ‘‘‘‘@DBOWNER‘‘‘‘.DBO.systypes c ON (b.xtype=c.xtype AND B.XUSERTYPE=C.XUSERTYPE)
WHERE A.XTYPE=‘‘‘‘U‘‘‘‘) A‘
--MYSQL结构查询语句
SET @MYSQLTEXT=N‘SELECT A.TABLE_NAME AS TABLENAME,B.COLUMN_NAME AS COLUMNNAME,
CASE B.COLUMN_TYPE
WHEN ‘‘‘‘INT(11)‘‘‘‘ THEN ‘‘‘‘INT‘‘‘‘
WHEN ‘‘‘‘TIMESTAMP‘‘‘‘ THEN ‘‘‘‘DATETIME‘‘‘‘
WHEN ‘‘‘‘BLOB‘‘‘‘ THEN ‘‘‘‘VARBINARY(4000)‘‘‘‘
ELSE UPPER(B.COLUMN_TYPE) END AS SQLTYPE,
B.COLUMN_TYPE AS ALLTYPE,
CASE B.IS_NULLABLE
WHEN ‘‘‘‘YES‘‘‘‘ THEN ‘‘‘‘NULL‘‘‘‘
WHEN ‘‘‘‘NO‘‘‘‘ THEN ‘‘‘‘NOT NULL‘‘‘‘
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=‘‘‘‘‘‘@DBOWNER‘‘‘‘‘‘‘‘ AND A.TABLE_TYPE=‘‘‘‘BASE TABLE‘‘‘‘‘
SELECT @SOURCETEXT=REPLACE(CASE @SOURCETYPE WHEN 1 THEN @ORACLETEXT WHEN 2 THEN @MSSQLTEXT ELSE @MYSQLTEXT END,‘‘‘‘‘@DBOWNER‘‘‘‘‘,@SOURCEDB)
SELECT @TARGETTEXT=REPLACE(CASE @TARGETTYPE WHEN 1 THEN @ORACLETEXT WHEN 2 THEN @MSSQLTEXT ELSE @MYSQLTEXT END,‘‘‘‘‘@DBOWNER‘‘‘‘‘,@TARGETDB)
SELECT @SOURCETEXT= CASE ISNULL(@SOURCELINK,‘‘) WHEN ‘‘ THEN ‘(‘+REPLACE(@SOURCETEXT,‘‘‘‘‘‘,‘‘‘‘)+‘)‘ ELSE ‘OPENQUERY(‘+@SOURCELINK+‘,‘‘‘+@SOURCETEXT+‘‘‘)‘ END
SELECT @TARGETTEXT= CASE ISNULL(@TARGETLINK,‘‘) WHEN ‘‘ THEN ‘(‘+REPLACE(@TARGETTEXT,‘‘‘‘‘‘,‘‘‘‘)+‘)‘ ELSE ‘OPENQUERY(‘+@TARGETLINK+‘,‘‘‘+@TARGETTEXT+‘‘‘)‘ END
----由于远程多表连接查询的开销比较大，因此将查询的结果均放入临时表中
----SET @SQLTEXT=N‘SELECT * FROM ‘+@SOURCETEXT+‘A ‘+CHAR(10)+‘JOIN(SELECT * FROM ‘+@TARGETTEXT+‘) B ‘+CHAR(10)+‘ON (A.TABLENAME=B.TABLENAME AND A.COLUMNNAME=B.COLUMNNAME)‘
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype=‘U‘ AND name=‘##TEMPSOURCE‘)
DROP TABLE ##TEMPSOURCE
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype=‘U‘ AND name=‘##TEMPTARGET‘)
DROP TABLE ##TEMPTARGET
EXEC (‘SELECT * INTO ##TEMPSOURCE FROM ‘+@SOURCETEXT+‘ A‘)
EXEC (‘SELECT * INTO ##TEMPTARGET FROM ‘+@TARGETTEXT+‘ A‘)
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
END
--------------------------------------------------
二：以下为ORACLE数据库断自动生成存储过程和获取数据库DDL语句的过程，最终通过调用CLOB_READ保存入文件中，目前暂未修正超过32767字节的写文本问题。超过32767字节文本会被自动截断。
create or replace PROCEDURE ADD_ALL_TABLES
--功能:执行添加表记录的存储过程,返回大字段，需要保存到文件进行读取
--参数:DBUSER-数据库用户，TBNAME-模糊匹配的表名，如果为空或‘‘则生成全库的过程，SQLTEXT-返回的存储过程大文本字段
--调用:
(
DBUSER IN VARCHAR2,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
AS
CURSOR R_TB IS --参数传递
SELECT * FROM DBA_TABLES WHERE OWNER=DBUSER
AND table_name LIKE ‘%‘||TBNAME||‘%‘ --模糊匹配表名。可生成相同部分结构表的过程
AND table_name NOT LIKE ‘%$%‘; --去掉回收站被删除的表
CURSOR R_COL(TABLENAME VARCHAR2) IS
SELECT * FROM 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;
LOOP--外循环
FETCH r_tb INTO r_tb_list;
EXIT WHEN r_tb%notfound;
sqltext:=sqltext||‘--------------------------------------------------------‘||CHR(10);
sqltext:=sqltext||‘-- DDL for Procedure ADD_‘||substr(r_tb_list.table_name,0,25)||‘(添加‘||r_tb_list.table_name||‘表记录)‘||CHR(10);
sqltext:=sqltext||‘--------------------------------------------------------‘||CHR(10);
sqltext:=sqltext||‘CREATE OR REPLACE PROCEDURE ADD_‘||substr(r_tb_list.table_name,0,25)||CHR(10)||‘(‘||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:=sqltext||‘ p‘||r_col_list.column_name||‘ IN ‘||r_col_list.data_type||‘,‘||CHR(10); --参数名称默认以小写‘p‘开头，可以修改该处的规则为想要的命名方式
ADDCOL1:=ADDCOL1||R_COL_LIST.COLUMN_NAME||‘,‘;
addcol2:=addcol2||‘p‘||r_col_list.column_name||‘,‘; --参数名称默认以小写‘p‘开头，可以修改该处的规则为想要的命名方式
END LOOP;
CLOSE r_col;
addcol1:= substr(addcol1, 0, LENGTH(addcol1)-1);
addcol2:= substr(addcol2, 0, LENGTH(addcol2)-1);
sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||CHR(10)||‘)‘||CHR(10)||‘AS‘||CHR(10)||‘BEGIN‘||CHR(10)||‘ INSERT INTO ‘||r_tb_list.table_name||‘(‘||addcol1||‘)‘||CHR(10)||‘ VALUES(‘||addcol2||‘);‘||CHR(10)||‘ COMMIT;‘||CHR(10)||‘END ADD_‘||substr(r_tb_list.table_name,0,25)||‘;‘||CHR(10)||‘/‘||CHR(10);
END LOOP;
CLOSE r_tb;
END;
/
create or replace PROCEDURE DEL_ALL_TABLES
--功能:执行删除的存储过程(根据主键列删除,注意标识符不能超过30个字符),返回大字段，需要保存到文件进行读取
--参数:DBUSER-数据库用户，TBNAME-模糊匹配的表名，如果为空或‘‘则生成全库的过程，SQLTEXT-返回的存储过程大文本字段
--调用:
(
DBUSER IN VARCHAR2,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
AS
CURSOR r_tb IS --参数传递
SELECT * FROM dba_tables WHERE owner=DBUSER
AND table_name LIKE ‘%‘||TBNAME||‘%‘ --模糊匹配表名。可生成相同部分结构表的过程
AND table_name NOT LIKE ‘%$%‘; --去掉回收站被删除的表
CURSOR r_pk(tablename VARCHAR2) IS
SELECT * FROM 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
WHERE con.constraint_name=col.constraint_name AND con.constraint_type=‘P‘ AND 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;
LOOP--外循环
FETCH r_tb INTO r_tb_list;
EXIT WHEN r_tb%notfound;
sqltext:=sqltext||‘--------------------------------------------------------‘||CHR(10);
sqltext:=sqltext||‘-- DDL for Procedure DEL_‘||substr(r_tb_list.table_name,0,25)||‘(删除‘||r_tb_list.table_name||‘表记录)‘||CHR(10);
sqltext:=sqltext||‘--------------------------------------------------------‘||CHR(10);
sqltext:=sqltext||‘CREATE OR REPLACE PROCEDURE DEL_‘||substr(r_tb_list.table_name,0,25)||CHR(10)||‘(‘||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:=wherecol||r_pk_list.column_name||‘=p‘||r_pk_list.column_name||‘ AND ‘; --参数名称默认以小写‘p‘开头，可以修改该处的规则为想要的命名方式
sqltext:=sqltext||‘ p‘||r_pk_list.column_name||‘ IN ‘||r_pk_list.data_type||‘,‘||CHR(10); --参数名称默认以小写‘p‘开头，可以修改该处的规则为想要的命名方式
END LOOP;
CLOSE r_pk;
IF LENGTH(wherecol)<>0 THEN
sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||CHR(10)||‘)‘||CHR(10)||‘AS‘||CHR(10)||‘BEGIN‘||CHR(10)||‘ DELETE ‘||r_tb_list.table_name||‘ WHERE ‘||substr(wherecol, 0, LENGTH(wherecol)-5)||‘;‘||CHR(10)||‘ COMMIT;‘||CHR(10)||‘END DEL_‘||substr(r_tb_list.table_name,0,25)||‘;‘||CHR(10)||‘/‘||CHR(10);
ELSE
sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||‘AS‘||CHR(10)||‘BEGIN‘||CHR(10)||‘ DELETE ‘||r_tb_list.table_name||‘;‘||CHR(10)||‘ COMMIT;‘||CHR(10)||‘END DEL_‘||substr(r_tb_list.table_name,0,25)||‘;‘||CHR(10)||‘/‘||CHR(10);
END IF;
END LOOP;
CLOSE r_tb;
END;
/
create or replace PROCEDURE UPDATE_ALL_TABLES
--功能:执行修改的存储过程(根据主键列修改,注意标识符不能超过30个字符),返回大字段，需要保存到文件进行读取
--参数:DBUSER-数据库用户，TBNAME-模糊匹配的表名，如果为空或‘‘则生成全库的过程，SQLTEXT-返回的存储过程大文本字段
--调用:
(
DBUSER IN VARCHAR2,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
AS
CURSOR r_tb IS --参数传递
SELECT * FROM dba_tables WHERE owner=DBUSER
AND table_name LIKE ‘%‘||TBNAME||‘%‘ --模糊匹配表名。可生成相同部分结构表的过程
AND table_name NOT LIKE ‘%$%‘; --去掉回收站被删除的表
CURSOR r_col(TABLENAME VARCHAR2) IS
SELECT * FROM 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
WHERE con.constraint_name=col.constraint_name AND con.constraint_type=‘P‘ AND col.table_name=TABLENAME
)
ORDER BY column_id;
CURSOR r_pk(TABLENAME VARCHAR2) IS
SELECT * FROM dba_tab_columns WHERE owner=DBUSER AND table_name NOT LIKE ‘%$%‘
AND table_name=TABLENAME AND column_name IN
(
SELECT col.column_name FROM user_constraints con, user_cons_columns col
WHERE con.constraint_name=col.constraint_name AND con.constraint_type=‘P‘ AND 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;
LOOP--外循环
FETCH r_tb INTO r_tb_list;
EXIT WHEN r_tb%notfound;
sqltext:=sqltext||‘--------------------------------------------------------‘||CHR(10);
sqltext:=sqltext||‘-- DDL for Procedure UPDATE_‘||substr(r_tb_list.table_name,0,25)||‘(修改‘||r_tb_list.table_name||‘表记录)‘||CHR(10);
sqltext:=sqltext||‘--------------------------------------------------------‘||CHR(10);
sqltext:=sqltext||‘CREATE OR REPLACE PROCEDURE UPDATE_‘||substr(r_tb_list.table_name,0,23)||CHR(10)||‘(‘||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:=sqltext||‘ p‘||r_pk_list.column_name||‘ IN ‘||r_pk_list.data_type||‘,‘||CHR(10);
WHERECOL:=WHERECOL||r_pk_list.column_name||‘=p‘||r_pk_list.column_name||‘ AND ‘;
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:=sqltext||‘ p‘||r_col_list.column_name||‘ IN ‘||r_col_list.data_type||‘,‘||CHR(10);
SETCOL:=SETCOL||r_col_list.column_name||‘=p‘||r_col_list.column_name||‘,‘;
END LOOP;
CLOSE r_col;
SETCOL:= substr(SETCOL, 0, LENGTH(SETCOL)-1);
SELECT DECODE(WHERECOL,‘‘,‘‘,‘ WHERE ‘||substr(WHERECOL, 0, LENGTH(WHERECOL)-5)) INTO WHERECOL FROM DUAL;
sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||CHR(10)||‘)‘||CHR(10)||‘AS‘||CHR(10)||‘BEGIN‘||CHR(10)||‘ UPDATE ‘||r_tb_list.table_name||‘ SET ‘||SETCOL||WHERECOL||‘;‘||CHR(10)||‘ COMMIT;‘||CHR(10)||‘END UPDATE_‘||substr(r_tb_list.table_name,0,23)||‘;‘||CHR(10)||‘/‘||CHR(10);
END LOOP;
CLOSE r_tb;
END;
/
create or replace PROCEDURE DROP_ALL_OBJECTS
--功能:删除用户下所有的结构关系
--参数:DBUSER-数据库用户，TAG-结构类型，见下方说明
--调用:EXECUTE DROP_ALL_OBJECTS(‘OPENVONE‘,0)
(
DBUSER IN VARCHAR2,
TAG IN NUMBER
)
--操作均基于指定用户下进行，其他用户不做处理
--0.删除全部
--1.删除视图
--2.删除函数
--3.删除过程
--4.删除类型
--5.删除包
--6.删除触发器
--7.删除表（解除主键关系再删除）
--8.删除序列
--9.清空作业
--10.清空回收站
AS
CURSOR CUR_OBJECTS IS --表、视图、函数、过程、类型、包、触发器、序列
SELECT * FROM DBA_OBJECTS WHERE OWNER=DBUSER
AND OBJECT_NAME<>‘DROP_ALL_OBJECTS‘ --不能删除自己
AND OBJECT_TYPE IN(‘VIEW‘,‘FUNCTION‘,‘PROCEDURE‘,‘TYPE‘,‘PACKAGE‘,‘TRIGGER‘,‘TABLE‘,‘SEQUENCE‘)
AND OBJECT_TYPE=DECODE(TAG,0,OBJECT_TYPE,1,‘VIEW‘,2,‘FUNCTION‘,3,‘PROCEDURE‘,4,‘TYPE‘,5,‘PACKAGE‘,6,‘TRIGGER‘,7,‘TABLE‘,8,‘SEQUENCE‘)
ORDER BY DECODE(OBJECT_TYPE,‘VIEW‘,1,‘FUNCTION‘,2,‘PROCEDURE‘,3,‘TYPE‘,4,‘PACKAGE‘,5,‘TRIGGER‘,6,‘TABLE‘,7,‘SEQUENCE‘,8);
CURSOR CUR_JOBS IS --作业
SELECT * FROM DBA_JOBS WHERE log_user=DBUSER AND schema_user=DBUSER AND (TAG=0 OR TAG=9);
OBJ_LIST DBA_OBJECTS%ROWTYPE;
JOB_LIST DBA_JOBS%ROWTYPE;
SQLTEXT VARCHAR2(2000);
BEGIN
OPEN CUR_OBJECTS; --循环删除结构关系
LOOP
FETCH CUR_OBJECTS INTO OBJ_LIST;
EXIT WHEN CUR_OBJECTS%NOTFOUND;
SQLTEXT:=‘DROP ‘||OBJ_LIST.OBJECT_TYPE||‘ "‘||OBJ_LIST.OBJECT_NAME||‘"‘;
IF (OBJ_LIST.OBJECT_TYPE=‘TABLE‘) THEN
SQLTEXT:=SQLTEXT||‘ CASCADE CONSTRAINTS‘;
END IF;
EXECUTE IMMEDIATE SQLTEXT;
END LOOP;
CLOSE CUR_OBJECTS;
-------------------------------------------
OPEN CUR_JOBS; --循环删除作业
LOOP
FETCH CUR_JOBS INTO JOB_LIST;
EXIT WHEN CUR_JOBS%NOTFOUND;
DBMS_JOB.REMOVE(JOB_LIST.JOB); --根据作业号删除作业
END LOOP;
CLOSE CUR_JOBS;
--------------------------------------------
IF (TAG=0 OR TAG=10) THEN
EXECUTE IMMEDIATE ‘PURGE USER_RECYCLEBIN‘;--清空回收站
END IF;
END DROP_ALL_OBJECTS;
/
create or replace PROCEDURE DROP_SINGLE_OBJECT
--功能:基于指定用户下删除单个结构(表、视图、函数、类型、包、触发器、序列),但不能删除该过程本身
--参数:DBUSER-数据库用户，pOBJECTNAME-结构名
--调用:EXECUTE DROP_SINGLE_OBJECT(‘OPENVONE‘,‘TB_USERS‘)
(
DBUSER IN VARCHAR2,
pOBJECTNAME IN VARCHAR2
)
AS
VCOUNT NUMBER;
OBJ_TYPE DBA_OBJECTS.OBJECT_TYPE%TYPE;
SQLTEXT VARCHAR2(2000);
BEGIN
SELECT COUNT(*) INTO VCOUNT FROM DBA_OBJECTS WHERE owner=DBUSER AND OBJECT_TYPE IN(‘TABLE‘,‘VIEW‘,‘FUNCTION‘,‘PROCEDURE‘,‘TYPE‘,‘PACKAGE‘,‘TRIGGER‘,‘SEQUENCE‘)
AND OBJECT_NAME<>‘DROP_SINGLE_OBJECT‘ AND OBJECT_NAME=pOBJECTNAME; --不能删除自己
IF VCOUNT=1 THEN
SELECT OBJECT_TYPE INTO OBJ_TYPE FROM DBA_OBJECTS WHERE owner=DBUSER AND OBJECT_NAME=pOBJECTNAME;
SQLTEXT:=‘DROP ‘||OBJ_TYPE||‘ "‘||pOBJECTNAME||‘"‘;
IF (OBJ_TYPE=‘TABLE‘) THEN
SQLTEXT:=SQLTEXT||‘ CASCADE CONSTRAINTS‘;
END IF;
EXECUTE IMMEDIATE SQLTEXT;
END IF;
COMMIT;
END;
/
create or replace PROCEDURE GET_ALL_OBJECTS
--功能:查询用户下所有的结构并返回大字段文段，需要保存到文件进行读取
--参数:DBUSER-数据库用户，TAG-结构类型，见下方说明，TBNAME-模糊匹配的结构名，如果为空或‘‘则生成全库的DDL语句，SQLTEXT-返回生成结构的DDL语句大文本字段
--调用:
(
DBUSER IN VARCHAR2,
TAG IN NUMBER,
TBNAME IN VARCHAR2,
SQLTEXT OUT CLOB
)
--操作均基于指定用户下进行，其他用户不做处理
--0.全部
--1.序列
--2.表（未解决键/索引对应关系以及创建先后顺序问题，需注意）
--3.索引
--4.触发器
--5.视图
--6.类型
--7.函数
--8.过程
--9.包
AS
CURSOR R_OBJ IS
SELECT * FROM DBA_OBJECTS WHERE OWNER=DBUSER
AND OBJECT_NAME LIKE ‘%‘||TBNAME||‘%‘ --模糊匹配结构名。可生成相同部分结构的DDL语句
AND OBJECT_TYPE IN(‘SEQUENCE‘,‘TABLE‘,‘INDEX‘,‘TRIGGER‘,‘VIEW‘,‘TYPE‘,‘FUNCTION‘,‘PROCEDURE‘,‘PACKAGE‘)
AND OBJECT_TYPE=DECODE(TAG,0,OBJECT_TYPE,1,‘SEQUENCE‘,2,‘TABLE‘,3,‘INDEX‘,4,‘TRIGGER‘,5,‘VIEW‘,6,‘TYPE‘,7,‘FUNCTION‘,8,‘PROCEDURE‘,9,‘PACKAGE‘)
ORDER BY DECODE(OBJECT_TYPE,‘SEQUENCE‘,1,‘TABLE‘,2,‘INDEX‘,3,‘TRIGGER‘,4,‘VIEW‘,5,‘TYPE‘,6,‘FUNCTION‘,7,‘PROCEDURE‘,8,‘PACKAGE‘,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;
/
create or replace PROCEDURE CLOB_READ
--功能:读取指定用户下某一存储过程返回的大对象文本，并保存到指定目下下的文本文件中
--参数:DBUSER-数据库用户名，PROCNAME-该用户下的存储过程名，PARAS-该用户下的存储过程参数列表(以‘,‘分隔)，DIR-指定目录，FILENAME-文件名，TITLE-文件标题说明，AUTHOR-文件作者说明
--调用:EXECUTE clob_read(‘OPENVONE‘,‘GET_ALL_OBJECTS‘,‘‘‘OPENVONE‘‘,0,‘‘‘‘‘,‘D:\backdb‘,‘添加所有结构脚本‘,‘执行添加用户添加所有结构脚本‘,‘郭君‘)
(
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 NUMBER; --输入参数个数，可以为0个或多个
BEGIN
SELECT COUNT(*) INTO out_count FROM DBA_ARGUMENTS WHERE OWNER=dbuser AND OBJECT_NAME=procname AND IN_OUT=‘OUT‘;
IF out_count=1 THEN
SELECT COUNT(*) INTO in_count FROM DBA_ARGUMENTS WHERE OWNER=dbuser AND OBJECT_NAME=procname AND IN_OUT=‘IN‘;
t_sql:=‘BEGIN ‘||PROCNAME||‘(‘;
FOR i IN 1..in_count LOOP
col_list:=col_list||REGEXP_SUBSTR(PARAS,‘[^,]+‘,1,i)||‘,‘; --构建调用过程参数列表，此处PARAS以‘,‘进行分割
END LOOP;
t_sql:=t_sql||col_list||‘:v1);END;‘; --构建调用过程输出参数，定义该过程只有一个输出参数且类型为clob类型
EXECUTE IMMEDIATE t_sql USING OUT l_clob; --此处为输出参数
l_clob_len:= dbms_lob.getlength(l_clob);
l_file:=utl_file.fopen(DIR,FILENAME||‘.txt‘,‘w‘,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,‘YYYY-MM-DD hh24:mi:ss‘));
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;
/
create or replace PROCEDURE RECOMPILE_ALL_PROJECTS
--功能:编译失效的结构，一般用的很少，查看失效过程可通过SELECT * FROM DBA_OBJECTS WHERE STATUS=‘INVALID‘实现
--参数:DBUSER-数据库用户名
--调用:
(
DBUSER IN VARCHAR2
)
--重新编译所有类型、视图、触发器、函数、过程、包，但不包括该过程本身
AS
CURSOR CUR_OBJECTS IS --类型、视图、触发器、函数、过程、包
SELECT * FROM DBA_OBJECTS WHERE owner=DBUSER AND STATUS=‘INVALID‘ AND OBJECT_TYPE IN(‘TYPE‘,‘VIEW‘,‘FUNCTION‘,‘PROCEDURE‘,‘PACKAGE‘,‘TRIGGER‘)
AND OBJECT_NAME<>‘RECOMPILE_ALL_PROJECTS‘ --不能编译自己
ORDER BY DECODE(OBJECT_TYPE,‘TYPE‘,1,‘VIEW‘,2,‘FUNCTION‘,3,‘PROCEDURE‘,4,‘PACKAGE‘,5,‘TRIGGER‘,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:=‘ALTER ‘||OBJ_LIST.OBJECT_TYPE||‘ "‘||OBJ_LIST.OBJECT_NAME||‘" COMPILE‘;
EXECUTE IMMEDIATE SQLTEXT;
END LOOP;
CLOSE CUR_OBJECTS;
END RECOMPILE_ALL_PROJECTS;
/
DBA管理脚本大全
标签：