Oracle Interview Questions - Generic

1, What is Index full scan and index fast full scan ? 

An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a "skinny" version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multiblock IO and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (unordered) data on the leaf blocks.

An index full scan is when we read the index a block at a time - from start to finish. We'll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block - we'll read across the entire bottom of the index - a block at a time - in sorted order. We use single block IO, not multiblock IO for this operation.  

** If the index covers the entire query , it is also called a covering index.

2, What recent issue you have faced and what solution you applied ?

- GG process was creating  blocking session in DB . Wait event was Enq- TM contention

Reason :  Foreign key column is not indexes and hence causing contention.  As per oracle doc, If ver is > 11.2 , Session take TM lock (table exclusive lock) while modifying foreign key column.

- ORA-04031   :  Shared pool out of memory 

CAn occur when Shared pool is configured low or its fragmented.

To avoid this error, 

- Make sure application code is reusing cursor as much as possible. Use bind variables.

- RMAN incremental backup is running slow.

Issue :  Some queries are taking long time and controlfile backup section has more data.

Solution :  We do not have RMAN catalog DB , so here are the options to resolve the issue.

1, Recreate controlfile.

2, Clear RMAN section using DBMS_BACKUP_RESTORE.resetCfileSection() package

3, Tune backup queries which are running long.

I trace session , identify long running queries and  implemented hint using SQL_PATCH to tune the queries. 

- Multiple session complained with message 'resource busy and acquire with NOWAIT specified or timeout expired' 

1, We found blocking session.

2, Different blocker had different wait events  such as  'library cache lock load' and 'enq-SQ contention' and 'Flashback log sync'

3,   I investigated block chain. and found that LGWR was the main holder and was running with wait event 'controlfile sequential read' 

Additionally , I found FS (ORACLE_HOME) was 100% full and archive generation rate was very high in last couple of hours. Due to high load on DB , LGWR was taking long time to read controlfile and same time file system got full so process got hanged.   

3, Why Library cache locks are needed?

Library cache locks aka parse locks are needed to maintain dependency mechanism between objects and their dependent objects like SQL etc. For example, if an object definition need to be modified or if parse locks are to be broken, then dependent objects objects must be invalidated. This dependency is maintained using library cache locks. For example, if a column is dropped from a table then all SQLs dependent upon the table must be invalidated and reparsed during next access to that object. Library cache locks are designed to implement this tracking mechanism.

In a regular enqueue locking scenarios there is a resource (example TM table level lock) and sessions enqueue to lock that resource. More discussion on enqueue locking can be found in Internal of locks. Similarly, library cache locks uses object handles as resource structures and locks are taken on that resource. If the resources are not available in a compatible mode, then sessions must wait for library cache objects to be available.

4, 

What is direct path read in oracle ?

A new feature introduced in Oracle 11gR2 is the way the Direct Path Read operation is done.  A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA.  Direct Path reads have been around for a while when doing parallel operations; however, serial direct path reads are new in Oracle 11gR2.  The direct path read limits the effect of a tablescan on the buffer cache by reading directly into PGA memory.  The direct path read is a feature that benefits both standard hardware and the Exadata Storage Cell, which performs table scans with amazing efficiency.

5, What is PGA (Program Global Area) & SGA ( System Global Area) , background processes ?

PGA is the memory reserved for each user process connecting to an Oracle Database and is allocated when a process is created and deallocated when a process is terminated.

Contents of PGA:-

Private SQL Area: Contains data such as bind information and run-time memory structures. It contains Persistent Area which contains bind information and is freed only when the cursor is closed and Run time Area which is created as the first step of an execute request. This area is freed only when the statement has been executed. The number of Private SQL areas that can be allocated to a user process depends on the OPEN_CURSORS initialization parameter.

Session Memory: Consists of memory allocated to hold a session’s variable and other info related to the session.

SQL Work Areas: Used for memory intensive operations such as: Sort, Hash-join, Bitmap merge, Bitmap Create.

To enable PGA Auto-Mem Management enable the parameter WORKAREA_SIZE_POLICY and allocate total memory to be used for this purpose to PGA_AGGREGATE_TARGET.

