Oracle interview questions and answers for beginners and professionals provides a list of top frequently asked oracle interview questions with java, .net, php so on.

If there is any new oracle interview question that have been asked to you, kindly post it in the the comment section.

 

1. Difference between varchar and varchar2 data types?

Ans:

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.

2. In which language Oracle has been developed?

Ans:

Oracle has been developed using C Language.

3. What is RAW datatype?

Ans:

RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.

4. What is the use of NVL function?

Ans:

The NVL function is used to replace NULL values with another or given value. Example is –
NVL(Value, replace value)

5. Whether any commands are used for Months calculation? If so, What are they?

Ans:

In Oracle, months_between function is used to find number of months between the given dates. Example is –
Months_between(Date 1, Date 2)

6. What are nested tables?

Ans:

Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.

7. What is COALESCE function?

Ans:

COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.
Coalesce(value1, value2,value3,…)

8. What is BLOB datatype?

Ans:

A BLOB data type is a varying length binary string which is used to store two gigabytes memory. Length should be specified in Bytes for BLOB.

9. How do we represent comments in Oracle?

Ans:

Comments in Oracle can be represented in two ways –
Two dashes(–) before beginning of the line – Single statement
/*—— */ is used to represent it as comments for block of statement

10. What is DML?

Ans:

Data Manipulation Language (DML) is used to access and manipulate data in the existing objects. DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.

 

11. What is the difference between TRANSLATE and REPLACE?

Ans:

Translate is used for character by character substitution and Replace is used substitute a single character with a word.

12. How do we display rows from the table without duplicates?

Ans:

Duplicate rows can be removed by using the keyword DISTINCT in the select statement.

13. What is the usage of Merge Statement?

Ans:

Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.

14. What is NULL value in oracle?

Ans:

NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.

15. What is USING Clause and give example?

Ans:

The USING clause is used to specify with the column to test for equality when two tables are joined.
[sql]Select * from employee join salary using employee ID[/sql]
Employee tables join with the Salary tables with the Employee ID.

16. What is key preserved table?

Ans:

A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.

17. What is WITH CHECK OPTION?

Ans:

The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.

18. What is the use of Aggregate functions in Oracle?

Ans:

Aggregate function is a function where values of multiple rows or records are joined together to get a single value output. Common aggregate functions are –

  • Average
  • Count
  • Sum

19. What do you mean by GROUP BY Clause?

Ans:

A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.

20. What is a sub query and what are the different types of subqueries?

Ans:

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.

 

21. What is cross join?

Ans:

Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.

22. What are temporal data types in Oracle?

Ans:

Oracle provides following temporal data types:

  • Date Data Type – Different formats of Dates
  • TimeStamp Data Type – Different formats of Time Stamp
  • Interval Data Type – Interval between dates and time

23. How do we create privileges in Oracle?

Ans:

A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]

24. What is VArray?

Ans:

VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.

25. How do we get field details of a table?

Ans:

Describe is used to get the field details of a specified table.

26. What is the difference between rename and alias?

Ans:

Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename
is nothing but replacement of name and Alias is an alternate name of the table or column.

27. What is a View?

Ans:

View is a logical table which based on one or more tables or views. The tables upon which the view is based are called Base Tables and it doesn’t contain data.

28. What is a cursor variable?

Ans:

A cursor variable is associated with different statements which can hold different values at run time. A cursor variable is a kind of reference type.

29. What are cursor attributes?

Ans:

Each cursor in Oracle has set of attributes which enables an application program to test the state of the cursor. The attributes can be used to check whether cursor is opened or closed, found or not found and also find row count.

30. What are SET operators?

Ans:

SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.

 

31. How can we delete duplicate rows in a table?

Ans:

Duplicate rows in the table can be deleted by using ROWID.

32. What are the attributes of Cursor?

Ans:

Attributes of Cursor are
%FOUND
Returns NULL if cursor is open and fetch has not been executed
Returns TRUE if the fetch of cursor is executed successfully.
Returns False if no rows are returned.
%NOT FOUND
Returns NULL if cursor is open and fetch has not been executed
Returns False if fetch has been executed
Returns True if no row was returned
%ISOPEN
Returns true if the cursor is open
Returns false if the cursor is closed
%ROWCOUNT
Returns the number of rows fetched. It has to be iterated through entire cursor to give exact real count.

33. Can we store pictures in the database and if so, how it can be done?

Ans:

Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.

34. What is an integrity constraint?

