EM Express Login – Get Flash

I have 12c setup on OEL 6.7 on  OVM and while trying to access EM express, I was receiving the error

em_express_error

Just for the notes, a user can log into EM Express using URL

https://<hostname>:<port>/em

and port details can be found using

lsnrctl status | grep HTTP
or  
select dbms_xdb_config.getHttpsPort() from dual;

Clicking on the “Get Flash” takes you to abode flash page from where you can download the rpm for flash.

[root@oel67-12c Downloads]# ls -lrt
-rw-r--r-- 1 root root 7270732 May 30 11:43 flash-plugin-11.2.202.621-release.x86_64.rpm
[root@oel67-12c Downloads]#
[root@oel67-12c Downloads]# rpm -ivh flash-plugin-11.2.202.621-release.x86_64.rpm
Preparing... ########################################### [100%]
1:flash-plugin ########################################### [100%]
[root@oel67-12c Downloads]#

After installing the rpm I still received the same error.So lets debug

[root@oel67-12c Downloads]# cd /usr/lib64/mozilla/plugins/
[root@oel67-12c plugins]# ls -lrth
total 368K
-rwxr-xr-x. 1 root root 5.2K Jul 25  2010 librhythmbox-itms-detection-plugin.so
-rwxr-xr-x. 1 root root  78K Oct 28  2013 libtotem-narrowspace-plugin.so
-rwxr-xr-x. 1 root root  70K Oct 28  2013 libtotem-mully-plugin.so
-rwxr-xr-x. 1 root root 105K Oct 28  2013 libtotem-gmp-plugin.so
-rwxr-xr-x. 1 root root  97K Oct 28  2013 libtotem-cone-plugin.so
lrwxrwxrwx  1 root root   41 May 30 11:43 libflashplayer.so -> /usr/lib64/flash-plugin/libflashplayer.so

[root@oel67-12c plugins]# ls -lrt /usr/lib64/flash-plugin/libflashplayer.so
-rwxr-xr-x 1 root root 0 Apr 26 20:10 /usr/lib64/flash-plugin/libflashplayer.so

A zero byte file, something is wrong!!! I downloaded the .tar.gz version of the file from adobe and after untar

[root@oel67-12c Downloads]# ls -lrt
total 48064
drwxr-xr-x  6 1001  1001     4096 Apr 26 20:10 usr
-r--r--r--  1 1001  1001     4009 Apr 26 20:10 readme.txt
-rw-r--r--  1 1001  1001 19314752 Apr 26 20:10 libflashplayer.so
drwxr-xr-x  2 1001  1001     4096 Apr 26 20:10 LGPL
-rw-r--r--  1 root root   7270732 May 30 11:43 flash-plugin-11.2.202.621-release.x86_64.rpm
-rw-r--r--  1 root root  20408320 May 30 11:51 install_flash_player_11_linux.x86_64.tar

Hmmm, so the file is ~18MB. Lets copy the file to /usr/lib64/mozilla/plugins/ and change the permissions

[root@oel67-12c plugins]# cp -p /root/Downloads/libflashplayer.so .
[root@oel67-12c plugins]# ls -lrt
total 19232
-rwxr-xr-x. 1 root root     5264 Jul 25  2010 librhythmbox-itms-detection-plugin.so
-rwxr-xr-x. 1 root root    79752 Oct 28  2013 libtotem-narrowspace-plugin.so
-rwxr-xr-x. 1 root root    70904 Oct 28  2013 libtotem-mully-plugin.so
-rwxr-xr-x. 1 root root   106984 Oct 28  2013 libtotem-gmp-plugin.so
-rwxr-xr-x. 1 root root    99176 Oct 28  2013 libtotem-cone-plugin.so
-rw-r--r--  1 1001 1001 19314752 Apr 26 20:10 libflashplayer.so
[root@oel67-12c plugins]# chown root:root libflashplayer.so
[root@oel67-12c plugins]# chmod 775 libflashplayer.so

And, now lets refresh the page and there you go !!!

em_express

 

Tuned-adm and Oracle

“Tuned” in RHEL7/OEL7 is tuning daemon for automatically tuning the system via the use of tuning profile. It can also be configured to react to changes to improve performance of the server and also to make system settings persistent.

“tuned-adm” is a command line tool that provides a number of different profiles to improve performance.

Below are the profiles provided and supported in RHEL 7 :-

  1. throughput-performance
  2. latency-performance
  3. network-latency
  4. network-throughput
  5. virtual-guest
  6. virtual-host

Apart from the provided profile, we can create custom profiles. All the profile are stored in /usr/lib/tuned/ in RHEL7.

