Oracle Database Important Topics

 

1. What are the components of the physical database structure of the Oracle database?

Components of the physical database structure are given below.

• One or more data files.
• Two or more redo log files.
• One or more control files.

2. What are the components of logical database structure in Oracle database?

Components of the logical database structure.

• Tablespaces
• Database's schema objects

3. What is a tablespace?

A database contains Logical Storage Unit called tablespaces. A tablespace is a set of
related logical structures. Actually, a tablespace group related logical structures together.

4. What is a SYSTEM tablespace and when it is created?

When the database is created in Oracle database system, it automatically generate a
SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary
tables for the entire database.

5. What is an Oracle table ?

A table is basic unit of data storage in Oracle database. A table contains all the
accessible information of a user in rows and columns.

6. What is bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it does not copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can also take the backup of data easily.

7. What is the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running.

8. What is a snapshot in the Oracle database?

A snapshot is a replica of a target master table from a single point in time. In simple words you can say, snapshot is a copy of a table on a remote database.

9. What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also.

Hot backup (Online Backup): A hot backup is also known as online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as an offline backup because it is done while the database has been shutdown using the SHUTDOWN normal command. If the database is suddenly shutdown with an uncertain condition, it should be restarted with RESTRICT mode and then shutdown with the NORMAL option.

For a complete cold backup, the following files must be backed up.

All data files, All control files, All online redo log files(optional) and the init.ora file (you can recreate it manually).

10. How many memory layers are in the Oracle shared pool?

Oracle shared pools contain two layers:

1. library cache
2. data dictionary cache

11 What is a save point in the Oracle database?

Save points are used to divide a transaction into smaller parts. It allows the rolling back of a transaction. A maximum of five save points are allowed. It is used to save our data. You can roll back whenever you encounter an error from where you save your SAVEPOINT.

12. What is a hash cluster in Oracle?

Hash cluster is a technique to store data in a hash table and improve the performance of data retrieval. A hash function is applied to the table row's cluster key value and stored in the hash cluster.

13. What are the various Oracle database objects?

Tables: This is a set of elements organized in a vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is a virtual table derived from one or more tables.

Indexes: This is a performance-tuning method to process the records.

Synonyms: This is a name for tables.

14. What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try to query. With this trigger's help, you can dynamically modify the where clause part.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Pre-query trigger fires before the Pre-select trigger.

15. What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:

• Form module
• Menu module
• Pl/SQL Library module
• Object Library module

16. Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

17 What types of joins are used in writing SUBQUERIES?

• Self join
• Outer Join
• Equi-join

18. What is the usage of the control file in Oracle?

In Oracle, the control file is used for database recovery. The control file is also used to identify the database and redo log files that must be opened for database operation to go ahead whenever an instance of an ORACLE database begins.

19. What is a synonym?

A synonym is also known as an alias for a table, view, sequence or program unit.

20. What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can only be accessed by the owner.

Public: It can be accessed by any database user.

21. What is the usage of synonyms?

• Synonyms can be used to hide an object's real name and owner.
• It provides public access to an object.
• It also provides location transparency for a remote database's tables, views or program units.
• It simplifies the SQL statements for database users.

22. How do you store pictures in a database?

You can store pictures in a database using the Long Raw Data type. This data type is used to store binary data for 2 gigabytes of length. However, the table can have only one Long Raw data type.

23. What is the BLOB data type in Oracle?

BLOB data type is a data type with varying length binary string. It is used to store two gigabytes memory. For the BLOB data type, the length needs to be specified in bytes.

24. What is the difference between TRANSLATE and REPLACE in Oracle?

Translate is used to substitute a character by character while Replace is used to substitute a single character with a word.

25. What are the different types of database objects?

A list of different types of database objects:

• Tables: This is a set of elements organized vertically and horizontally.
• Tablespaces: This is a logical storage unit in Oracle.
• Views: It is a virtual table derived from one or more tables.
• Indexes: This is a performance-tuning method to process the records.
• Synonyms: This is a name for tables.

26. What is the usage of Save Points in the Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There is a maximum of 5 save points allowed in the Oracle Database. Whenever an error is encountered, it is possible to roll back from where the SAVEPOINT has been saved.

