show user
show con_name
/*
USER is "HR"
CON_NAME
------------------------------
ORCLPDB
*/
select * from employees;
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- --------------
198 Donald OConnell DOCONNEL
199 Douglas Grant DGRANT
200 Jennifer Whalen JWHALEN
201 Michael Hartstein MHARTSTE
202 Pat Fay PFAY
203 Susan Mavris SMAVRIS
204 Hermann Baer HBAER
...
*/
create table emp_copy3
as
select * from employees;
/*
Table EMP_COPY3 created.
*/
select * from emp_copy3;
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- --------------
198 Donald OConnell DOCONNEL
199 Douglas Grant DGRANT
200 Jennifer Whalen JWHALEN
201 Michael Hartstein MHARTSTE
202 Pat Fay PFAY
203 Susan Mavris SMAVRIS
204 Hermann Baer HBAER
...
*/
drop table emp_copy3;
/*
Table EMP_COPY3 dropped.
*/
select * from emp_copy3;
/*
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*/
--select * from recyclebin;
select object_name, original_name, operation, ts_name, dropscn from recyclebin;
/*
The SCN is an internal number maintained by the database management system
to log changes made to a database
Whenever an application commits a transaction, the log writer process (LGWR)
writes records from the redo log buffers in the (SGA) to the online redo logs on disk.
LGWR also writes the transaction�s SCN to the online redo log file.
*/
/*
OBJECT_NAME ORIGINAL_NAME OPERATION TS_NAME DROPSCN
------------------------------ --------------- --------- ---------- ----------
BIN$DM+JOB5OhMDgY+ECqMBMUg==$0 EMP_COPY3 DROP SYSAUX 9398086
*/
flashback table emp_copy3 to before drop;
/*
Flashback succeeded.
*/
--select * from emp_copy3;
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL from emp_copy3;
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -------------------------
198 Donald OConnell DOCONNEL
199 Douglas Grant DGRANT
200 Jennifer Whalen JWHALEN
201 Michael Hartstein MHARTSTE
202 Pat Fay PFAY
203 Susan Mavris SMAVRIS
204 Hermann Baer HBAER
....
*/
select * from recyclebin;
--you can do this command( purge recyclebin) to make the recyclebin empty;
/*
Using Oracle Flashback Version Query
Use Oracle Flashback Version Query to retrieve the different versions of specific rows
that existed during a given time interval.
A row version is created whenever a COMMIT statement is executed.
*/
select salary from employees where employee_id=107;
/*
SALARY
----------
4200
*/
update employees set salary=salary+100 where employee_id=107;
/*
1 row updated.
*/
commit; -- this will make new scn
/*
Commit complete.
*/
select salary from employees where employee_id=107;
/*
SALARY
----------
4300
*/
select versions_starttime,versions_endtime, salary from employees
versions between scn minvalue and maxvalue
where employee_id=107;
/*
VERSIONS_STARTTIME VERSIONS_ENDTIME SALARY
------------------------------- ------------------------------- ----------
18-DEC-23 08.40.48.000000000 PM 4300
18-DEC-23 08.40.48.000000000 PM 4200
*/
update employees set salary=salary+100 where employee_id=107;
commit;
/*
1 row updated.
Commit complete.
*/
select employee_id, versions_starttime,versions_endtime, salary from
employees
versions between scn minvalue and maxvalue
where employee_id=107;
-- Q: until when i can see these records?
-- This depend on many factors
-- 1-Tablespace type (fixed or auto extend )
-- 2-The retention period UNDO_RETENTION
-- 3-Retention Guarantee
/*
VERSIONS_STARTTIME VERSIONS_ENDTIME SALARY
------------------------------- ------------------------------- ----------
18-DEC-23 08.44.23.000000000 PM 4400
18-DEC-23 08.40.48.000000000 PM 18-DEC-23 08.44.23.000000000 PM 4300
18-DEC-23 08.40.48.000000000 PM 4200
*/
