DBA Blogs

----- In Oracle 12.1.0.2, created the wallet by using below commands:

TEST$ orapki wallet create -wallet "/u01/app/oracle/admin/TEST/wallet" -pwd ****  -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.


TEST$ mkstore -wrl "/u01/app/oracle/admin/TEST/wallet" -createCredential TEST2 sys ********
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1

----- But when I logged into the database with sys user, the show user showed OPS$ORACLE user instead of sys:

TEST$ sqlplus /@TEST2

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 9 13:09:38 2018

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

Last Successful login time: Thu Aug 09 2018 03:18:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> sho user
USER is "OPS$ORACLE"
SQL>

----- So made following changes and it worked fine:

Put following entry in sqlnet.ora file:

SQLNET.WALLET_OVERRIDE = TRUE
The SQLNET.WALLET_OVERRIDE entry allows this method to override any existing OS authentication configuration.

and used mkstore to create the wallet:

TEST$  mkstore -wrl "/u01/app/oracle/admin/TEST/wallet" -createCredential TEST2 sys

Range Partitioning allows you to separate a logical table into a number of distinct physical segments, each segment holding data that maps to a range of values.
(I encourage you to read the Introduction in the first post in this series)

The simplest and most common implementation is Range Partitioning by a DATE column.

SQL> l
  1  create table sales_data
  2  (sale_id number primary key,
  3   sale_date date,
  4   invoice_number varchar2(21),
  5   customer_id number,
  6   product_id number,
  7   sale_value number)
  8  partition by range (sale_date)
  9  (partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY'))
 10   tablespace TBS_YEAR_2015,
 11  partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY'))
 12   tablespace TBS_YEAR_2016,
 13  partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY'))
 14   tablespace TBS_YEAR_2017,
 15  partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
 16   tablespace TBS_YEAR_2018,
 17  partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
 18   tablespace TBS_YEAR_2019,
 19  partition P_MAXVALUE values less than (MAXVALUE)
 20   tablespace USERS
 21* )
SQL> /

Table created.

SQL> 

Here, I have created each Partition in a separate tablespace.  Note that the Partition Key (SALE_DATE) does not have to be the same as the Primary Key (SALE_ID)

I have also created a MAXVALUE Partition  (Some DBAs/Developers may mistakenly assume this to be a *default* partition.  Range Partitioning, unlike List Partitioning, does not have the concept of a "default" partition.  This simply is the Partition for incoming rows that have Partition Key value that is higher than the last (highest) defined Partition Key Upper Bound (31-Dec-2019 23:59:59 in this case)).

I can look up the data dictionary for these partitions in this manner :

SQL> select partition_name, tablespace_name   
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
P_2015          TBS_YEAR_2015
P_2016          TBS_YEAR_2016
P_2017          TBS_YEAR_2017
P_2018          TBS_YEAR_2018
P_2019          TBS_YEAR_2019
P_MAXVALUE         USERS

6 rows selected.

SQL> 

Partitions are ordered by Partition *Position*  not Name.

How do I add a new partition for data for the year 2020 ?  By "splitting" the MAXVALUE partition.

SQL> alter table sales_data                 
  2  split partition P_MAXVALUE
  3  at (to_date('01-JAN-2021','DD-MON-YYYY'))
  4  into
  5  (partition P_2020 tablespace TBS_YEAR_2020, partition P_MAXVALUE)
  6  /

Table altered.

SQL> 
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME         HIGH_VALUE
------------------------------ ---------------------------------------------
P_2015          TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD
P_2016          TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD
P_2017          TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD
P_2018          TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
P_2019          TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD
P_2020          TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD
P_MAXVALUE         MAXVALUE

7 rows selected.

SQL> 
SQL> l
  1  select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4* order by partition_position
SQL> /

PARTITION_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
P_2015          TBS_YEAR_2015
P_2016          TBS_YEAR_2016
P_2017          TBS_YEAR_2017
P_2018          TBS_YEAR_2018
P_2019          TBS_YEAR_2019
P_2020          TBS_YEAR_2020
P_MAXVALUE         USERS

7 rows selected.

SQL> 

Note that, irrespective of the data format I specify in the CREATE or SPLIT commands, Oracle presents the Upper Bound Date (HIGH_VALUE) in it's own format, using a Gregorian Calendar.

How do I remove an older partition ?

SQL> alter table sales_data
  2  drop partition P_2015
  3  /

Table altered.

SQL> 

A DROP command is very simple.

In my next post, I will add Indexes to this table.

I am beginning a new series of Blog Posts on Partitioning in Oracle.  I plan to cover 11g and 12c.   I might add posts on changes in 18c  (which is really 12.2.0.2 currently)

First, this is my presentation at AIOUG Sangam 11
and this the corresponding article

This series of posts will have new examples, from the simple to the complex, not present in the above presentation / article.