标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,589)
- DB2 (22)
- MySQL (70)
- Oracle (1,459)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (526)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (13)
- PostgreSQL恢复 (3)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
- 近期又遇到ORA-600 16703和ORA-702故障
- RECOVER_YOUR_DATA勒索恢复
- ORA-01033: ORACLE initialization or shutdown in progress 故障处理
- Oracle 19c/21c最新patch信息-202401
- 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
- ORA-600 kcrf_resilver_log_1故障处理
月归档:十一月 2010
使用sqlplus生产批量plsql
要点
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-------------
发表在 Oracle
评论关闭
pl/sql 常用语法
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;
发表在 Oracle 开发
评论关闭
oracle 开机自动启动
一、使用root用户修改/etc/oratab 文件:
$ vi /etc/oratab
orcl:/u01/oracle:N (ORACLE_SID:ORACLE_HOME:N)
改为:
orcl:/u01/oracle:Y 也就是将最后的N改为Y
二、使用Oracle用户修改$ORACLE_HOME/bin/dbstart和dbstop文件:
ORACLE_HOME_LISTNER=$1
改为:
ORACLE_HOME_LISTNER=$ORACLE_HOME
三、创建服务
$su – root
# cd /etc/rc.d/init.d/
# vi oradb
在oradb中添加文本
#!/bin/bash # chkconfig: 345 99 10 # description: Startup Script for Oracle Databases export ORACLE_BASE=/u01 export ORACLE_HOME=/u01/oracle export ORACLE_SID=orcl export PATH=$PATH:$ORACLE_HOME/bin ORA_OWNR="oracle" # if the executables do not exist -- display error if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ] then echo "Oracle startup: cannot start" exit 1 fi # depending on parameter -- startup, shutdown, restart # of the instance and listener or usage display case "$1" in start) # Oracle listener and instance startup and oem echo -n "Starting Oracle: " # 启动listener and instance su - $ORA_OWNR -c "$ORACLE_HOME/bin/dbstart" touch /var/lock/Oracle # 启动oem su - $ORA_OWNR -c "$ORACLE_HOME/bin/emctl start dbconsole" echo "OK" ;; stop) # Oracle listener and instance shutdown and oem echo -n "Shutdown Oracle: " # 关闭oem su - $ORA_OWNR -c "$ORACLE_HOME/bin/emctl stop dbconsole" # 关闭数据库和listener su - $ORA_OWNR -c "$ORACLE_HOME/bin/dbshut" rm -f /var/lock/Oracle echo "OK" ;; reload|restart) $0 stop $0 start ;; *) echo "Usage: `basename $0` start|stop|restart|reload" exit 1 esac exit 0
修改所属组和用户
#chown oracle.oinstall /etc/rc.d/init.d/oradb
修改访问权限
#chmod 775 /etc/rc.d/init.d/oradb
添加服务到自动启动中
#chkconfig –add oradb
查看数据库服务开机启动状态
#chkconfig –list oradb
四、# chkconfig: 345 99 10说明
指出3,4,5级别启动这个服务,99是在相应的/etc/rc.d/rcN.d(N为前面指定的级别,这里是345)目录下生成的链接文件的序号(启动优先级别)S99oradbstart,10为在除前面指出的级别对应的/etc/rc.d/rcN.d(N为除345之外的级别)目录生成的链接文件的序号(服务停止的优先级别)