DBA管理脚本大全

-- 日期：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.ISNULLABLEB.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_posl_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; /