Read-only reference tables and views for Oracle database metadata. Oracle’s data dictionary includes definitions of schema objects, tablespace and auditing information, etc. Most data dictionary views come in three flavors distinguished by their prefixes: DBA, ALL and USER.
It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken. This article takes a closer look at:
Consider a PL/SQL stored procedure that handles a heavy transaction. The procedure is extremely slow – when executed from a UI, the application hangs for minutes. On analysis it is found that the procedure is performing a complex series of steps, a portion of which are non-critical and need not hold up the entire transaction. In other words, it would be acceptable if:
Oracle PL/SQL helps us achieve these objectives with asynchronous processing using Oracle job scheduler DBMS_SCHEDULER. Here’s a demo to show you how.
Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.
What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not impossible. Try DBMS_REDEFINITION: here’s how.
“In which package is this procedure placed?”
Typical scenario: in a project’s design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored: packageX gets split into packageY and packageZ. All goes well — the application gets deployed and is running merrily – till a change request comes in.
A new developer refers to the design documentation to understand the change request.
The design documentation should have been updated to reflect the new package structure — but it isn’t.
Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).
I recently thought of using a Java method in PL/SQL. And so I wrote the Java stored procedure and ran it on Oracle XE.
Things didn’t go as planned.
Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.
Hello range interval partitioning.
Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your Oracle database version.
Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.
How will you find out the number of rows in each table in a schema in Oracle?
Here are two approaches with their pros and cons.
How would you delete old data from a database schema so that it retains only the current year’s rows in all the tables in the schema, and the rest of the data is erased?