Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

VirtualBox

By arkytekture | Published December 18, 2010

Oracle VM VirtualBox

If you haven't had a chance to work with it, consider visiting

http://dlc.sun.com/virtualbox/vboxdownload.html

It runs on windows, linux, mac os/x and solaris.

I downloaded a pre-installed VM with a database, development tools, and an application server pre-installed.  Though I had the minimum required config (~2 GHz processor, 2 GB RAM), I was surprised that it ran as well as it did.

Though the initial setup is quite quick, the downloads of actual VMs to use in VirtualBox can take a while, but compared to the aggravation of pulling together Oracle 11gR2, SQL Developer, Apex, and a Weblogic server on your own, it's a more than fair trade.

If you've been putting off exploring Hands-On Database Technology on a home PC or a spare machine at work (http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html), then this could be a good way to jump in — with a virtual machine.

Posted in Architecture | Tagged , , , , | Leave a comment

Search PSOUG from SQL Developer !

By arkytekture | Published October 20, 2010

Extend Oracle's SQL Developer to search PSOUG web pages and  see how much more productive you can be!

Anyone who's used SQL Developer may have noticed the search engines tucked away in the upper right corner of the screen.  With a nod to Firefox, the people who created SQL Developer made it easy to look up an error message on Google, consider going to Oracle Technology Network (OTN) forums with your SQL Developer issue, or checking the AskTom or Oracle documentation sites.  

While the sites provide valuable information, often they get into esoteric areas or long blogs.  PSOUG succinctly exposes the concepts that a developer needs right away with examples of usage and scripts that illuminate topics such as CREATE MATERIALIZED VIEW.

It's exceedingly easy to try out.  Add PSOUG to your search domains by placing an image file and a source file in the sqldeveloper/sqldeveloper/searchengines path of your installation.

First file: psoug.src

<search
   name="PSOUG"
   description="PSOUG Search"
   method="POST"
   action="http://psoug.org/search.htm"
   searchForm="http://psoug.org/search.htm"
   queryEncoding="utf-8"
   queryCharset="utf-8"
>

<input name="qwords" user>
<input name="querytype" value="allwords">
<input name="catid" value="0">
<input name="searchtype" value="basic">
<input name="submit" value="GO">

<interpret
    browserResultType="result"
    charset = "UTF-8"
    resultListStart="<!–a–>"
    resultListEnd="<!–z–>"
    resultItemStart="<!–m–>"
    resultItemEnd="<!–n–>"
>
</search>

Second file: psoug.gif

I downloaded the PSOUG title.gif and scaled it down by a factor of about 4.  Use whatever tool makes sense to manipulate your image such as GIMP (multi-platform)  or Microsoft's Image editor, or whatever works on the Mac OS/X into an icon-sized image.  Jpeg, png, gif are all acceptable formats.

Your files will be discovered automatically the next time you start SQL Developer.

Posted in Architecture, Uncategorized | Leave a comment

How to build and maintain an Oracle shell

By arkytekture | Published August 22, 2010

In productized development where installation scripts and Oracle dump files are the basis for building a new database instance, complexities can be managed by storing Oracle exports and all SQL files in a version control system (such as CVS, Perforce, or Subversion).

A shell database is essentially the logical starting point of a database wth some tables preloaded with essential lookup data, stored database code, constraints, and views defined, but the many of the segments start out empty.

Continue reading »

Posted in Uncategorized | 1 Comment

Paying attention to Oracle sequences when upgrading / migrating

By arkytekture | Published April 24, 2010

The task of upgrading databases in Oracle comes along about as often as new U.S. presidents, however many of the important tasks are the same, setting up new infrastructure, parting ways with the past letting some of it wither on the vine, and planning what needs to be preserved while moving ahead.

Instead of upgrading an Oracle database in place, some plans also call for moving to new hardware. A tried and true approach is to generate scripts and use DataPump exports to recreate your application data on the future database. 