SGA : 

- Redo log buffer

The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily, before it is written to disk.

• Every three seconds

• Whenever someone commits

• When LGWR is asked to switch log files

• When the redo buffer gets one-third full or contains 1MB of cached redo log data

• Shared pool

Library cache : 

The library cache stores information about the most recently used SQL and PL/SQL statements.Enables the sharing of commonly used statements.Consists of two structures: 1.Shared SQL area 2.Shared PL/SQL area

Dictionary cache

The data dictionary cache is a collection of the most recently used definitions in the database. It includes information about database files, tables, indexes, columns, users, privileges, and other database objects. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.

- Buffer cache

-Large pool

- Java pool

- Streams pool

Background Processes : 

DBWn defers writing to the data files until one of the following events occurs: 

• Checkpoint 

• Dirty buffers threshold reached 

• No free buffers 

• Timeout 

• RAC ping request 

• Tablespace offline 

• Tablespace read only 

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations: 

• At commit 

• When one-third full 

• When there is 1 MB of redo 

• Every 3 seconds 

• Before DBWn writes

SMON : 

• Instance recovery: 

  Rolls forward changes in the redo logs 

  Opens the database for user access 

  Rolls back uncommitted transactions 

• Coalesces free space ever 3 sec

• Deallocates temporary segments

PMON : 

Cleans up after failed processes by: 

• Rolling back the transaction 

• Releasing locks 

• Releasing other resources 

• Restarts dead dispatchers 

CKPT reponsible for : 

• Signalling DBWn at checkpoints 

• Updating datafile headers with checkpoint information 

• Updating control files with checkpoint information

6,PGA_AGGREGATE_TARGET == Soft limit   PGA_AGGREGATE_LIMIT== Hard limit

set the PGA_AGGREGATE_LIMIT parameter to "0", which means the maximum PGA usage will no longer be managed.

7. When oracle uses temporary tablespace ?

Temporary tablespaces are used to manage space for database sort and joining operations and for storing global temporary tables. For joining two large tables or sorting a bigger result set, Oracle cannot do in memory by using SORT_AREA_SIZE in PGA (Programmable Global Area). Space will be allocated in a temporary tablespace for doing these types of operations. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

8, What is access and filter predicates in explain plan ?

When actual data is accessed then it represented as access in explain plan. For example , Index scan.

Filter : More data is retrived and need to filter data as per predicate information.

9, What happen when commit happened in DB ?

-Oracle generates undo records in the undo segment buffers in the SGA. As you know, the

undo records contain the old values of the updated and deleted table rows.

- Oracle generates redo log entries in the redo log buffers in the SGA.

- Oracle modifies the database buffers in the SGA.

- things that happen AFTER Commit:

- The transaction tables in the redo records are tagged with the unique system change number

(SCN) of the committed transaction.

- The log writer writes the redo log information for the transaction from the redo log buffer to

the redo log files on disk, along with the transaction’s SCN. This is the point at which a commit

is considered complete in Oracle.

- Any locks that Oracle holds are released, and Oracle marks the transaction as complete.

10, What is ACID properties  & CAP theorem ?

Atomicity

Consistency

Isolation

Durability

OLAP Vs OLTP 

11,

Difference between TCP and UDP protocol ?

TCP : TCP stands for Transmission Control Protocol.When you load a web page, your computer sends TCP packets to the web server’s address, asking it to send the web page to you.

UDP : UDP stands for User Datagram Protocol. UDP is used when speed is desirable and error correction is not necessary. For example, UDP is frequently used for live broadcasts and online games.

12, what is isolation level oracle & postgres ?

Isolation Level Dirty Read Nonrepeatable Read Phantom Read

READ UNCOMMITTED Permitted Permitted Permitted

READ COMMITTED -- Permitted Permitted

REPEATABLE READ -- -- Permitted

SERIALIZABLE -- -- --

A SERIALIZABLE transaction operates in an environment that makes it appear as if there are no other users modifying data in the database.

Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.

Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.

13, What is extended statistics ? 

