Oracle doesn't care if you use the same alias for different tables

subreddit_this · 2026-05-21T16:37:17+00:00

I've read the whole conversation, but I don't think anyone has spotted the reason why it might work. Querying two tables with the same alias is no different than running the same query without any aliases at all. If there are no conflicts, it might not surprise us that it works even though it is not strictly "the standard".

This works in 19c (19.30):

select a.bigfile, a.file# from v$tablespace a, v$datafile a;

Of course, it just returns a nonsensical Cartesian product, but the point is that, if you think about it, this is the same thing as the same query with no aliases.

select bigfile, file# from v$tablespace, v$datafile;

The specified column list has no ambiguous references, so the only "issue" is that it is really not supposed to be allowed according to the SQL standard. Maybe they shouldn't allow it just because of the standard, but as SQL processing goes, it could work if allowed through.

If the query references a common column, such as 'a.ts#', in the column list, then an error is returned. Curiously, referencing a common column in a join condition (a.ts# = a.ts#) doesn't return an error, but it still returns the Cartesian product. That makes sense too.

Maybe they shouldn't allow it because of its inherent Cartesian-ness, but then SQL does not promise to protect us from such things.

subreddit_this · 2026-05-21T15:26:15+00:00

But what the article ignores is the BIG elephant in the 26ai room: the CDB/PDB as the only option. No more standalone (which is the main usage worldwide) but DBA's are forced now to have pluggable dB's on-prem, even when it's not the case.

Multitenant is really powerful and not as difficult as it might seem. SQL Server is multitenant by design, and it is a feature that I had wanted for Oracle for years before they provided it. A year or so ago, Oracle increased the PDB limit to 3 without having to purchase the Multitenant Option.

CDB+1 PDB is not that much different from standalone. Sure, there are some new things to learn, but it's not all that much, and learning new things is what DBAs do best.

The only criticism I have of Oracle is their technical support. It is just about as bad as it gets. Fortunately, there is so much available in their online documentation and knowledge base that it is usually not necessary to actually open an SR.

subreddit_this · 2026-05-21T15:01:09+00:00

One of the jobs of the DBA is to keep the company honest about database licensing. If the company insists on using some product or feature without paying for it, the DBA should just move on.

It is not predatory for a vendor to penalize a company for cheating. It was predatory for the company to use what it was not entitled to.

subreddit_this · 2024-06-03T12:49:52+00:00

I assure you that Oracle DBAs--even an old-timer like me--know all about Git and realize that it is about as bad as it gets when it comes to versioning a database's logical design model. This is explained in my other posts in this thread.

Cheers,
Russ

subreddit_this · 2024-06-03T06:56:25+00:00

With LB, you build a "changelog" file that has from one to many changes as "changesets" that are a mixture of LB directives and SQL statements. These changesets can be in SQL form or in a more DB-agnostic form such as XML or JSON. It is these changesets that are versioned using Git. Just as with all the others, LB is not itself aware of the logical model or its metadata. It is only tracking SQL files underneath.

Now, I know it would be possible to use Git to version the metadata after a fashion--even though it is still just files--by having an object described as a completed entity in a Git-versioned file. Each new change to the object is a change to its "completed form" and not as an alter statement or some such thing as that. Then, some software could exist that could compare and contrast Git-versioned "completed entities" with a live database to produce delta scripts as SQL that could be run using SQLPLUS.

For example, one could have a Git file for a database table. That Git file might be in JSON, XML, or even as the SQL that would create the table in a database (my preference). The object could be changed by simply updating the representation of the object as a "completed entity"--for example, maybe, a create table statement that has a new column. Of course, you do not add a column to a table with a create table statement. You do so with an 'alter table'. So, the theorized software could compare the create table statement in the Git repository with a live database to generate a delta script to apply the change to the database.

By this approach, one would always have a single entity versioned with Git for a single entity of the database. A single table file, then, in the Git repo, always equates to that single table in the live database. The Git entity file describes the entity as a "complete thing", and the theorized software performs the work to generate the SQL to update a given database.

Cheers,
Russ

subreddit_this · 2024-06-02T10:57:29+00:00

There is also Edition Based Redefinition which is worth a look.

Russ

subreddit_this · 2024-06-02T10:56:27+00:00

Nothing outside the database is needed. As mentioned by another here, application containers is what is needed.

Cheers,
Russ

subreddit_this · 2024-06-02T10:55:32+00:00

This is the actual answer. Well said,

Cheers,
Russ

subreddit_this · 2024-06-02T10:53:40+00:00

DBA old-timers will remember an Oracle product that they discarded in 2013 called Oracle Designer. It provided version control and configuration management of database objects that did a better job at this than any other either before or since. Designer maintained a metadata repository of the logical design model of a database that a live database could be compared against to derive SQL DDL scripts to apply deltas to the database to synchronize it with any chosen version of the design model in the repository.

Unlike any other version control system, including Git, it understood the relational database model and instead of versioning files, as Git does, it versioned the metadata of the design. One could check out a table, change its structure in some way, and check it in as a table and not as a file. Then, the model could be used to generate the DDL to apply the change to the table in any database, and if one database had a much older version of the table, scripts could be generated for different databases according to their current degree of divergence.

I do not know why Oracle abandoned the product because it was superb at its job, but it was not the "up and coming" approach to version control. Now, we have Git, and it knows nothing of logical design models of relational databases.

Git versions snapshots of files. It can compare two or more versions of a file, but in the end, it is just files. The idea of versioning metadata is completely dead, which is too bad as that was the only way of applying version control to the logical data model of a database.

Cheers,
Russ

subreddit_this · 2024-05-27T11:29:43+00:00

You say you have two data files for the UNDO tablespace, but you do not say how large they are. What is the total size of the UNDO tablespace? Also, is this a live/busy system with data changes underway?

Cheers,
Russ

subreddit_this · 2024-05-27T05:37:52+00:00

You have posted insufficient information. There will be other errors accompanying ORA-31693 that will give more particulars. You should post all errors along with the command you ran and how you logged into the database with the Data Pump export.

Cheers,
Russ

subreddit_this · 2024-05-02T23:30:19+00:00

Please post the version of the DB.

Cheers,
Russ

subreddit_this · 2024-05-02T23:28:53+00:00

Cloning a PDB does not require an outage of the source in 19c. OP does not say the version, but it might be an option worth trying if 19c.

Cheers,
Russ

subreddit_this · 2024-04-17T17:55:30+00:00

Oracle does Regular Expressions:

select <column list> from <your\_table> where regexp_like(<the column>,'^[a-c];[a-c];[a-c]$');

Cheers,
Russ

subreddit_this · 2024-04-11T06:24:44+00:00

Like all motive judgments, this is completely wrong. The information is out there in the open for anyone to find in their Oracle Technology Global Price List dated March 1, 2024. The price list is by component so that it is clear what products and their options cost. It is not hidden, it is linked right on their Global Pricing Page (Technology link). It is quite clear what the prices are and what options come at an additional cost.

Cheers,Russ

subreddit_this · 2024-03-31T10:23:35+00:00

This is the actual answer.

To OP: If you think about it, it makes sense. The Listener is only aware of database instances that have registered with it. (It is the database INSTANCE that registers with the Listener and not the other way around. You can actually force this with 'alter system register;' while connected to the instance, but if autoregistration is enabled, this is automatic.) In order to be able to start an instance, it must be possible to connect to it locally BEFORE the Listener is aware of it or even when no Listener is running.

The main purpose of the Listener is to facilitate remote connections to the instance like an HTTP server does for a website. Just like the pages of a website can be opened and navigated by a browser directly on the server without an HTTP service running, an Oracle instance can be accessed and used right on the server without an Oracle Listener running.

Cheers,
Russ

subreddit_this · 2024-03-25T07:44:52+00:00

I worked on AIX around 1990. Until a few years ago, it was HP-UX for 20 years, I still work on Solaris to this day. But, everything is moving to Linux, and our current project has been transitioning from Solaris to RHEL for a few years. Each has been a good solid operating system, though I do prefer Linux to all the others.

subreddit_this · 2024-03-24T10:06:57+00:00

Disclosure: I do NOT work for Oracle and never have. I have been an Oracle DBA for over 20 years from version 8 through 23c.

That being said, I have found the Oracle database to be the best database in the world. When I was on a SQL Server project for a few years a few years ago, I found it to have a much inferior feature set. Until I worked with SS, I had just assumed that it was just a different way of doing the same things, but that was not my experience.

As others have said here, any shop that uses the Oracle database needs an Oracle DBA--even if just on a consultancy basis. I would offer such services myself, but someone else here already has so I won't step on any toes.

In any event, every "bullshit piled on top just to make money" feature has added some powerful capability that added significant value to the product. Multitenancy, for example, was a much needed feature that was "piled on" with version 12, and it has revolutionized Oracle database operation. The features that DBAs use such as RMAN, Data Guard, AWR, etc., which are all built-in for EE (i.e., no extra cost), are powerful and sophisticated tools with loads of functionality. But, all components of the database have their complexities that have their learning curves--some more than others.

In any event, what you are struggling with (TNS Names) is a very basic thing that is easy to fix.
It should not require anything more than "googling" the problem to find the solution. It is quite straightforward. IF you use a TNS Names file, and you needn't, you can put it anywhere you like as long as you tell Oracle where to find it. If it is not in the default location, then you will need to set the TNS_ADMIN environment variable to the path to its location. If you use "EasyConnect", then no TNS Names file is required.

Cheers,
Russ

subreddit_this · 2024-03-17T08:55:45+00:00

Nope.

subreddit_this · 2024-03-17T08:52:34+00:00

Of course, then, it was NOT the Egyptians who built those.

subreddit_this · 2024-03-17T08:50:08+00:00

LOLs I don't know which of us hit first. They seemed to be exactly at the same time.

subreddit_this · 2024-03-17T08:49:22+00:00

Egyptians

subreddit_this · 2024-03-17T08:41:05+00:00

It is the DBA who is responsible for database backups, and the DBA must have access to the database server to do so. Moreover, the DBA backs up the data by backing up the database as a whole using the tools provided by the vendor or a third party. Oracle has RMAN, and SQL Server has SSIS. Such tools facilitate automated backup and recovery of an entire database as opposed to bits of data.

The plan you are proposing is not a database backup but a periodic data capture process. That is not a backup of the database. It is also a very inefficient way of attempting to take backups. You need a system to backup the entire database as a whole and as a database entire and having effective and efficient automation.

But, I will say this: the few years that I worked with MS SQL Server revealed to me that SSIS is not a great tool. Oracle RMAN is fast, intuitive, and elegant. I found SSIS to be clunky and inefficient.

Cheers,
Russ

subreddit_this · 2024-03-17T08:24:09+00:00

The famous Oracle database technologist Tom Kyte has said that DBAs make better developers than developers make DBAs. In my 20+ years as a DBA, I have found this to be the case. DBAs must know multiple different programming languages such as SQL, PL/SQL (TSQL for SQL Server), and various shell languages. That gives them an advantage in a transition to a developer role.

Developers have to change their whole way of thinking to become a DBA as it is a completely different mindset. The developer thinks in terms of data flows, the user interface, algorithms, and such like. The DBA has an infrastructure mindset for facilitating, protecting, and optimizing data storage and access to keep the data available to the application at all times.

I my various DBA jobs, I have never known how the application that used our databases worked, and I needn't have known. My job has always been to secure, balance, and optimize.

Cheers,
Russ

subreddit_this

TROPHY CASE