27. What is the difference between a post-database commit and a post-form commit?

The post-database commit trigger is executed after Oracle forms issue the commit to the finalized transaction while the post-form commit is fired during the post-and-commit transactions process, after the database commit occurs.

28. What is Logical backup in Oracle?

Logical backup is used to read a set of database records and write them into a file. An Export utility is used to take the backup, while an Import utility is used to recover from the backup.

29. What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. It is done by creating a group of log files together. This ensures that LGWR automatically writes them to all the members of the current online redo log group. If the group fails, the database automatically switches over to the next group. It diminishes the performance.

30. What is the meaning of recursive hints in Oracle?

The number of times a dictionary table is repeatedly called by various processes is known as a recursive hint. The recursive hint is occurred because of the small size of the data dictionary cache.

31. What are the limitations of the CHECK constraint?

The main limitation of the CHECK constraint is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub-queries.

32. What is the use of the GRANT option in the IMP command?

GRANT is used to import object grants.

33. What is the use of the ROWS option in the IMP command?

The ROWS option indicates whether the table rows should be imported.

34. What is the use of the INDEXES option in the IMP command?

The INDEXES option is used to determine whether indexes are imported.

35. What is the use of IGNORE option in the IMP command?

The IGNORE option is used to specify how object creation errors should be handled.

36. What is the use of the SHOW option in the IMP command?

The SHOW option specifies when the value of show=y, the DDL within the export file is displayed.

---

37. What is the use of the FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple files can be listed, separated by commas.

38. What are the extensions used by Oracle reports?

Oracle reports are used to make business enable with the facility to provide information on all levels within or outside in a secure way. Oracle report uses REP files and RDF file extensions.

39. How does ROWID help in running a query faster?

ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row and results in a faster query.

RowNum: RowNum is pseudo column. A pseudo column means a column which not exist the table physically, but we can use this in any select statement. Roenum is used to get the row number of the select query. if we have 10 records in the SQL query, then rownum will be 1 to 10 for this query.

40. What are database links used for?

Database links are created in order to form communication between various databases or different environments like test, development and production. The database links are read-only to access other information as well.

Different Between Oracle & SQL Server :

• Oracle supports star query optimization, while SQL server doesn’t offer query optimization.
• In oracle, values do not change before committing, whereas in SQL Server, values are changed even before committing.
• Oracle allows rollback during the transaction process, whereas SQL server doesn’t allow rollback in the transaction process.
• Oracle supports many “Schemas” with the instance, whereas SQL server offers “Schemas” within each user database.
• Oracle allows database, full, file-level, incremental & differential backups, on the other hand, SQL server allows full, partial, and incremental backups.
• Oracle uses both “after” and “before” triggers, whereas SQL server mostly uses only “after” triggers.
• Oracle allows Query optimization.
• Values do not change before committing.
• Oracle allows rollback during transactions.
• Oracle use after and before trigger, but the SQL server allows only after the trigger.

Mutating Trigger error :

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger.

A mutating table error (ORA-04091) occurs when a row-level trigger tries to modify or select data from the table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). If the  DML operation fire a trigger and from within the body of trigger if we are trying to read or write the same table then we will get a mutating trigger error. Mutating trigger will get only row level trigger and it is not from statement level trigger.

How to solved Mutating trigger ?

To avoid mutating error first we need to declare global variable within package specification. Then we need to create a statement level trigger and put the select statement into this trigger and value assign into global variable. After that we need to create low level trigger and use this global variable instead of select statement.

Beside this we can also avoid mutating error using compound trigger.

Compound Trigger: A compound trigger combines of the following triggers into one single trigger: 
1.Before statement trigger
2.Before row trigger
3.After row trigger
4.After statement trigger

What is locking in SQL? Describe its types?

Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are: 

• Implicit Locking: This occurs for all SQL statements except SELECT.
• Explicit Locking: This can be done by the user manually.

Further, there are two locking methods:

1. Exclusive: Locks out other users
2. Share: Allows other users to access

Analytical Function :

Analytic functions calculate an aggregate value based on a group of rows. Analytical functions are 