In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, consider a customers table where the values in a cust_state_province column are influenced by the values in a country_id column, because the state of California is only going to be found in the United States. If the Oracle Optimizer is not aware of these real-world relationships, it could potentially miscalculate the cardinality estimate if multiple columns from the same table are used in the where clause of a statement. With extended statistics you have an opportunity to tell the optimizer about these real-world relationships between the columns.

14, What is concurrent statistics ?

Gathering statistics on multiple tables and (sub)partitions concurrently can reduce the overall time it takes to gather statistics by allowing Oracle to fully utilize a multi-processor environment. Concurrent statistics gathering is controlled by a global preference

15, what is parameter file in postgresql ?

postgresql.conf  : 

 This file is read on server startup and when the server receives a SIGHUP

# signal.  If you edit the file on a running system, you have to SIGHUP the

# server for the changes to take effect, run "pg_ctl reload", or execute

# "SELECT pg_reload_conf()".  Some parameters, which are marked below,

# require a server shutdown and restart to take effect.

16, Locks and compression techniques in oracle vs postgresql

17, Hugepages in oracle.

For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle 11g systems running on x86-64 architectures.

Starting from RHEL6/OL6, Transparent HugePages are implemented and enabled by default. They are meant to improve memory management by allowing HugePages to be allocated dynamically by the "khugepaged" kernel thread, rather than at boot time like conventional HugePages. That sounds like a good idea, but unfortunately Transparent HugePages don't play well with Oracle databases and are associated with node reboots in RAC installations and performance problems on both single instance and RAC installations. As a result Oracle recommends disabling Transparent HugePages on all servers running Oracle databases.

Set DB parameter USE_LARGE_PAGES =  TRUE|FALSE|ONLY|AUTO

TRUE : Oracle allocates as much of the SGA as it can in large pages, and if it runs out, it will allocate the rest of the SGA using regular sized pages. This can cause the instance to create additional shared memory segments for the SGA, but the total SGA size will be unchanged. In this supported mixed page mode allocation, the database will exhaust the available large pages before switching to regular sized pages.

FALSE :  Specifies that the instance will not use large pages.

AUTO : Specifies that, during startup, the instance will calculate and request the number of large pages it requires. If the operating system cannot fulfill this request, then the instance will start with a combination of large and regular pages.

ONLY :  Specifies that the instance will fail to start if large pages cannot be used for the entire SGA memory. Oracle recommends this setting for consistent performance.

18, Join methods :

Nested loop join :

Nested loops joins are useful when the database joins small subsets of data, the database joins large sets of data with the optimizer mode set to FIRST_ROWS, or the join condition is an efficient method of accessing the inner table.

The optimizer always tries to put the smallest row source first, making it the driving table.

If the access path for the inner loop is not dependent on the outer loop, then the result can be a Cartesian product: for every iteration of the outer loop, the inner loop produces the same set of rows. To avoid this problem, use other join methods to join two independent row sources.

Hash join :

The database uses a hash join to join larger data sets.

The optimizer uses the smaller of two data sets to build a hash table on the join key in memory, using a deterministic hash function to specify the location in the hash table in which to store each row. The database then scans the larger data set, probing the hash table to find the rows that meet the join condition.

Sort merge join :

A sort merge join is a variation on a nested loops join.

If the two data sets in the join are not already sorted, then the database sorts them. These are the SORT JOIN operations. For each row in the first data set, the database probes the second data set for matching rows and joins them, basing its start position on the match made in the previous iteration. This is the MERGE JOIN operation.

19, IOPS and throughput in storage.

In contrast to sort merges, hash joins require an equality condition.

For new database :  

30 IOPS per transaction is a good number to consider.

20 ,

What is the most complex situation you have ever faced in your career and how did you resolve it ?

XTTS transportable tablespace refresh from ver 12.1 to 12.2 from non-oraas to oraas DB with 4+ region with bi-directional replication 

- Standby 

- DB refresh

21, 

All about ASM : 

-----------------

Advantage of ASM : 

The advantages of ASM are

Disk Addition - Adding a disk is very easy. No downtime is required and file extents are redistributed automatically.