Let me tell you about of a team of DBAs who took an export and began to import the data structures and data rows into the new database.  After a while, they realized one of the schemas was kind of large and that the whole operation  might take longer to complete than allotted.  Desparate to beat the clock, they began reverse engineering all the tables schema by schema and applying the resulting scripts to the new target database.  Now DBAs were competing with the import to create schemas and move data.

Triggers were not firing in the new system, and applications fell flat on their faces.  Alas, the grants, synonyms, roles, and sequences were left behind in this haste, and the applications were not working on the new production environment.

What to do to rescue the installation?  Well, reverse engineering sequences would be an onerous chore with the shiny, trusted front-end tool that the DBAs used every day because navigating to each application schema and manually clicking on each sequence to reveal its definition. 

The other issue was not ALL of the sequences were needed.  Not all of those SYS, sample, special purpose schemas created by Oracle installations were going to be migrated.  Besides the new production database was all set and we didn't want collisions or kibble.

The solution:  use dynamic SQL to generate a single script of the sequences required from the old system, and run them on the new system, excluding unnecessary schemas.

Here's the Oracle SQL script that took care of that problem:

Continue reading »

Posted in DBA tips | Tagged , , , , , , | Leave a comment

Dear DBA ( a note from a principal developer)

By arkytekture | Published April 17, 2010

Dear DBA, as I am running loadtests with product XYZ in preparation
for uploading of many thousands of XML documents , I find that there is something
that's bogging down your database. I thought your equipment
should easily outpace the old Compaqs that we have running Windows, and that
Oracle should do better than our PostgreSQL-based setup. Yet
I find the numbers show the opposite:

733 Documents in 2325.713 s for an average of 3.173 s
at ~11% CPU load

944 Documents in 1960.4 s for an average of 2.077 s
at ~15% CPU load

It takes 50% longer on the Oracle setup. Since the
application server load is negligible at the same time, it
goes to show that we are waiting for the database.

I wonder why.

Continue reading »

Posted in Architecture, data loading | Tagged , , , , , , , , | Leave a comment

What’s your XML strategy?

By arkytekture | Published April 15, 2010

The choices you have on implementing XML depends upon the architecture of your solution.

1) Do you plan to

a) parse the XML docs and store them into multiple relational tables   ?

If so, what languages or techniques do you intend to use to accomplish this?

b) store an XML doc in a single XMLType column in the Oracle database?

Do you need to index the data to perform XML or "free text" searches?

If so, what XPATH indexing do you intend to perform?

c) Register XSD as an XML Schema and have Oracle validate incoming XML documents such that
data is automatically shredded into object-relational tables based upon the different
types and attributes?

If you choose (c), how will you reload and revalidate data against the new XSD should
new data elements become available in the XSD?

2) How important is it to preserve the exact XML document ingested in Oracle?

 

  • Will you  need to reconstruct or otherwise output data in the exact format to interchange with
    people working in a regulated industry such as banking, health care, education ?

    •  

     

  • Or will a logical copy that is faithful to the structure be acceptable?

    •  

    About Character Large Objects (CLOBs)

    Yes, you can store CLOBs in line with a database row up to 4,000 characters, else it's written to a separate LOB segment.

    Oracle automatically creates LOB indexes whether you want them or not, so you might want to review the non-XML CLOBS in your design and determine whether they would be well under the 4,000 characters and thus fit into a VARCHAR2 data column.

    Posted in Architecture | Tagged , , , , , , , | Leave a comment

    In the Arkytekture

    By arkytekture | Published April 9, 2010

    Welcome to Arkytekture.

    PSOUG.org is a great resource that people should be looking at when solving data management problems in Oracle.  Sharing knowledge and assisting others is part and parcel of the user group experience.

    Although  a new feature from Oracle holds great promise, some strategies work better than others.  Developers and architects should know about the different ways and choose carefully to achieve better successes with their projects.

    The spirit of Arkytekture is to share some real-world experiences (ok, maybe thinly disguised ones) that illustrate either database technology issues, software engineering practices, or some other philosophy.

    Upcoming topics will include bulk-loading, measurements, error-handling, dynamic PL/SQL, and Apex.

    Posted in Uncategorized | Leave a comment