When executing a Materialized View refresh an ORA-01555 is returned. This returns the following type of error either in the alert.log : There are several solutions that can assist here, outside of the normal UNDO Tuning practices from the note above: 1. Increase the UNDO_RETENTION parameter beyond the threshold seen from the alert.log message for … Read more
Refreshing a Materialized View via the Oracle job scheduler reports the following errors, however manual refresh succeeds. Troubleshooting steps: Object Privileges – If the owner has not granted explicit rights to these objects the scheduled job will fail. To implement the solution, please execute the following steps: Example: If the mview is REFRESH FAST type, … Read more
Oracle’s Distributed Database Option provides the ability to perform transactions spanning multiple networked databases. As in standalone databases, transactions may block each other as they try to exclusively access database table rows, causing Locking Conflicts.In the former case, tools and scripts exist that can help the DBA detect and resolve such conflicts. In distributed databases, … Read more
You can use Oracle OPatch utility to check all the Oracle Patches applied in Oracle database. opatch utility is located under $ORACLE_HOME/OPatch directory. Run the opatch lsinventory command to get the list of interim patches applied. 1. Listing Applied Patches 2. Using opatch lsinventory 3. To get information on particular patch ID/ Check patch is … Read more
What is Oracle Clusterware – RAC ? Oracle Clusterware enables servers to communicate with each other, so that they appear to function as a collective unit. This combination of servers is commonly known as a “cluster”. Oracle Real Application Clusters known as Oracle RAC uses Oracle Clusterware as the infrastructure that binds multiple nodes that … Read more
You can query the v$session_longops view to find long-running queries and you can query the AWR to find historical queries (if you have purchased the AWR packs).. The Oracle data dictionary contains a little-known view called the v$session_longops. The v$session_longops view allows the Oracle professional contract the amount of time that is used by long-running … Read more
To find stale objects and the last analyzed date for tables and indexes in an Oracle Database, you can query the data dictionary views DBA_TAB_STATISTICS, DBA_IND_STATISTICS, and DBA_OBJECTS. Stale statistics are indicated by the STALE_STATS column in the DBA_TAB_STATISTICS view for tables and in the DBA_IND_STATISTICS view for indexes. Find Stale Tables Find Stale Indexes … Read more
Temporary Tablespaces A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as … Read more
A materialized view create with the automatic refresh can not be alter to stop refreshing. In order to do that you must break the dbms_job that was created in order to refresh the view. Follow the steps for 12.2.0.1: 1. Connect as mview owner. 2. select * from user_jobs where broken =’N’; 3. In the … Read more
Oracle RMAN was able to take incremental backups already in 9i. However, prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup. Oracle … Read more