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.
Tuesday 19 February 2008
Thursday 14 February 2008
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
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;
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.
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.
Subscribe to:
Posts (Atom)