Month: October 2014

Sample script to drop interval partitions

SITUATION

The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts.

SQL> desc user_tab_partitions;

Name                    Null?         Type
----------------------- -------- --------------
TABLE_NAME                         VARCHAR2(30)
COMPOSITE                           VARCHAR2(3)
PARTITION_NAME                     VARCHAR2(30)
SUBPARTITION_COUNT                       NUMBER
HIGH_VALUE                                 LONG
HIGH_VALUE_LENGTH                        NUMBER
PARTITION_POSITION                       NUMBER

SOLUTION

Here is a basic sample script for dropping partitions more than 20 days old.

SQL> set serveroutput on
SQL> declare
     dt date;
     begin
       for x in (select table_name,partition_name, high_value 
                   from user_tab_partitions where INTERVAL='YES' )
       loop
        execute immediate 'select '||x.high_value||' from dual' into dt;
        if dt < sysdate - 20
        then
          dbms_output.put_line('to drop partition: '||x.table_name||'.'||x.partition_name);
          execute immediate 'alter table '||x.table_name||' drop partition '|| x.partition_name||' update indexes ';
        end if;
       end loop;
    end;
/

to drop partition: TBL_TEST1.SYS_P277
to drop partition: TBL_TEST1.SYS_P221
to drop partition: TBL_TEST2.SYS_P231
to drop partition: TBL_TEST2.SYS_P281
to drop partition: TBL_TEST3.SYS_P226
to drop partition: TBL_TEST3..SYS_P242

PL/SQL procedure successfully completed.

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

SITUATION

An interval partition table structure is just created, then try to import the data from exported dump file. It causes ORA-14300 error. The same error occurs when insert records from another online backup table:

SQL> insert into tbl_test select * from tbl_test_old;
insert into tbl_test select * from tbl_test_old;
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

CAUSE

There are NULL values in partition key column.

SOLUTION

Update NULL values of  partition key column with non-null values, or remove those invalid records from source data.

Please note there are possible 1048575 partitions or sub-partitions for the partition key:

$ oerr ora 14300
14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"
// *Cause: The row inserted had a partitioning key that maps to a partition number greater than 1048575
// *Action Ensure that the partitioning key falls within 1048575 partitions or subpartitions.

ORA-24247: network access denied by access control list (ACL)

While executing PL/SQL procedure to send mails by using UTL_SMTP, it fails with error “ORA-24247” in 11g databases.

SOLUTION

ACL needs to be created with following instructions.

DECLARE
acl_path VARCHAR2(4000);
BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
  WHERE host = '*' AND lower_port IS NULL AND upper_port IS NULL;
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'SCOTT','connect')
     IS NULL 
 THEN
     DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'SCOTT', TRUE,    'connect');
  END IF;
EXCEPTION
WHEN no_data_found THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_mail.xml','ACL description',  'SCOTT', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('utl_mail.xml','*');
END;

COMMIT;
SQL> select ACL,PRINCIPAL,PRIVILEGE,IS_GRANT 
       from DBA_NETWORK_ACL_PRIVILEGES;

ACL                    PRINCIPAL  PRIVILEGE IS_GRANT
---------------------- ---------- --------- --------------------
/sys/acls/utl_mail.xml SCOTT      connect   true

ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher (Doc ID 557070.1)

The dataguard broker keeps killing process RSM

Dataguard broker keeps killing RSM process overnight and oncall DBA is pagered by alerts.

DG 2014-10-04-04:17:17 0 2 0 DMON: killing process RSM0, pid = 26742
DG 2014-10-04-04:17:18 0 2 0 DMON: waiting for subscribers to disappear...
DG 2014-10-04-04:17:18 0 2 0 PMON: delete state object for RSM0
DG 2014-10-04-04:17:18 0 2 0 PMON: RSM0 died unexpectedly while processing request 1.1.833218860,
DG 2014-10-04-04:17:18 0 2 0 notifying DMON of RSM failure
DG 2014-10-04-04:17:18 0 2 0 DMON: Creating process RSM0
DG 2014-10-04-04:17:21 0 2 0 RSM0: Attach state object
DG 2014-10-04-04:17:21 0 2 0 DMON: Process RSM0 re-created with pid = 22898

WORKAROUND

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '30'

DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=120;
Property "operationtimeout" updated

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '120'
DGMGRL>

