Oracle provides the ability to examine how a SQL statement will be parsed by oracle with explain plan.

1.  Logon to sqlplus with a user that has DBA privileges.

2.  Create the plan table with the oracle script $ORACLE_HOME/rdbms/admin/utlxplan.sql.  NOTE:  The ‘?’ sign in sqlplus refers to the variable ORACLE_HOME

3.  Now you have the tables created to hold the parsing information, you can execute the explain command.   NOTE: We use the ‘SET’ clause in this example to allow us to easily identify our statement.

4.  Now we can query the table plan_table in our schema.

5.  This completes the use of explain plan for Oracl

Network setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration network for an Oracle Enterprise Linux 6.5 installation on Oracle VirtualBox in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator, open a command prompt and execute the command IPCONFIG to gather current IP configuration for your host system.
C:\ >ipconfig
Windows IP Configuration
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . : C3CEN.Net
Link-local IPv6 Address . . . . . : fe80::b9b2:d070:d80f:82d1%11
IPv4 Address. . . . . . . . . . . : 10.30.15.90
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

Ethernet adapter VirtualBox Host-Only Network:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::bdca:1b67:c60c:7b1%19
IPv4 Address. . . . . . . . . . . : 10.30.15.127
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

C:\ >

NOTE: The following information:
HOST IP 10.30.15.90
Host Subnet 255.255.255.0
Gateway 10.30.15.1
Guest IP 10.30.15.127
Guest Subnet 255.s255.255.0

2. Open Oracle VirtualBox, from the main top menu of VirtualBox and Select File ? Preferences…

3. On the Left panel menu select Network and in the right hand panel Host-only Network.

4. Click the PLUS sign symbol to the right of the right hand panel to create a new Host-adaptor.

5. Once the new Adapter is created highlite it and select the screwdriver edit icon on the right hand side.

6. Select the enter a new IP address for your adapter and network mask under the Adapter tab, do not feel anything out under the DHCP Server tap and press OK and press OK at the original Network configuration screen.

7. On your host machine, open another command prompt and execute the “IPCONFIG” command to see your new adapter does exist.
C:\ >ipconfig
Windows IP Configuration
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . : C3CEN.Net
Link-local IPv6 Address . . . . . : fe80::b9b2:d070:d80f:82d1%11
IPv4 Address. . . . . . . . . . . : 10.30.15.93
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

Ethernet adapter VirtualBox Host-Only Network:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::bdca:1b67:c60c:7b1%19
IPv4 Address. . . . . . . . . . . : 10.30.15.127
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

Ethernet adapter VirtualBox Host-Only Network #2:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::c82c:7a2f:242c:fcfb%35
IPv4 Address. . . . . . . . . . . : 192.168.130.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

C:\ >
8. High light guest OS you created in Part 3 of this serious and select Settings tag from tool bar ? Network from left panel ? check the Enable Network Adapter ? Select Host-only Adapter and ensure the Adapter you just selected is in the Name: field from drop down button then press the OK button.
?

9. High light guest OS you created in Part 3 of this serious and select Settings tag from tool bar ? Network from left panel ? select Adapter 2 Tab ?check the Enable Network Adapter ? Select Internal Network from drop down button and ensure Promiscuous Mode is set to Allow All then press the OK button.
?

10. High light guest OS you created in Part 3 of this serious and select Settings tag from tool bar ? Network from left panel ? select Adapter 3 Tab ?check the Enable Network Adapter ? Select Bridge Adapter from drop down button with the name of the physical device and ensure Promiscuous Mode is set to Allow All then press the OK button.
?
11. Double click on the guest OS you created in Part 3 of this serious in the left hand panel of VirtualBox. In this instruction set the name is OEL_65.

12. Once the Oracle Linux Server prompt appear below logon as the user root with the password used during installation in Part 4 of this serious. NOTE: In this series, we installed a base Linux Server without desktop support. If your installation has desktop support, just open a terminal screen to perform the rest of this procedure.

login as: root
root@10.30.15.127’s password:
Last login: Sat Jan 4 15:24:18 2015
[root@oel65 ~]#

13. Open the file /etc/hosts and add the host IP and hostname you wish to use.

