找出数据库中所有表表中REMARK列中含有WN、wind、wlr中表名和数量
declare
i number:=0;
begin
for c1 in
(select table_name from user_tab_columns where column_name='REMARK')
loop
EXECUTE IMMEDIATE 'select count(*) from '|| c1.table_name
||' where UPPER(REMARK) LIKE ''%WN%'' or UPPER(remark) like ''%WIND%'' or UPPER(remark) like ''%WLR%'''
into i;
if i>0 then
dbms_output.put_line(c1.table_name||'------'||i);
end if;
end loop;
end;
1、创建允许登录用户表
CREATE TABLE "CHF"."LOG$LOGIN_OS"
(
"OS_USER" VARCHAR2(60 BYTE)
)
2、创建触发器实现限制用户登录
create or replace
TRIGGER TR_LOGIN_RECORD_TEST
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
SELECT * FROM v$session where USERNAME is not null
and nvl(osuser,'x') <> 'SYSTEM' and type <> 'BACKGROUND' and audsid = iiQuerySid;
USER_NUM NUMBER(5);
V_SQL VARCHAR2(100);
BEGIN
OPEN cSession(userenv('SESSIONID'));
FETCH cSession INTO mtSession;
IF cSession%FOUND THEN
select count(*) into USER_NUM FROM CHF.LOG$LOGIN_OS WHERE OS_USER=mtSession.Osuser;
IF USER_NUM!=0 THEN
V_SQL:=' alter system kill session '||''''||mtSession.Sid||','||mtSession.Serial#||'''';
EXECUTE IMMEDIATE V_SQL;
END IF;
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
-- dbms_output.put_line('登记登录信息错误：'||SQLERRM);
RAISE;
END;
注：使用sysdba帐号创建触发器，因为在oracle中user不能kill掉自己的session，如果是用sysdba那么就可以kill掉其他的任何非自身的session
1、先需要建立一张表，用于存放登陆信息
create table LOG$INFORMATION ( USERNAME VARCHAR2(30), TERMINAL VARCHAR2(50), IPADRESS VARCHAR2(20), OSUSER VARCHAR2(30), MACHINE VARCHAR2(64), PROGRAM VARCHAR2(64), SID NUMBER, SERIAL# NUMBER, AUSID NUMBER, LOGINTIME DATE default sysdate, LOGout_TIME date )
2、创建触发器，记载登录信息
CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD
AFTER logon ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
SELECT * FROM v$session
WHERE nvl(osuser,'x') <> 'SYSTEM' and type <> 'BACKGROUND' and audsid = iiQuerySid;
BEGIN
OPEN cSession(userenv('SESSIONID'));
FETCH cSession INTO mtSession;
IF cSession%FOUND THEN
INSERT INTO log$information(username,logintime,terminal,ipadress,osuser,machine,
program,sid,serial#,ausid)
VALUES(USER,SYSDATE,mtSession.Terminal,
SYS_CONTEXT ('USERENV','IP_ADDRESS'),mtSession.Osuser,
mtSession.Machine,mtSession.Program,mtSession.Sid,mtSession.Serial#,userenv('SESSIONID'));
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
3、用户登出触发器，记录登出时间
create or replace trigger TR_LOGOFF_RECORD
before LOGOFF ON DATABASE
DECLARE
mtSession v$session%ROWTYPE;
CURSOR cSession(iiQuerySid IN NUMBER) IS
SELECT * FROM v$session where
nvl(osuser,'x') <> 'SYSTEM' and type <> 'BACKGROUND' and audsid = iiQuerySid;
BEGIN
OPEN cSession(userenv('SESSIONID'));
FETCH cSession INTO mtSession;
IF cSession%FOUND THEN
UPDATE LOG$INFORMATION SET LOGOUT_TIME=SYSDATE WHERE sid=mtSession.Sid AND serial#=mtSession.Serial#;
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
要点
chr(10)–表示换行
set heading off–不显示表格头部
set pagesize 0–不分页
set linesize 800–行宽
spool filename–写入到文件中
sql语句
select 'declare'||chr(10)||' i integer:=0;'||chr(10)||'cursor cur is select rowid as rid from '||t1 ||' WHERE DATASOURCE=''WN'' OR MODIFYSOURCE=''WN'';'||chr(10)||'begin for c in cur loop '||chr(10)||'i:=i+1;'||chr(10)|| 'update '||t1||chr(10)||'SET DATASOURCE=F_BYF_UPDATE_WN(datasource),MODIFYSOURCE=F_BYF_UPDATE_WN(MODIFYSOURCE),REMARK=F_BYF_UPDATE_WN(REMARK) WHERE rowid=c.rid;'||chr(10)|| ' if mod(i,200)=0 then'||chr(10)|| ' commit;'||chr(10)||'end if;'||chr(10)||' if mod(i,20000)=0 then'||chr(10)||' dbms_lock.sleep(40);'||chr(10)||' if i>30000 then'||chr(10)|| ' dbms_lock.sleep(40);'||chr(10)||' end if;'||chr(10)||' end if;'||chr(10)|| ' if i>500000 then'||chr(10)||' exit;'||chr(10)||' end if;'||chr(10)||' end loop;'||chr(10)||' commit;'||chr(10)||'end;'||chr(10)||'/'||chr(10)||'--------'||t1||'-------------' from t1;
效果
declare i integer:=0; cursor cur is select rowid as rid from FUND294 WHERE DATASOURCE='WN' OR MODIFYSOURCE='WN'; begin for c in cur loop i:=i+1; update FUND294 SET DATASOURCE=F_BYF_UPDATE_WN(datasource),MODIFYSOURCE=F_BYF_UPDATE_WN(MODIFYSOURCE),REMARK=F_BYF_UPDATE_WN(REMARK) WHERE rowid=c.rid; if mod(i,200)=0 then commit; end if; if mod(i,20000)=0 then dbms_lock.sleep(40); if i>30000 then dbms_lock.sleep(40); end if; end if; if i>500000 then exit; end if; end loop; commit; end; / --------FUND294-------------
1、procedure
CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
--举例
create or replace
procedure dos_fx
is
cursor c1 is
select get from dos_gj;
begin
for c2 in c1 loop
insert into dos_gj_1(ip,gettime,get)values(REGEXP_SUBSTR( c2.get, '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3} '),
REGEXP_SUBSTR( c2.get, '^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})'),c2.get);
end loop;
commit;
end;
2、function
CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name]
--举例
CREATE OR REPLACE function fn_md5(input_string VARCHAR2) return varchar2
IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw);
return rawtohex(decrypted_raw);
END;
3、package
--header
CREATE [OR REPLACE] PACKAGE package_name
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--body
CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--举例
--header
create or replace
package pk_t1
as
procedure get_num(getnum in number,aname varchar2);
end pk_t1;
--body
create or replace
package body pk_t1
as
procedure get_num(getnum in number,aname varchar2)
is
begin
insert into shell_1 values(getnum,aname);
end;
end pk_t1;
4、trigger
CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
--举例
create or replace trigger add_shell
before update
on shell_1 for each row
declare
begin
if :OLD.name!=:new.name then
:new.name:='ggggg';
end if;
end;
 |
|