Install Oracle Linux 7.0

the “/boot” shall be minimal 200MB, suggest assign 300MB

Reduce ZFS File Data to free up memory used for Oracle or MySQL Database

root@solaris:~# echo "::memstat"|mdb -k
Page Summary                 Pages             Bytes  %Tot
----------------- ----------------  ----------------  ----
Kernel                      137139            535.6M   21%
Guest                            0                 0    0%
ZFS Metadata                 10569             41.2M    2%
ZFS File Data               216961            847.5M   33%
Anon                         36668            143.2M    6%
Exec and libs                 1135              4.4M    0%
Page cache                    5639             22.0M    1%
Free (cachelist)             30191            117.9M    5%
Free (freelist)             210289            821.4M   32%
Total                       648591              2.4G

root@solaris:~# echo "set zfs:zfs_arc_max = 104857600" >> /etc/system
root@solaris:~# init 6

root@solaris:~# echo "::memstat"|mdb -k
Page Summary                 Pages             Bytes  %Tot
----------------- ----------------  ----------------  ----
Kernel                      137401            536.7M   21%
Guest                            0                 0    0%
ZFS Metadata                  8837             34.5M    1%
ZFS File Data                20755             81.0M    3%
Anon                         36965            144.3M    6%
Exec and libs                 1706              6.6M    0%
Page cache                    9972             38.9M    2%
Free (cachelist)             36747            143.5M    6%
Free (freelist)             396208              1.5G   61%
Total                       648591              2.4G

1z0-060 Upgrade to 12c: CONTAINER CLAUSE & Common granted privilege

If the current container is a pluggable database (PDB):

• Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a local user, common user, local role, or common role. The privilege or role is revoked from the user or role only in the current PDB. This clause does not revoke privileges granted withCONTAINER = ALL.

If the current container is the root:

• Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a common user or common role. The privilege or role is revoked from the user or role only in the root. This clause does not revoke privileges granted with CONTAINER = ALL.

• Specify CONTAINER = ALL to revoke a commonly granted system privilege, object privilege on a common object, or role from a common user or common role. The privilege or role is revoked from the user or role across the entire CDB. This clause can revoke only a privilege or role granted withCONTAINER = ALL from the specified common user or common role. This clause does not revoke privileges granted locally with CONTAINER = CURRENT. However, any locally granted privileges that depend on the commonly granted privilege being revoked are also revoked.

If you omit this clause, then CONTAINER = CURRENT is the default.

 

SQL> desc cdb_sys_privs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                            VARCHAR2(128)
PRIVILEGE                                          VARCHAR2(40)
ADMIN_OPTION                                       VARCHAR2(3)
COMMON                                             VARCHAR2(3)
CON_ID                                             NUMBER

SQL> create user c##admin identified by password;

User created.

SQL> grant create table to c##admin container=ALL;

Grant succeeded.

SQL> grant create view to c##admin container=CURRENT;

Grant succeeded.

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
CREATE VIEW                              NO           1
CREATE TABLE                             YES          1
CREATE TABLE                             YES          3

SQL> revoke create table from c##admin;
revoke create table from c##admin
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

SQL> revoke create table from c##admin container=CURRENT;
revoke create table from c##admin container=CURRENT
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

SQL> revoke create table from c##admin container=ALL;

Revoke succeeded.

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
CREATE VIEW                              NO           1

SQL> revoke create view from c##admin container=ALL;
revoke create view from c##admin container=ALL
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
CREATE VIEW                              NO           1

SQL> revoke create view from c##admin container=CURRENT;

Revoke succeeded.

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

no rows selected

1z0-060 Upgrade to 12c: Managing Column Group Statistics

You can use DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to determine which column groups are required for a table based on a specified workload. This technique is useful when you do not know which extended statistics to create. This technique does not work for expression statistics.

Setup the simulation environment

SQL> CREATE TABLE customers_test AS SELECT * FROM sh.customers;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

PL/SQL procedure successfully completed.

Detecting Useful Column Groups for a Specific Workload

SQL> BEGIN
  2    DBMS_STATS.SEED_COL_USAGE(null,null,300);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT *
  3    FROM      customers_test
  4    WHERE  cust_city = 'Los Angeles'
  5    AND       cust_state_province = 'CA'
  6    AND       country_id = 52790;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
