oracle 运维常用脚本（函数篇）
2017-09-30 本文已影响0人
猿人记
- 创建表空间
CREATE OR REPLACE PROCEDURE PRC_CREATE_TABLESPACE (v_name varchar2,
v_initSize NUMBER,
v_extendSize NUMBER,
v_path varchar2) authid current_user as
/*********************************
名称：prc_create_tablespace
功能描述：创建表空间
**********************************/
v_flag number(10, 0);
v_sqlfalg varchar2(200);
v_sql varchar2(1000);
vErrInfo varchar2(500);
begin
v_flag := 0;
v_sqlfalg := 'select count(*) from dba_data_files where tablespace_name=''' ||
v_name || '''';
execute immediate v_sqlfalg
into v_flag;
if v_flag = 0 then
begin
v_sql:=' create tablespace '||v_name||
' datafile '''||v_path||v_name||'.dbf'' ' ||
'size '||v_initSize||'m ' || 'autoextend on ' ||
'next '||v_extendSize||'m maxsize unlimited ' ||
'extent management local';
execute immediate v_sql;
end;
end if;
EXCEPTION
WHEN OTHERS THEN
BEGIN
vErrInfo := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line(TO_CHAR(vErrInfo));
END;
end;
- 表空间容量视图
CREATE OR REPLACE VIEW V_TBS_FREE AS
SELECT
/*********************************
名称：V_TBS_FREE
功能描述：查看表空间剩余量
需要的权限，如
GRANT SELECT ON DBA_FREE_SPACE TO xxx;
GRANT SELECT ON DBA_DATA_FILES TO xxx;
GRANT SELECT ON DBA_TABLESPACES TO xxx;
-- **********************************/
A.TABLESPACE_NAME,
A.TOTAL_SPACE_MB ALLOCATED_SPACE_MB, --已分配总共
ROUND(B.FREE_SPACE_MB, 2) FREE_SPACE_MB, --空余的
(A.MAX_SPACE - A.TOTAL_SPACE_MB) FREE_ALLOCATE_MB, --剩余的
ROUND(A.MAX_SPACE, 2) MAX_SPACE_MB,
ROUND((A.TOTAL_SPACE_MB - B.FREE_SPACE_MB) / A.TOTAL_SPACE_MB * 100,
2) PCT_USAGE,
ROUND(A.TOTAL_SPACE_MB / A.MAX_SPACE * 100, 2) PCT_ALLOCATED
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 TOTAL_SPACE_MB,
DECODE(SUM(MAXBYTES / 1024 / 1024),
0,
SUM(BYTES) / 1024 / 1024,
SUM(CASE
WHEN AUTOEXTENSIBLE = 'YES' THEN
MAXBYTES
ELSE
BYTES
END) / 1024 / 1024) MAX_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM((BYTES) / 1024 / 1024) FREE_SPACE_MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
WITH READ ONLY;
- 自动追加数据文件（表空间自动维护集群版）
CREATE OR REPLACE PROCEDURE PRC_TBS_MONITOR AUTHID CURRENT_USER AS
/*********************************
名称：PRC_TBS_MONITOR
功能描述：自动管理-表空间文件，当空间不足时自动追加数据文件
需要的权限，如
GRANT SELECT ON V_$DATABASE TO xxx;
GRANT SELECT ON V_$ASM_DISKGROUP TO xxx;
GRANT ALTER TABLESPACE TO xxx;
**********************************/
V_TBS_FREE_GB NUMBER :=30; --剩余表空间阀值,默认30GB，需要在此设置########
V_ASM_FREE_GB NUMBER :=200; --磁盘组空余的阈值，默认为200G，需要在此设置########
V_ASM_NAME VARCHAR2(50) := NULL; --受监控的磁盘组，需要在此设置########
V_MESSAGE VARCHAR(250);
V_DB_NAME VARCHAR2(50); --当前数据库名，自动获取
V_NAME VARCHAR2(200); --数据文件名称
CN INTEGER;
ERROR_MSG VARCHAR2(500);
V_SQL VARCHAR2(1000);
BEGIN
--获取数据库名
SELECT NAME INTO V_DB_NAME FROM V$DATABASE;
--磁盘组预警，优先选用剩余空间最大的ASM组来添加数据文件
FOR V IN(
SELECT NAME,ROUND(FREE_MB/1024,0) AS FREE_GB FROM V$ASM_DISKGROUP WHERE VOTING_FILES = 'N' ORDER BY FREE_MB DESC)
LOOP
IF V_ASM_NAME IS NULL AND V.FREE_GB < V_ASM_FREE_GB THEN
V_MESSAGE := '磁盘组['|| V.NAME||']空间不足'||V_ASM_FREE_GB||'G';
--PRC_INSERT_CREATELOG('PRC_TBS_MONITOR',V_MESSAGE,'WARN');
END IF;
--优先选用剩余空间最大的ASM组
IF V_ASM_NAME IS NULL THEN
V_ASM_NAME := V.NAME;
END IF;
END LOOP;
FOR X IN (SELECT TABLESPACE_NAME,(FREE_SPACE_MB+FREE_ALLOCATE_MB) TOTAL_FREE_MB FROM V_TBS_FREE
WHERE TABLESPACE_NAME LIKE 'TS_%') LOOP
IF (ROUND(X.TOTAL_FREE_MB/1024,2)<= V_TBS_FREE_GB) THEN
BEGIN
--新增数据文件
V_SQL := 'alter tablespace '||X.TABLESPACE_NAME||' add datafile '''||'+'||V_ASM_NAME||''' size 100M autoextend on next 100M maxsize unlimited';
BEGIN
EXECUTE IMMEDIATE V_SQL;
--获取新增的数据文件名称
SELECT FILE_NAME INTO V_NAME FROM (SELECT FILE_NAME
FROM DBA_DATA_FILES F
WHERE F.TABLESPACE_NAME = X.TABLESPACE_NAME
ORDER BY F.FILE_ID DESC) A
WHERE ROWNUM = 1;
--发送提醒
V_MESSAGE := '成功追加数据文件，表空间['||X.TABLESPACE_NAME||']，数据文件['||V_NAME||']';
-- PRC_INSERT_CREATELOG('PRC_TBS_MONITOR',V_MESSAGE,'INFO');
EXCEPTION
WHEN OTHERS THEN
BEGIN
ERROR_MSG := SQLERRM;
V_MESSAGE := '失败追加数据文件，表空间['||X.TABLESPACE_NAME||']：';
--PRC_INSERT_CREATELOG('PRC_TBS_MONITOR',V_MESSAGE|| ERROR_MSG,'ERROR');
END;
END;
END;
END IF;
END LOOP;
END;
- httpPost 与外部交互（需开ACL网络权限，看本文第5点）
CREATE OR REPLACE PROCEDURE PRC_MSG_ADD(V_DATA VARCHAR2, --内容
V_KEY VARCHAR2 DEFAULT 'dataBase'
/*********************************
名称：PRC_MSG_ADD
功能描述：http调用外部接口Post模式
**********************************/) AS
V_URL VARCHAR2(100) := 'http://xxxxx:8080/api/msg/add'; --需要配置
REQ UTL_HTTP.REQ;
RESP UTL_HTTP.RESP;
L_CLOB CLOB;
L_BUF_RAW RAW(10000);
AMOUNT NUMBER := 9000;
VALUE VARCHAR2(1000);
L_PROCESS VARCHAR2(20);
L_REQ_BLOB BLOB;
L_BUF_LEN_STD NUMBER := 900;
L_BUF_LEN_CUR NUMBER;
L_BOD_LEN NUMBER;
ERROR_MSG VARCHAR2(500);
BEGIN
REQ := UTL_HTTP.BEGIN_REQUEST(V_URL, 'POST');
UTL_HTTP.SET_HEADER(REQ,
'Content-Type',
'application/x-www-form-urlencoded'); --POST
UTL_HTTP.SET_HEADER(REQ, 'Keep-Alive', 'timeout=1'); --超时
DBMS_LOB.CREATETEMPORARY(LOB_LOC => L_CLOB, CACHE => TRUE);
L_CLOB := 'data='||V_DATA||'&'||'&'||'key='||V_KEY; --POST的内容
DBMS_LOB.CREATETEMPORARY(LOB_LOC => L_REQ_BLOB, CACHE => TRUE);
L_REQ_BLOB := FUN_CLOB2BLOB(L_CLOB);
UTL_HTTP.SET_HEADER(REQ,
'Content-Length',
DBMS_LOB.GETLENGTH(L_REQ_BLOB));
UTL_HTTP.WRITE_RAW(REQ, L_REQ_BLOB);
--无需关注结果返回
/*
RESP := UTL_HTTP.GET_RESPONSE(REQ);
LOOP
UTL_HTTP.READ_LINE(RESP, VALUE, TRUE);
DBMS_OUTPUT.PUT_LINE(VALUE);
END LOOP;
UTL_HTTP.END_RESPONSE(RESP);
*/
UTL_HTTP.END_REQUEST(REQ);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
BEGIN
ERROR_MSG := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ERROR_MSG));
PRC_INSERT_CREATELOG('PRC_MSG_ADD', ERROR_MSG);
/*
IF RESP IS NOT NULL THEN
UTL_HTTP.END_RESPONSE(RESP);
END IF;
*/
UTL_HTTP.END_REQUEST(REQ);
EXCEPTION
WHEN OTHERS THEN
ERROR_MSG := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ERROR_MSG));
END;
WHEN OTHERS THEN
ERROR_MSG := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ERROR_MSG));
BEGIN
PRC_INSERT_CREATELOG('PRC_MSG_ADD', ERROR_MSG);
/*
IF RESP IS NOT NULL THEN
UTL_HTTP.END_RESPONSE(RESP);
END IF;
*/
UTL_HTTP.END_REQUEST(REQ);
EXCEPTION
WHEN OTHERS THEN
ERROR_MSG := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ERROR_MSG));
END;
END PRC_MSG_ADD;
- 配置ACL网络规则，允许访问外部主机
--1.创建访问控制列表（ACLemail_server_permissions），
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'web_api_acl.xml',
description => '将数据库信息同步到WEB接口',
principal => 'xxx', --此为将来要进行操作的用户
is_grant => TRUE,
privilege => 'connect');
END;
/
--2. 将此 ACL 与API服务器相关联，
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'web_api_acl.xml',
host => '*', --WEB服务器地址，这里写任意
lower_port => 8080,
upper_port => NULL);
COMMIT;
END;
/
