Querying Oracle Nested Tables

Nested tables are one method you can use in Oracle to store a one-to-many relationship. You essentially put a table inside of another table. The nested table is actually stored separately and is linked to the original table by the unique row id. Working with this kind of table and the data within is different. … Continue reading Querying Oracle Nested Tables

Using Oracle’s srvctl to Complete a RAC Flashback

A flashback restore point is handy for deployments of new code and testing. Remember: You cannot properly shutdown a RAC database using sqlplus command. Use srvctl commands instead. Flashback to Restore Point (RAC) >$ srvctl stop database -d myDB -o immediate >$ srvctl start instance -d myDB -i myDB1 -o mount SQL> flashback database to restore … Continue reading Using Oracle’s srvctl to Complete a RAC Flashback

Oracle’s SRVCTL: Enable an Instance. Make an Instance Preferred. Check a Service’s Status.

How to use the Oracle Server Control (srvctl) to enable instances, and see configurations and statuses. Enabling an Instance # srvctl enable instance -d [unique database name] -i "[instance1],[instance2]" Enables instances. If repeated, get messages, PRCC-1011: [db] was already enabled on [server name 1], [server name 2] PRCR-1002: Resource [resource name] is already enabled See … Continue reading Oracle’s SRVCTL: Enable an Instance. Make an Instance Preferred. Check a Service’s Status.

Oracle srvctl Basics – Listener and Database Commands

When a database is getting started, generally the listener control service (lsnrctl) is started and the database is started by logging into the database and executing the startup command. If you're in a RAC configuration, you should be using srvctl; this command knows your configuration and works on the entire RAC environment instead of just … Continue reading Oracle srvctl Basics – Listener and Database Commands

Convert Decimal to Other Number Systems

Just for fun, I made a block of code that will convert decimal numbers to any other number system. Maybe I'll go the other direction next. It's basically a loop. It takes the remainder of the original number divided by the number system (MOD) and adds that to a string. The string is made by … Continue reading Convert Decimal to Other Number Systems

Generate Random Values

One of the best builtin packages in Oracle for anyone who needs to create random data is DBMS_RANDOM. Not only can you use it to create random values, but you can also use it to make random selections. For instance, if you have a list of values that you want to assign randomly, you can weight … Continue reading Generate Random Values

Nested Tables as Collection Data Type and a Table Column

Sometimes, a table is designed with one column that can hold an array. An array in database design is a table. So, in Oracle, you can create a type as a table and then use this type as a column in another table. What we end up with is a nested table as a table column … Continue reading Nested Tables as Collection Data Type and a Table Column

Oracle Recycle Bin: BIN$ and RB$$ files

Ever query all_source and come up with a strange object name that starts with BIN$ or RB$$? That is the recyclebin, holding on to tables and indexes you have deleted in the past until they are either released or aged out. If you are trying to make space by dropping objects, be sure to purge … Continue reading Oracle Recycle Bin: BIN$ and RB$$ files

Change Oracle’s SQLPLUS EDitor

Not everyone wants to use sqlplus' built in editor. It is a bit of a pain and not at all WYSIWYG. Thankfully, changing sqlplus to use another editor is an easy process where you change the _editor session variable to another value. For a more permanent change, you can alternatively change the setting in the … Continue reading Change Oracle’s SQLPLUS EDitor

Handling Nulls

Null is an unknown value. Null is Schrodinger's Cat's state before opening the box. There are ways to deal with null that are unlike anything else in the database, both in concept and in function. Equalities/Inequalities Nothing can equal or not equal Null. Not even another Null. It is an unknown value. Only something that is … Continue reading Handling Nulls