----------------------------------------------------

8 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT   country_id, cust_state_province, count(cust_city)
  3    FROM     customers_test
  4    GROUP BY country_id, cust_state_province;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |  1949 |
|   1 |  HASH GROUP BY     |                |  1949 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

SQL> SET LONG 100000
SQL> SET LINES 120
SQL> SET PAGES 0
SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
  2  FROM   DUAL;
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR DONGHUA.CUSTOMERS_TEST
..............................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###############################################################################

Creating Column Groups Detected During Workload Monitoring

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
###############################################################################

EXTENSIONS FOR DONGHUA.CUSTOMERS_TEST
.....................................

1. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID)   : SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###############################################################################

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

PL/SQL procedure successfully completed.

SQL> col COL_GROUP for a40
SQL> col EXTENSION_NAME for a40
SQL> col EXTENSION for a70
SQL> set pages 999
SQL> col COLUMN_NAME for a40
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  2  FROM   USER_TAB_COL_STATISTICS
  3  WHERE  TABLE_NAME = 'CUSTOMERS_TEST'
  4  ORDER BY 1;

COLUMN_NAME                              NUM_DISTINCT HISTOGRAM
---------------------------------------- ------------ ---------------
COUNTRY_ID                                         19 FREQUENCY
CUST_CITY                                         620 HYBRID
CUST_CITY_ID                                      620 NONE
CUST_CREDIT_LIMIT                                   8 NONE
CUST_EFF_FROM                                       1 NONE
CUST_EFF_TO                                         0 NONE
CUST_EMAIL                                       1699 NONE
CUST_FIRST_NAME                                  1300 NONE
CUST_GENDER                                         2 NONE
CUST_ID                                         55500 NONE
CUST_INCOME_LEVEL                                  12 NONE
CUST_LAST_NAME                                    908 NONE
CUST_MAIN_PHONE_NUMBER                          51344 NONE
CUST_MARITAL_STATUS                                11 NONE
CUST_POSTAL_CODE                                  623 NONE
CUST_SRC_ID                                         0 NONE
CUST_STATE_PROVINCE                               145 FREQUENCY
CUST_STATE_PROVINCE_ID                            145 NONE
CUST_STREET_ADDRESS                             49900 NONE
CUST_TOTAL                                          1 NONE
CUST_TOTAL_ID                                       1 NONE
CUST_VALID                                          2 NONE
CUST_YEAR_OF_BIRTH                                 75 NONE
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                    145 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N                    620 HYBRID

25 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT *
  3    FROM      customers_test
  4    WHERE  cust_city = 'Los Angeles'
  5    AND       cust_state_province = 'CA'
  6    AND       country_id = 52790;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |   871 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |   871 |
----------------------------------------------------

8 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT   country_id, cust_state_province, count(cust_city)
  3    FROM     customers_test
  4    GROUP BY country_id, cust_state_province;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |   145 |
|   1 |  HASH GROUP BY     |                |   145 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

SQL>
SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM   USER_STAT_EXTENSIONS
  3  WHERE  TABLE_NAME='CUSTOMERS_TEST';

EXTENSION_NAME
----------------------------------------
EXTENSION
----------------------------------------------------------------------
SYS_STUMZ$C3AIHLPBROI#SKA58H_N
("CUST_CITY","CUST_STATE_PROVINCE","COUNTRY_ID")

SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
("CUST_STATE_PROVINCE","COUNTRY_ID")

SQL>
SQL> SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
  2  FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
  3  WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
  4  AND    e.TABLE_NAME=t.TABLE_NAME
  5  AND    t.TABLE_NAME='CUSTOMERS_TEST';

