Repository navigation
Expand file tree
/
Copy pathbackup_script
More file actions
91 lines (75 loc) · 3 KB
/
Copy pathbackup_script
File metadata and controls
91 lines (75 loc) · 3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
/** Drop Old Objects **/
set timing off;
drop trigger plsql_trigger;
drop table plsql_params purge;
drop table plsql_history cascade constraints purge;
drop table plsql_history_source cascade constraints purge;
drop table plsql_log cascade constraints purge;
/** Creation of Objects Needed **/
create table plsql_params as select 10 versions_kept from dual;
create table plsql_history (
TSTAMP TIMESTAMP
,OWNER VARCHAR2(128)
,NAME VARCHAR2(128)
,TYPE VARCHAR2(12)
,constraint plsql_history_pk primary key ( tstamp,owner,name,type)
) organization index;
create table plsql_history_source (
TSTAMP TIMESTAMP
,OWNER VARCHAR2(128)
,NAME VARCHAR2(128)
,TYPE VARCHAR2(12)
,LINE NUMBER
,TEXT VARCHAR2(4000)
,constraint plsql_history_source_pk primary key ( tstamp,owner,name,type,line)
,constraint plsql_history_source_fk foreign key ( tstamp,owner,name,type) references plsql_history ( tstamp,owner,name,type ));
create table plsql_log
(
TSTAMP TIMESTAMP
,MSG varchar2(1000));
/** Grant priviledge to User **/
GRANT SELECT ON dba_source TO XXX;
/** Create Trigger **/
create or replace trigger plsql_trigger
before create on SCHEMA
declare
l_owner varchar2(128) := ora_dict_obj_owner;
l_name varchar2(128) := ora_dict_obj_name;
l_type varchar2(128) := ora_dict_obj_type;
l_archived timestamp := systimestamp;
l_tstamp_to_clear timestamp;
procedure logger(m varchar2) is
pragma autonomous_transaction;
begin
insert into plsql_log values (systimestamp,m);
commit;
end;
begin
if l_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','TRIGGER') then
insert into plsql_history values (l_archived, l_owner, l_name, l_type);
insert into plsql_history_source
select l_archived, owner, name, type, line, text
from dba_source
where owner = l_owner
and name = l_name
and type = l_type;
logger('Archived '||l_type||'-'||l_owner||'.'||l_name);
select max(case when tot > versions_kept and seq = tot - versions_kept then tstamp end)
into l_tstamp_to_clear
from plsql_params,
( select ph.*,
row_number() over ( order by tstamp ) as seq,
count(*) over () as tot
from plsql_history ph
)
where owner = l_owner
and name = l_name
and type = l_type;
if l_tstamp_to_clear is not null then
logger('Clearance timestamp for '||l_type||'-'||l_owner||'.'||l_name||' is '||l_tstamp_to_clear);
delete from plsql_history_source where tstamp <= l_tstamp_to_clear and owner=l_owner and type =l_type and name =l_name;
delete from plsql_history where tstamp <= l_tstamp_to_clear and owner=l_owner and type =l_type and name =l_name;
logger('Cleared '||sql%rowcount||' versions for '||l_type||'-'||l_owner||'.'||l_name);
end if;
end if;
end;