Ans:

An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.

35. What is an ALERT?

Ans:

An alert is a window which appears in the center of the screen overlaying a portion of the current display.

36. What is hash cluster?

Ans:

Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table.

37. What are the various constraints used in Oracle?

Ans:

Following are constraints used:
NULL – It is to indicate that particular column can contain NULL values
NOT NULL – It is to indicate that particular column cannot contain NULL values
CHECK – Validate that values in the given column to meet the specific criteria
DEFAULT – It is to indicate the value is assigned to default value

38. What is difference between SUBSTR and INSTR?

Ans:

SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.
SUBSTR returns string whereas INSTR returns numeric.

39. What is the parameter mode that can be passed to a procedure?

Ans:

IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.

40. What are the different Oracle Database objects?

Ans:

There are different data objects in Oracle –
Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespaces – Logical storage unit in Oracle

 

41. What are the differences between LOV and List Item?

Ans:

LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.

42. What are privileges and Grants?

Ans:

Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.

43. What is the difference between $ORACLE_BASE and $ORACLE_HOME?

Ans:

Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.

44. What is the fastest query method to fetch data from the table?

Ans:

Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.

45. What is the maximum number of triggers that can be applied to a single table?

Ans:

12 is the maximum number of triggers that can be applied to a single table.

46. How to display row numbers with the records?

Ans:

Display row numbers with the records numbers –

Select rownum, from table;

This query will display row numbers and the field values from the given table.

47. How can we view last record added to a table?

Ans:

Last record can be added to a table and this can be done by –

Select * from (select * from employees order by rownum desc) where rownum<2;

48. What is the data type of DUAL table?

Ans:

The DUAL table is a one-column table present in oracle database. The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.

49. What is difference between Cartesian Join and Cross Join?

Ans:

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.

50. How to display employee records who gets more salary than the average salary in the department?

Ans:

This can be done by this query –

Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;

 

51. Explain: Integrity Constraints, Index, Extent

Ans:

  • Integrity Constraints
    It can be called as a declarative way in order to define a business rule for a table’s column
  • Index
    It can be called as an optional structure which is associated with a table for direct access to the rows
    Index can be created for one or more columns in a table
  • Extent
    It can be defined as a specific number of contiguous data blocks in single allocation.
    It is used to store a specific type of information.

52. What is ANALYZE command used for?

Ans:

ANALYZE command is used to perform various functions on index, table, or cluster, as listed below:

  • It helps in dentifying migrated and chained rows of the table or cluster.
  • It helps in validating the structure of the object.
  • It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
  • It helps in deleting statistics used by object from the data dictionary.

53. List the types of joins used in writing SUBQUERIES.

Ans:

  • Self join
  • Outer Join
  • Equi-join

54. List the various Oracle database objects.

Ans:

  • TABLES
  • VIEWS
  • INDEXES
  • SYNONYMS
  • SEQUENCES
  • TABLESPACES

55. Explain i.) Rename ii.) Alias.

Ans:

Rename – It is a permanent name provided to a table or column.
Alias – It is a temporary name provided to a table or column which gets over after the execution of SQL statement.

56. What is a view?

Ans:

It is virtual table which is defined as a stored procedure based on one or more tables.

57. What are the varoius components of physical database structure of Oracle database?

Ans:

Oracle database comprises of three kinds of files:

  • Datafiles,
  • Redo log files,
  • Control files.

58. List out the components of logical database structure of Oracle database.

Ans:

  • Tablespaces
  • Database’s schema objects.

59. What do you mean by a tablespace?

Ans:

  • These are the Logical Storage Units into which a database is divided.
  • It is used to group together the related logical structures.

60. What is Control File used for?

Ans:

Control File is used for:

  • Database recovery.
  • Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.

 

61. What is a synonym? What are its various types?

Ans:

A synonym can be called as an alias for a table, view, sequence or program unit.

It is basically of two types:

Private – Only the owner can access it.
Public – Can be accessed by any database user.

62. What are the uses of synonyms?

Ans:

Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables, views or program units of a remote database.
Simplify the SQL statements for database users.

63. What do you mean by a deadlock?

Ans:

When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.

The reasons for it to happen are:

  • lack of proper row lock commands.
  • Poor design of front-end application
  • It reduces the performance of the server severely.
  • These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.

64. What suggestions do you have to reduce the network traffic?

Ans:

Following are some of the actions which can be taken to reduce the network traffic:

  • Use snapshots to replicate data.
  • Use remote procedure calls.
  • Replicate data in distributed environment.

