Using partitions may decrease search time when searching through a lot of data from minutes to seconds, from hours to minutes. Storing information partitioned per month/year and retrieving it knowing which month/year you want from millions of records is more efficient than an index on all those months and years. Unfortunately changing to partitions afterwards almost always needs application changes, (I actually never tried an sql_profile…).
When looking for a solution using partitioning, I came across another problem. They were using a function in the one and only predicate in the query and the fact is that when a database uses partitioning for storing data, using a function on the partitioned column will the optimizer searching all data, not the partition. Understandable, because with the function you are searching for derived values, not the actual values in the partition. Oracle must search all the partitions and can not ‘prune’ the partitions. Determining and using a partition is called Partition Pruning.
The solution to be used here is called Virtual Column Partitioning and as a coincidence and nice extra they didn’t even had to change the application! Continue reading
You can update your Oracle VM Servers (3.x) with the Oracle Public Yum Server. In the Oracle VM Manager console go to the ‘Tools and Resources’ tab and click ‘Server Update Management (YUM)’.
For the ‘YUM Base URL:’, use:
When enabling the GPG key, use:
(Oracle VM 3.x is based on OEL5) Continue reading
In Oracle Linux 6, it looks like the ‘oracle-validated’ package is missing, but Oracle actually renamed it to ‘oracle-rdbms-server-11gR2-preinstall.x86_64‘.
To quickly use the public yum repository of Oracle and install ‘oracle-rdbms-server-11gR2-preinstall.x86_64’:
yum clean all
yum install oracle-rdbms-server-11gR2-preinstall.x86_64
You will get the ‘latest’ build enabled.
If you don’t want this, vi /etc/yum.repos.d/public-yum-ol6.repo .
Port 1521 is used for ages as the default port for the Oracle listener, but while I was looking at some default port numbers on Wikipedia, I saw port 1521 is not the official listener port. 1521 is the nCube License Manager official port!
Port 2483 and 2484 are the official Oracle ports according to IANA!
ttc 2483 tcp Oracle TTC
ttc 2483 udp Oracle TTC
ttc-ssl 2484 tcp Oracle TTC SSL
ttc-ssl 2484 udp Oracle TTC SSL
According to Metalink’s document “Listening Port numbers [ID 99721.1]”, in Future releases the 1521 port may change to 2483 TCP and 2484 TCP with SSL, but this is document is written in 2008…
Edit: The metalink note has been removed…
If you don’t want to use Null, Guest or External Host authentication when using VirtualBox Remote Display, one can add an extra external option called VBoxAuthSimple as explained here: http://www.virtualbox.org/manual/ch07.html#vbox-auth . You will use a separate password for connecting remotely to the VirtualBox’ Guest. This username/password will be stored in the machine’s XML settings file.
(trouble with mstsc.exe?)
I had this problem, when trying to connect with the External Authentication Method, using the Remote Display server in VirtualBox (4.1.8 and 4.2.0). When I want to connect with Microsoft’s Remote Desktop (mstsc.exe), it failed every time with “Your Remote Desktop session has ended. Your network administrator might have ended the connection (…)”.
Today I ran into an add-on for FireFox which can create a wget command from a download file dialog box. First thing that came in mind was; Will this work for Metalink / My Oracle Support? Yes is does!
The add-on is called ‘cliget’ and can be downloaded here: cliget add-on for FireFox. Continue reading
[Commands used were against VirtualBox version 4…]
Sometimes you don’t want to use a lot of disk space on your VirtualBox for install software or any ‘temporary’ need of space.
Adding shared folders from your host to your VirtualBox guest is a good option to use. If you have a Windows guest and a GUI based Host, it it’s very easy; click, click, done.
But if you are using a command line based guest and/or host, it a bit more complex. Use this post as a guideline, Linux knowledge is assumed… Continue reading
The expdp and impdp utilities are command-line driven, but when starting them from the OS-prompt, one does not notice it. When you want to kill, cancel, start or resume a job, you will and up in the datapump command prompt… now what?!
All command shown here can be used with expdp and impdp datapump. Continue reading
Just a little tip… If you move from Enterprise Edition with Partitions, to Standard without or removing the partition option: Create an index on your old partitioned column!
Happy pruning… or indexing!