Video : LAG and LEAD : Problem Solving using Analytic Functions

Today’s video gives a quick demo of the LAG and LEAD analytic functions.

There is more information about these and other analytic functions in the following articles.

The star of today’s video is Gwen (Chen) Shapira of Kafka fame!

Cheers

Tim…


Video : LAG and LEAD : Problem Solving using Analytic Functions was first posted on June 24, 2019 at 8:12 (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...)

I lost a database, for the 1st time since 1992.

Well, I lost a pluggable database, not the real instance.  What I did is the following:

ALTER PLUGGABLE DATABASE PDB_NAME UNPLUG INTO ‘/my/dir/PDB_NAME.pdb’;

So far, so good. However, the pluggable database was around 800 GB and was getting unplugged  at the whopping speed of 12 GB per hour. Seeing that this will take forever, I pressed Ctrl-C and killed the “unplug” process. The result was a disaster: database was recorded as unplugged and couldn’t (more...)

ANSI bug

The following note is about a script that I found on my laptop while I was searching for some details about a bug that appears when you write SQL using the ANSI style format rather than traditional Oracle style. The script is clearly one that I must have cut and pasted from somewhere (possibly the OTN/ODC database forum) many years ago without making any notes about its source or resolution. All I can say about (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...)

ORA-14652: reference partitioning foreign key is not supported

SQL> CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)  2    3    4    5    6    7  ;
PARTITION BY REFERENCE(TRAN_DTL_FK)
                       *
ERROR at line 7:
ORA-14652: reference partitioning foreign key is not supported

Note : while creating reference partitioning table , foreign key must be not null

SQL> 
CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER NOT NULL,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)SQL>   2    3     (more...)

“Just One More…”


"So each one of you agrees to disagree with whatever the other
one agrees with, but if you both disagree with the same thing,
aren't you really in agreement?" 
― Norton Juster, The Phantom Tollbooth

Partitions can make DBA life easier; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There’s not much that can go wrong with partitions [ominous silence]… until you (more...)

Enterprise Manager Cloud Control 13.3 Vagrant Build

A little short of a year ago I knocked up a Vagrant build to prepare an environment for practising Cloud Control 13.3 installations and upgrades. At the time, this just automated the creation of the environment and installation of the database, ready for me to start playing around with the Cloud Control bit. At the time I released these articles.

Seattle PostgreSQL Meetup This Thursday: New Location

I’m looking forward to the Seattle PostgreSQL User Group meetup this Thursday (June 20, 2019) at 5:30pm! We’re going to get an early sneak peek at what’s coming later this year in PostgreSQL’s next major release. The current velocity of development in this open source community is staggering and this is an exciting and valuable opportunity to keep up with where PostgreSQL is going next.

One thing that’s a bit unusual about this meetup is (more...)

Oracle database 12c (12.2.0.1.0) step by step installation guide on windows server 2016

Oracle Database 12cR2 installation documentation on Windows Server 2016

Oracle database 12c (12.2.0.1.0) step by step installation guide on windows server 2016

Why you cannot use #Oracle’s SQL Developer to connect to #Exasol

Many of our customers are using Oracle together with SQL Developer, so this question comes up regularly: Can we use SQL Developer also for Exasol?

Short answer is: Unfortunately not.

I tried myself to make that work with no success. Then I found this on Stackoverflow:

Jeff Smith: “No, that’s not supported. SQL Developer’s 3rd party JDBC connectivity is provided for one use case – migrations to Oracle Database.
There’s no support on that for (more...)

Can’t Unnest

In an echo of a very old “conditional SQL” posting, a recent posting on the ODC general database discussion forum ran into a few classic errors of trouble-shooting. By a lucky coincidence this allowed me to rediscover and publish an old example of parallel execution gone wild before moving on to talk about the fundamental problem exhibited in the latest query.

The ODC thread started with a question along the lines of “why isn’t Oracle (more...)

Oracle database 12c (12.2.0.1.0) step by step installation guide on oracle linux 7.6

Installation document :

Oracle Database 12.2.0.1 installation guide

How Insert Statement Works

  • When Oracle receives sql/insert query, it requires to run some pre-tasks before actually being able to really run the query.

 

  • During parsing, Database validate the syntax of the statement whether the query is valid or not.

 

  • Database validate the semantic of the statement. It checks whether a statement is meaningful or not.

 

  • If syntax/Semantic check pass, then server process will continue execution of the query.The server process will go to the (more...)

Oracle car parking

I have been involved over the years with both Microsoft SQL Server licensing and Oracle licensing management and the following image made me chuckle.

Produced by House of Brick who have a lot of interesting content about license management

 

 

 

 

Video : Ranking using RANK, DENSE_RANK and ROW_NUMBER : Problem Solving using Analytic Functions

Today’s video is a run through ranking data using the RANK, DENSE_RANK and ROW_NUMBER analytic functions.

There is more information about these and other analytic functions in the following articles.

The star of today’s video is Chris Saxon, who is one of the folks keeping the masses up to speed at AskTom.

Cheers

Tim…


Video : Ranking (more...)

Shell Scripting Using set -v

set -v : Print shell input lines as they are read.

show_gds_status.sh

#!/bin/sh
##############################
# GDSCTL> configure -width 132
# GDSCTL> configure -save_config
##############################
. ~/gsm1.sh
set -evx
gdsctl -show << END
status
databases
services
exit
END
exit

Excute show_gds_status.sh

[oracle@SLC02PNY GDS]$ ./show_gds_status.sh
gdsctl -show << END
status
databases
services
exit
END
+ gdsctl -show
gsm       : GSM1
TNS_ADMIN : /u01/app/oracle/product/18.0.0/gsmhome_1/network/admin
driver    : jdbc:oracle:thin:
resolve   : QUAL_HOSTNAME
timeout   :  (more...)

JSON_TABLE() and date/time columns in Oracle 19c

While researching the use of JSON in Oracle 19c I came some interesting behaviour that wasn’t immediately obvious (to me). With this post I am hoping to save you a couple of minutes scratching your head when working with JSON_TABLE(). This is Oracle 19.3.0 on Linux and I’m connecting to it using SQLcl 19.1.

Some background

As part of my JSON-support-in-Oracle research I had a good look at JSON_TABLE. Although complex at (more...)

Trouble-shooting

Here’s an answer I’ve just offered on the ODC database forum to a fairly generic type of problem.

The question was about finding out why a “program” that used to take only 10 minutes to complete is currently taking significantly longer. The system is running Standard Edition, and the program runs once per day. There’s some emphasis on the desirability of taking action while the program is still running with the following as the most (more...)