The recommended profile for Oracle database workloads is “throughput-performance”.

In my virtualbox, by default “vitual-guest” was set as active profile

[root@racnode1 ~]# cd /usr/lib/tuned/
[root@racnode1 tuned]# tuned-adm active
Current active profile: virtual-guest

If tuned is not installed, install it using yum

#yum install tuned

Enabled tuned to ensure it is started upon boot time

# systemctl enable tuned.service

Start the tuned service

#systemctl start tuned.service

To check the status of tuned service

#systemctl status tuned.service

Now, create new “oracle” profile to be used

1. Create oracle directory –

[root@racnode1 ~]# mkdir /usr/lib/tuned/oracle

2. Create tuned.conf –

[root@racnode1 ~]# cd /usr/lib/tuned/oracle
[root@racnode1 oracle]# vi tuned.conf
[root@racnode1 oracle]# more tuned.conf
#
# tuned configuration
#
[main]
include=throughput-performance

[sysctl]
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
kernel.shmmax = 4398046511104
kernel.shmall = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.panic_on_oops = 1

[vm]
transparent_hugepages=never

[root@racnode1 oracle]#

3. Activate the newly added oracle profile –

[root@racnode1 oracle]# tuned-adm profile oracle
[root@racnode1 oracle]# sysctl -a | grep vm.swappiness
vm.swappiness = 1
[root@racnode1 oracle]# sysctl -a | grep vm.dirty_ratio
vm.dirty_ratio = 80
[root@racnode1 oracle]#

4. To check the profile list –

[root@racnode1 ~]# tuned-adm list

One of the benefits of tuned is the profiles can be applied dynamically.
To configure dynamic tuning behavior, edit the dynamic_tuning parameter in the /etc/tuned/tuned-main.conf file.

If we are using tuned profile (as shown above) which make system settings persistent, it is recommended to remove all the oracle related entries from /etc/sysctl.conf as the entries are susceptible of being overwrittern, replaced or removed all together.

Hope the article is helpful!!

Move Table Partition between Tables

I was recently asked if we can move partition of one table into another? Basically a developers had truncated the partition and the team wanted to move the partition from one table to another.Both the table were structurally the same and had same data.

Here I will be showing how we can do that

1. Create a monthly partitioned table –

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2013','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2013','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2013','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2013','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2013','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2013','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2013','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2013','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2013','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2013','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2013','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2014','dd-mm-yyyy'))
   ) TABLESPACE DEMO
 19  ;

Table created.

2. Insert some data into the table

SQL> insert into sales_part
    select
    'Oracle Enterprise Edition' as product,
     mod(rownum,5) as channel_id,
     mod(rownum,1000) as cust_id ,
     5000 as amount_sold,
     to_date
     ('01.' || lpad(to_char(mod(rownum,6)+4),2,'0') || '.2013' ,'dd.mm.yyyy')
     as time_id
     from dual connect by level commit;

Commit complete.

3. Create Index

SQL> create index idx_sale_local on sales_part (cust_id,time_id)local tablespace DEMO;

Index created.

SQL>
SQL> create index idx_sale_global on sales_part (channel_id)  tablespace DEMO;

Index created.

4. Gathers stats

