How to reformat the highwatermark of a table after a delete?

For test purpose I had to delete and recreate a lot of records in a database.
So I just did a delete of all the records, every time I wanted to clean up my database.

Some of these tables have a few millions of records. So after a couple of times cleaning up and re-entering data into the database,
I had the impression that the database was getting slower and slower in showing me the results of my query.

I thought that this could have something to do with my indexes but I didn’t know how to fix this.

That’s why I contacted a few of my expert database colleagues and they learned me the following:
The table uses a highwatermark value and every time you add a record into the table the table will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was wrong in suspecting the index to be the cause of this problem it was in fact the table itself who caused this.

So how do you fix this?

If you want to clean a lot of records in a database(in bulk) you have 2 possibilities to keep your Highwatermark clean.

First option, and the one that worked best in my case was: use TRUNCATE TABLE

This will remove all the content of the table and will put the ‘highwatermark’ back to 0.

The advantage of this option is that is will will work a bit faster then a normal deletion.
But there is also one disadvantage. If you want to use this, you will have to disable all the foreign keys for this table before starting the truncate.

So doing this in a live database is not really the best thing to do I believe :-)

The other option is just use the delete as you used to do, but after the deletion of the records use : ALTER TABLE

SHRINK SPACE
You can even use the cascade option for this shrink, this will shrink all the highwatermarks of the depending objects.
This option is only available from oracle 10g.

Thanks Erwin and Hans for helping me with this!

Webcenter Development – SRDemo

When you want to start learning Webcenter, getting a grasp of the technology, the features … have a more practical view, you can download the SRDemo application to get you started.

When you’re going through the installation-guide to set everything up, following tips can help you step-through this installation:
- modify the oc4j.cmd before installing the standalone OC4J in the SRDemo directory and make sure to define the JAVA_HOME, ORACLE_HOME and J2EE-home.
- modifications you need to perform in the adf-config.xml are setting the mds-file location correctly, which is available under your application-folder in OC4J

Oracle BI – Dashboard Prompts & BI Publisher

Situation:
One of the common questions that generally come up with regard to BI EE and BI Publisher is ‘how does one pass parameters to BI Publisher reports in Dashboards?’. One of the solution is to create a BI Publisher Report using HR-scheme as data source. Define a parameter for the BI Publisher report and then match this parameter name while assigning the presentation variable for the dashboard prompt.

Solution:
This method is generally the most used since most of the BI Publisher reports would have database as the data source.

We create a simple BI Publisher report with data coming in from HR-schema of the database.

Report-details:

  • Default Data Source: HR
  • Parameters per line: 1
  • Show Controls: unchecked

Overview BI Publisher Report:


First we will create a new data model, called DS_HR. SQL query will be:

Now add a parameter to the report. In our case, we will have ‘pDept’ as the parameter name.


Create a new RTF-template with following fields:


At least add a template to our report. Upload the template you’ve created before and create a new template name ‘TemplateHR’.


Save Report and test your BI Publisher report to be sure everything is OK.
Then go to the BI Answers and create a dashboard prompt on ‘department name’.

Note: The name of the presentation variable must exactly match that of BI Publisher parameter name.

We set default value to ‘IT’ and assign presentation variable to ‘pDept’.


Include this dashboard prompt and the BI Publisher report into the dashboards.


Final result in Oracle BI Dashboard:


When you checked ‘show controls’ in the BI Publisher report:


With this option enabled, you can choose different templates and output formats.


Oracle BI ODBC-connection [SQORAS32]


I have locally installed a 10gR2-database which I have patched to an 10.2.0.3 (Patch-nr: 5337014)

Situation:
I would create a new System Data Source Name to import tables from the HR-scheme. When we configure a new ODBC data source connection, we get following error:

SQORAS32 – An unsupported operation was attempted

SQORA32 is a dll-file that will be called if you select Oracle 10gR2 as driver to set up a data source.

Solution:
We download patch-nr 5699495 from metalink and follow the instructions below.

Part 1 – Exploding the Kit onto your system:

  • Expand the self-extracting archive file onto your hard drive.
  • Extract contents of the archive, then unzip the contents of the .zip file into a new directory.

Part 2 – Installation instructions:

This section assumes the following:

  1. Oracle 10.2.0.1.0 client has already been installed on your system, and the RDBMS version 10.2.0.3.0 patch # 5337014 has been applied.
  2. Part 1 has been completed.

Installation Instructions:
Once the self-extracting archive file has been exploded it will create an directory structure as shown below on your hard drive.

The directory structure after unzipping and where the files to be copied are shown below. should be replaced with the directory of your 10.2 Oracle Home. IE. c:\Oracle10.2.0\db

Steps:

  • ORA10203\bin\sqora32.dll —> <ORACLE_HOME>\bin
  • ORA10203\bin\sqoras32.dll —> <ORACLE_HOME>\bin
  • ORA10203\bin\sqresus.dll —> <ORACLE_HOME>\bin
  • ORA10203\ODBC\html\ODBCRelnotesUS.htm —> <ORACLE_HOME>\ODBC\html\
  • ORA10203\ODBC\readme.txt —> <ORACLE_HOME>\ODBC\html\
  • ORA10203\ODBC\mesg\oraodbus.msb —> <ORACLE_HOME>\ODBC\mesg\

We solved the problem and now we can create a new ODBC-connection.

Apex Seminar

End of this month we organize together with Oracle Belgium our annual seminar on Apex.

For more then 3 years we are doing projects with Oracle Application Express and want to share our knowledge and our experience with the Oracle community.

In the first part of the seminar we will give first a small introduction on the product, its positioning and architecture. Next we will give an overview of the different kind of applications you can build with Apex from internet- over extranet- to intranet or back-office applications. Where possible we will refer to applications we already realized.

After a break we will explain our Apex Development Approach. This is a very pragmatic approach and can be considered as an assemblage of standards, guidelines, tips, standard solutions and custom-developed tools (like xTra4o, QA, …).

In between we will give a demo of the most important new features of the latest version Apex 3.1.

All presentations will be in Dutch. Belgium customers that want to join the seminar, can subscribe here.

ApEx: HTTP-400 Too many arguments passed

Recently I came across an apex application where a specific page generated the next error:

We got this error message because there are to many page items generated on the page. We could discuss why a page would need so many aguments but I leave that in the middle.

After some browsing on the oracle apex forum, I came accross the tip to set the PlsqlMaxParameters parameter to a higher value. It would have a default value of 2000.

You can set this parameter in your modplsql conf file, wich you can possibly find under Apache\modplsql\conf. For example, I added

I restarted the Apache server and the page submitted without any problem.

Follow

Get every new post delivered to your Inbox.

Join 34 other followers