>ORACLE Flashback

>fashback default da 15 dk . en az 2 saat olmalıdır.
hr hesabı ilk kurulumda kilitlidir.
alter user hr identified by hr account unlock;
conn hr/hr — hr ile bağlanıyoruz.

select rowid, employee_id, first_name from employees;

rowid — db veri girildiğinde hangi datafile hangi block ve sırasını tutar, rowid değişmez.

buffer cache küçükse wait event artar.
oracle read e kilit vurmaz. undotablespace bunun için var.
commit edilince undotable space siliniyor. flash back olunca tutuyor.

ör:
select * from $table_name
drop table $table_name
flashback table $table_name to before drop

10 g ile recyclebin geldi. flashback buradan alıyor.
purge dersek flashback ile de geri alınamaz.

create table kopya_emp5 as
select * from employees

delete from kopya_emp5 where employee_id=107;
commit;

select * from kopya_emp5
as of timestamp to_timestamp(’15/07/2009 14:04′, ‘DD/MM/Yyyy hh24:mi’)
minus
select * from kopya_emp5

tablo üzerinde ddl varsa flash back yapılamıyor.

alter table employees disable|enable row movement — 10g ile default geliyor.

Select
SCN,
timestamp,
session# session_num,
sql_redo
From V$LOGMNR_CONTENTS
where upper(sql_redo) like ‘%KOPYA_EMP5%’
Order by 1
——————-
declare

cursor c_tablo is
select table_name
from user_tables
order by table_name;
sayi number;

begin

for r_tablo in c_tablo
loop
execute immediate ‘select count(*) from ‘||r_tablo.table_name into sayi;
dbms_output.put_line(‘Tablo:’||r_tablo.table_name||’ Sayi’||sayi);
end loop;
end;
—————–
–tablo analiz
create or replace procedure tablo_analiz is
cursor c_tablo is
select table_name
from user_tables;
begin
for r_tablo in c_tablo
loop
execute immediate ‘analyze table ‘||r_tablo.table_name||’ compute statistics’;
end loop;
end;

exec tablo_analiz
———————
select * from v$log;
alter system switch logfile;
show parameter undo_retention;
select name from v$parameter;
show parameter sessions
–dolu geliyorsa spfile ile başladı
–null ise init.ora ile başladı.
select name, value fromv$parameter where name like ‘%spfile%’ — spfile ile mi başlıyor?
create pfile from spfile;
archive log list;

shutdwon [normal]
immediate — işlemleri roolback yapar, kullanıcıyı atar,
transactional– işlemlerin bitmesini bekleri, kullanıcıyı atar
abort– hemen atar
——-
drop table kopya_emp

create table kopya_emp as
select * from employees

insert into kopya_emp
select * from kopya_emp

select count(*)
from kopya_emp
where department_id in (select distinct department_id
from kopya_emp
where last_name=’King’)
———-
declare
sayi number;
begin

for i in 1..100
loop
select count(*)
into sayi
from kopya_emp
where department_id in (select distinct department_id
from kopya_emp
where last_name=’King’);
end loop;

end;
—advisor hr
grant advisor to hr
alter system set undo_retention=900 scope=both;

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s