Sunday, August 3, 2014

1z0-060 Upgrade to Oracle 12c–Flashback Data Archive Example

oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release Production on Mon Aug 4 07:21:18 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Mon Aug 04 2014 07:19:37 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create tablespace fbda_1y datafile '/u01/app/oracle/oradata/orcl/fbda_1y.dbf' size 10M autoextend on;

Tablespace created.

SQL> create flashback archive FBDA1 tablespace fbda_1y quota 1G retention 1 year;

Flashback archive created.

SQL> create table emp1 tablespace users flashback archive FBDA1
  2  as select * from hr.employees where 1=2;

Table created.

SQL> alter flashback archive FBDA1 set default;
alter flashback archive FBDA1 set default
ERROR at line 1:
ORA-55611: No privilege to manage default Flashback Archive

SQL> conn / as sysdba
SQL> alter flashback archive FBDA1 set default;

Flashback archive altered.

SQL> conn donghua
Enter password:

SQL> col flashback_archive_name for a30
SQL> select owner_name, flashback_archive_name, status
  2  from dba_flashback_archive;

------------------------------ ------------------------------ -------
DONGHUA                        FBDA1                          DEFAULT

SQL> select * from dba_flashback_archive_ts;

----------------------- ------------------ ---------------- -------------
FBDA1                                    1 FBDA_1Y          1024

SQL> select * from dba_flashback_archive_tables;

-------------------- ---------- ----------------------- ------------------- --------
EMP1                 DONGHUA    FBDA1                   SYS_FBA_HIST_109887 ENABLED

SQL> insert into emp1
  2  select * from hr.employees where employee_id=107;

1 row created.

SQL> commit;

Commit complete.

SQL> select first_name,last_name from emp1 where employee_id=107;

-------------------- -------------------------
Diana                Lorentz

SQL> truncate table emp1;

Table truncated.

SQL> select * from emp1;

no rows selected

SQL> select * from emp1 versions between scn minvalue AND maxvalue;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
---------- ---------- -------------- ---------- -------------
        107 Diana                Lorentz                   DLORENTZ                  590.423.5567         07-FEB-07
IT_PROG          4200                       103            60

SQL> drop table emp1;
drop table emp1
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

SQL> alter table emp1 drop column email;
SQL> alter table emp1 drop column hire_date;
SQL> alter table emp1 drop column job_id;
SQL> alter table emp1 drop column salary;
SQL> alter table emp1 drop column commission_pct;
SQL> alter table emp1 drop column manager_id;
SQL> alter table emp1 drop column department_id;
SQL> alter table emp1 add (note varchar2(5));

SQL> insert into emp1 values(1,'Donghua','Luo','12c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp1;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 NOTE
----------- -------------------- ------------------------- -----
          1 Donghua              Luo                       12c

SQL> select * from emp1 versions between scn minvalue AND maxvalue;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 NOTE  D_4956408_EMAIL           D_4956474_PHONE_NUMB
----------- -------------------- ------------------------- ----- ------------------------- --------------------
D_4956528 D_4956605_ D_4956645_SALARY D_4956689_COMMISSION_PCT D_4956735_MANAGER_ID D_4956780_DEPARTMENT_ID
--------- ---------- ---------------- ------------------------ -------------------- -----------------------
        107 Diana                Lorentz                         DLORENTZ                  590.423.5567
07-FEB-07 IT_PROG                4200                                           103                      60

          1 Donghua              Luo                       12c

What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled? Oracle is smart enough to archive the data before executing the truncation on the table.


  1. Evolution of DevOps: As organizations look at new technologies to enhance their business agility, speed and competitive edge, they also face new challenges that can't be merely met with traditional, out-of-date and on-premise monitoring tools. Data Analytics Courses

  2. I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts.
    a href=""> Data Analytics Course in Pune/">You re in point of fact a just right webmaster. The website loading speed is amazing. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterpiece. you have done a fantastic activity on this subject!
    I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon…

  3. Hi there, You have done a fantastic job. I’ll certainly digg it and personally suggest to my friends. I am sure they will be benefited from this site.|data science course in jodhpur