[root@oel65 ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.130.1 oel65

14. As the root user navigate to the directory /etc/sysconfig/network-scripts and use VI editor to adjust the file ifcfg-eth0. Ensure that the ONBOOT is set to YES and

[root@oel65 ~]# cd /etc/sysconfig/network-scripts/
[root@oel65 network-scripts]# cat ifcfg-eth0
DEVICE=eth0
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=none
HWADDR=08:00:27:A2:02:20
IPADDR=10.30.15.127
PREFIX=24
GATEWAY=10.30.15.1
DNS1=10.10.15.21
DEFROUTE=yes
IPV4_FAILURE_FATAL=yes
IPV6INIT=no
NAME=”System eth0″
NETMASK=255.255.255.0
USERCTL=no

[root@oel65 network-scripts]#

15. Restart the network services with command “service network restart”

[root@oel65 network-scripts]# service network restart
Shutting down interface eth0: [ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface eth0: Determining if ip address 10.30.15.127 is already in use for device eth0…
[ OK ]
[root@oel65 network-scripts]#

16. Test your network connection to and from an outside machine with ping command:

17. This completes the setup of the VM network.

Larry Catt, OCP

Installation of Oracle Enterprise Linux 6.5

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process Installation of Oracle Enterprise Linux 6.5 using your personal desktop/laptop and Oracle VirtualBox.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Double click on the guest OS you created in Part 3 of this serious in the left hand panel of VirtualBox. In this instruction set the name is OEL_65.

3. Once the Oracle Linux Server 6.5 Installation screen appears, ensure that ‘Install or Upgrade existing system’ is highlighted and press the return button.

4. The screen will appear asking you to validate your installation media, use the error keys to select Skip button and press the Enter key.

5. At the Welcome screen, select the Next button and press the return key.

6. At the Language screen, select your language preference and press the Next Key.

7. At the keyboard selection screen, select your desired keyboard and press Next button.

8. At the devices installation screen, select Basic Storage Devices and press the Next button.

9. At the Storage Device Warning page, select Yes, discard any data.

10. At the name this computer screen, type in the name of your computer and press the Next button.

11. At the time zone screen select your nearest city and press the Next button.

12. At the root account password page, enter the password you wish to use for root access. NOTE: root access is the primary administrative account on a linux system.

13. If the Weak Password warning appears, either select cancel or use Anyways buttons.

14. At the type of installation page, select Replace Existing Linux System(s) and press the Next button.

15. If a Writing storage configuration to disk warning appears, select the Write changes to disk button.

16. A Default installation page, select Basic Server and all defaults and press the Next button.

17. Now the installation will proceed.
18. At the Congratulations screen, click the Reboot button.

19. This completes the installation of Oracle Enterprise Linux 6.5 on Oracle VirtualBox software.
Larry Catt

Media packages required for installation of Oracle Enterprise Linux 6.5
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the required packages to download from Oracle Corporation website to deploy a development site on your personal windows based desktop or laptop.

NOTE: This suite can be installed on any type of OS, just download the appropriate VirtualBox software package for your base system.
Required downloads:
VirtualBox-4.3.20-96997-Win.exe
V41362-01 – Oracle Linux Release 6 Update 5 for x86_64 (64 Bit)
1. If you do not already have an Oracle Corporation Developers logon, go to www.oracle.com and create an account.
2. Logon to the oracle website with your developers account and navigate the website to the latest Virtual Box download. As of this writing, the URL was: http://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html?ssSourceSiteId=ocomen#vbox
3. Logon to the oracle website with your developers account and navigate the website to the latest Oracle Enterprise Linux download. As of this writing, the URL was: https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=17052719 Select the media package V41362-01 for Oracle Linux 6.5 Release Date: Dec – 10- 2013.
4. This are the two required packages to build an Oracle Linux system on your Windows based desktop or laptop.

Larry Catt

Oracle provides for handling of error messages in programming blocks through the use of exceptions.    During the execution of a loop, the general exception clause at the end of a procedure, function or pl/sql block will exit the loop for handling of error, thus stopping the processing.    However, you can change this behavior by placing an exception clause within the loop block itself.    This procedure shows how to setup a loop with an exception clause, so the loop is not exited on error.  NOTE:  To just see the loop exception go to step 5.

1. Create the following test table:

1. Now execute the following block to extract the data from table test_a

PL/SQL procedure successfully completed.

1. Create an error by Insert the line “if mod(rec.a,2)=0 then n:=rec.a/0 else dbms_output.put_line(rec.a);” after the

1. Now insert the exception “exception when others then null;” at the end of the block and we see that the exception is handled by the loop exited before giving us the value of 3.

1. Wrap the internals of the for loop in its own block and move the exception clause inside the block, now you see the exception is handled and the loop continues, giving us the the value of 3.

1. This completes handling of exceptions in a oracle loop which continues.

Larry Catt, OCP