COL_GROUP                                NUM_DISTINCT HISTOGRAM
---------------------------------------- ------------ ---------------
("CUST_STATE_PROVINCE","COUNTRY_ID")              145 NONE
("CUST_CITY","CUST_STATE_PROVINCE","COUN          620 HYBRID
TRY_ID")

 

Manually creating and dropping a Column Group

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS( USER,'customers_test',
  3    METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
  4                  'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_STATS.DROP_EXTENDED_STATS( 'donghua', 'customers_test',
  3                                    '(cust_state_province, country_id)' );
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM   USER_STAT_EXTENSIONS
  3  WHERE  TABLE_NAME='CUSTOMERS_TEST';

EXTENSION_NAME                           EXTENSION
---------------------------------------- ----------------------------------------------------------------------
SYS_STUMZ$C3AIHLPBROI#SKA58H_N           ("CUST_CITY","CUST_STATE_PROVINCE","COUNTRY_ID")

1z0-060 Upgrade to 12c: ENABLE_DDL_LOGGING changing behaviour in 12c

ENABLE_DDL_LOGGING enables or disables the writing of a subset of data definition language (DDL) statements to a DDL alert log.

The DDL log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database. The DDL log is created only for the RDBMS component and only if the ENABLE_DDL_LOGGING initialization parameter is set to true. When this parameter is set to false, DDL statements are not included in any log.

The DDL log contains one log record for each DDL statement issued by the database. The DDL log is included in IPS incident packages.

There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home.

SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> create table t1 (id number);
create table t1 (id number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop table t1;

Table dropped.

SQL> create table t1 (id number);

Table created.

oracle@solaris:/u01/app/oracle/diag/rdbms/orcl/orcl/log$ cat ddl_orcl.log
diag_adl:drop table t1
diag_adl:create table t1 (id number)

oracle@solaris:/u01/app/oracle/diag/rdbms/orcl/orcl/log$ cat ddl/log.xml
<msg time='2014-08-05T21:20:08.030+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='solaris' host_addr='::1'
version='1'>
<txt>drop table t1
</txt>
</msg>
<msg time='2014-08-05T21:20:09.390+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='solaris' host_addr='::1'>
<txt>create table t1 (id number)
</txt>
</msg>

1z0-060 Upgrade to 12c: Valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB)

Create a PDB by using the seed

Create a PDB in a CDB using the files of the seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB.

Create a PDB by cloning an existing PDB or non-CDB

Create a PDB by cloning a source PDB or non-CDB and plugging the clone into the CDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, or a non-CDB. This technique copies the files associated with the source to a new location and associates the copied files with the new PDB.

Create a PDB by plugging an unplugged PDB into a CDB

Create a PDB by using the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.

Create a PDB by using a non-CDB

Create a PDB by moving a non-CDB into a PDB. You can use the DBMS_PDB package to create an unplugged PDB from an Oracle Database 12c non-CDB. You can then plug the unplugged PDB into the CDB.

http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13551

Upgrade Solaris from 11.1 to 11.2 using pkg update

root@solaris:~# pkg info entire
          Name: entire
       Summary: Incorporation to lock all system packages to the same build
   Description: This package constrains system package versions to the same
                build.  WARNING: Proper system update and correct package
                selection depend on the presence of this incorporation.
                Removing this package will result in an unsupported system.
      Category: Meta Packages/Incorporations
         State: Installed
     Publisher: solaris
       Version: 0.5.11
Build Release: 5.11
        Branch: 0.175.1.0.0.24.2
Packaging Date: September 19, 2012 07:01:35 PM
          Size: 5.46 kB
          FMRI: pkg://solaris/entire@0.5.11,5.11-0.175.1.0.0.24.2:20120919T190135Z

root@solaris:~#   pkg publisher
PUBLISHER                   TYPE     STATUS P LOCATION
solaris                     origin   online F http://pkg.oracle.com/solaris/release/

root@solaris:~# pkg update --accept
------------------------------------------------------------
Package: pkg://solaris/consolidation/osnet/osnet-incorporation@0.5.11,5.11-0.175.2.0.0.42.2:20140624T183842Z
License: lic_OTN

<<<<<<<<<<<<<<<<<<< Ommited length license agreement >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Entire Agreement

You agree that this agreement is the complete agreement for the
Programs and licenses, and this agreement supersedes all prior or
contemporaneous agreements or representations. Any other license terms
that may be included in or with the Program shall not apply.  If any
term of this agreement is found to be invalid or unenforceable, the
remaining provisions will remain effective.

Last updated:  12 May 2014
Should you have any questions concerning this License Agreement, or if
you desire to contact Oracle for any reason, please write:

Oracle America, Inc.
500 Oracle Parkway,
Redwood City, CA 94065

Oracle may contact you to ask if you had a satisfactory experience
installing and using this OTN software download.

            Packages to remove:   6
           Packages to install:  85
            Packages to update: 515
           Mediators to change:   2
       Create boot environment: Yes
Create backup boot environment:  No

DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                            606/606   39003/39003  633.4/633.4  161k/s

PHASE                                          ITEMS
Removing old actions                       7730/7730
Installing new actions                   27495/27495
Updating modified actions                28698/28934
driver (cmdk) upgrade (addition of policy 'tpd_member=true') failed with return code 1
command run was: /usr/sbin/update_drv -b /tmp/tmptiazWh -a -p tpd_member=true cmdk
command output was:
------------------------------------------------------------
Bad policy token: ``tpd_member''.
------------------------------------------------------------
Updating modified actions                28701/28934
driver (fp) upgrade (addition of policy 'tpd_member=true') failed with return code 1
command run was: /usr/sbin/update_drv -b /tmp/tmptiazWh -a -p tpd_member=true fp
command output was:
------------------------------------------------------------
Bad policy token: ``tpd_member''.
------------------------------------------------------------
Updating modified actions                28710/28934
driver (sd) upgrade (addition of policy 'tpd_member=true') failed with return code 1
command run was: /usr/sbin/update_drv -b /tmp/tmptiazWh -a -p tpd_member=true sd
command output was:
------------------------------------------------------------
Bad policy token: ``tpd_member''.
------------------------------------------------------------
Updating modified actions                28712/28934
driver (sgen) upgrade (addition of policy 'tpd_member=true') failed with return code 1
command run was: /usr/sbin/update_drv -b /tmp/tmptiazWh -a -p tpd_member=true sgen
command output was:
------------------------------------------------------------
Bad policy token: ``tpd_member''.
------------------------------------------------------------
Updating modified actions                28934/28934
Updating package state database                 Done
Updating package cache                       521/521
Updating image state                            Done
Creating fast lookup database                   Done

A clone of solaris exists and has been updated and activated.
On the next boot the Boot Environment solaris-1 will be
mounted on '/'.  Reboot when ready to switch to this updated BE.

The following unexpected or editable files and directories were
salvaged while executing the requested package operation; they
have been moved to the displayed location in the image:

  usr/lib/python2.6/vendor-packages/solaris_install/auto_install -> /tmp/tmptiazWh/var/pkg/lost+found/usr/lib/python2.6/vendor-packages/solaris_install/auto_install-20140804T091641Z

---------------------------------------------------------------------------
NOTE: Please review release notes posted at:

http://www.oracle.com/pls/topic/lookup?ctx=E26502&id=SERNS
---------------------------------------------------------------------------

root@solaris:~# init 6

root@solaris:~# uname -a
SunOS solaris 5.11 11.2 i86pc i386 i86pc

root@solaris:~# uname -X
System = SunOS
Node = solaris
Release = 5.11
KernelID = 11.2
Machine = i86pc
BusType = <unknown>
Serial = <unknown>
Users = <unknown>
OEM# = 0
Origin# = 1
NumCPU = 2

root@solaris:~# beadm list
BE        Active Mountpoint Space  Policy Created
--        ------ ---------- -----  ------ -------
solaris   -      -          9.61M  static 2012-11-19 19:56
solaris-1 NR     /          21.94G static 2014-08-04 09:16

root@solaris:~# pkg info entire
          Name: entire
       Summary: Incorporation to lock all system packages to the same build
   Description: This package constrains system package versions to the same
                build.  WARNING: Proper system update and correct package
                selection depend on the presence of this incorporation.
                Removing this package will result in an unsupported system.
      Category: Meta Packages/Incorporations
         State: Installed
     Publisher: solaris
       Version: 0.5.11
Build Release: 5.11
        Branch: 0.175.2.0.0.42.0
Packaging Date: June 24, 2014 07:38:32 PM
          Size: 5.46 kB
          FMRI: pkg://solaris/entire@0.5.11,5.11-0.175.2.0.0.42.0:20140624T193832Z

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.