ORACLE Technical Interview Question And Answers

1.What are the components of physical database structure of Oracle database? Ans .Oracle database is comprised of three types of files. ...

1.What are the components of physical database structure of Oracle database?
Ans.Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

2.What are the components of logical database structure of Oracle database? 
Ans.There are tablespaces and database's schema objects

3.How do you switch from an init.ora file to a spfile?
Ans.Issue the create spfile from pfile command.

4.What is a CO-RELATED SUBQUERY
Ans.CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.

5.What is SYSTEM tablespace and when is it created? 
Ans.Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

6.What are various joins used while writing SUBQUERIES
Ans.Self join-Its a join foreign key of a table references the same table.

Outer Join-Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.

Equi-join-Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table

7.What is schema? 
Ans.A schema is collection of database objects of a user.

8.Give the reasoning behind using an index.
Ans.Faster access to data blocks in a table.

9.What type of index should you use on a fact table?
Ans.A Bitmap index.

10.What are various constraints used in SQL
Ans
  • NULL
  • NOT NULL
  • CHECK
  • DEFAULT
11.What are different Oracle database objects
Ans.
  • TABLES
  • VIEWS
  • INDEXES
  • SYNONYMS
  • SEQUENCES
  • TABLESPACES etc
12.What command would you use to encrypt a PL/SQL application?
Ans.WRAP

13.What is difference between Rename and Alias
Ans.Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

14.What is a view
Ans.A view is stored procedure based on one or more tables, its a virtual table

15.What are various privileges that a user can grant to another user
Ans.
  • SELECT
  • CONNECT
  • RESOURCE
16.What is the use of Control File ? 
Ans.When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

17.Do View contain Data ? 
Ans.Views do not contain Data or store data.

18.Can a primary key contain more than one columns
Ans. yes

19. How you will avoid duplicating records in a query
Ans.By using DISTINCT

20.What are Clusters?
Ans.Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

21.What does ROLLBACK do?
Ans.ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

22.What are the dictionary tables used to monitor a database spaces ?
Ans.
  • DBA_FREE_SPACE
  • DBA_SEGMENTS
  • DBA_DATA_FILES
23.How can you rebuild an index?
Ans.ALTER INDEX index_name REBUILD;

24.How can you gather statistics on a table?
Ans.exec dbms_stats.gather_table_stats
Also, remember to analyze all associated indexes on that table using dbms_stats.gather_index_stats

25.What must be installed with ODBC on the client in order for it to work with Oracle?
Ans.SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

26.How can you enable a trace for a session?
Ans.alter session set sql_trace='TRUE';

27.What are the different file extensions that are created by oracle reports?
Ans.Rep file and Rdf file

28.Give one method for transferring a table from one schema to another:
Ans.There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.

29.what is trigger?
Ans.Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

30.What is the proper method for disabling and re-enabling a primary key constraint
Ans.This results in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.

31.What is bind reference and how can it be created?
Ans.Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name

32.What is the maximum number of triggers, can apply to a single table
Ans.12 Triggers

33.Which date function returns number value?
Ans.months_between

34.what is integrity constrains?
Ans.Integrity constraints are used to ensure accuracy and consistency of data in a relational database.

35.What are the type of Synonyms? 
Ans.There are two types of Synonyms Private and Public.

36.What are the different Levels of Auditing ? 
Ans.Statement Auditing, Privilege Auditing and Object Auditing.

37.What is a shared pool? 
Ans.The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.

38.What are the components of physical database structure of Oracle database
Ans.Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

39.What is DECODE function used for?
Ans.DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

40.What is the difference between oracle,sql and sql server?
Ans.
  • Oracle is based on RDBMS.
  • SQL is Structured Query Language.
  • SQL Server is another tool for RDBMS provided by MicroSoft.
41.What are different modes of parameters used in functions and procedures
Ans.IN OUT INOUT

42.Can the default values be assigned to actual parameters
Ans. yes

43.What are the different windows events activated at runtimes? 
Ans.
  • When_window_activated
  • When_window_closed
  • When_window_deactivated
  • When_window_resized
44.What is ROWID?
Ans.ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, row number are the components of ROWID.

45.What is the diff. when Flex mode is mode on and when it is off? 
Ans.When flex mode is on, reports automatically resizes the parent when the child is resized

46.What is the use of image_zoom built-in? 
Ans.To manipulate images in image items.

47.What is the basic data structure that is required for creating an LOV?
Ans.Record Group.

48.What are modal windows?
Ans.Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

49.What are the built-ins that are used for setting the LOV properties at runtime? 
Ans.get_lov_property
set_lov_property 

50.What are the built-ins used for finding Object ID function? 
Ans.FIND_GROUP(function)
FIND_COLUMN(function)




Related

Interview Questions 2885450082853729064

Post a Comment

Follow Us

Hot in week

Recent

Comments

Side Ads

item