I/O Distribution - I/O is spread over all the available disks automatically, without manual intervention, reducing chances of a hot spot.

Stripe Width - Striping can be fine grained as in redolog files (128K for faster transfer rate) and coarse for datafiles (1MB for transfer of a large number of blocks at one time).

Mirroring - Software mirroring can be set up easily, if hardware mirroring is not available.

Buffering - The ASM file system is not buffered, making it direct I/O capable by design.

Kernelized Asynchronous I/O - There is no special setup necessary to enable kernelized asynchronous I/O, without using raw or third-party file systems such as Veritas Quick I/O.

- External

- Normal ( Two way)

- High (Three Way)

NORMAL REDUNDANCY is the default if you omit the REDUNDANCY clause. Therefore, if you omit this clause, you must create at least two failure groups, or the create operation will fail.

-Extents consist of one or more allocation units (AU)

-Extent size always equals the disk group AU size for the first 20000 extent sets (0 - 19999).

-Extent size equals 4*AU size for the next 20000 extent sets (20000 - 39999).

-Extent size equals 16*AU size for the next 20000 and higher extent sets (40000+).

Oracle ASM Striping :

To stripe data, Oracle ASM separates files into stripes and spreads data evenly across all of the disks in a disk group. The fine-grained stripe size always equals 128 KB in any configuration; this provides lower I/O latency for small I/O operations. The coarse-grained stripe size is always equal to the AU size.

Voting Disk and quorum failure group : 

-You cannot directly influence the number of voting files in one disk group.

-You cannot use the crsctl add | delete votedisk commands on voting files stored in Oracle ASM disk groups because Oracle ASM manages the number of voting files according to the redundancy level of the disk group.

-You cannot add a voting file to a cluster file system if the voting files are stored in an Oracle ASM disk group. Oracle does not support having voting files in Oracle ASM and directly on a cluster file system for the same cluster at the same time.

External redundancy  = 1 Voting disk

Normal redundancy   = 3 Voting disks

High redundancy       = 5 Voting disks

22, What is multiplexing of oracle redo logs and control files ?

A database need atleast two redo log group.  You can multiplex redo log files by adding member to each group in different disks.

Similarly, control file can be multiplexed by defining control_files parameter.

23, What is default block size on OS and Oracle DB ?

- 99% of oracle database runs with 8kB block size.

- Linux has block size 4KB.

24, What is ER (Entity Relationship) diagram ? 

An entity relationship model, also called an entity-relationship (ER) diagram, is a graphical representation of entities and their relationships to each other, typically used in computing in regard to the organization of data within databases or information systems.

There are three main reasons to normalize a database.  The first is to minimize duplicate data, the second is to minimize or avoid data modification issues, and the third is to simplify queries. 

1NF,2NF ,3NF

First Normal Form – The information is stored in a relational table with each column containing atomic values. There are no repeating groups of columns.

Second Normal Form – The table is in first normal form and all the columns depend on the table’s primary key.

Third Normal Form – the table is in second normal form and all of its columns are not transitively dependent on the primary key. Dependency on non primary key attributes.

25, What is ATCSN and AFTERCSN option in goldengate ?

ATCSN

Causes Replicat to start processing at the transaction that has the specified CSN. Any transactions in the trail that have CSN values that are less than the specified one are skipped.

AFTERCSN

Causes Replicat to start processing at the transaction that occurred after the one with the specified CSN. Any transactions in the trail that have CSN values that are less than, or equal to, the specified one are skipped.

25, What is cardinality?  What is access and filter predicates ?

Cardinality is the estimated number of rows the step will return.

Cost is the estimated amount of work the plan will do. 

Access means we are using something to "access" the data - we only "access" relevant data.

Filter means we are getting more data then we need, and we filter it after we get it. The filter will be applied to all rows and only those that pass the filter get sent along.

In general - you'll see "access" when using an index for example. We are using the index to "access" only the rows of interest. You'll see filter when we don't have something to "access" only interesting rows - when we get too many rows and have to filter them.

26, What are enqueues in oracle ?

The Enqueues Oracle metric are locks that serialize access to database resources. 

