Materialized View - exchange partition - primary key

When exchanging partitions I have always not included the global primary key index on the table to be moved in. This is because the exchange partition fails if it is present. Makes sense since a primary key index has to be global and how does that fit in with having the same index in a new partition. The database has to check for uniqueness globally not locally....Anyway.... In turns out the primary key index can be present on the table to be passed in IF you specify DISABLE VALIDATE;

e.g.
ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_pk_jan01
PRIMARY KEY (sales_transaction_id) DISABLE VALIDATE;


This link explains it all http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/refresh.htm

The key thing is once the table is exchanged in, does the global index go invalid and need rebuilding? I know it does go invalid when the index is NOT present on the table to be exchanged in. We'll see ..... I guess.

Materialized view - permissions and ora00600 error

Tried to create a materialized view on a schema I did not own, which produced permission errors. Solution turned out to be to grant the owner GLOBAL QUERY REWRITE. The solution was found here http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2162987

Other problem was that even thought a fairly complicated query materialized view created OK, (It consisted of two inline views) when the optimizer tried to use it via 'query rewrite' the query bombed out with ora-00600 [] [] [] etc.

Solution turned out to be to create two separate materialized views.
The optimizer then used the two materialized views with no problem and the query run time went from 45-60 seconds to instant! No rewriting of the query. Result.

Finally, if the mat view is created on a prebuilt table the user creating the view must have 'select ....with grant option' on the 'prebuilt' table.

Materialzied Views

I'm a bit like a born again Christian when it comes to materialized views. They've been around for ages and I've never used them... until now. Now I cann't get enough of them and feel the need to convert everybody and everything to use them.

Advantages are:-

With query rewrite enabled the optimizer will use the materialized view if it is quicker and the materialized view is up to date.

Materialized views, used in conjunction with the correct table partitioning and using dbms_mview.pmarker function in the view, will ensure that even a 'FULL' refresh only does the necessary partitions.

There more, much much more which I will write when I get time

dbms_crypto

Great little db package for encrypting data.

Below is an example which wraps the dbms_crypo package so it can be more readily used

CREATE OR REPLACE PACKAGE BODY d_security
AS
l_mod NUMBER := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;

FUNCTION encrypt(p_plainText IN VARCHAR2
,p_key IN VARCHAR2 DEFAULT '1234567890ABCDE'
)
RETURN VARCHAR2
AS

BEGIN
IF p_plainText IS NOT NULL THEN
RETURN(dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW (p_plainText, 'AL32UTF8')
,l_mod
,UTL_I18N.STRING_TO_RAW (p_key, 'AL32UTF8')
)
);
ELSE
RETURN(NULL);
END IF;
END encrypt;

FUNCTION decrypt(p_encryptedText IN VARCHAR2
,p_key IN VARCHAR2 DEFAULT '1234567890ABCDE'
)
RETURN VARCHAR2
AS
BEGIN

IF p_encryptedText IS NOT NULL THEN
RETURN( UTL_I18N.RAW_TO_CHAR(dbms_crypto.decrypt(
p_encryptedText
,l_mod
,UTL_I18N.STRING_TO_RAW (p_key, 'AL32UTF8')
)
)
);
ELSE
RETURN(NULL);
END IF;
END decrypt;

end d_security;

Hetrogenious services

Great for getting Oracle to talk to other databases (via ODBC) as if the other database was just another Oracle remote database. i.e. Through a db link

see
http://www.acs.ilstu.edu/docs/Oracle/server.101/b10764/tgvsgc.htm
and
http://www.dbasupport.com/oracle/ora10g/connection01.shtml

Straight forward to set up, at least for SQL Server. I had no luck however, getting it to talk to Timesten (thro the odbc), which was a shame. It did prove to be a bit flaky under Windows as the hsodbc.exe would hang around long after the query to the remote database had been chopped. The tnslistener process would not restart until after this process had been killed. I needed to do regular restarts of the tnslistener process. Not something I'd want to use in a production environment but OK for one off data extractions.

Oracle BI and Oracle ODBC driver

Oracle BI - using Oracle ODBC driver

It seems that name of the connection pool has to be the same as the tnsnames entry if you are using the Oracle ODBC driver with Oracle BI. If it is different then all appears to be OK when using the administration tool but all falls apart when you try and run a report with cannot find entry in tnsnames. Put an entry in the relevant tnsnames.ora file which the same as the connection pool name and it all magically starts working. Good eh?