Tuesday, September 16, 2014

Bring ASM disk back online after transient failure (with feature Oracle ASM Fast Mirror Resync)

Messages in the ASM alert log:

2014-09-16 22:05:59.529000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12777) secs on ASM inst 1
2014-09-16 22:09:02.620000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12594) secs on ASM inst 1
2014-09-16 22:12:05.702000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12410) secs on ASM inst 1
2014-09-16 22:15:08.793000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12227) secs on ASM inst 1

How to bring it back:

SQL> select name,header_status,mount_status,path from v$asm_disk;

NAME            HEADER_STATU MOUNT_S PATH
--------------- ------------ ------- ----------------------------------------
                MEMBER       CLOSED  /dev/raw/raw1
LODG_0002       UNKNOWN      MISSING
VOL12G          MEMBER       CACHED  ORCL:VOL12G
VOL4G           MEMBER       CACHED  ORCL:VOL4G
LODG_0001       MEMBER       CACHED  /dev/raw/raw2

SQL> alter diskgroup lodg online disk 'LODG_0002';

Diskgroup altered.

SQL> select name,header_status,mount_status,path from v$asm_disk;

NAME            HEADER_STATU MOUNT_S PATH
--------------- ------------ ------- ----------------------------------------
VOL12G          MEMBER       CACHED  ORCL:VOL12G
VOL4G           MEMBER       CACHED  ORCL:VOL4G
LODG_0001       MEMBER       CACHED  /dev/raw/raw2
LODG_0002       MEMBER       CACHED  /dev/raw/raw1

Alternatively, you could run “alter diskgroup lodg online all;”

 

Message you are expecting from ASM alert log:

SQL> alter diskgroup lodg online disk 'LODG_0002'
NOTE: initiating online disk group 2 disks
LODG_0002 (2)
NOTE: process _s000_+asm (5843) initiating offline of disk 2.3915946016 (LODG_0002) with mask 0x7e in group 2
NOTE: sending set offline flag message 1439557556 to 1 disk(s) in group 2
WARNING: Disk LODG_0002 in mode 0x1 is now being offlined
NOTE: initiating PST update: grp = 2, dsk = 2/0xe9689820, mask = 0x6a, op = clear
GMON updating disk modes for group 2 at 36 for pid 23, osid 5843
NOTE: cache closing disk 2 of grp 2: (not open) LODG_0002
NOTE: PST update grp = 2 completed successfully
NOTE: initiating PST update: grp = 2, dsk = 2/0xe9689820, mask = 0x7e, op = clear
GMON updating disk modes for group 2 at 37 for pid 23, osid 5843
NOTE: cache closing disk 2 of grp 2: (not open) LODG_0002
NOTE: PST update grp = 2 completed successfully
NOTE: requesting all-instance membership refresh for group=2
2014-09-16 22:16:58.772000 +08:00
NOTE: F1X0 copy 2 relocating from 2:2 to 2:4294967294 for diskgroup 2 (LODG)
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x11, op = assign
GMON updating disk modes for group 2 at 38 for pid 23, osid 5843
NOTE: cache closing disk 2 of grp 2: (not open) LODG_0002
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: PST update grp = 2 completed successfully
NOTE: requesting all-instance disk validation for group=2
NOTE: disk validation pending for group 2/0xeff868ea (LODG)
2014-09-16 22:17:01.835000 +08:00
NOTE: Found /dev/raw/raw1 for disk LODG_0002
WARNING: ignoring disk  in deep discovery
SUCCESS: validated disks for 2/0xeff868ea (LODG)
GMON querying group 2 at 39 for pid 23, osid 5843
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x19, op = assign
GMON updating disk modes for group 2 at 40 for pid 23, osid 5843
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: group LODG: updated PST location: disk 0002 (PST copy 1)
NOTE: PST update grp = 2 completed successfully
NOTE: membership refresh pending for group 2/0xeff868ea (LODG)
2014-09-16 22:17:04.845000 +08:00
GMON querying group 2 at 41 for pid 13, osid 1845
NOTE: cache opening disk 2 of grp 2: LODG_0002 path:/dev/raw/raw1
SUCCESS: refreshed membership for 2/0xeff868ea (LODG)
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x5d, op = assign
SUCCESS: alter diskgroup lodg online disk 'LODG_0002'
GMON updating disk modes for group 2 at 42 for pid 23, osid 5843
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: group LODG: updated PST location: disk 0002 (PST copy 1)
NOTE: PST update grp = 2 completed successfully
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x7d, op = assign
GMON updating disk modes for group 2 at 43 for pid 23, osid 5843
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: group LODG: updated PST location: disk 0002 (PST copy 1)
NOTE: PST update grp = 2 completed successfully
NOTE: Voting File refresh pending for group 2/0xeff868ea (LODG)
NOTE: F1X0 copy 2 relocating from 2:4294967294 to 2:2 for diskgroup 2 (LODG)
2014-09-16 22:17:07.856000 +08:00
NOTE: Attempting voting file refresh on diskgroup LODG

