Oracle RU Update Assistant

Uncategorized
| Sep 16, 2019

It is sometimes difficult to find the right Metalink note and then the correct RU update.

Now there is the interactive Oracle Assistant DOC ID 2118136.2 which is very helpful and offers quick help.

Just try it out and be amazed. It is very helpful

 

How to install Oracle Database 18c XE on Windows

Uncategorized
| Aug 19, 2019
Get up and running with the free Oracle Database edition on Windows

Exchange Subpartition in Oracle

Uncategorized
| Aug 11, 2019

We can exchange subpartition in oracle the same way we exchange partition.Let’s see few examples.

1.Exchange subpartition of *-Hash partitioned table :

Here I took example of List-Hash Partitioning method.

SQL> CREATE TABLE LIST_HASH
 (CITY_CD CHAR(2),
 CITY_NAME VARCHAR2(50),
 STATE_CD CHAR(2),
 ZONE_CD CHAR(2))
 PARTITION BY LIST(STATE_CD) SUBPARTITION BY HASH(ZONE_CD)
 (PARTITION P1 VALUES('GJ')
 (SUBPARTITION P1_H1,
  SUBPARTITION P1_H2) ,
  PARTITION P2 VALUES('RJ')
  (SUBPARTITION P2_H1,
   SUBPARTITION P2_H2))  2    3    4    5    6    7    8    9   10   11    (more...)

Exchange Partition of *-List and *-Range Partition

Uncategorized
| Aug 11, 2019

1.Exchange Partition of *-List Partition

Here I took example of Range-List partitioning method.