aggregate functions, but we can apply the aggregate function on the table or the selected area in the where clause. The analytical function is an aggregate function but offers additional stuff.

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

Different Types of Join :

Inner Join: Inner Join return only matching record.

Left Outer Join: Inner Join+fetch any additional records from the left table which is not present

in the right table.

Right Outer Join: Inner Join+fetch any additional records from the right table which is not present

in the left table.

Full Outer Join: Inner Join

               +Fetch any additional records from the left table which is not present in the right table.

               +Fetch any additional records from the right table which is not present in the left table.

Cross Join: Every record from the first table will try to match every record from the second table.

Natural Join: Natural join is not recommended. There is no need to mention the joining condition. It will

automatically calculate the joining column name if it is the same column.

If the same column name exists within two tables, then it will return Inner Join, and if the same.

If the column does not exist within two tables, it will return Cross Join.

Self Join: Usually a join happens between two tables which are based on certain conditions when instead of two different tables, we use the same table for joining which is referred to as a self join.

Index In Oracle :

Indexes are database objects which can be created on a table or table cluster and, if applied correctly, can

speed up data retrieval in certain cases. Indexes are database object which actually resides in memory.

The index is useful when we use 5%-10% of the data.

Query Explain: EXPLAIN PLAN FOR (Add This before select statement).

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

Analyze Index: analyze index idx_ind(index name) validate structure

select height, name,blks,br_blks from index_status;

Types of indexes: 1. B-Tree Index 2.Bitmap Index 3. Function-Based Index 4. Reverse key Index 5. Composite Index

1.BTree Index: Default Index. B-Tree shorts, for balanced trees, are the most common type of

database index. If we don't specify any keyword while creating an index, it will go ahead and

create a B-tree Index. By default, Oracle creates B-Tree Index. B-tree index scan types are-

Range Scan, Unique Scan, Full Scan, Full Scan, Fast full scan.

2. Bitmap Index

Oracle uses two methods to access the data when we submit a query. One is a Table scan, and

another is an Index scan. If Index is not there, then oracle uses a Table scan. Table scan searches each

and every record of the table.

Two types of Index scans -Index full scan and Index fast full scan. If all the columns that are part of

our select query should be part of our composite index, and if we use order by clause, then we should

use the column as taken in my composite index. Also, it will be a fas1.t full scan.

Different between B-Tree and BITMAP Index:

1. BTREE Store data in tree format. BITMAP indexes are stored as dimensional arrays.

2. BTREE Index is created on high cardinality columns. BITMAP  Index is created on 

    low cardinality (low number of distinct values in a column. Ex-Male, Female ).

3. we should create a BITMAP index only if the data is being modified by a single proc

BTREE Reverse key Index :

Does creating primary key constraints always create an index automatically?

When we create a constraint oracle will create an index if the column is not indexed already if the column is already indexed then oracle will not create a new index.

Will dropping constraints will drop the underlying index? 

Yes, but not always. Whenever we are dropping a constraint the associated index will get automatically dropped provided that index was created as part of the constraint creation otherwise the index will not drop.

Can a primary key create a NONUNIQUE index?

Normally when we create a primary key constraint oracle will automatically create a unique index. But in some scenarios, a primary key will create a non-unique index. By using 

DEFERRABLE NOVALIDATE keyword we can create a primary key that will create a non-unique index.

Different between Constraints & Index :

1. Unique Constraints are for data integrity, whereas Unique Index is for performance improvement.

Table Partition:

Partition subdivided our table into smaller pieces. Any table bigger than 2GB may be a possible contender for partitioning. So the first thing that partition gave us was an improvement in performance. The second thing is data manageability.

Local Index: If we create an index that is not part of the partitioning key, we should consider a local index.

Global Index: If we create an index which is part of the partitioning key, then we should consider a global index.

List Partition:

Range Partition:

Hash Partition: If we want to distribute data equally among all the nodes, then we can create a hash partition.

Composite Partition :

Advantages of Partition :

Performance: 

    1. Partition pruning 

    2. Partition wise join

