Last week a i defined a new materialized view, and the on-commit was not working. After hours reading manuals, i found out that in the definition if you define FAST and ON COMMIT, fast is taken Yell. That means

    "CREATE MATERIALIZED VIEW MV_TEST BUILD IMMEDIATE REFRESH FAST ON COMMIT AS...." does a fast-refresh

    "CREATE MATERIALIZED VIEW MV_TEST BUILD IMMEDIATE REFRESH ON COMMIT AS...." does a on-commit

materie

I am using materialized views a lot, and they are a handy thing (in older versions the were named SNAPSHOT). If you have for example a lot of statistic stuff on your customer (how many purchases the last 7 days, how many in average over the year, how many contacts.... it is a lot of CPU and IO you need just for information that are not so important to be actual in real-time. In this case you can use Materialized Views (MV). 

A creation of a materialized views would look like this (more infos in the manuals there are a lot of options!!!):

CREATE MATERIALIZED VIEW MV_TEST
START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT * FROM .....

In Oracle also a "fast refreshe" is possible, that means just the changed rows of the "master" table are written to the materialized view (100000 customers, just 5000 purchased something today so just 5000 changes are written to the MV).

 In some cases you also need the "on commit" feature. This is basically a fast refresh every time a record gets commited to the master-table. 

characterset

 

 

If you want to know why some characters are not displayed directly and want to know the character-set of your Oracle-database just use the following select-statement:

select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; 

 

After installing a database sometimes sys objects are invalid, and don't recompile. In this case you have to use the script utlrp.sql. You can find this script on the database-machine in the RDBMS-directory where the oracle-software is installed for example C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN. These scripts sometimes don't work with sql-tools. So i prefer to use plain SQLPLUS on the machine directly. Just connect as SYS and execute the scripts like this:

  • cd C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN
  • SET ORACLE_SID=STRANGEDATABASE 
  • sqlplus sys/change_on_install as sysdba
  • start utlrp.sql

timestampI was converting date columns in a log-table to the new timestamp datatype. This datatype is more precise and can handle milliseconds. After recompiling my Packages i got the errors

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

There was no line that was wrong so this error-message didn't give me a glue what was wrong (i guess that's the sense of some oracle-error-messages "just to confuse the russians" Wink).

The reason was that the column name was "timestamp" what is the exact name of the datatype. After renaming it to "timestmp" it worked perfectly.

lock

 

If you want to ensure in Oracle that a user can just access his own records, for example example his contacts, you can do it in several ways.

  • The first would be to realize it in the application, that means you add an where-clause there. So this where-clause has to be added in every select-statement that access the "contacts" table. I dislike this option because it is to much wacork and very soon anyone will forget the where-clause and somebody can see data he is not supposed to see.
  • The second would be to use the "fine grained access control" a feature from Oracl. With this feature you can add a where-clause to the table. That is a perfect solution but has one big disadvantage, the tuning of an select-statement. The access-control adds another select-statement that restricts the original query. If you have a huge table that you want to restrict, it is uncomfortable to tune. But nevertheless it can be the perfect solution. In my case it was to untransparent to other developers and we would have to rewrite a huge part of our application. So i choose the following.
  • Another way would be to use an application context and use views for access-controls. Following example to demonstrate that.

 

CREATE CONTEXT TESTCONTEXT USING SCOTT.TESTPACKAGE;

This creates a context and defines that just the package scott.testpackage is allowed to set values to this context, this Package has the procedure setUser just a minimal version without error-handling etc.:

PROCEDURE setUser (pUser   IN USER_INFO.USERNAME%TYPE) IS

BEGIN

DBMS_SESSION.SET_CONTEXT( 'UserInfoContext' 'UserName', pUser);

END;

CREATE OR REPLACE VIEW V_CONTACTS AS SELECT * FROM CONTACTS  WHERE USR = SYS_CONTEXT('UserInfoContext','UserName')

After setting the user (at logon-time) with "exec scott.testpackage.setuser('iamthesuperuser');" you get just the records from this user.You could say that is would also be possible to use a simple pl/sql-function and that is true, but there would be performance-costing switchings between the pl/sql and the sql-engine and also the optimizer would not be able to interpret this pl/sql-function as a constant and so it would be much slower, i tested it with pl/sql and with the context and using the context is much faster.