Sunday, September 7, 2014

Implementing Temporal Validity


oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Sep 7 10:08:36 2014

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

Enter password:
Last Successful login time: Wed Sep 03 2014 22:28:04 +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 table emp_temp as
  2  select employee_id, first_name, salary
  3  from hr.employees
  4  where rownum<4;

Table created.

SQL> desc emp_temp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                        NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
SALARY                                             NUMBER(8,2)


SQL>  alter table emp_temp add period for valid_time;

Table altered.

SQL> desc emp_temp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                        NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
SALARY                                             NUMBER(8,2)

 
SQL> select column_name,data_type from dba_tab_columns where table_name='EMP_TEMP';

COLUMN_NAME          DATA_TYPE
-------------------- --------------------
SALARY               NUMBER
FIRST_NAME           VARCHAR2
EMPLOYEE_ID          NUMBER


SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Donald
Douglas
Jennifer

SQL> insert into emp_temp values (100,'Donghua',5000);

1 row created.

SQL> commit;

Commit complete.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Donald
Douglas
Jennifer
Donghua

SQL>

SQL> update emp_temp
  2  set valid_time_start = to_date('01-JUN-1995','dd-MON-yyyy'), valid_time_end = to_date('15-SEP-2010','dd-MON-yyyy')
  3 where first_name in ('Donald');

1 row updated.

SQL> update emp_temp
  2  set valid_time_start = to_date('01-AUG-1999','dd-MON-yyyy'), valid_time_end = to_date('01-MAR-2012','dd-MON-yyyy')
  3  where first_name in ('Douglas');

1 row updated.

SQL> update emp_temp
  2  set valid_time_start = to_date('20-MAY-1998','dd-MON-yyyy')
  3  where first_name in ('Jennifer');

1 row updated.

SQL> update emp_temp
  2  set valid_time_end = to_date('20-MAY-2017','dd-MON-yyyy')
  3 where first_name in ('Donghua');

1 row updated.

SQL> commit;

Commit complete.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5  order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Donald               01-jun-1995 15-sep-2010
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> select first_name,
  2 to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5 as of period for valid_time to_date('01-JUN-2011')
  6 order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4 from emp_temp
  5 versions period for valid_time
  6  between to_date('01-SEP-1995') and to_date('01-SEP-1996')
  7  order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Donald               01-jun-1995 15-sep-2010
Donghua                          20-may-2017

SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5 order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4 from emp_temp
  5  order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Donald               01-jun-1995 15-sep-2010
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_timestamp('2012-01-01','yyyy-mm-dd'));

PL/SQL procedure successfully completed.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5 order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Jennifer             20-may-1998
Donghua                          20-may-2017


SQL> alter table emp_temp add (Last_Name varchar2(20) default 'Unknown');

Table altered.

SQL> truncate table emp_temp;
truncate table emp_temp
                      *
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
30x0C1618A880x0BD751A000x0C5208FA0


SQL> select * from emp_temp;

EMPLOYEE_ID FIRST_NAME               SALARY LAST_NAME
----------- -------------------- ---------- --------------------
        199 Douglas                    2600 Unknown
        200 Jennifer                   4400 Unknown
        100 Donghua                    5000 Unknown

Wednesday, August 27, 2014

Install Oracle Linux 7.0

clip_image001

clip_image003

clip_image005

clip_image007

clip_image009

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

clip_image011

clip_image013

clip_image015

clip_image017

clip_image019

clip_image021

clip_image023

clip_image025

Saturday, August 23, 2014

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

Thursday, August 7, 2014

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

Tuesday, August 5, 2014

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>