Manageability: 

    1. Divide and conquer approach 

    2. Faster backup and restore

    3. Low storage usage

Example: CREATE TABLE TRANSACTION_DEMO (TRANSACTION_ID NUMBER,TRANSACTION_TYPE VARCHAR2(30),AMOUNT NUMBER, TRANSACTION_DATE DATE); CREATE UNIQUE INDEX IDX_TRAN_ID ON TRANSACTION_DEMO(TRANSACTION_ID); SELECT * FROM TRANSACTION_DEMO; INSERT INTO TRANSACTION_DEMO SELECT ROWNUM,'CREDIT',FLOOR(DBMS_RANDOM.VALUE(90,9000)),TRUNC(SYSDATE) FROM DUAL CONNECT BY LEVEL <=1000000; SELECT SEGMENT_NAME,BLOCKS,BYTES/1024/1024 AS MB FROM USER_SEGMENTS WHERE SEGMENT_NAME='TRANSACTION_DEMO'; ANALYZE INDEX IDX_TRAN_ID VALIDATE STRUCTURE; SELECT * FROM INDEX_STATS;

Dual Table: When the Oracle database is installed, then it will automatically create a DUAL table. It is

created on the sys user and available for all users. The column of this table is DUMMY value of this column is

X and Data Type is Varchar2(1);

Different between Delete, Truncate and Drop:

Delete: Can delete partial data.DML Operation. Recover using rollback.No impact on dependent objects.

Deleted data is temporary store in the redo log.

Truncate: Remove the entire data.DDL Operation. Data lost forever.No impact dependent objects.

Drop: Removes the object.DDL Operation. Data can be recovered (Flashback option).

Dependent objects will have to be created.

Different between Procedure and function: Functions are normally used for computations, while

Procedures usually execute business logic. The procedure can't be called from the SQL statement but

The function can be called from an SQL statement.

Can we use OUT and INOUT parameters in the function?

Yes, we can use OUT and INOUT parameters in a function. But there is a restriction. This type of function we can't use within select statements. We can call this type of function within PL/SQL block.

Can the function return more than one value?

From a function, we can have only one return statement. But we can send more than one value as a

part of the Return. That means if we want to return more than one value, we can use the collection and

Ref Cursor as a return statement.

Data Type :

Scalar Data Type: Contains only one value.

Composite Data Type :

Collection: A collection is an ordered group of logically related elements. In a collection, the internal component always has the same data type.

Records: The internal component can have different data types in a record.

SQL: Structured Query Language. SQL is used to communicate with the database. There are different types of SQL.,

DDL: Data Definition Language. Create, Alter, Drop, Truncate. All the DDL are auto-commit.

DML: Data Manipulation Language. Insert, Update, Delete, Merge.

DCL: Data Control Language. Grant, Revoke.

TCL: Transaction Control Language. Commit, Rollback, Savepoint.

DRL: Data Retrieval Language. Select.

Table Function & Pipelined Function: Both Table Function & Pipelined Function returns collection to the caller, and the function can be used as if it's a table.

The Table function loads all the values into the collection, and finally, the collection is returned to the caller.

The pipelined function returns the value as and when the value is ready to send back to the caller.

Commit Inside Trigger: Normally, we can't use Commit within the trigger. But if we use Pragma autonomous_transaction within the trigger, we use Commit within the trigger.

Calling a function having DML Statement in a Select statement: Normally, we can't call a function within a select statement with a DML statement. But if we use Pragma autonomous_transaction within the function, we can call this function within the select statement.

How can we get unique records without using Distinct in oracle?

We can use unique keywords in the select statement. (select unique roll, name from sts). We can use group by clause. We can also use unique records by using the Union keyword. We can also use intersect and Analytical functions (ROW_NUMBER(), RANK()).

Data Integrity: Data integrity adherence to business rules.

Delete duplicate records:
1. By using ROWID we can delete duplicate rows. For defining specific rows we can use the aggregate function max or min. Example :
select  * from emp where rowid not in(select max(rowid), empno, empname from emp 

group by empno, empname);

2. By using co-related subquery we can also delete duplicate rows.

Cursor: A cursor is a private memory area where data is stored temporarily and processing a specific select or DML statement.

