Services or Service names in Oracle specify one or more names that clients can use to connect to an Oracle Database. It is a logical representation of a database. When an instance starts, it registers itself with the listener using one or more service names. They are a good way of separating various application connections […]
With the announcement of 26ai on premises, many people (myself included) using 19c will be looking to migrate their existing databases over to 26ai. As announced earlier, 19c is the last release in which the option to use a non-CDB is allowed; from 21c onwards, it’s mandatory to change your database architecture to multitenant. Therefore, anyone
Create a pluggable database(19c) from a non-container database. Read More »
In this post, I explain with examples the new oracle database 26ai feature “Group by Alias and group by All” I also discuss using the Having clause and Rollup as well as what to watch out for.
Group by Alias and Group by All in Oracle Database 26ai Read More »
A quick guide to Oracle 26ai’s extended VALUES constructor, showing how to insert, update, and merge multiple rows with cleaner, simpler SQL.
Table Values Constructor In Oracle Database 26ai Read More »
BACKGROUND Consider this problem from LeetCode: leetcode_db_1158, about figuring out for each user, the number of orders they made in 2019. The code below can create the sample data set needed for the question. Here’s an Oracle SQL solution to this problem Notice the left join to a subquery highlighted above! This is done to
Conditional Filter For Aggregates In Oracle Database 26ai Read More »
Introduction As an IT professional, especially one working closely with data, surely, almost definitely, you have been asked a question from the users along the lines of “Can we know what the value was of col1 in the table t1 at 25:00 on Thursday, the 30th of February 8000 years ago?” (Silly, but that’s really
Flashback Data Archive: Recreating History, After The Fact Read More »
Background Story time When I just started working as a DBA, the Senior Database Administrator was taking me through the environment and how everything was set up, including all the servers, databases, applications, and more. He was talking about one of the purchased applications that runs in production, and then explained that the vendor had
BACKGROUND Working in IT is one of those things: you love it, but sometimes it can annoy the hell out of you, and that is extremely frustrating. You wake up in the morning, come to work, have plans of what you’re going to do, and then. All hell breaks loose. It can be exciting, and
The ORA-01017 invalid username/password; logon denied Read More »
Background Oracle AI Database 26ai provides new, easier and self documenting syntax for insert statements: Non-positional Inserts. This makes use of the INSERT INTO SET and BY NAME clauses The SET clause works similarly to the existing set clause for update statements, and the by name clause makes it easier to insert the results of
Non-Positional Inserts in Oracle AI Database 26ai Read More »
The Qualify clause in Oracle database 26ai makes it easier to filter on the results of window functions. See an example in this blog post.
Easier filtering of window functions with the Qualify clause in oracle AI Database 26ai Read More »
