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

Partition Pruning with Example

In previous article we saw the benefits of partition pruning.

Partition Pruning in Oracle

Here we will see how partition pruning takes place when you run the query accessing your partitioned table.

For instance , you have transaction table from year 2011 to 2019 with different partition for each year. 

And you want statement of your transaction from year 2017 to 2019. 

In traditional way oracle will scan whole table finds your data (more...)

Partition Pruning in Oracle

Pruning itself means to cut the dead parts and work on the pieces which are indeed important one.

In Oracle partition pruning works in the same manner. Oracle can recognize which partitions and sub-partitions need to be eliminated and which partitions should be accessed for the query to be processed.

When you have created some partition on the table and you are using joins or where condition when accessing the same table , oracle prune (more...)

Types of Index Partitions

In previous article we have seen overview of index partitioning and advantages of it.

In this article we will see example of different types of indexes on partitioned tables.

1.Non-partitioned Index:

We can create normal non-partitioned index on partitioned table.

Example : TRANSACTIONS table is partitioned table in my database.

SQL> CREATE INDEX TRANSACTIONS_IND ON TRANSACTIONS(DATE_OF_TRANSACTION);

Index created.

SQL>

2.Global Range Partitioned Indexes : We can create GLOBAL index on partitioned (more...)

Composite Partitioning in Oracle

In this article we will see examples of various types of composite partitions

1.RANGE-HASH composite partitioning :

SQL> CREATE TABLE TRANSACTIONS_MAIN_COMP_HASH(
  2    TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER,
CONSTRAINT TRAN_MAIN_COMP_HASH_PK PRIMARY KEY(TRAN_ID)
)
PARTITION BY RANGE (DATE_OF_TRANSACTION) SUBPARTITION BY HASH(ACCOUNT_NUMBER) SUBPARTITIONS 4
(PARTITION Y_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION Y_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION Y_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
 PARTITION Y_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION Y_2015  (more...)

Types of Table Partitions in Oracle

In this article we will see types of partitions available in Oracle.

Range Partitioning :

Range Partitioning works with range of column values. It sets lower boundaries and upper boundaries to the partition. It is widely used with DATE data types.

Lets create one range partition to understand it better.It is generally used with DATE columns.

Example :

CREATE TABLE TRANSACTIONS(
  TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER)
PARTITION BY RANGE (DATE_OF_TRANSACTION)
 (PARTITION  (more...)

Index Partitioning

In previous article we saw what is partitioning in oracle and the benefits its brings .

http://oracle-help.com/oracle-database/oracle-partitioning/        

In this article we will see Index partitioning.

Indexes are the essential part of any SELECT and DML operations which we run on the database. So , when we tune the table we must consider index too. Index partitioning is a magnificent feature when it comes to performance. Just like we saw partitioning in (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...)

Connecting Go Lang to Oracle Database

It seems like more and more people are using Go. With that comes the need to  access a database or databases. This blog will show you how to get connected to an Oracle Database and to perform some basic operations using Go.

The first thing you need is to have Go installed. There are a couple of options for you. The first is go download from the Go Lang website, or if you are (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

 

 

 

ORAchk (EXAchk) und der Collection Manager (Teil 2)

Nun geht es mit Teil 2 weiter.

Nach dem nun die APEX Installation fertig ist, geht es darum den Collection Manager zu installieren und zu konfigurieren.

Dazu empfehle ich sich für das Setup parallel die Dokumentation, also das User’s Guide „Oracle® ORAchk and EXAchk“ und hier Kapitel 1.9 Oracle Health Check Collections Manager for Oracle“ zu öffnen.

Wir melden uns nun am Admin Service an:

 

http://o1:8080/apex/apex_admin

Der neue „Workspace“ wird erstellt. Im zweiten Schritt (more...)

ORAchk (EXAchk) und der Collection Manager (Teil 1)

ORAchk & EXAchk sind wichtige Tools im täglichen Oracle DBA Geschäft. Allerdings habe diese Tools wenn man Sie einmalig oder mehrfach startet den großen Nachteil das man jeweils einzelne Reports erhält und diese nicht miteinander vergleichen kann, oder eine Historie erhält. Es bedeutet im Umkehrschluss man muss sich selbst eine „Ordnung“ überlegen und die Verwaltung übernehmen.

Genau an diesem Punkt kommt nun der „Collection Manager“ ins Spiel, der eine komplette APEX Anwendung ist und die (more...)

DBMS_CLOUD Package – A Reference Guide

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

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