标签云
asm mount 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-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)
- 操作系统 (97)
- 数据库 (1,545)
- DB2 (22)
- MySQL (65)
- Oracle (1,420)
- Data Guard (46)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (157)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (12)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (46)
- Oracle 安全 (6)
- Oracle 开发 (26)
- Oracle 监听 (26)
- Oracle备份恢复 (500)
- Oracle安装升级 (81)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (74)
- 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-07445: exception encountered: core dump [kdxlin()+4088]处理
- bbed解决ORA-01578
- asm disk被加入到另外一个磁盘组故障恢复
- ORA-600 ksuloget2 恢复
- ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
- Patch SCN一键解决ORA-600 2662故障
- amdu参数详解
- ORA-600 kghstack_underflow_internal_2
- WRH$_LATCH, WRH$_SYSSTAT, WRH$_PARAMETER对象较大
- Oracle 19C 报ORA-704 ORA-01555故障处理
- Exadata磁盘损坏导致磁盘组无法mount恢复(oracle一体机磁盘组异常恢复)
- .faust加密勒索数据库恢复
- Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases-202307
- ORA-01122 ORA-01208 故障处理
- 硬件故障恢复出文件之后数据库故障处理
- dul支持arm版本Oracle数据库恢复
- ARM上的oracle数据库存储字节序分析—小字节序
- ARM Linux(麒麟操作系统)安装Oracle数据库
- win系统删除oracle数据文件恢复
- ORA-01122 ORA-01200故障处理
友情链接
分类目录归档:Oracle 开发
plsql 插入blob
plsql 插入blob
SQL> create bigfile tablespace t_xifenfei datafile 'e:/oradata/orcl/t_blob.dbf' size 128M autoextend on; 表空间已创建。 SQL> create table u0425.t_blob(a int, b varchar(10), c blob) tablespace t_xifenfei ; 表已创建。 SQL> create or replace directory expdp_dir as 'e:/'; 目录已创建。 SQL> declare 2 b_file bfile; 3 b_lob blob; 4 begin 5 insert into u0425.t_blob(a,b,c) values(1,'xifenfei', empty_blob()) return c into b_lob; 6 b_file:=bfilename('EXPDP_DIR','0430.DMP'); 7 dbms_lob.open(b_file,dbms_lob.file_readonly); 8 dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file)); 9 dbms_lob.close(b_file); 10 commit; 11 end; 12 / PL/SQL 过程已成功完成。 SQL> select * from u0425.t_blob where rownum<2; A B ---------- ---------- C -------------------------------------------------------------------------------- 1 xifenfei 0301914E3B98006780BC00019FC70146A1A3633DBC4B089DCF3DFCD951D559000000010000100003 6907E6041E0D3037000000020200000077D10000000000FA1A880022535953222E225359535F4558 SQL> select a,b,dbms_lob.getlength(c) from u0425.t_blob where rownum<2; A B DBMS_LOB.GETLENGTH(C) ---------- ---------- --------------------- 1 xifenfei 142024704
发表在 Oracle 开发
评论关闭
ORACEL坏查询对象批量脚本
查询坏块
SQL> set lines 120 SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 3 35418 1 0 FRACTURED 3 61344 1 0 FRACTURED 3 31065 1 0 CORRUPT 3 36673 1 0 CORRUPT 3 36721 1 0 CORRUPT 3 42881 1 0 CORRUPT 1 66738 1 0 CORRUPT 3 36329 1 0 CORRUPT 3 36617 1 0 CORRUPT 3 32404 1 0 FRACTURED 3 36281 1 0 FRACTURED FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 3 36625 1 0 FRACTURED 1 39041 1 0 CORRUPT 3 36713 1 0 CORRUPT 10 69927 1 0 FRACTURED 26 94244 1 0 CORRUPT 已选择16行。
查询坏块对应对象
SQL> set pagesize 2000 SQL> set linesize 250 SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# 2 , greatest(e.block_id, c.block#) corr_start_block# 3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# 4 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 5 - greatest(e.block_id, c.block#) + 1 blocks_corrupted 6 , null description 7 FROM dba_extents e, v$database_block_corruption c 8 WHERE e.file_id = c.file# 9 AND e.block_id <= c.block# + c.blocks - 1 10 AND e.block_id + e.blocks - 1 >= c.block# 11 UNION 12 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# 13 , header_block corr_start_block# 14 , header_block corr_end_block# 15 , 1 blocks_corrupted 16 , 'Segment Header' description 17 FROM dba_segments s, v$database_block_corruption c 18 WHERE s.header_file = c.file# 19 AND s.header_block between c.block# and c.block# + c.blocks - 1 20 UNION 21 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# 22 , greatest(f.block_id, c.block#) corr_start_block# 23 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# 24 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 25 - greatest(f.block_id, c.block#) + 1 blocks_corrupted 26 , 'Free Block' description 27 FROM dba_free_space f, v$database_block_corruption c 28 WHERE f.file_id = c.file# 29 AND f.block_id <= c.block# + c.blocks - 1 30 AND f.block_id + f.blocks - 1 >= c.block# 31 order by file#, corr_start_block#; OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION ---------------- ----------------- ----------------------- ------------------------------- ------ ----------------- --------------- ---------------- -------------- SYS TABLE OBJ$ 1 39041 39041 1 1 66738 66738 1 Free Block SYSMAN INDEX MGMT_METRICS_1HOUR_PK 3 31065 31065 1 SYS TABLE WRH$_SQL_BIND_METADATA 3 32404 32404 1 SYS TABLE WRH$_BG_EVENT_SUMMARY 3 35418 35418 1 SYS INDEX PARTITION WRH$_FILESTATXS_PK WRH$_FILEST_1232289473_41482 3 36281 36281 1 SYS TABLE PARTITION WRH$_SYSTEM_EVENT WRH$_SYSTEM_1232289473_41482 3 36329 36329 1 SYS TABLE PARTITION WRH$_SGASTAT WRH$_SGASTA_1232289473_41482 3 36617 36617 1 SYS INDEX PARTITION WRH$_SGASTAT_U WRH$_SGASTA_1232289473_41482 3 36625 36625 1 SYS INDEX PARTITION WRH$_PARAMETER_PK WRH$_PARAME_1232289473_41482 3 36673 36673 1 SYS TABLE PARTITION WRH$_SERVICE_STAT WRH$_SERVIC_1232289473_41482 3 36713 36713 1 SYS INDEX PARTITION WRH$_SERVICE_STAT_PK WRH$_SERVIC_1232289473_41482 3 36721 36721 1 SYS TABLE PARTITION WRH$_LATCH WRH$_LATCH_1232289473_41482 3 42881 42881 1 SYS TABLE WRI$_ADV_ACTIONS 3 61344 61344 1 EXAM TABLE EXAM_ITEMS_OLD 10 69927 69927 1 CPR TABLE NEED_MONITOR 26 94244 94244 1 已选择16行。 SQL> SQL> spool off
实现trigger集中记录所有库ddl操作
今天客户说了一个我感觉有意思的需求:在一个库上的一张表记录所有库的ddl操作,实现方式:在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决.在我们的一起努力下,解决了该触发器在dblink同义词的库上出错的问题.我这里测试使用的是10g的库做为存储所有库的ddl记录的库,11g库做为一个通过dblink插入ddl操作记录的库.
在10g数据库库中操作
1.创建记录ddl操作表
SQL> conn chf/xifenfei Connected. SQL> create table t_ddl_audit( 2 db_name varchar2(30), 3 login_user varchar2(30), 4 ddl_time date, 5 ip_address varchar2(20), 6 audsid varchar2(20), 7 schema_user varchar2(30), 8 schema_object varchar2(40), 9 login_tool varchar2(40), 10 os_user varchar2(40), 11 ddl_sql varchar2(4000)); Table created.
2.创建触发器
SQL> create or replace trigger tri_ddl_audit 2 before ddl on database 3 declare 4 n number; 5 str_stmt varchar2(4000); 6 sql_text ora_name_list_t; 7 l_trace number; 8 v_module varchar2(50); 9 v_action varchar2(50); 10 str_session v$session%rowtype; 11 begin 12 n := ora_sql_txt(sql_text); 13 for i in 1 .. n loop 14 str_stmt := substr(str_stmt || sql_text(i), 1, 3000); 15 end loop; 16 dbms_application_info.READ_MODULE(v_module, v_action); 17 INSERT INTO chf.t_ddl_audit 18 (db_name, 19 login_user, 20 ddl_time, 21 ip_address, 22 audsid, 23 schema_user, 24 schema_object, 25 login_tool, 26 os_user, 27 ddl_sql) 28 VALUES 29 (sys_context('USERENV', 'db_name'), 30 ora_login_user, 31 SYSDATE, 32 sys_context('USERENV', 'IP_ADDRESS'), 33 userenv('SESSIONID'), 34 ora_dict_obj_owner, 35 ora_dict_obj_name, 36 v_module, 37 sys_context('userenv', 'os_user'), 38 str_stmt); 39 exception 40 when no_data_found then 41 null; 42 end; 43 / Trigger created.
3.测试触发器
SQL> conn chf/xifenfei Connected. SQL> create table t_xff as select * from dba_tables where rownum=1; Table created. SQL> select db_name,login_user,ddl_sql from t_ddl_audit; DB_NAME LOGIN_USER ------------------------------ ------------------------------ DDL_SQL ----------------------------------------------------------------- XFF CHF create table t_xff as select * from dba_tables where rownum=1
在11g数据库中操作
1.创建dblink和同义词
SQL> create database link "ora10g_dblink" 2 connect to chf 3 identified by "xifenfei" 4 using 'ora10g'; Database link created. SQL> create synonym t_ddl_audit for t_ddl_audit@ora10g_dblink; Synonym created.
2.第一次创建触发器
SQL> create or replace trigger tri_ddl_audit 2 before ddl on database 3 declare 4 n number; 5 str_stmt varchar2(4000); 6 sql_text ora_name_list_t; 7 l_trace number; 8 v_module varchar2(50); 9 v_action varchar2(50); 10 str_session v$session%rowtype; 11 begin 12 n := ora_sql_txt(sql_text); 13 for i in 1 .. n loop 14 str_stmt := substr(str_stmt || sql_text(i), 1, 3000); 15 end loop; 16 dbms_application_info.READ_MODULE(v_module, v_action); 17 INSERT INTO t_ddl_audit 18 (db_name, 19 login_user, 20 ddl_time, 21 ip_address, 22 audsid, 23 schema_user, 24 schema_object, 25 login_tool, 26 os_user, 27 ddl_sql) 28 VALUES 29 (sys_context('USERENV', 'db_name'), 30 ora_login_user, 31 SYSDATE, 32 sys_context('USERENV', 'IP_ADDRESS'), 33 userenv('SESSIONID'), 34 ora_dict_obj_owner, 35 ora_dict_obj_name, 36 v_module, 37 sys_context('userenv', 'os_user'), 38 str_stmt); 39 exception 40 when no_data_found then 41 null; 42 end; 43 / Trigger created.
3.测试触发器
SQL> create table t_xff as select * from dba_objects where rownum<10; create table t_xff as select * from dba_objects where rownum<10 * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-02070: database does not support in this context ORA-06512: at line 15
出现ORA-02070错误,估计是类此sys_context(‘userenv’, ‘os_user’)导致。
4.第二次创建触发器
SQL> create or replace trigger tri_ddl_audit 2 before ddl on database 3 declare 4 n number; 5 str_stmt varchar2(4000); 6 sql_text ora_name_list_t; 7 l_trace number; 8 v_module varchar2(50); 9 v_action varchar2(50); 10 v_db_name varchar2(50); 11 v_ip_addr varchar2(50); 12 v_os varchar2(50); 13 v_session_id varchar2(50); 14 str_session v$session%rowtype; 15 begin 16 n := ora_sql_txt(sql_text); 17 for i in 1 .. n loop 18 str_stmt := substr(str_stmt || sql_text(i), 1, 3000); 19 end loop; 20 dbms_application_info.READ_MODULE(v_module, v_action); 21 v_db_name :=sys_context('USERENV', 'db_name'); 22 v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS'); 23 v_os:=sys_context('userenv', 'os_user'); 24 v_session_id:=userenv('SESSIONID'); 25 INSERT INTO t_ddl_audit 26 (db_name, 27 login_user, 28 ddl_time, 29 ip_address, 30 audsid, 31 schema_user, 32 schema_object, 33 login_tool, 34 os_user, 35 ddl_sql) 36 VALUES 37 (v_db_name, 38 ora_login_user, 39 SYSDATE, 40 v_ip_addr, 41 v_session_id, 42 ora_dict_obj_owner, 43 ora_dict_obj_name, 44 v_module, 45 v_os, 46 str_stmt); 47 exception 48 when no_data_found then 49 null; 50 end; 51 / Trigger created.
5.继续测试触发器
SQL> drop table t3; drop table t3 * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-02069: global_names parameter must be set to TRUE for this operation ORA-06512: at line 23
根据ORA-02069,查询资料发现是通过dblink插入数据使用了变量和常量的方式混合使用导致该错误,修改触发器全部使用变量方式
6.第三次创建触发器
SQL> create or replace trigger tri_ddl_audit 2 before ddl on database 3 declare 4 n number; 5 str_stmt varchar2(4000); 6 sql_text ora_name_list_t; 7 l_trace number; 8 v_module varchar2(50); 9 v_action varchar2(50); 10 v_db_name varchar2(50); 11 v_ip_addr varchar2(50); 12 v_os varchar2(50); 13 v_session_id varchar2(50); 14 v_loginuser varchar2(50); 15 v_obj_name varchar2(50); 16 v_owner varchar2(50); 17 str_session v$session%rowtype; 18 begin 19 n := ora_sql_txt(sql_text); 20 for i in 1 .. n loop 21 str_stmt := substr(str_stmt || sql_text(i), 1, 3000); 22 end loop; 23 dbms_application_info.READ_MODULE(v_module, v_action); 24 v_db_name :=sys_context('USERENV', 'db_name'); 25 v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS'); 26 v_os:=sys_context('userenv', 'os_user'); 27 v_session_id:=userenv('SESSIONID'); 28 v_loginuser:= ora_login_user; 29 v_owner:=ora_dict_obj_owner; 30 v_obj_name:=ora_dict_obj_name; 31 INSERT INTO t_ddl_audit 32 (db_name, 33 login_user, 34 ddl_time, 35 ip_address, 36 audsid, 37 schema_user, 38 schema_object, 39 login_tool, 40 os_user, 41 ddl_sql) 42 VALUES 43 (v_db_name, 44 v_loginuser, 45 SYSDATE, 46 v_ip_addr, 47 v_session_id, 48 v_owner, 49 v_obj_name, 50 v_module, 51 v_os, 52 str_stmt); 53 exception 54 when no_data_found then 55 null; 56 end; 57 / Trigger created.
7.测试触发器
SQL> create table t_xff11 as select * from dba_tables where rownum<10; Table created. SQL> select db_name,login_user,ddl_sql from t_ddl_audit; DB_NAME LOGIN_USER ------------------------------ ------------------------------ DDL_SQL ----------------------------------------------------------------- ora11g CHF create table t_xff11 as select * from dba_tables where rownum<10 XFF CHF create table t_xff as select * from dba_tables where rownum=1
补充说明
这个方案个人感觉是一个实验室中的方案,在实际的生成环境中很难应用上
1.trigger记录ddl操作本身效率不高
2.如果某个库不能访问存储ddl操作的表的数据库,将导致该数据库所有ddl操作hang住,从而可能使得该数据库hang住的风险.