SQL> exec dbms_stats.gather_table_stats('ANAND','SALES_PART', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.56
SQL> @partition_table
Enter value for table_name: SALES_PART
old  12: WHERE  a.table_name  = UPPER('&table_name')
new  12: WHERE  a.table_name  = UPPER('SALES_PART')
Enter value for owner: ANAND
old  13: AND    a.table_owner = Upper('&owner')
new  13: AND    a.table_owner = Upper('ANAND')

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                LAST_ANAL TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS AVG_ROW_LEN
--------------- ------------------------- ------------------------- --------- ------------------------------ -------------- ----------- ------------ ---------- -----------
.....................
.....................
SALES_PART      SALE_APR                  TO_DATE(' 2013-05-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_MAY                  TO_DATE(' 2013-06-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_JUN                  TO_DATE(' 2013-07-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_JUL                  TO_DATE(' 2013-08-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_AUG                  TO_DATE(' 2013-09-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')

SALES_PART      SALE_SEP                  TO_DATE(' 2013-10-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
......................
.....................


12 rows selected.

5. Lets create another table, exactly the same

SQL> create table sales_part_new
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2013','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2013','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2013','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2013','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2013','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2013','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2013','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2013','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2013','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2013','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2013','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2014','dd-mm-yyyy'))
   ) TABLESPACE DEMO
 19  ;

Table created.

6. Inserted the data using the same insert command,gathered the stats and created index as previously done in step 2,3 and 4.

7. Truncated one of the partition SALE_JUN, from SALES_PART_NEW

SQL> alter table SALES_PART_NEW truncate partition SALE_JUN;

Table truncated.

Now that the data is truncated from partition SALE_JUN of table SALES_PART_NEW, can we move same table partition of SALES_PART to SALES_PART_NEW.

We can use EXCHANGE PARTITION for the same

1. Create an interim table – Same structure without any partition

SQL> create table sales_part_interim
     (product char(25),
     channel_id number,
     cust_id number,
     amount_sold number,
     time_id date)
     TABLESPACE DEMO
    ;

Table created.

SQL> select count(1) from sales_part_interim;

  COUNT(1)
----------
         0

2. Perform Exchange Partition between Interim Table and SALE_JUN partition of SALES_PART.

SQL> alter table sales_part exchange partition SALE_JUN with table sales_part_interim;

Table altered.

Elapsed: 00:00:00.12
SQL> select count(1) from sales_part_interim;

  COUNT(1)
----------
    165000

SQL> @partition_table
Enter value for table_name: SALES_PART
old   8: WHERE  a.table_name  = UPPER('&table_name')
new   8: WHERE  a.table_name  = UPPER('SALES_PART')
Enter value for owner: ANAND
old   9: AND    a.table_owner = Upper('&owner')
new   9: AND    a.table_owner = Upper('ANAND')

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                                              LAST_ANAL TABLESPACE_NAME                  NUM_ROWS
--------------- ------------------------- ------------------------------------------------------- --------- ------------------------------ ----------
SALES_PART      SALE_MAY                  TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS 14-AUG-15 DEMO                               165000
                                          ', 'NLS_CALENDAR=GREGORIAN')

SALES_PART      SALE_JUN                  TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS           DEMO
                                          ', 'NLS_CALENDAR=GREGORIAN')

SALES_PART      SALE_JUL                  TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS 14-AUG-15 DEMO                               165000
                                          ', 'NLS_CALENDAR=GREGORIAN')
.................

3. Rebuild the global indexes on SALES_PART as it would be UNUSABLE.

4. Now the 2nd Exchange Partition to where INTERIM table will be exchanged with the Partition SALE_JUN of SALE_PART_NEW table

SQL> alter table sales_part_new exchange partition SALE_JUN with table  sales_part_interim;

Table altered.

Elapsed: 00:00:00.27
SQL> select count(1) from sales_part_interim;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.01
SQL>
SQL> select count(*) from sales_part_new partition (sale_jun);

  COUNT(*)
----------
    165000

Elapsed: 00:00:00.03
SQL>

5. Check the index status and rebuild the required ones.

Exchange Partition – Exchanging Table Partitions

Recently I came across a requirement of moving table to new tablespace. The table had 12 range based partitions and each partition had ~8K list subpartitions and was storing historic data. After few discussion, it was decided to use exchange partition instead of dbms_redefinition.

As I did not have much practical experience in exchange partition, I thought to test it and I am just posting my test here in the blog

CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE demo,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE demo,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE demo) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2014','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

Table created.

SQL> insert into composite_rng_list values (1,'A','NY',to_date('01/02/2015','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.06
SQL> insert into composite_rng_list values (2,'B','CT',to_date('09/09/2014','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.06
SQL> insert into composite_rng_list values (3,'C','TX',to_date('10/10/2015','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (4,'D','WA',to_date('11/11/2013','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (5,'E','OK',to_date('21/02/2014','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (6,'F','OR',to_date('07/07/2013','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('ANAND','COMPOSITE_RNG_LIST');

PL/SQL procedure successfully completed.


SQL>  select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST'
;

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- --------------- ------------------ ---------- ------------------------------
ANAND     COMPOSITE_RNG_LIST     FUTURE                           3          0 DEMO
ANAND     COMPOSITE_RNG_LIST     PER1                             3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER2                             3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER3                             3          2 DEMO

SQL> select table_owner,table_name,partition_name,subpartition_count from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT
--------- ---------------------- --------------- ------------------
ANAND     COMPOSITE_RNG_LIST     FUTURE                           3
ANAND     COMPOSITE_RNG_LIST     PER1                             3
ANAND     COMPOSITE_RNG_LIST     PER2                             3
ANAND     COMPOSITE_RNG_LIST     PER3                             3

SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows from dba_tab_subpartitions  where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_NA HIGH_VALUE      NUM_ROWS
--------- ---------------------- --------------- --------------- --------------- ----------
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_CENT     'OK', 'TX'          0
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_EAST     'NY', 'CT'          0
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_WEST     'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_CENT       'OK', 'TX'          1
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_EAST       'NY', 'CT'          1
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_WEST       'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_CENT       'OK', 'TX'          1
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_EAST       'NY', 'CT'          1
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_WEST       'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_CENT       'OK', 'TX'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_EAST       'NY', 'CT'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_WEST       'OR', 'WA'          2

Created a local and a global index on the table

SQL> create index i_crl_state on composite_rng_list(cust_state);

Index created.

SQL> create index i_crl_time on composite_rng_list(time_id) local;

Index created.

Index details

SQL> @index
Enter value for owner: ANAND
Enter value for table_name: composite_rng_list

TABLE_OWNER     TABLE_NAME            INDEX_OWNER     INDEX_NAME            TABLESPACE_NAME   NUM_ROWS      CLUST STATUS   INDEX_TYPE
--------------- --------------------- --------------- --------------------- --------------- ---------- ---------- -------- ------------
ANAND           COMPOSITE_RNG_LIST    ANAND           I_CRL_STATE           DEMO                     6          6 VALID    NORMAL
ANAND           COMPOSITE_RNG_LIST    ANAND           I_CRL_TIME                                     6          5 N/A      NORMAL

SQL> @partition_index
Enter value for index_name: I_CRL_TIME
Enter value for owner: ANAND

INDEX_NAME            PARTITION_NA STATUS   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS
--------------------- ------------ -------- --------------- -------------- ----------- ------------ ----------
I_CRL_TIME            PER1         N/A                                                                    2
I_CRL_TIME            PER2         N/A                                                                    2
I_CRL_TIME            PER3         N/A                                                                    2
I_CRL_TIME            FUTURE       N/A                                                                    0

Now lets create a table with which we would be exchanging the partition. Below I have created EXP_TEMP_PER1 table, which will exchange partition PER1 from COMPOSITE_RNG_LIST table. Note table is created as list partition (based on subpartition of original table). In case you create the table same as source table (range-list partition) and try exchange partition you would be hit with

ORA-14292: Partitioning type of table must match subpartitioning type of composite partition


SQL> create table ANAND.EXP_TEMP_PER1 (
   CUST_ID NUMBER(10)
   , CUST_NAME VARCHAR2(25)
   , CUST_STATE VARCHAR2(2)
   , TIME_ID DATE
 ) tablespace TEST
 partition by LIST (CUST_STATE)
 (
     partition PER1_WEST values ('OR', 'WA')
   , partition PER1_EAST values ('NY', 'CT')
   , partition PER1_CENT values ('OK', 'TX')
 )
 ;

Table created.

Insert the data, into the newly created table.

SQL> insert into EXP_TEMP_PER1 select * from composite_rng_list partition (per1);

2 rows created.

Elapsed: 00:00:00.26
SQL> commit;

Commit complete.

SQL> select * from composite_rng_list partition (per1);

   CUST_ID CUST_NAME                 CU TIME_ID
---------- ------------------------- -- ---------
         4 D                         WA 11-NOV-13
         6 F                         OR 07-JUL-13
SQL> select * from EXP_TEMP_PER1 ;

   CUST_ID CUST_NAME                 CU TIME_ID
---------- ------------------------- -- ---------
         4 D                         WA 11-NOV-13
         6 F                         OR 07-JUL-13

Note, the table created has partitions, no subpartitions

SQL> select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='EXP_TEMP_PER1';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- --------------- ------------------ ---------- ---------------
ANAND     EXP_TEMP_PER1          PER1_WEST                        0          2 TEST
ANAND     EXP_TEMP_PER1          PER1_EAST                        0          0 TEST
ANAND     EXP_TEMP_PER1          PER1_CENT                        0          0 TEST

Elapsed: 00:00:00.10
SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows from dba_tab_subpartitions where table_owner='ANAND' and table_name='EXP_TEMP_PER1';

no rows selected

Lets create local indexes similar to source table on the new table

SQL> create index i_exptper1_time on EXP_TEMP_PER1 (time_id) local;

Index created.

SQL> @partition_index
Enter value for index_name: i_exptper1_time
Enter value for owner: ANAND

INDEX_NAME            PARTITION_NA STATUS   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS
--------------------- ------------ -------- --------------- -------------- ----------- ------------ ----------
I_EXPTPER1_TIME       PER1_WEST    USABLE   TEST                   8388608     1048576                       2
I_EXPTPER1_TIME       PER1_EAST    USABLE   TEST                                                             0
I_EXPTPER1_TIME       PER1_CENT    USABLE   TEST                                                             0

Lets try the exchange partition

SQL> alter table COMPOSITE_RNG_LIST exchange partition per1 with table EXP_TEMP_PER1 including indexes with validation update global indexes;

Table altered.

SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows,tablespace_name from dba_tab_subpartitions  where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST' and subpartition_name='PER1';

TABLE_OWN TABLE_NAME             PARTITION_NA SUBPARTITION HIGH_VALUE   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- ------------ ------------ ---------- ---------- ---------------
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_WEST    'OR', 'WA'          2 TEST
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_EAST    'NY', 'CT'          0 TEST
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_CENT    'OK', 'TX'          0 TEST

SQL> select index_name,partition_name,subpartition_name,tablespace_name,status from dba_ind_subpartitions where index_owner='ANAND' and index_name='I_CRL_TIME';

INDEX_NAME            PARTITION_NA SUBPARTITION TABLESPACE_NAME STATUS
--------------------- ------------ ------------ --------------- --------
I_CRL_TIME            FUTURE       FUTURE_CENT  DEMO            USABLE
I_CRL_TIME            FUTURE       FUTURE_EAST  DEMO            USABLE
I_CRL_TIME            FUTURE       FUTURE_WEST  DEMO            USABLE
I_CRL_TIME            PER3         PER3_CENT    DEMO            UNUSABLE
I_CRL_TIME            PER3         PER3_EAST    DEMO            UNUSABLE
I_CRL_TIME            PER3         PER3_WEST    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_CENT    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_EAST    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_WEST    DEMO            UNUSABLE
I_CRL_TIME            PER1         PER1_CENT    TEST            USABLE
I_CRL_TIME            PER1         PER1_EAST    TEST            USABLE
I_CRL_TIME            PER1         PER1_WEST    TEST            USABLE

SQL> select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';


TABLE_OWN TABLE_NAME             PARTITION_NA SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- ------------ ------------------ ---------- ---------------
ANAND     COMPOSITE_RNG_LIST     PER1                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER2                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER3                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     FUTURE                        3          0 DEMO

As we can see above the local indexes status is “USABLE”. For partitions PER3 and PER2 we see status “UNUSABLE” because I did not use “including indexes” attribute in exchange partition for them.

From Oracle Documentation

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX
Then, enable the constraints after the exchange.

If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.

Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

DBA_TAB_SUPARTITIONS shows TEST tablespace, but still the view dba_tab_partitions, show DEMO tablespace for the partition. What needs to be done, so dba_tab_partitions show TEST tablespace for the partition? Please do respond 🙂

Reference

Exchanging Partitions

http://psoug.org/reference/partitions.html

Sangam 2014

I am back from AIOUG meet “SANGAM 14 – Meeting of Minds” and it was a wonderful experience. Had really nice time meeting some old friends and making few new ones :). I think i should mention here, finally i met Amit Bansal , mind behind http://askdba.org/ . It was fun meeting him 🙂

It was 3 day conference with

1st day :- Optimizer Master Class by Tom Kyte. It was full day seminar. Learned some new optimizer features of 12c. We all were very thankful to Tom for being in India for the 3rd time to present in Sangam.

2nd day :- The day started with “What You Need To Know About Oracle Database In-Memory Option” by Maria Colgan. If i need to describe in a word i would say “Awesome!!!!” . I loved every minute of the presentation and was well presented and very informative. Rest of the day i moved from room to room, attending some good sessions on 12c and optimization.

3rd day: – I wasn’t much serious on the 3rd day 😀 . Mostly spent time meeting people around and discussing about oracle. What i loved on 3rd day was an hour session on “Time to Reinvent Yourself – Through Learning, Leading, and Failing” by Dr. Rajdeep Manwani. He shared his life experiences and some truth about human nature. It was amazing.

Overall it was nice to be part of Sangam 14 and learn some really new things mostly on 12c. Thanks to all the speakers and organizing committee for the efforts and valuable time they have put in.

Thanks all!!!!!

Interesting blogs after OOW

It has been long time since i have posted something. After the OOW week and 12c db version being on its way, there have been few blogs which i would recommend to read

http://portrix-systems.de/blog/brost/favourite-oracle-12c-database-features-of-openworld-bloggers/

http://hansforbrich.blogspot.sg/2012/10/toms-12-top-new-features.html

http://dbastreet.com/blog/?p=908

http://momendba.blogspot.in/2012/10/oow-2012-oracle-12c-new-features.html

http://momendba.blogspot.in/2012/10/oracle-rac-12c-new-featues.html

http://uhesse.com/2012/10/01/exadata-x3-key-points/

Happy reading 🙂