Category: Oracle
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...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.
Continue Reading...Oracle Single Row Character Functions Returning Numeric Values
Exam Topic: Using Single-Row Functions to Customize Output – Describe various types of functions available in SQL Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has […]
Continue Reading...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.
Continue Reading...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...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...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...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...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...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...