LISTAGG: By using the LISTAGG function we can convert the Row value into a column value. But we can convert only 4000 bytes. If the value is greater than 4000 bytes then it will give an error. By using ON OVERFLOW TRUNCATE we can avoid this error. Syntex:

LISTAGG(EMP_NAME,',') GROUP WITHIN (ORDER BY EMP_NAME) 

LISTAGG(EMP_NAME,',' ON OVERFLOW TRUNCATE) GROUP WITHIN (ORDER BY EMP_NAME) 

IDENTITY COLUMN: If we use the IDENTITY  keyword in table creation then oracle will automatically generate an auto number. There are three options available for IDENTITY.

1. Generated always as identity (not allowed manually insert)

2. Generated by default as identity(It can allow manually insert value but not allow null value)

3. Generated by default on null as identity(It will allow null value but if we insert null value then it will automatically insert value)

Regular Expression: There are five Regular Expression support Oracle.

1.REGEXP_LIKE

2. REGEXP_COUNT

3. REGEXP_INSTR

4. REGEXP_REPLACE

5. REGEXP_SUBSTR

How SQL Statement works internally in Oracle?

When the Oracle database server receives the SQL statement the database server processes the statement in a sequence of steps:

1. Syntax Check: Check the SQL statement for syntactic validity(All keywords are present).

2. Semantic Check: (Priviledge check,Object existence,Dependancy Check) A semantic check determines whether objects used in the SQL statement is meaningful, for eg, whether accessed tables and column exists in the database. If the mentioned table or object is invalid or not present in the database the Oracle database throws an appropriate exception to the caller.

3. Shared Pool Check: In case if exactly the same statement was previously executed by Oracle, then all the execution-related information will be available in the shared pool memory then oracle will reuse the same information for executing the statement again otherwise, oracle will proceed with the next set of processes.

4. Optimization: Generate multiple execution plans, and compare and pick the optimal plan for execution.

5. Row source generation: Oracle will generate the step wise execution plan which will be given as an input to the executor.

6. Execution : Executor Execute the statement and send back the result to user.

Interview questions for SQL Developer:

1) what is the query optimization technique in Oracle?
2) What are the types of SQL joins, and when would you use each one?
3) what are the global temporary tables?
4) what are the constraints? what are its types?
5) Diff between union and union all?
6) Query to display an even number of records?
7) Query to display the employee whose name starts with S?
8) Query to display the name of the employee, which contains M in any position in their name?
9) How to delete duplicate records?
10) What is an index, and how can it improve query performance?
11) What is normalization? Explain the first normal form and second normal form?
12) Diff between varchar and varchar2?
13) Write a query to display the current date?
14) Refresh types in materialized view?-dbms_mview.refresh
15) Diff between a clustered and a non-clustered index in SQL?
16) Diff between delete and truncate?
17) What are views? Types of views?
18) Explain the candidate key, primary key, and super key?
19) Explain the concept of a self-join in SQL and when it might be used?
20) What is the dual table?
21) What is Explain plan?
22) What are Oracle hints?
23) Different types of SQL commands?
24) Diff between exists and in?
25) Oracle 11g, 12c, 19c features?
26) What are window functions? Explain with examples?
27) What is synonyms? Types of synonyms?
28) Diff between decode and case?
29) Diff between view and materialized view?
30) What is inline view?
31) What are the analytical functions?
32) Diff between left outer join and right outer join?
33) What are the different types of subqueries in SQL and how are they used?
34) what are the joins? types of joins?
35) what is diff between DDL and DML?
36) what are the union, unionall, intersect, minus operators?
37) how to find list of the indexes for a given table?
38) what are the benefits and drawbacks of indexes?
39) what is function based index?
40) what are partitions? explain diff types?
41) what is substr() , instr() functions?
42) query to find nth highest salary?
43) diff between nvl, nvl2 and coalsce function?
44) diff between replace and translate?
45) what is with check option clause in view? what is force view?
46) Explain on delete cascade?
47) what is pivot function?
48) what is explain plan?
49) how to update complex view?
50) how to get unique records without using distinct clause?