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. very well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

  4. I really thank you for the valuable info on this great subject and look forward to more great posts ExcelR Data Analytics Course

  5. 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