Initialization parameters are configuration parameters that affect the operation of an instance. The instance reads these initialization parameters from either a text initialization parameter file(pfile/init.ora) or a server parameter file (spfile) at startup time. There are many different initialization parameters to optimize operation in various environments. Normally when you want to start your oracle database […]
oracle
What is a FACTLESS FACT TABLE? Where we use Factless Fact
In a dimensional design, the fact table is the locus for process measurement. It is the place where measurements are stored. The measurements are called facts, which is where the fact table gets its name. Paradoxically, a fact table does not always require facts to measure a process. A fact table that contains no facts […]
Sample listener.ora file for Oracle
LISTENER.ORA is a configuration file used to control the operation of an Oracle listener on the server. You can have more than one listener configured in one LISTENER.ORA file if each has a unique name. The listerner.ora file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server. […]
Archiving Failures with ORA-16038, ORA-19504, ORA-00312
Problem 1 After the server was patched on OS level, the following database error messages are reported in the alert.log file: Unable to create archive log file ‘/path/name.arc’ ARC3: Error 19504 Creating archive log file to ‘/path/name.arc’ ARCH: Archival stopped, error occured. Will continue retrying. ORACLE instance “name” – Archival error ORA-16038: log 3 sequence […]
IN and EXISTS SQL Operator
EXISTS Operator Use EXISTS to test if a subquery returns results or not. Typically, the subquery references another table. The following query returns employees who also happen to be customers: SELECT e.id, e.name FROM employees e WHERE EXISTS (SELECT * FROM customers c WHERE c.email = e.email); IN Operator Use IN to test whether a […]
Oracle Interview Questions – Indexes on Partitioned Table
What is local and global index? Local index is where the index is equipartitioned with its table, the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its table. So each index partition is associated with exactly one partition of the underlying table, that is, […]
SQL Script to Monitor Usage of Indexes
You have an environment that is heavily indexed, and you want to monitor the usage of the indexes. For example, at the end of the week before the batch loads, you would like to check which indexes are being used in queries throughout the week. You can find the index usage from the explain plan. […]
Table Vs. Materialized View
What are Materialized Views Oracle Materialized Views or MVs are a good option for data warehousing and replication. MVs based on inner/outer equijoin can be refreshed on-demand or periodically if desired. MVs that are based on sub-queries on remote tables support bi-directional replication. An MV includes a query that is transparent to the user, and […]
What are Local and Global Indexes in Oracle Database
Background information In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute. In a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table […]
WITH Clause in ORACLE
As per Oracle Documentation, the WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table. You […]