27,  What are the different types of Oracle enqueues? 

Oracle enqueues are always specified in the form of a 2-digit ID. User enqueues and system enqueues are differentiated as follows:

User enqueues:

TX (transaction enqueue): This enqueue type occurs if you want to change a data record but you cannot do this because a transaction is running in parallel (for example, because the transaction changed the same data record because a unique or primary constraint cannot be guaranteed or because a free ITL slot is no longer available in the block header).

TM (DML enqueue): This enqueue type occurs if a complete object has to be protected against changes (for example, as part of an index rebuild or a consistency check using VALIDATE STRUCTURE). Whenever a TX enqueue blocks table entries, a TM enqueue is also set so that parallel activities such as index rebuilds or consistency checks are not possible. ONE TM enqueue is set for each transaction and changed object.

UL (user-defined enqueue): A transaction has set an enqueue using DBMS_LOCK.REQUEST

System enqueues:

ST (space transaction enqueue): This enqueue is held in dictionary-managed tablespaces within extent allocations and releases.

CI (Cross instance call invocation enqueue)

TT (Temporary table enqueue)

US (Undo segment enqueue)

CF (Control file enqueue)

TC (Thread checkpoint enqueue)

RO (Reuse object enqueue)

... and so on However, there are numerous other system enqueues which are generally negligible because system enqueues are only held for a very short time.

As of Oracle 10g, the table V$LOCK_TYPE contains an overview of all enqueues that exist.

28 ,

FREELIST, PCTFREE and PCTUSED

While creating / altering any table/index, Oracle used two storage parameters for space control.

PCTFREE - The percentage of space reserved for future update of existing data.
The default value is 10.

PCTUSED - The percentage of minimum space used for insertion of new row data. Defaults to 40.

This value determines when the block gets back into the FREELISTS structure.

• You cannot specify this parameter for an index or for the index segment of an index-organized table.
• This parameter is not useful and is ignored for objects with automatic segment-space management.

FREELIST - Structure where Oracle maintains a list of all free available blocks.

Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.

When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value.

Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

INITRANS

Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1 and default value for an index is 2.

MAXTRANS

Oracle now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

29 , Difference between union and union all.

By default, the UNION operator returns the unique rows from both result sets. If you want to retain the duplicate rows, you explicitly use UNION ALL.

30, What is SPA ( SQL Performance Analyzer ) ?

you can execute the SQL workload remotely on a separate database using a database link. SQL Performance Analyzer will establish a connection to the remote database using the database link, execute the SQL statements on that database, collect the execution plans and run-time statistics for each SQL statement, and store the results in a SQL trial on the local database that can be used for later analysis. This method is useful in cases where you want to:

• Test a database upgrade
• Execute the SQL workload on a system running another version of Oracle Database
• Store the results from the SQL Performance Analyzer analysis on a separate test system
• Perform testing on multiple systems with different hardware configurations
• Use the newest features in SQL Performance Analyzer even if you are using an older version of Oracle Database on your production system

Once the SQL workload is executed, the resulting execution plans and run-time statistics are stored in a SQL trial.

31, Performance Tuning tips : 

- Use hash join for large data sets

- Use Nested loop for small datasets.

- A helpful tip to remember is to use the EXISTS function instead of the IN function in most circumstances. 

32, What is domain index ? 

A domain index is an index designed for a specialized domain, such as spatial or image processing.

33,  Difference between varchar and varchar2 data types?

Varchar can store upto 2000 bytes and varchar2 can store upto 4000 bytes. Varchar will occupy space for NULL values and Varchar2 will not occupy any space. Both are differed with respect to space.

34, What is a sub query and what are the different types of subqueries?

Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.

There are two different types of subqueries:

• Correlated sub query

A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.

• Non-Correlated subquery

This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.

35, What is difference between Cartesian Join and Cross Join?

There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.

Cross join without where clause gives Cartesian product.

35, CLUSTERING_FACTOR for Index. 

This defines how ordered the rows are in the index.If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

36, OPTIMIZER_MODE in oracle.

first_rows mode

