In this post, I’m sharing some handy PL/SQL queries that help with monitoring and maintaining Oracle databases...

SELECT * FROM TABLE1 WHERE ID NOT IN ( SELECT ID FROM TABLE2 ) When I ran the above query I know the condition will give me the results, because the data exists in the TABLE1 which is not in TABLE2. But I didn’t go any results. I got surprised. After I a while I found out the issue.

To extract the value of a tag from the given XML-like string in PL/SQL, you can use the EXTRACTVALUE function or XMLTABLE for XML processing.

For NVARCHAR2 and VARCHAR2 maximum size is 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED. This is useful you have to allocate more data to a variable.

In Oracle PL/SQL, you can use the following commands to view table details:

If your Oracle database has Flashback enabled, you can query past versions of data within a specified retention period. Here’s how to use Flashback to retrieve a prior state of data:

To update a column with a random value in PL/SQL, you can use the DBMS_RANDOM package, which provides functions for generating random numbers or strings.

The PL/SQL MINUS operator returns all rows from the first query that are not present in the second query. Each SELECT statement defines a dataset, and the MINUS operator retrieves all records from the first dataset, excluding any that also appear in the second dataset.

The LISTAGG analytic function, introduced in Oracle 11g Release 2, greatly simplifies string aggregations within SQL queries.