About Oracle: Fast refreshable materialized view errors, part seven: a summary

Great article... pretymuch summs it all for MVs and Fast Refresh!

All credit to the author...
About Oracle: Fast refreshable materialized view errors, part seven: a summary

Checking Materialized View Compatibility with FAST REFRESH

This has been very helpful to me... I hope this will help some one out there as well :)

Materialized view can be FAST AUTO REFRESHED. There will be times when the one is unable to create a view due to some error that says, cannot create fast refresh view.

1st thing to do is execute the following script usually found in ../admin/utlxmv.sql

if not then can simply do a search for the above file.

Once the script has been executed. A proposed Materialized view compatibility with FAST REFRESH can be checked by executing the following Oracle package.

BEGIN

    DBMS_MVIEW.EXPLAIN_MVIEW ('Your complete MV script');
    COMMIT;
END;

Execute the above script to check the result as

SELECT * FROM MV_CAPABILITIES_TABLE;


Remember to delete data from this table before executing the package again.

Oracle Pagination

In case you want to retrieve records from a query paginated, for a given range of start to end row, the following will do the trick.


SELECT *
FROM (SELECT acct_id, session_id, ROW_NUMBER () OVER (ORDER BY acct_id) AS rn
            FROM tb_cwlogin
            ORDER BY acct_id)
WHERE rn BETWEEN 1000 AND 1050

There is no issue with ORDER BY clause as well as with the usual "ROWNUM" method.
 
Hope this helped :)

Connecting to sqlplus without TNSNAMES

In case you want to connect to oracle through sqlplus without using TNSNAMES.ORA definition the following connection string can be used

sqlplus userid/password@dbserverIP:1521/dbSID

or

sqlplus /nolog

SQL> connect userid/password@dbserverIP:1521/dbSID

This has been tested on 10g and 11g, not too sure if previous versions can connect like this or not :)

Oracle 11g Win32 Installation - Unable to Retrieve Credentials

I just installed Oracle 11g on my personal notebook, running WinXP SP3 and had the strangest of problems :)

While creating the DB, I got this error, "Unable to Retrieve Credentials" Well here is what I did:

1. Install Oracle 11g as just Software and no DB Creation
2. Update the SQLNet.ORA and changed the Authentication to (NONE)
3. Using Oracle Net Configuration Assistant, created the default Listener
4. Using Oracle Database Configuration Assistant to create a DB Instance

And it worked fine! The Authentication = (NTS) does not seems to work for me in 11g!

Hope I helped some one somewhere ;)

Oracle 10g Rel 2.0 and LOG Errors + Direct Path Insert

Ok... We all know how the batch jobs for loading the data are written...

You know the usual

Cursor

Fetch / FOR CURSOR Loops

Exception Blocks etc.

Oracle 10g Rel 2 has some thing very powerful and efficient that can make the life much easier and improve the performance to a mind bogggling level!

Its the new feature for Logging errors while loading data from one table to another.

Here is an example:

INSERT INTO Target (Col1, Col2, Col3, ... Coln)
SELECT Col1, Col2, Col3, ... Coln FROM Source;

When we use this thing, and there is some problem while loading the data into the Target table, one cannot tell what column caused the problem so we are forced to use the Cursors and loops and handle records individually to trap the errors records.


With Oracle 10g Rel 2.0 we can use the new error loging clause for the Insert statement as:

INSERT INTO Target (Col1, Col2, Col3, ... Coln)
SELECT Col1, Col2, Col3, ... Coln FROM Source
LOG ERRORS REJECT LIMIT UNLIMITED;

Ok that is cool now...

But before we do this, we have got to tell Oracle that there is a table to be used for Error Handling, for this we have to execute a package as:


BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('Target', 'ErrorLog_Target');
END;

This will tell Oracle to create a new table called "ErrorLog_Target" and use it to log errors for any DML on "Target" table where ever we specify the LOG ERRORS clause

After creating the ErrorLog_Target table, loading can take place and it will be much faster compared to CURSORS and LOOPS. We will see how!


There is another very useful Hint that we can use to improve the INSERT performance alot! This is called Driect-Path Inserting

Here is how its done:

INSERT /*+ APPEND */ INTO Target (Col1, Col2, Col3, ... Coln)
SELECT Col1, Col2, Col3, ... Coln FROM Source
LOG ERRORS REJECT LIMIT UNLIMITED;

Simply using the APPEND Hint will do the trick :-)

Here is my test based on a 500k!


SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> SET TIMING ON;
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
Elapsed: 00:00:00.57

SQL>
SQL> DECLARE
2 P_SOURCE VARCHAR2(200);
3 P_DATE DATE;
4
5 BEGIN
6 P_COUNTRY := 'AU';
7 P_DATE := '22-AUG-2008';
8
9 SP_INPUT_FULL ( P_SOURCE, P_DATE );
10 COMMIT;
11 END;
12 /

PL/SQL procedure successfully completed.
Elapsed: 00:10:20.45
SQL>

After I add the APPEND Hint
SQL> /
Elapsed: 00:02:05.35

:-)

ORA-24324 & ORA-24323 Oracle Configuration Assistant

I just installed Oracle 10g Rel 2 on my notebook, Installation was successful but at the time of Oracle Configuration Assistant I got the following Error

ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-28547: connection to server failed, probable Oracle Net admin error

After checking/banging my head for hours I changed one of the parameters in the SQLNet.ORA and it worked :-)


Here is what I changed to solve the issue:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Modified to

SQLNET.AUTHENTICATION_SERVICES = (NONE)

:-)