This is a cost-based optimizer mode that will return rows as soon as possible, even if the overall query runs longer or consumes more resources. The first_rows optimizer mode usually involves choosing a full-index scan over a parallel full-table scan. Because the first_rows mode favors index scans over full-table scans, the first_rows mode is most appropriate for online systems where the end userwants to see some results as quickly as possible.

all_rows mode (Default)

This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row. This usually involves choosing a parallel full-table scan over a full-index scan. Because the all_rows mode favors full-table scans, the all_rows mode is best suited for batch-oriented queries where intermediate rows are not required for viewing.

37, Dictionary and fixed objects Statistics. 

Dictionary statistics include the statistics on the tables and indexes owned by SYS (and other internal RDBMS schemas like SYSTEM) and the statistics on the fixed objects. Fixed objects are the internal X$ tables and the so called dynamic performance views or V$ views which are based upon them. These are not real tables and indexes, but rather memory structures. The statistics for the fixed objects need to be gathered manually; they are not updated by the automatic statistics gathering.

38, What is select ..for update ?

The SELECT statement associated with the cursor does not have any locks on the rows it returns, allowing any session to perform any operation on those rows during the cursor operation.

When we want to issue a lock over the record set returned by the cursor associated SELECT query, we can opt for the FOR UPDATE clause, which automatically places an exclusive row-level lock on all the rows retrieved, forbidding any other session from performing a DML operation on them until we perform a commit or a rollback to release the lock.

39, What is temporary table and global temporary table ?

In Oracle there isn't any difference. When you create a temporary table in an Oracle database, it is automatically global, and you are required to include the "Global" key word. 

ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session. 

ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

Miscellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.

What is external table in oracle ? 

External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations.

Local Vs Global Index in oracle.

Global Index:  A global index is a one-to-many relationship, allowing one index partition to map to many table partitions.  The docs says that a "global index  can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table".

Local Index: A local index is a one-to-one mapping between a index partition and a table partition.  In general, local indexes allow for a cleaner "divide and conquer" approach for generating fast SQL execution plans with partition pruning.

What is Index Organized Table  in oracle ? 

An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure.

Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast to this, index-organized tables store rows in a B-tree index structure that is logically sorted in primary key order. Unlike normal primary key indexes, which store only the columns included in its definition, IOT indexes store all the columns of the table.

XTTS Method : 

• Phase 1 - Initial Setup phase
• Phase 2 - Prepare phase
• Phase 3 - Roll Forward phase
• Phase 4 - Final Incremental Backup
• Phase 5 - Transport Phase: Import Metadata
• Phase 6 - Validate the Transported Data
• Phase 7 - Cleanup

What is fuzzy file in oracle ? 

A datafile that contains a block whose System Change Number (SCN) is more recent than the SCN of its header is called a fuzzy datafile. If FUZZY=YES for some datafiles, it means more recovery is required.

Undo extents, Undo retention and undo guaranteed retention :

Active undo extents are used by running transactions. These extents will never be overwritten as they are needed in order to perform ROLLBACK operations. 

Expired Undo Extents hold committed information that is older than the UNDO_RETENTION period.

Unexpired undo Extents hold committed information that its age is less than the UNDO_RETENTION period.

Undo retention Guarantee : 

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

IMP parameters : 

• Cursor_sharing = Exact / Force (default : EXACT ): 

If cursor_sharing = force then bind peeking will take place and optimizer can pick plan based on bind peeking. Enable adaptive cursor sharing help choose optimal plan for specific bind values. 

 

• optimizer_mode=FIRST_ROWS/ALL_ROWS (default : ALL_ROWS): 

Data Warehouse : 

1. Fact and dimension table : 

Fact Table:

A fact table is a primary table in a dimensional model.

A Fact Table contains

Measurements/facts

Foreign key to dimension table

Dimension table:

A dimension table contains dimensions of a fact.

They are joined to fact table via a foreign key.

Dimension tables are de-normalized tables.

The Dimension Attributes are the various columns in a dimension table

Dimensions offers descriptive characteristics of the facts with the help of their attributes

No set limit set for given for number of dimensions

The dimension can also contain one or more hierarchical relationships

2. Start and snow flake schema :