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:
{ 0 comments }
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.
{ 3 comments }
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.
{ 4 comments }
“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.
{ 0 comments }
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).
{ 1 comment }
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.
{ 0 comments }
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.
{ 2 comments }
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.
{ 0 comments }
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.
{ 0 comments }
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?
{ 0 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