SQL> CREATE TABLE LIST_COMP_PART
  2      (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY RANGE(AGE) SUBPARTITION BY LIST(DEPT_ID) 
(PARTITION P_R1 VALUES LESS THAN(25)
(SUBPARTITION P_R1_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R1_L2 VALUES('HRM','MAR')
),
PARTITION P_R2 VALUES LESS THAN(35)
(SUBPARTITION P_R2_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R2_L2 VALUES('HRM','MAR')
),
PARTITION P_R3 VALUES LESS THAN(MAXVALUE)
(SUBPARTITION P_R3_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R3_L2 VALUES('HRM','MAR')
));  3    4    5    6    7    8     (more...)

Exchanging a Partition of an Interval Partitioned Table

Uncategorized
| Aug 10, 2019

Let’s create Interval-Partitioned table and exchange partitions.

Step 1 : Create Interval Partitioned Table

SQL> CREATE TABLE TRAN_ALL
  2  (TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER
  )
PARTITION BY RANGE(DATE_OF_TRANSACTION) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P1 VALUES LESS THAN(TO_DATE('01-01-2019','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN(TO_DATE('01-02-2019','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN(TO_DATE('01-03-2019','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN(TO_DATE('01-04-2019','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN(TO_DATE('01-05-2019','DD-MM-YYYY')),
PARTITION P6 VALUES LESS THAN(TO_DATE('01-06-2019','DD-MM-YYYY')))
;

Let’s insert some records in TRAN_ALL table. I have staging table which (more...)

Virtual Column Partitioning in Oracle

Uncategorized
| Aug 10, 2019

Oracle 11g came up with concept of Virtual Column which is created with expression. Which does not stores data physically rather it calculates at run time and give us output when we fetch using query.

We saw different types of partitioning in previous posts, today we will see how we can partition the table using virtual column.

To see Types of Partitioning :

Let’s create a table using virtual (more...)

Read-Only Partitions in Oracle

Uncategorized
| Aug 8, 2019

Read-Only partitions are of benefits

1.When you are working in Data-Warehouse environment there must be many partitions which are not updated frequently you can add those partitions to read-only state and then add those partitions to read-only tablespace. These read-only tablespace can help you take your RMAN backup faster as read-only tablespace can be avoided to be backed up in incremental backups.

2.By putting partitions to read-only state you can perform maintenance activity.

(more...)

Interval-* Composite Partitioned Tables

Uncategorized
| Aug 8, 2019

In my previous composite partitioning in oracle article I saw example of various types of composite partitioning methods. In addition to that post today we will see composite partitioning with INTERVAL partitioning type.

To read about previous composite partitioning :

In Interval-* composite partitioning method we will see Interval-Range , Interval-List , Interval-Hash.

1.Create a Interval-range composite partitioned table

SQL> CREATE TABLE TRAN_INTERVAL_range
(TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2))
PARTITION  (more...)

Modify Partitioned Table to Non-partitioned Table

Uncategorized
| Aug 6, 2019

In previous post we saw modifying non-partitioned table into partitioned one.Today we will see how we can convert partitioned table into non-partitioned table.

To read non-partitioned table to partitioned table:

Step 1 : Lets check first table is partitioned one.

SQL> SELECT TABLE_NAME,PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME															 PAR
-------------------------------------------------------------------------------------------------------------------------------- ---
TRANSACTIONS															 YES

Step 2 : To convert partitioned table to non-partitioned one we need to take backup and (more...)

Adding Table Partition in Oracle

Uncategorized
| Aug 5, 2019

Here we will see examples to add partition in all different types of partitioning methods.

1.Add Partition in Range-Partitioned table 

SQL> ALTER TABLE TRANSACTIONS ADD PARTITION Y_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'));

Table altered.

2.Add Partition in Hash-Partitioned table

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRAN_HASH                      SYS_P121
TRAN_HASH                      SYS_P122
TRAN_HASH                      SYS_P123
TRAN_HASH                      SYS_P124
TRAN_HASH                      SYS_P125

5 rows selected.

SQL> ALTER TABLE TRAN_HASH ADD PARTITION SYS_P126;

Table altered.

SQL>  SELECT  (more...)

GoLang: Inserting records into Oracle Database using goracle

Uncategorized
| Jul 15, 2019

In this blog post I’ll give some examples of how to process data for inserting into a table in an Oracle Database. I’ve had some previous blog posts on how to setup and connecting to an Oracle Database, and another on retrieving data from an Oracle Database and the importance of setting the Array Fetch Size.

When manipulating data the statements can be grouped (generally) into creating new data and updating existing data.

When (more...)

impdp with parameter „cluster and parallel“ for a RAC Cluster

Uncategorized
| Jul 12, 2019

For using the „parallel“ parameter during an import (impdp) on a Oracle RAC Cluster you need to prepare your environment.

The „parallel“ parameter works correctly when you do the following:

– mount point were the export dump resides must be available on ALL cluster members

– create a Service on the database for the impdp job

srvctl add service -s impdp_service -d xdb1 -pdb xpdb1 -preferred xdb11,xdb12 -available xdb13

srvctl start (more...)

GoLang: Querying records from Oracle Database using goracle

Uncategorized
| Jul 8, 2019

Continuing my series of blog posts on using Go Lang with Oracle, in this blog I’ll look at how to setup a query, run the query and parse the query results. I’ll give some examples that include setting up the query as a prepared statement and how to run a query and retrieve the first record returned. Another version of this last example is a query that returns one row.

Check out my previous post (more...)

Oracle Developer Tools for Visual Studio Code

Uncategorized
| Jun 20, 2019
Since yesterday Oracle published the Visual Studio Code Extension "Oracle Developer Tools for VS Code" https://www.oracle.com/database/technologies/appdev/dotnet/odtvscodequickstart.html

I am normally not a Microsoft guy, but I have to admit that the Visual Studio Code from Microsoft is really cool, even on my Ubuntu Laptop.

In order to setup the Oracle Developer Tools for VS Code on Ubuntu you will need to complete following pre-requirements:

Install at first the .NET Core 2.2 SDK (more...)

How to enable auto start/shutdown for Oracle Database 18c Express Edition

Uncategorized
| Oct 30, 2018
Oracle Database 18c Express Edition can be enabled for automatic startup and shutdown with the operating system. This will not only allow you not to worry about starting and stopping the database but it will also ensure that the database is properly shutdown before the machine is powered off. Enabling auto startup/shutdown is done via … Continue reading "How to enable auto start/shutdown for Oracle Database 18c Express Edition"

DBMS_CLOUD Package – A Reference Guide

Uncategorized
| Jul 12, 2018

The Appendix A of the Using Oracle Autonomous Data Warehouse Cloud guide describes the DBMS_CLOUD package. Unfortunately, it documents only a subset of the subroutines. And, for some of them, the description could also be enhanced. Therefore, while I was testing all the subroutines the DBMS_CLOUD package provides, I took a number of notes. By the end of my tests, I got what I can call my personal reference guide to the package. Since it (more...)

Beware of loopback MTU size with RAC on Oracle Linux 7

Uncategorized
| May 12, 2018

I recently had to troubleshoot a hung instance in a 2 node RAC system. 4 months earlier, the system was reinstalled in a rolling fashion due to the requirement of Linux Upgrade from Oracle Linux 5 to Oracle Linux 7. This was required because of lack of certification for a storage migration to an AllFlash Storage. The system has been stable when running with Oracle Linux 5 for several years. Around 4 months after the (more...)

Patch Recommendations for Oracle Database 12cR1 and Cloud Control 13cR2

Uncategorized
| May 11, 2018

During my consulting engagements I see a lot of systems and many bugs. Most of the time, there is already a patch available to avoid the bug. I have collected all the recommended patches for the Oracle Database 12.1.0.2 (SE2 and EE) and Oracle Enterprise Manager 13cR2. This should help to avoid most critical known issues. Versions 12.2.0.1 and 18c will be added later this year.

Patch Recommendations

BAAS: Battle Against Archiver Stuck – ALTERNATE Archive Location

Uncategorized
| Apr 27, 2018

If you are managing volatile systems, you might have encountered Archiver-Stuck in the past.

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1789 not archived, no available destinations

If this is the case, you might be interested in a rarely used feature called “ALTERNATE Archive Locations”. This is a second independent archive log destination, that is only used in case the primary destination is failing (full). In that case, the dest_2 is automatically (more...)

Modifications to hidden parameters between 12.1.0.2 and 12.2.0.1

Uncategorized
| Apr 26, 2018

I recently troubleshooted an issue with RMAN catalog database using seperate schemas for each target database and more than 300 schemas present. Catalog Database was on 12.2.0.1 and suffering from severe mutex contention because of huge version counts for RMAN catalog SQL statements. I then realized that the default number of max child cursors was increased from 1024 to 8192 in 12.2.0.1. This made me check what other parameters (more...)