65. What are the various types of snapshots ?

Ans:

There are two types of snapshots:

  • Simple snapshots – Based on a query that does not contain GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
  • Complex snapshots- which contains atleast any one of the above.

66. What is a sub query? What are its various types?

Ans:

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

There can be two types of subqueries:

  • Correlated sub query
    It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .
  • Non-Correlated sub query
    Results of this sub query are submitted to the main query or parent query.
    It is independent like a query

67. Will you be able to store pictures in the database?Explain.

Ans:

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

68. Explain: a.) Integrity constraint. b.) COALESCE function

Ans:

  • Integrity constraint.
    It is a declaration defined in a business rule for a table column.
    Integrity constraint ensures the accuracy and consistency of data in a database.
    It is of three types – namely – Domain Integrity, Referential Integrity and Domain Integrity.
  • COALESCE function
    This function is used to return the value which is set not to be null in the list.
    Incase all values in the list are null the coalesce function will return NULL.
    Its representation: Coalesce(value1, value2, value3,…)

69. Explain the BLOB datatype and DML

Ans:

BLOB datatype

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

DML

  • DML – it is also termed as Data Manipulation Language (DML).
  • It is used to access and manipulate data in the existing objects.
  • DML statements are insert, select, update and delete.

70. Differentiate between a)TRANSLATE and REPLACE, b) What is Merge Statement used for

Ans:

a) TRANSLATE and REPLACE.

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

b)What is Merge Statement used for?

  • Merge statement is used to select rows from one or more data source to updating and insert into a table or a view.

 

71. What are the various Oracle Database objects?

Ans:

Various database objects are as follows:

Tables – This is a set of elements organized in vertical and horizontal fashion.
Tablespaces – This is a logical storage unit in Oracle.
Views – It is 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.

72. What is the purpose of Save Points in Oracle database?

Ans:

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

73. What is an ALERT?

Ans:

It a window which appears on the screen overlaying a portion of the current display.

74. Differentiate between post-database commit and post-form commit.

Ans:

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

75. Differentiate between pre-select and pre-query.

Ans:

  • Once oracle forms construct the select statement to be issued Pre-select is fired during the execute query and count query processing. All this happens before the statement is actually issued.
  • The pre-query trigger is fired just before oracle forms issue the select statement to the database.

76. What is hot backup and logical backup?

Ans:

Hot backup

  • Backing up the archive log files when database is open is called Hot backup.
  • To do this, the ARCHIVELOG mode is enabled.
  • Following files are backed up – All data files, Archive log, redo log files and control files.

Logical backup

  • Logical back ip is reading a set of database records and writing them into a file.
  • An Export utility is required to take the backup while an Import utility is required to recover from the backup.

77. What do you mean by Redo Log file mirroring?

Ans:

  • The process of having a copy of redo log files is called mirroring.
  • It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group.
  • In case a group fails, the database automatically switches over to the next group. It diminishes the performance.

78. Hot backup vs. cold backup

Ans:

  • A database backup while it is still up and running is a Hot backup and it must be in archive log mode.
  • A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode.
  • The benefit of a hot backup is that the database is still available for use while the backup is occurring.
  • A cold backup is easier to administer the backup and recovery process.
  • Cold backups does not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.

79. What are actual and formal parameters?

Ans:

Actual Parameters
The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.
Following procedure call lists two actual parameters named empno and amt:
raise_sal(empno, amt);

Formal Parameters
The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.
Following procedure declares two formal parameters named empid and amt:
PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;

80. Can we protect our PL/SQL source code?

Ans:

Yes, we can protect. PL/SQL V2.2 implements a binary wrapper for PL/SQL programs to protect the source code.
The utility available in this version transforms the PL/SQL source code into portable binary object code and thus protect from exposing your proprietary algorithms and methods.

 

81. Explain an Exception and its types.

Ans:

Exception is the error handling part of PL/SQL block.
Exception is of two types: Predefined and user defined.

Some of Predefined exceptions are

  • ZERO_DIVIDE
  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • LOGON_DENIED
  • CURSOR_ALREADY_OPEN
  • INVALID_NUMBER
  • NOT_LOGGED_ON
  • STORAGE_ERROR
  • PROGRAM-ERROR
  • TIMEOUT_ON_RESOURCE
  • VALUE_ERROR
  • DUP_VAL_ON_INDEX
  • INVALID_CURSOR.

