Oracle RU Update Assistant

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

Get up and running with the free Oracle Database edition on Windows

Exchange Subpartition in Oracle

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

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

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

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...)

Monitoring Oracle Database using Prometheus

Prometheus is a very popular framework for gathering metrics from a plethora of runtime components, recording, analyzing, visualizing them and making them available for companion technologies such as Grafana. Prometheus harvests information from ‘exporters’ – processes that expose an HTTP endpoint where Prometheus can scrape metrics in a format that it understands.

Recently, I received an email with the following question:

I need help on monitoring Oracle Database using Prometheus. I Googled but not find (more...)

Read-Only Partitions in Oracle

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

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

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

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

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

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

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

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...)

Importance of setting Fetched Rows size for Database Query using Golang

When issuing queries to the database one of the challenges every developer faces is how to get the results quickly. If your queries are only returning a small number of records, eg. < 5, then you don’t really have to worry about execution time. That is unless your query is performing some complex processing, joining lots of tables, etc.

Most of the time developers are working with one or a small number of records, using (more...)

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

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"

How to install Oracle Database 18c XE on Linux

The new version of the free Oracle Database edition, Oracle Database 18c Express Edition, just got released for Linux 64-bit. Getting started is really simple on Oracle Linux, basically a three step process of downloading the RPM file, installing it and then configuring the database. On other Red Hat  compatible Linux distributions you will also have … Continue reading "How to install Oracle Database 18c XE on Linux"

Oracle Database 18c Express Edition is Generally Available!

Oracle just released the new version of Oracle Database Express Edition (XE), a free edition of Oracle Database. This release means something special to me, as I have been closely involved in it and with the great team that brought us Oracle Database 18c XE. It has been 7 years since its predecessor, Oracle Database … Continue reading "Oracle Database 18c Express Edition is Generally Available!"

using AWR on Exadata

Oracle released a brand new Whitepaper for Exadata

How to use AWR Report on Exadata Machines

This Whitepaper gives details how the AWR reports can be used in conjunction with Exadata to monitor and analyze database performance issues

http://www.oracle.com/technetwork/database/availability/exadata-maa-best-practices-155385.html