Sunday, August 3, 2014

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

oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release 12.1.0.2.0 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 12.1.0.2.0 - 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
Connected.
SQL> alter flashback archive FBDA1 set default;

Flashback archive altered.

SQL> conn donghua
Enter password:
Connected.
SQL>

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

OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         STATUS
------------------------------ ------------------------------ -------
DONGHUA                        FBDA1                          DEFAULT

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME  FLASHBACK_ARCHIVE# TABLESPACE_NAME  QUOTA_IN_MB
----------------------- ------------------ ---------------- -------------
FBDA1                                    1 FBDA_1Y          1024


SQL> select * from dba_flashback_archive_tables;

TABLE_NAME           OWNER_NAME FLASHBACK_ARCHIVE_NAME  ARCHIVE_TABLE_NAME  STATUS
-------------------- ---------- ----------------------- ------------------- --------
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;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
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
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
        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.

2 comments:

  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

    ReplyDelete
  2. 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.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete