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 ;)