Mixing
How to get old code from replaced procedure (PL/SQL)?
2
I have a procedure which was working until I mistakenly replaced by other code.
So my procedure started with CREATE OR REPLACE PROCEDURE xxx.yyy, and I mistakenly replaced with some simple code and saved it.
Since I had replaced in my procedure so it got replaced but now it doesn't work the way I want any more. How can I get my old code that was in that procedure (before replaced)?
oracle stored-procedures plsql
edited Nov 22 '18 at 7:32
halfer
14.6k758112
asked Nov 21 '18 at 22:12
kklkkl
1269
add a comment |
2
I have a procedure which was working until I mistakenly replaced by other code.
So my procedure started with CREATE OR REPLACE PROCEDURE xxx.yyy, and I mistakenly replaced with some simple code and saved it.
Since I had replaced in my procedure so it got replaced but now it doesn't work the way I want any more. How can I get my old code that was in that procedure (before replaced)?
oracle stored-procedures plsql
edited Nov 22 '18 at 7:32
halfer
14.6k758112
asked Nov 21 '18 at 22:12
kklkkl
1269
1
Backup or get code from version control(Git/SVN) if you have any.
– Lukasz Szozda
Nov 21 '18 at 22:14
Hi @Lukasz, I dont have any but it was under my schema wondering if there are other way??
– kkl
Nov 21 '18 at 22:17
Well I guess you don't have any DDL auditing enabled or system-level trigger to catch DDL changes. I propose to search for backup or try to check this on different environment like QA/DEV/...
– Lukasz Szozda
Nov 21 '18 at 22:21
1
So the teaching is: use source control for your code.
– APC
Nov 22 '18 at 8:02
add a comment |
2
2
2
I have a procedure which was working until I mistakenly replaced by other code.
So my procedure started with CREATE OR REPLACE PROCEDURE xxx.yyy, and I mistakenly replaced with some simple code and saved it.
Since I had replaced in my procedure so it got replaced but now it doesn't work the way I want any more. How can I get my old code that was in that procedure (before replaced)?
oracle stored-procedures plsql
edited Nov 22 '18 at 7:32
halfer
14.6k758112
asked Nov 21 '18 at 22:12
kklkkl
1269
I have a procedure which was working until I mistakenly replaced by other code.
So my procedure started with CREATE OR REPLACE PROCEDURE xxx.yyy, and I mistakenly replaced with some simple code and saved it.
Since I had replaced in my procedure so it got replaced but now it doesn't work the way I want any more. How can I get my old code that was in that procedure (before replaced)?
oracle stored-procedures plsql
oracle stored-procedures plsql
edited Nov 22 '18 at 7:32
halfer
14.6k758112
asked Nov 21 '18 at 22:12
kklkkl
1269
edited Nov 22 '18 at 7:32
halfer
14.6k758112
asked Nov 21 '18 at 22:12
kklkkl
1269
edited Nov 22 '18 at 7:32
halfer
14.6k758112
edited Nov 22 '18 at 7:32
halfer
14.6k758112
edited Nov 22 '18 at 7:32
halfer
14.6k758112
14.6k758112
asked Nov 21 '18 at 22:12
kklkkl
1269
asked Nov 21 '18 at 22:12
kklkkl
1269
asked Nov 21 '18 at 22:12
kklkkl
1269
1269
1
Backup or get code from version control(Git/SVN) if you have any.
– Lukasz Szozda
Nov 21 '18 at 22:14
Hi @Lukasz, I dont have any but it was under my schema wondering if there are other way??
– kkl
Nov 21 '18 at 22:17
Well I guess you don't have any DDL auditing enabled or system-level trigger to catch DDL changes. I propose to search for backup or try to check this on different environment like QA/DEV/...
– Lukasz Szozda
Nov 21 '18 at 22:21
1
So the teaching is: use source control for your code.
– APC
Nov 22 '18 at 8:02
add a comment |
1
Backup or get code from version control(Git/SVN) if you have any.
– Lukasz Szozda
Nov 21 '18 at 22:14
Hi @Lukasz, I dont have any but it was under my schema wondering if there are other way??
– kkl
Nov 21 '18 at 22:17
Well I guess you don't have any DDL auditing enabled or system-level trigger to catch DDL changes. I propose to search for backup or try to check this on different environment like QA/DEV/...
– Lukasz Szozda
Nov 21 '18 at 22:21
1
So the teaching is: use source control for your code.
– APC
Nov 22 '18 at 8:02
1
1
Backup or get code from version control(Git/SVN) if you have any.
– Lukasz Szozda
Nov 21 '18 at 22:14
Backup or get code from version control(Git/SVN) if you have any.
– Lukasz Szozda
Nov 21 '18 at 22:14
Hi @Lukasz, I dont have any but it was under my schema wondering if there are other way??
– kkl
Nov 21 '18 at 22:17
Hi @Lukasz, I dont have any but it was under my schema wondering if there are other way??
– kkl
Nov 21 '18 at 22:17
Well I guess you don't have any DDL auditing enabled or system-level trigger to catch DDL changes. I propose to search for backup or try to check this on different environment like QA/DEV/...
– Lukasz Szozda
Nov 21 '18 at 22:21
Well I guess you don't have any DDL auditing enabled or system-level trigger to catch DDL changes. I propose to search for backup or try to check this on different environment like QA/DEV/...
– Lukasz Szozda
Nov 21 '18 at 22:21
1
1
So the teaching is: use source control for your code.
– APC
Nov 22 '18 at 8:02
So the teaching is: use source control for your code.
– APC
Nov 22 '18 at 8:02
add a comment |
2 Answers
2
active
oldest
votes
2
When you're connected at xxx schema, try to use :
select s.text
from user_source
as of timestamp systimestamp - interval '1' day s
where s.name = 'YYY';
to get your procedure's source. It gives your the data of one day before. You may convert '1' day to '10' hour, '150' minute .. etc. depending on your need, in the case your db_flashback_retention_target database parameter has been set as large as enough.
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
add a comment |
0
If there are archive logs for the search period. You can use the utility LogMiner.
for example:
CREATE OR REPLACE PACKAGE BODY acs_ss2.rest_of_day is
/*------------------------------------------------------------------*/
procedure starting(p_date date) is
rec_newrest r_rest%rowtype;
rec_oldrest r_rest%rowtype;
rec_rest r_rest%rowtype;
v_station integer := 8902;
v_dt1 date;
v_dt2 date;
begin
SQL CODE...
Determine the desired log files by the time of the incident.
select name, first_time, next_time
from v$archived_log
where first_time >sysdate -3/24
/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf 22-ноя-2018 10:56:12 22-ноя-2018 12:13:48
/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf 22-ноя-2018 12:13:48 22-ноя-2018 13:17:06
/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf 22-ноя-2018 13:17:06 22-ноя-2018 13:39:38
Run the logminer utility.
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
Run SQL for restore our CODE, see column sql_undo.
SELECT
to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
operation,
sql_redo,
sql_undo
FROM v$logmnr_contents
where
seg_owner='SYS' and
seg_name='SOURCE$'
==>
TO_CHAR(TIMESTAMP,'DD-MM-YYYYHH24:MI:SS') OPERATION SQL_REDO SQL_UNDO
----------------------------------------------- -------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '1' and "SOURCE" = 'package body re insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','1','package body rest_of_day i
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '2' and "SOURCE" = '
' and ROWID = insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','2','
');
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '3' and "SOURCE" = '/*------------- insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','3','/*------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '4' and "SOURCE" = 'procedure start insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','4','procedure starting(p_date
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '5' and "SOURCE" = ' rec_newrest r insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','5',' rec_newrest r_rest%rowty
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421165%2fhow-to-get-old-code-from-replaced-procedure-pl-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
When you're connected at xxx schema, try to use :
select s.text
from user_source
as of timestamp systimestamp - interval '1' day s
where s.name = 'YYY';
to get your procedure's source. It gives your the data of one day before. You may convert '1' day to '10' hour, '150' minute .. etc. depending on your need, in the case your db_flashback_retention_target database parameter has been set as large as enough.
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
add a comment |
2
When you're connected at xxx schema, try to use :
select s.text
from user_source
as of timestamp systimestamp - interval '1' day s
where s.name = 'YYY';
to get your procedure's source. It gives your the data of one day before. You may convert '1' day to '10' hour, '150' minute .. etc. depending on your need, in the case your db_flashback_retention_target database parameter has been set as large as enough.
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
add a comment |
2
2
2
When you're connected at xxx schema, try to use :
select s.text
from user_source
as of timestamp systimestamp - interval '1' day s
where s.name = 'YYY';
to get your procedure's source. It gives your the data of one day before. You may convert '1' day to '10' hour, '150' minute .. etc. depending on your need, in the case your db_flashback_retention_target database parameter has been set as large as enough.
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
When you're connected at xxx schema, try to use :
select s.text
from user_source
as of timestamp systimestamp - interval '1' day s
where s.name = 'YYY';
to get your procedure's source. It gives your the data of one day before. You may convert '1' day to '10' hour, '150' minute .. etc. depending on your need, in the case your db_flashback_retention_target database parameter has been set as large as enough.
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
answered Nov 22 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.6k71633
13.6k71633
add a comment |
add a comment |
0
If there are archive logs for the search period. You can use the utility LogMiner.
for example:
CREATE OR REPLACE PACKAGE BODY acs_ss2.rest_of_day is
/*------------------------------------------------------------------*/
procedure starting(p_date date) is
rec_newrest r_rest%rowtype;
rec_oldrest r_rest%rowtype;
rec_rest r_rest%rowtype;
v_station integer := 8902;
v_dt1 date;
v_dt2 date;
begin
SQL CODE...
Determine the desired log files by the time of the incident.
select name, first_time, next_time
from v$archived_log
where first_time >sysdate -3/24
/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf 22-ноя-2018 10:56:12 22-ноя-2018 12:13:48
/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf 22-ноя-2018 12:13:48 22-ноя-2018 13:17:06
/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf 22-ноя-2018 13:17:06 22-ноя-2018 13:39:38
Run the logminer utility.
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
Run SQL for restore our CODE, see column sql_undo.
SELECT
to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
operation,
sql_redo,
sql_undo
FROM v$logmnr_contents
where
seg_owner='SYS' and
seg_name='SOURCE$'
==>
TO_CHAR(TIMESTAMP,'DD-MM-YYYYHH24:MI:SS') OPERATION SQL_REDO SQL_UNDO
----------------------------------------------- -------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '1' and "SOURCE" = 'package body re insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','1','package body rest_of_day i
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '2' and "SOURCE" = '
' and ROWID = insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','2','
');
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '3' and "SOURCE" = '/*------------- insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','3','/*------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '4' and "SOURCE" = 'procedure start insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','4','procedure starting(p_date
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '5' and "SOURCE" = ' rec_newrest r insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','5',' rec_newrest r_rest%rowty
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
add a comment |
0
If there are archive logs for the search period. You can use the utility LogMiner.
for example:
CREATE OR REPLACE PACKAGE BODY acs_ss2.rest_of_day is
/*------------------------------------------------------------------*/
procedure starting(p_date date) is
rec_newrest r_rest%rowtype;
rec_oldrest r_rest%rowtype;
rec_rest r_rest%rowtype;
v_station integer := 8902;
v_dt1 date;
v_dt2 date;
begin
SQL CODE...
Determine the desired log files by the time of the incident.
select name, first_time, next_time
from v$archived_log
where first_time >sysdate -3/24
/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf 22-ноя-2018 10:56:12 22-ноя-2018 12:13:48
/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf 22-ноя-2018 12:13:48 22-ноя-2018 13:17:06
/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf 22-ноя-2018 13:17:06 22-ноя-2018 13:39:38
Run the logminer utility.
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
Run SQL for restore our CODE, see column sql_undo.
SELECT
to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
operation,
sql_redo,
sql_undo
FROM v$logmnr_contents
where
seg_owner='SYS' and
seg_name='SOURCE$'
==>
TO_CHAR(TIMESTAMP,'DD-MM-YYYYHH24:MI:SS') OPERATION SQL_REDO SQL_UNDO
----------------------------------------------- -------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '1' and "SOURCE" = 'package body re insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','1','package body rest_of_day i
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '2' and "SOURCE" = '
' and ROWID = insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','2','
');
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '3' and "SOURCE" = '/*------------- insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','3','/*------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '4' and "SOURCE" = 'procedure start insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','4','procedure starting(p_date
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '5' and "SOURCE" = ' rec_newrest r insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','5',' rec_newrest r_rest%rowty
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
add a comment |
0
0
0
If there are archive logs for the search period. You can use the utility LogMiner.
for example:
CREATE OR REPLACE PACKAGE BODY acs_ss2.rest_of_day is
/*------------------------------------------------------------------*/
procedure starting(p_date date) is
rec_newrest r_rest%rowtype;
rec_oldrest r_rest%rowtype;
rec_rest r_rest%rowtype;
v_station integer := 8902;
v_dt1 date;
v_dt2 date;
begin
SQL CODE...
Determine the desired log files by the time of the incident.
select name, first_time, next_time
from v$archived_log
where first_time >sysdate -3/24
/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf 22-ноя-2018 10:56:12 22-ноя-2018 12:13:48
/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf 22-ноя-2018 12:13:48 22-ноя-2018 13:17:06
/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf 22-ноя-2018 13:17:06 22-ноя-2018 13:39:38
Run the logminer utility.
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
Run SQL for restore our CODE, see column sql_undo.
SELECT
to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
operation,
sql_redo,
sql_undo
FROM v$logmnr_contents
where
seg_owner='SYS' and
seg_name='SOURCE$'
==>
TO_CHAR(TIMESTAMP,'DD-MM-YYYYHH24:MI:SS') OPERATION SQL_REDO SQL_UNDO
----------------------------------------------- -------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '1' and "SOURCE" = 'package body re insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','1','package body rest_of_day i
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '2' and "SOURCE" = '
' and ROWID = insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','2','
');
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '3' and "SOURCE" = '/*------------- insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','3','/*------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '4' and "SOURCE" = 'procedure start insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','4','procedure starting(p_date
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '5' and "SOURCE" = ' rec_newrest r insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','5',' rec_newrest r_rest%rowty
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
If there are archive logs for the search period. You can use the utility LogMiner.
for example:
CREATE OR REPLACE PACKAGE BODY acs_ss2.rest_of_day is
/*------------------------------------------------------------------*/
procedure starting(p_date date) is
rec_newrest r_rest%rowtype;
rec_oldrest r_rest%rowtype;
rec_rest r_rest%rowtype;
v_station integer := 8902;
v_dt1 date;
v_dt2 date;
begin
SQL CODE...
Determine the desired log files by the time of the incident.
select name, first_time, next_time
from v$archived_log
where first_time >sysdate -3/24
/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf 22-ноя-2018 10:56:12 22-ноя-2018 12:13:48
/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf 22-ноя-2018 12:13:48 22-ноя-2018 13:17:06
/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf 22-ноя-2018 13:17:06 22-ноя-2018 13:39:38
Run the logminer utility.
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47429_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47430_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/redolog/edcu/1_47431_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
Run SQL for restore our CODE, see column sql_undo.
SELECT
to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
operation,
sql_redo,
sql_undo
FROM v$logmnr_contents
where
seg_owner='SYS' and
seg_name='SOURCE$'
==>
TO_CHAR(TIMESTAMP,'DD-MM-YYYYHH24:MI:SS') OPERATION SQL_REDO SQL_UNDO
----------------------------------------------- -------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '1' and "SOURCE" = 'package body re insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','1','package body rest_of_day i
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '2' and "SOURCE" = '
' and ROWID = insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','2','
');
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '3' and "SOURCE" = '/*------------- insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','3','/*------------------------
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '4' and "SOURCE" = 'procedure start insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','4','procedure starting(p_date
22-11-2018 13:39:14 DELETE delete from "SYS"."SOURCE$" where "OBJ#" = '197353' and "LINE" = '5' and "SOURCE" = ' rec_newrest r insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('197353','5',' rec_newrest r_rest%rowty
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
answered Nov 22 '18 at 11:13
Dmitry DeminDmitry Demin
980149
980149
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421165%2fhow-to-get-old-code-from-replaced-procedure-pl-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Backup or get code from version control(Git/SVN) if you have any.
– Lukasz Szozda
Nov 21 '18 at 22:14
Hi @Lukasz, I dont have any but it was under my schema wondering if there are other way??
– kkl
Nov 21 '18 at 22:17
Well I guess you don't have any DDL auditing enabled or system-level trigger to catch DDL changes. I propose to search for backup or try to check this on different environment like QA/DEV/...
– Lukasz Szozda
Nov 21 '18 at 22:21
1
So the teaching is: use source control for your code.
– APC
Nov 22 '18 at 8:02