qtek9100

 

 

 I am using the MDA-Vario a branded version of the HTC-QTEK 9100, and like it. It is a PocketPC, and so it synchronizes pretty good with mircosoft office. One thing i don't like is that the stylus-pen to operate the touch-screen is kind of loose in the case. I was looking often for the pen and always could find it, but since last week i could not find it again. T-Mobile was not able to send me some of this pens because the run out of these pens (guess why... Laughing). So i looked at ebay, and they are sold there for 1-3€ for one, that means with freight costs it is about 10€ for one pen, and about 15 for three of them. I thought that was too expensive for such a 10-Cent product. So i consulted my favourite Shop (Amazon), and there i found these pens (3 for 10€) and if you buy it with the the dockingstation (didn't come with the vario) you pay 20€ for all (no freight costs). 

By the way i am currently working on a Amazon-PHP-Framework to get the Amazon-Items that are really worth to buy (cheap).

Workspace Manager

In my developing-life there was often the question vor "versioning" a table. That means somebody wanted to know WHO WHEN changed what column of the database, what was the old value, what was the new value...

There were different possibilities how you could implement it, save the data in the same table, make a second table, and some tools that could automate the generation of the "history" and also called "journal" table, and the PL/SQL-Code to fill them correctly, for example the Oracle-Designer.

What i didn't knew was that Oracle implemented a feature that enables the versioning of a table by just entering:

DBMS_WM.EnableVersioning ('emp',  'VIEW_WO_OVERWRITE');


And the best: it works...  More info of the so called Workload Manager (WM) can be found here.

pumpe

 

Today i set up a test-environment to play around, and after a database-clone attempt failed i had to do it with an logical exort, means a datapump in 10g. So i did it by using the Enterprise-Manager (10.2.0.2) and did a full-export that worked fine. The import failed with ORA-06502 (numeric or value error) and no further error-message. After setting the NLS-language and also the browser-language from German to English, the import worked fine. Seems to be a problem with the language...

Opatch is a tool for applying one-off-patches for oracle-products, that means if you are running in bug xy the the support will tell you to apply the patch 08154711... 

In my case a found a metalink-note that could solve my problem, guess what... YES i had to apply a patch...  It took me a lot of time to find the download-page for opatch. The note described exactly what opatch is doing, referred to a lot of other notes....

So here is the shortcut directly to the opatch-download-page: 

http://updates.oracle.com/download/2617419.html

By the way, you need an valid metalink-account for that link 

Migrating from a pfile to a spfile is really simple. You can use the following command on the console:

CREATE SPFILE FROM PFILE = 'C:\oradata\mydb\init.ora' 

... of course you have to enter the right path to the init.ora file... Wink

To do it the other way is also possible, just type

CREATE PFILE = 'C:\oradata\mydb\init.ora' FROM SPFILE = 'C:\oradata\mydb\spfilemydb.ORA'; 

  

repwiz

In my current project a lot of customers are from east-european countries. For this reason the reports (printed as PDF) need to be printed with a lot of special characters, also cyrillic ones. For this reason i have to use UTF8. In Reports it is a almost simple procedure to configure. The hard part is to find the right documentation, there is a huge amount of whitepapers etc. floating around on metalink and OTN. Here in some simple steps, this example works with Windows-XP.

  • check where the fonts are located on the operating-system (f.e.: "c:\windows\fonts")
  • find the file uifont.ali file (should be in ...oracle...\tools\common....)
  • add a pdf subset-section or uncomment it (there are a lot of examples in this file)

example:

[ PDF:Subset ]
"Times New Roman"..Italic.Bold.. = "timesbi.ttf"
"Times New Roman"...Bold.. = "timesbd.ttf"
"Times New Roman"..Italic... = "timesi.ttf"
"Times New Roman"..... = "times.ttf"
"Arial"..Italic.Bold.. = "Arialbi.ttf"
"Arial"...Bold.. = "Arialbd.ttf"
"Arial"..Italic... = "Ariali.ttf"
"Arial"..... = "Arial.ttf"

  • add the font-directory to the Reports-Path the reports-path is a registry-entry on windows and a environment-variable on linux/unix.
  • If it is a local installation for developing, you also have to set the nls-language to UTF8 (registry in windows, environment on linux/unix...)


Done.

If you want to learn more about PDF-subsetting take a look at the documentation, the document-number for 10g Developer is B13895-01, you can download it on metalink, or just google for it.