82. When do you get a .PLL extension in oracle? Explain its importance

Ans:

.PLL extension is created when we save a library module. It contains both source code and platform specific complied executable code.

83. What is the use of SYSTEM.EFFECTIVE.DATE variable in oracle?

Ans:

It represents the effective database date.

84. Use of an integrity constraint is better to validate data. Explain

Ans:

Use of an integrity constraint is better to validate data because it prevents invalid data entry at the basic level into the database tables.

85. Explain the function of optimizer in oracle.

Ans:

The optimizer determines the most efficient way to execute a SQL statement based on the kind of data in the table and the statements fired to fetch that data like indexes, full table scans, loops, joins etc. Optimizers are also used to avoid excessive I/O resources.

86. What is meant by recursive hints in oracle?

Ans:

Number of times a dictionary table is repeatedly called by various processes is known as recursive hint. It occurs because of the small size of data dictionary cache.

87. What are the limitations of a CHECK Constraint?

Ans:

The limitation of CHECK 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.

88. Explain the use of ROWS option in IMP command.

Ans:

It indicates whether or not the table rows should be imported.

89. Explain the use of INDEXES option in IMP command.

Ans:

It determines whether indexes are imported.

90. Explain the use of GRANT option in IMP command.

Ans:

GRANT specifies to import object grants.

 

91. Explain the use of IGNORE option in IMP command.

Ans:

IGNORE defines how object creation errors should be handled.

92. Explain the use of SHOW option in IMP command.

Ans:

When the value of show=y, the DDL within the export file is displayed.

93. What is the use of FILE option in IMP command?

Ans:

FILE param defines the name of the export file to import. Multiple files can be listed, s93eparated by commas.

94. Explain the use of LOG option in EXP command.

Ans:

LOG specifies the log file to write messages.

95. What is hot backup and how it can be taken?

Ans:

Hot backup is taking backup of archived log files when database is open. He ARCHIVELOG switch should be enabled for this to happen. Hot backup happens while the database is still being accessed by users and tables are being updated. It does not need the database to have a downtime.

It includes the following:

  • Data files
  • Archived log files
  • Redo log files
  • Control files

Steps to take a hot backup:

  • Enable ARCHIVE Log
  • Change tablespace mode to begin backup
  • Now create backup of your control file in Human Readable format
  • Copy all your datafiles, redo logs and control file from your database server to backup location
  • Copy database software $ORACLE_HOME from server to backup location
  • Copy Apps Middle tier all TOPs (APPL_TOP, COMMON_TOP, ORA_TOP)

96. What are the different kind of export backups?

Ans:

Following are the different kinds of export backups:

  • Full/Complete backup: Backup of the whole database
  • Incremental backup: Only backup the data that has been modified since last incremental backup
  • Cumulative backup: Only affected tables from the last cumulative backup date

97. Difference between pre-select and pre-query

Ans:

Pre-select
This fires during the execute and count query processing after an oracle form builds the select statement to be executed, but before it’s execution

Pre-query
This fires before an oracle form issues the select statement. It executes before the pre-select trigger.

98. Difference between open_form and call_form in oracle.

Ans:

CALL_FORM: This runs a specified form while keeping the parent form active. This allows moving between the forms. Oracle forms run the new form with Run form preferences based on the parent form. When the called form exits, oracle forms resume the parent form along with its last state. Either of the forms can be hidden or displayed based on logic.

OPEN_FORM: This executes the current form and executes the new specified form as a modal form. Oracle release the memory for the parent form and the new form is executed using the same Run form preferences as the parent form.

99. What are the different types of Record groups in oracle? Explain each of them

Ans:

Record group is an internal oracle forms data structure having a similar column-row structure and relationship as a database table.

They are logical groups and never displayed to the user as such. Various types of Record groups in oracle are:

  • Query record group: This record group is based on an attached SELECT query. Columns in this record group derive their default names, data types, length etc from the tables referred I the SELECT query.
  • Static record group: This record group is not associated with any query. Their structure and values are defined at design time and thus remain fixed at run time.
  • Non query record group: This record group also does not have an associated query. However, its structure and values can be defined during run time programmatically.

100. What is a trace file and how is it created in oracle?

Ans:

Trace files are files used to store details of exceptions thrown by Oracle background processes i.e. dbwr, lgwr, pmon, smon etc. They are usually created for diagnostic dumps as well and help in debugging and solving exceptions in Oracle.

To create a Trace file in oracle:
Set sql_trace=true with alter session command. This will generate a trace file for all sql commands issued by your user session. This is known as a level-1 trace file. One can also create super detailed level-4 trace files with additional details if the need be. These files are stored in the form $ORACLE_SID_ora_xxx.trc in the trace directory, where xxx is sequential number.
To create a lelevl-4 detailed trace file, we need to know the SID and SERIAL# for the session to trace.
Eg: to trace for session for SID 5:
Connect system as sysdba;
ORADEBUG SETOSPID 5;
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4

 

101. Explain the use of online redo log files in oracle.

Ans:

Every Oracle database has a redo log, which records all changes that have been made in the data files. These files then enable us to replay the SQL Statements. Oracle writes all the changes to the redo log before making any changes in the data files. If something happens to any data file, a backed up data file is restored and the redo log helps in doing so. However, a database can only be recovered if it runs under Archive log mode.

102. Explain ENABLE NOVALIDATE constraint.

Ans:

When a constraint has ENABLE NOVALIDATE state, all subsequent statements are checked for conformity to this constraint. A Table with this constraint can contain invalid data, however, it does not allow any further invalid data to be added to that table. Enabling this constraint is much faster than enabling and validating any other constraint. Also, it does not need any DML locks during validation with this constraint in place.

Eg:
ALTER TABLE table1 ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (table2_id)
REFERENCES table2 (id)
ENABLE NOVALIDATE;

103. How to create LOV dynamically at runtime & attach to text field?

Ans:

Steps to create a dynamic LOV:

  • Create a record group, eg: RG
  • Create RG Sql query as Select col1,col2,col3 from dual; Keep in mind to adjust data types accordingly
  • Create an LOV and attach this RG to it

Use SET_ITEM_PROPERTY to attach text field, dynamic LOV, and value, eg: SET_ITEM_PROPERTY(item_id, LOV_NAME, value);

104. How can we force the database to use the user specified rollback segment?

Ans:

We can do so by using the following SQL statement
SET TRANSACTION USE ROLLBACK SEGMENT User_Rollback_Segment_Name

105. Explain the use of CONSISTENT option in EXP command.

Ans:

It specifies the read only statement for export to ensure data consistency.

106. Explain the use of ANALYSE option in EXP command.

Ans:

It is a flag to indicate if the statistical information about the exported objects should be written to export dump file or not.

107. Explain the use of PARFILE option in EXP command.

Ans:

It specifies the file that contains the export parameters.

108. Explain the use of PARFILE option in EXP command.

Ans:

It specifies the file that contains the export parameters.

109. Explain the use of RECORD option in EXP command.

Ans:

It is the flag that indicates if a record will be stored in data dictionary tables recording the export.

110. Explain the use of INCTYPE option in EXP command.

Ans:

It specifies the type of export to be performed. It can be either COMPLETE, CUMULATIVE, INCREMENTAL.

 

111. Explain the use of RECORD LENGTH option in EXP command.

Ans:

Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk.

112. Explain the use of TABLES option in EXP command.

Ans:

Indicates that the type of export is table-mode and lists the tables to be exported.

113. Explain the use of OWNER option in EXP command.

Ans:

It tells that only the owner’s objects will be exported.

114. Explain the use of FULL option in EXP command.

Ans:

It tells that the entire database is to be exported.

115. Explain the use of CONSTRAINTS option in EXP command.

Ans:

It specifies whether table constraints should be exported with table data.

116. Explain the use of ROWS option in EXP command.

Ans:

It is the condition to decide if the table rows should be exported or not.

117.Explain the use of INDEXES option in EXP command.

Ans:

It determines whether index definitions are exported.

118. Explain the use of GRANT option in EXP command.

Ans:

It specifies the object grants to export.

119. Explain the use of COMPRESS option in EXP command.

Ans:

When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be used.

120. Explain the use of FILE option in EXP command.

Ans:

File parameter takes the name of the export file. Multiple files can be listed, separated by commas.

 

121. Describe Oracle architecture in brief

Ans:

The Oracle database has: – Logical layer: The components of the logical layer map the data to these physical components – Physical layer: The physical layer consists of the files that reside on the disk…..

122. What is the function of SMON?

Ans:

The SMON background process performs all system monitoring functions on the oracle database….

123. Explain different types of segment

Ans:

There are four types of segments used in Oracle databases: – data segments – index segments – rollback segments – temporary segments…..

About the author

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Our Sponsors