恢复DELETE数据的PACKAGE介绍篇目的在“不小心”对表执行了delete操作并“不小心”提交之后如果没有闪回、备份等常规恢复手段那就傻眼了。我在学习bbed的时候有学到通过修改行标记来恢复delete的数据的手段但这种方法存在风险且较为复杂于是思考是否有更简单更安全的方法。至此这个PACKAGE诞生了虽然还很稚嫩。目前该版本仅支持恢复NUMBER, VARCHAR2, CHAR, DATE, TIMESTAMP, RAW这六种数据类型。其他数据类型将被恢复为null如CLOB、BLOB、XMLTYPE、INTERVAL 等。需注意部分不支持的数据类型会导致数据读取错误目前已知会导致错误的有LONG、LONG RAW。适用环境目前已知适用于Oracle11g和Oracle19c期间版本和更高版理论是支持的但未经过测试。仅支持恢复普通表暂不支持如分区表、索引组织表等类型的表。支持CDB/PDB环境暂不支持RAC不支持共享存储前提条件源表没有被drop数据块未被重用或覆盖原理简介Oracle的数据存储于数据文件当中而delete一个表后被delete的数据不会立刻消失在被复用之前仍会存储在数据文件上。此时只需要找到目标表数据存储的位置并将底层数据读取出来并进行对应转换就能得到真实的数据再将这些数据插入到一张结构一致的新表上数据就成功恢复了。使用演示创建PACKAGESQLaltersessionsetcontainerpdb1;Sessionaltered.SQLdel_recover_basic.sqlInstalling DEL_RECOVER_BASIC package...Package created.Package body created.Package DEL_RECOVER_BASIC installedend.Todisplayusage,execute:EXECDEL_RECOVER_BASIC.usageDisconnectedfromOracleDatabase19c Enterprise EditionRelease19.0.0.0.0-Production Version19.28.0.0.0准备测试表DROPTABLEtest.basic_types_testPURGE;CREATETABLEtest.basic_types_test(id NUMBER(10)PRIMARYKEY,num_col NUMBER(12,2),vc_col VARCHAR2(200),char_colCHAR(10),date_colDATE,ts_colTIMESTAMP(6),raw_col RAW(200));INSERTINTOtest.basic_types_testVALUES(1,12345.67,Hello, World!,FIXED,TO_DATE(2024-01-15 10:30:00,YYYY-MM-DD HH24:MI:SS),TIMESTAMP2024-01-15 10:30:00.123456,HEXTORAW(1A2B3C4D));INSERTINTOtest.basic_types_testVALUES(2,-9876.54,RPAD(Long string test ,190,X),ABC ,TO_DATE(1999-12-31 23:59:59,YYYY-MM-DD HH24:MI:SS),TIMESTAMP1999-12-31 23:59:59.999999,HEXTORAW(FFEEDDCCBBAA));INSERTINTOtest.basic_types_testVALUES(3,0,,,NULL,NULL,NULL);INSERTINTOtest.basic_types_testVALUES(4,NULL,NULL,NULL,SYSDATE,SYSTIMESTAMP,HEXTORAW(RPAD(FF,200,FF)));INSERTINTOtest.basic_types_testVALUES(5,0.01,A,1234567890,TO_DATE(2020-02-29 12:00:00,YYYY-MM-DD HH24:MI:SS),TIMESTAMP2020-02-29 12:00:00.000001,HEXTORAW(01));COMMIT;setlinesize999setpagesize999COLUMNvc_col FORMAT a30COLUMNchar_col FORMAT a10COLUMNdate_col FORMAT a19COLUMNts_col FORMAT a19COLUMNraw_hex FORMAT a30SQLSELECTid,num_col,vc_col,char_col,TO_CHAR(date_col,YYYY-MM-DD HH24:MI:SS)ASdate_col,TO_CHAR(ts_col,YYYY-MM-DD HH24:MI:SS)ASts_col,RAWTOHEX(raw_col)ASraw_hexFROMtest.basic_types_testORDERBYid;ID NUM_COL VC_COL CHAR_COL DATE_STR TS_STR RAW_HEX------ --------- ------------------------------ ---------- ------------------- -------------------------- ------------------------------112345.67Hello,World!FIXED2024-01-1510:30:002024-01-1510:30:00.1234561A2B3C4D2-9876.54Long string test XXXXXXXXXXXXX ABC1999-12-3123:59:591999-12-3123:59:59.999999FFEEDDCCBBAA XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX3.0042026-06-0317:44:062026-06-0317:44:06.359346FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFF5.01A12345678902020-02-2912:00:002020-02-2912:00:00.00000101模拟误删除SQLdeletefromtest.basic_types_test;5rowsdeleted.SQLcommit;Commitcomplete.SQLaltersystemcheckpoint;System altered.SQLselect*fromtest.basic_types_test;norowsselected执行恢复在执行package之前需要有一个指向“要恢复的表所在的数据文件所在的路径”的目录如没有执行时会报错并提供推荐的创建语句创建后重新执行即可。更多详细的用法请见下一文章操作手册篇。SQLSETSERVEROUTPUTONSQLEXECDEL_RECOVER_BASIC.recover_deleted_table(TEST,BASIC_TYPES_TEST,TEST);17:57:06: Sourcetable: TEST.BASIC_TYPES_TEST,DATA_OBJ#75104, TSTESTDATAFILE17:57:06: Creating recoverytable: TEST.R_BASIC_TYPES_TEST17:57:06: Recoverytablecreated successfully: TEST.R_BASIC_TYPES_TEST17:57:06: Processing datafile:/u01/app/oracle/oradata/ORCL/4A74127AED179CCAE0632400A8C0B3DF/datafile/o1_mf_tes tdata_nvhbjhnd_.dbfusingdirectory BBED_DATA_DIR17:57:06: Recovery completed.Totalrowsrecovered:517:57:06:Datastoredintable: TEST.R_BASIC_TYPES_TEST PL/SQLproceduresuccessfully completed.验证恢复结果SQLSELECTid,num_col,vc_col,char_col,TO_CHAR(date_col,YYYY-MM-DD HH24:MI:SS)ASdate_col,TO_CHAR(ts_col,YYYY-MM-DD HH24:MI:SS)ASts_col,RAWTOHEX(raw_col)ASraw_hexFROMtest.r_basic_types_testORDERBYid;ID NUM_COL VC_COL CHAR_COL DATE_COL TS_COL RAW_HEX---------- ---------- ------------------------------ ---------- ------------------- ------------------- ------------------------------112345.67Hello,World!FIXED2024-01-1510:30:002024-01-1510:30:001A2B3C4D2-9876.54Long string test XXXXXXXXXXXXX ABC1999-12-3123:59:591999-12-3123:59:59FFEEDDCCBBAA XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX3042026-06-0317:44:062026-06-0317:44:06FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFF5.01A12345678902020-02-2912:00:002020-02-2912:00:0001package下载链接https://download.csdn.net/download/sp60cn/92942843
Oracle恢复DELETE数据的PACKAGE（介绍篇）（仅做研究使用）
发布时间：2026/6/4 18:35:24
恢复DELETE数据的PACKAGE介绍篇目的在“不小心”对表执行了delete操作并“不小心”提交之后如果没有闪回、备份等常规恢复手段那就傻眼了。我在学习bbed的时候有学到通过修改行标记来恢复delete的数据的手段但这种方法存在风险且较为复杂于是思考是否有更简单更安全的方法。至此这个PACKAGE诞生了虽然还很稚嫩。目前该版本仅支持恢复NUMBER, VARCHAR2, CHAR, DATE, TIMESTAMP, RAW这六种数据类型。其他数据类型将被恢复为null如CLOB、BLOB、XMLTYPE、INTERVAL 等。需注意部分不支持的数据类型会导致数据读取错误目前已知会导致错误的有LONG、LONG RAW。适用环境目前已知适用于Oracle11g和Oracle19c期间版本和更高版理论是支持的但未经过测试。仅支持恢复普通表暂不支持如分区表、索引组织表等类型的表。支持CDB/PDB环境暂不支持RAC不支持共享存储前提条件源表没有被drop数据块未被重用或覆盖原理简介Oracle的数据存储于数据文件当中而delete一个表后被delete的数据不会立刻消失在被复用之前仍会存储在数据文件上。此时只需要找到目标表数据存储的位置并将底层数据读取出来并进行对应转换就能得到真实的数据再将这些数据插入到一张结构一致的新表上数据就成功恢复了。使用演示创建PACKAGESQLaltersessionsetcontainerpdb1;Sessionaltered.SQLdel_recover_basic.sqlInstalling DEL_RECOVER_BASIC package...Package created.Package body created.Package DEL_RECOVER_BASIC installedend.Todisplayusage,execute:EXECDEL_RECOVER_BASIC.usageDisconnectedfromOracleDatabase19c Enterprise EditionRelease19.0.0.0.0-Production Version19.28.0.0.0准备测试表DROPTABLEtest.basic_types_testPURGE;CREATETABLEtest.basic_types_test(id NUMBER(10)PRIMARYKEY,num_col NUMBER(12,2),vc_col VARCHAR2(200),char_colCHAR(10),date_colDATE,ts_colTIMESTAMP(6),raw_col RAW(200));INSERTINTOtest.basic_types_testVALUES(1,12345.67,Hello, World!,FIXED,TO_DATE(2024-01-15 10:30:00,YYYY-MM-DD HH24:MI:SS),TIMESTAMP2024-01-15 10:30:00.123456,HEXTORAW(1A2B3C4D));INSERTINTOtest.basic_types_testVALUES(2,-9876.54,RPAD(Long string test ,190,X),ABC ,TO_DATE(1999-12-31 23:59:59,YYYY-MM-DD HH24:MI:SS),TIMESTAMP1999-12-31 23:59:59.999999,HEXTORAW(FFEEDDCCBBAA));INSERTINTOtest.basic_types_testVALUES(3,0,,,NULL,NULL,NULL);INSERTINTOtest.basic_types_testVALUES(4,NULL,NULL,NULL,SYSDATE,SYSTIMESTAMP,HEXTORAW(RPAD(FF,200,FF)));INSERTINTOtest.basic_types_testVALUES(5,0.01,A,1234567890,TO_DATE(2020-02-29 12:00:00,YYYY-MM-DD HH24:MI:SS),TIMESTAMP2020-02-29 12:00:00.000001,HEXTORAW(01));COMMIT;setlinesize999setpagesize999COLUMNvc_col FORMAT a30COLUMNchar_col FORMAT a10COLUMNdate_col FORMAT a19COLUMNts_col FORMAT a19COLUMNraw_hex FORMAT a30SQLSELECTid,num_col,vc_col,char_col,TO_CHAR(date_col,YYYY-MM-DD HH24:MI:SS)ASdate_col,TO_CHAR(ts_col,YYYY-MM-DD HH24:MI:SS)ASts_col,RAWTOHEX(raw_col)ASraw_hexFROMtest.basic_types_testORDERBYid;ID NUM_COL VC_COL CHAR_COL DATE_STR TS_STR RAW_HEX------ --------- ------------------------------ ---------- ------------------- -------------------------- ------------------------------112345.67Hello,World!FIXED2024-01-1510:30:002024-01-1510:30:00.1234561A2B3C4D2-9876.54Long string test XXXXXXXXXXXXX ABC1999-12-3123:59:591999-12-3123:59:59.999999FFEEDDCCBBAA XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX3.0042026-06-0317:44:062026-06-0317:44:06.359346FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFF5.01A12345678902020-02-2912:00:002020-02-2912:00:00.00000101模拟误删除SQLdeletefromtest.basic_types_test;5rowsdeleted.SQLcommit;Commitcomplete.SQLaltersystemcheckpoint;System altered.SQLselect*fromtest.basic_types_test;norowsselected执行恢复在执行package之前需要有一个指向“要恢复的表所在的数据文件所在的路径”的目录如没有执行时会报错并提供推荐的创建语句创建后重新执行即可。更多详细的用法请见下一文章操作手册篇。SQLSETSERVEROUTPUTONSQLEXECDEL_RECOVER_BASIC.recover_deleted_table(TEST,BASIC_TYPES_TEST,TEST);17:57:06: Sourcetable: TEST.BASIC_TYPES_TEST,DATA_OBJ#75104, TSTESTDATAFILE17:57:06: Creating recoverytable: TEST.R_BASIC_TYPES_TEST17:57:06: Recoverytablecreated successfully: TEST.R_BASIC_TYPES_TEST17:57:06: Processing datafile:/u01/app/oracle/oradata/ORCL/4A74127AED179CCAE0632400A8C0B3DF/datafile/o1_mf_tes tdata_nvhbjhnd_.dbfusingdirectory BBED_DATA_DIR17:57:06: Recovery completed.Totalrowsrecovered:517:57:06:Datastoredintable: TEST.R_BASIC_TYPES_TEST PL/SQLproceduresuccessfully completed.验证恢复结果SQLSELECTid,num_col,vc_col,char_col,TO_CHAR(date_col,YYYY-MM-DD HH24:MI:SS)ASdate_col,TO_CHAR(ts_col,YYYY-MM-DD HH24:MI:SS)ASts_col,RAWTOHEX(raw_col)ASraw_hexFROMtest.r_basic_types_testORDERBYid;ID NUM_COL VC_COL CHAR_COL DATE_COL TS_COL RAW_HEX---------- ---------- ------------------------------ ---------- ------------------- ------------------- ------------------------------112345.67Hello,World!FIXED2024-01-1510:30:002024-01-1510:30:001A2B3C4D2-9876.54Long string test XXXXXXXXXXXXX ABC1999-12-3123:59:591999-12-3123:59:59FFEEDDCCBBAA XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXX3042026-06-0317:44:062026-06-0317:44:06FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFF5.01A12345678902020-02-2912:00:002020-02-2912:00:0001package下载链接https://download.csdn.net/download/sp60cn/92942843