Reference Oracle Doc:
Data Guard: Server Hang And Crash Because RSM0 Keeps Re-spawning (Doc ID 1322877.1)
Cause : The Broker is killing the RSM0 process and restarting it because of an internal timeout that is too low.

 

Interval-Reference Partitioned Tables

Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.

When creating an interval partition in a child table, the partition name is inherited from the associated parent table fragment. If the child table has a table-level default tablespace, then it is used as tablespace for the new interval partition; otherwise, the tablespace is inherited from the parent table fragment.

Interval-reference functionality requires that the database compatibility level (Oracle Database COMPATIBLE initialization parameter setting) be set to greater than or equal to 12.0.0.0.

  • Create a parent table and local index.
SQL>create table reserve 
(
          res_id   number primary key not null,
          res_date date,
          hotel_id number(3),
          guest_id number
)
partition by range (res_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(partition P_FIRST values less than (TO_DATE('1900-01-01','YYYY-MM-DD'))
);

Table created.

SQL> create index idx_res_date on reserve(res_date) local;

Index created.
  • Create a child table.
SQL>create table transactions 
   (
     trans_id number not null,
       res_id number not null,
   trans_date date not null,
          amt number,
   constraint fk_trans_01 foreign key (res_id) references reserve
   )  partition by reference (fk_trans_01);

Table created.
  • Query table and index  partitions.
SQL> select table_name, partition_name, high_value  
      from user_tab_partitions  where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ---------------------------------------------------------
RESERVE         P_FIRST         TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS   P_FIRST

SQL>select INDEX_NAME,PARTITION_NAME,HIGH_VALUE 
      from user_ind_partitions;

INDEX_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------------------------
IDX_RES_DATE    P_FIRST         TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
  • Insert data into parent table
 SQL> insert into reserve values (1,sysdate-5,1,1);

1 row created.

 SQL> insert into reserve values (2,sysdate-4,1,2);

1 row created.

SQL> insert into reserve values (3,sysdate-3,2,3);
1 row created.

SQL> insert into reserve values (4,sysdate,3,3);
1 row created.

SQL> commit;

Commit complete.
  • Query table and index  partitions.
SQL> select table_name, partition_name, high_value  
       from user_tab_partitions  
      where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME  PARTITION_NAME  HIGH_VALUE
----------- --------------- ---------------------------------------
RESERVE     P_FIRST         TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE     SYS_P301        TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE     SYS_P321        TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE     SYS_P341        TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE     SYS_P342        TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE 
       from user_ind_partitions;

INDEX_NAME   PARTITION_NAME HIGH_VALUE
------------ -------------- ---------------------------------------------------------
IDX_RES_DATE SYS_P301       TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P321       TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P341       TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P342       TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE P_FIRST        TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
  • Insert data into child table.
SQL> insert into transactions values(1,1,sysdate-5,1000);
1 row created.

SQL> insert into transactions values(2,2,sysdate-4,2000);
1 row created.

SQL> insert into transactions values(3,3,sysdate-3,3000);
1 row created.

SQL> insert into transactions values(4,4,sysdate,4000);
1 row created.

SQL> commit;
Commit complete.
  • Query tables partitions.
SQL> select table_name, partition_name, high_value  
       from user_tab_partitions  where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- ----------------------------------------
RESERVE           P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE          SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE          SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE          SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE          SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS      P_FIRST
TRANSACTIONS     SYS_P301
TRANSACTIONS     SYS_P321
TRANSACTIONS     SYS_P341
TRANSACTIONS     SYS_P342

10 rows selected.
  • Drop a partition of parent table.
SQL> alter table RESERVE drop partition SYS_P301 update indexes;

Table altered.
SQL> select table_name, partition_name  
       from user_tab_partitions 
      where table_name in ( 'RESERVE','TRANSACTIONS');

TABLE_NAME       PARTITION_NAME
--------------- ---------------
RESERVE                 P_FIRST
RESERVE                SYS_P321
RESERVE                SYS_P341
RESERVE                SYS_P342
TRANSACTIONS            P_FIRST
TRANSACTIONS           SYS_P321
TRANSACTIONS           SYS_P341
TRANSACTIONS           SYS_P342

8 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME from user_ind_partitions;

INDEX_NAME       PARTITION_NAME
--------------- ---------------
IDX_RES_DATE           SYS_P321
IDX_RES_DATE           SYS_P341
IDX_RES_DATE           SYS_P342
IDX_RES_DATE            P_FIRST

We can see both index partition and  child partition are dropped automatically.