Oracle interviews Question answers.
1. Name some of the inbuilt method of Nested Table of Oracle?
Answer: a)First : Returns the subscript of the first element in the nested table b)Last : Returns the subscript of the last element in the nested table. c)Prior : Returns the subscript of the previous element in the nested table. d)Next : Returns the subscript of the next element in the nested table. e)Exists : Returns true if the element at te specified position is found else false f)Trim : Removes the last N elements from the collection
2. What are different types of modules available in oracle form?
Answer: Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application
3. What are the use of Roles ?
Answer: REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group. DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role. SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation. APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.
4. How can one see who is using a temporary segment? (for DBA )
Answer: For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'. All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment. If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples: select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks from sys.v_$session s, sys.v_$sort_usage u where s.addr = u.session_addr / select s.osuser, s.process, s.username, s.serial#, Sum (u.blocks)*vp.value/1024 sort_size from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter VP where s.saddr = u.session_addr and vp.name = 'db_block_size' and s.osuser like '&1' group by s.osuser, s.process, s.username, s.serial#, vp.value /
5. Explain the difference between a hot backup and a cold backup and the benefits associated with each?
Answer: A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
6. Tell about some of the Built-In Methods of ASSOCIATIVE ARRAYS?
Answer: It provides many built in methods to work with e.g. a)First: Returns the subscript of the first element in the associative array. b)Last: Returns the subscript of the last element in the associative array. c)Prior: Returns the subscript of the previous element in the associative array. d)Next: Returns the subscript of the next element in the associative array. e)Exists: Returns true if the element at te specified position is found else false f)Trim: Removes the last N elements from the collection g)Count: Returns number of elemets in the collection h)Delete: Delete the element in the collection
7. Explain the difference between $ORACLE_HOME and $ORACLE_BASE?
Answer: ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside. Well, we have gone through the first 25 questions as I would answer them during an interview. Please feel free to add your personal experiences to the answers as it will always improve the process and add your particular touch. As always remember these are "core" DBA questions and not necessarily related to the Oracle options that you may encounter in some interviews. Take a close look at the requirements for any job and try to come up with questions that the interviewer may ask. Next time we will tackle the rest of the questions. Until then, good luck with the process.
8. How can we specify the Archived log file name format and destination?
Answer: By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left paded, %s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.
9. What is VARRAY in Oracle?
Answer: They are variable length,1D array and store elements of same type as arrays of other languages. We need to specify the size at the time of VArray declaration. Let us say that a person has 2 mobile numbers. We can store these mobile numbers into a varray. Let us see how. Step1:First we need to create a Varray Type as under Declare Type Mobile_Numbers_Type IS VARRAY(2) OF NUMBER; Mobile_Numbers Mobile_Numbers_Type; Step 2: Then we need to initialize it Declare Type Mobile_Numbers_Type IS VARRAY(2) OF NUMBER; Mobile_Numbers Mobile_Numbers_Type; Begin Mobile_Numbers := Mobile_Numbers_Type(1111111111,1000000000); End; Step3:We can access the values as under SQL> Set ServerOutput On; SQL> Declare 2 3 Type Mobile_Numbers_Type IS VARRAY(2) OF NUMBER; 4 5 Mobile_Numbers Mobile_Numbers_Type; 6 7 Begin 8 9 Mobile_Numbers := Mobile_Numbers_Type(1111111111,1000000000); 10 11 DBMS_OUTPUT.PUT_LINE('First value is: ' || Mobile_Numbers(1)); 12 DBMS_OUTPUT.PUT_LINE('Second value is: ' || Mobile_Numbers(2)); 13 End; 14 / First value is: 1111111111 Second value is: 1000000000 PL/SQL procedure successfully completed.
10. What is WHEN-Database-record trigger?
Answer: Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. c generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.
11. Describe two phases of Two-phase commit ?
Answer: Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to commit or rollback the transaction, even if there is a failure) Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.
12. What is a Database instance ? Explain
Answer: A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file
13. What is the advantage of the library?
Answer: Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.
14. How will you convert a date to char in Oracle give one example?
Answer: to_char() function is used to convert date to character we can specify format also in which we want the output. SELECT to_char( to_date('11-01-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual; or SELECT to_char( to_date('11-01-2012, 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;
15. What is Snap shot in Oracle database?
Answer: Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
16. How do you find current date and time in oracle?
Answer: This is one of the frequently asked Oracle Interview questions. I have seen this question every now and then. By the way SYSDATE function is used in oracle to find current date and time of operating system on which the database is running return type of function is DATE Syntax: SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date" FROM DUAL.
17. Oracle version .... what does each number refers to?
Answer: oracle version number refers 9-Major database release number 2-Database Maintenance release number 0-Application server release number 4-Component Specific release number 0-Platform specific release number
18. What are different normalization forms?
Answer: 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (Read More Here) BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be in DKNF. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
19. Wht is Dual Table in Oracle?
Answer: It is a dummy table in Oracle with one row and one Varchar2 column whose length is 1. We can ask for any dummy values from dual as shown under SQL> Select 1, SysDate From dual; 1 SYSDATE ---------- --------- 1 30-APR-13
20. What is bulk copy or BCP in oracle?
Answer: BCP or bulk copy tool is one type of command line tool for unload data from database came into existence after oracle 8 .it is used to import or export data from tables and views but it will not copy structure of data same. Main advantage is fast mechanism for copying data and we can take backup of important data easily.
21. Why are OPS$ accounts a security risk in a client/server environment? (for DBA)
Answer: If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot. If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user. If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.
22. What is a difference between pre-select and pre-query?
Answer: Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode.Pre-query trigger fires before pre-select trigger.
23. Explain Associative Array with an example?
Answer: Let us say that a person has 2 houses.So it means he has two residential addresses. We can store these addresses into an Associative Array. Let us see how First we need to create an Associative Array Type as under. Please note that the Existing type can only be BINARY_INTERGER or VARCHAR. Declare Type Resedential_Address_Type IS TABLE OF VARCHAR(20) INDEX BY VARCHAR(20); Resedential_Addresses Resedential_Address_Type; Then assign the value Declare Type Resedential_Address_Type IS TABLE OF VARCHAR(20) INDEX BY VARCHAR(20); Resedential_Addresses Resedential_Address_Type; Begin Resedential_Addresses(1) := '407 St John Street London EC1V 4AD'; Resedential_Addresses(2) := 'Street Address, 64 Newman Street. City, London'; End; We can access the values as under SQL> Set ServerOutput On; SQL> Declare 2 Type Resedential_Address_Type IS TABLE OF VARCHAR(20) INDEX BY VARCHAR(20); 3 Resedential_Addresses Resedential_Address_Type; 4 Begin 5 Resedential_Addresses(1) := '407 St John Street London EC1V 4AD'; 6 Resedential_Addresses(2) := 'Street Address, 64 Newman Street. City, London'; 7 DBMS_OUTPUT.PUT_LINE('First Value is: ' || Resedential_Addresses(1)); 8 DBMS_OUTPUT.PUT_LINE('Second Value is: ' || Resedential_Addresses(2)); 9 End; 10 / First Value is: 407 St John Street London EC1V 4AD Second Value is: Street Address, 64 Newman Street. City, London PL/SQL procedure successfully completed.
24. What is hash cluster in Oracle?
Answer: This is one of my favorite Oracle Interview question. Hash cluster is one of the techniques to store the table in a hash cluster to improve the performance of data retrieval .we apply hash function on the table row’s cluster key value and store in the hash cluster. All rows with the same hash key value are stores together on disk.key value is same like key of index cluster ,for data retrieval Oracle applies the hash function to the row's cluster key value.
25. When will the data in the snapshot log be used?
Answer: We must be able to create a after row trigger on table (i.e., it should be not be already available) After giving table privileges. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects that support its snapshot log. The master table name should be less than or equal to 23 characters. (The table name created will be MLOGS_tablename, and trigger name will be TLOGS name).
26. What are the system resources that can be controlled through Profile ?
Answer: The number of concurrent sessions the user can establish the CPU processing time available to the user's session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user's session the amout of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user's session the allowed amount of connect time for the user's session.
27. What is an Index ? How it is implemented in Oracle Database ?
Answer: An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table comman (Ver 7.0)
28. Name some of the inbuilt methods of VARRAY?
Answer: a)Count : Returns the number of elements in the collection. b)Delete : Delete all elements in the collection c)Extend : Increase the size of the collection by the number specified d)Exists : Returns true if the element at te specified position is found else false e)Trim :Removes the last N elements from the collection
29. What is the purpose of Ratio_To_Report Function of Oracle?
Answer: It is an analytical function that computes the ratio of a value over the total set of values.RATIO_TO_REPORT of 5 over (1,2,3,4,5,6,7,8,9,10) is (5/55) i.e. 0.09090909090909090909090909090909 e.g SQL> Select EmpName,Salary,Ratio_to_Report(SALARY) Over() As RatioReportExample 2 From tblEmployee 3 Where DeptId = 1; EMPNAME SALARY RATIOREPORTEXAMPLE -------------------------------------------------- ---------- ------------------ Arina Biswas 6000 .146305779 Jitesh Mallik 16000 .390148744 Deepak Singh 6890 .168007803 Shashi Bhushan 5120 .124847598 Atithi Salonki 7000 .170690076
30. How to change Data-Type of a column in Oracle Database?
Answer: With the help of Modify Column,we can change data-type of a column in Oracle database? It's same as in MySql Database. Syntax:- Alter Table table_name Modify Column column_name datatype. For Example:- Alter Table employee_master Modify Column employee_code varchar(30);
31. What is Nested Table in Oracle?
Answer: They are 1D array like VArrays and can be use either in a relational table or in functions.Like VArrays, they are use for columns having multiple values.But they does not need any size to be define on them like VArrays and hence are unbound.They are,however, limited to the amount of memeory available.Each row of the nested table should be of the same type. e.g. Let us say that a person has 2 mobile numbers. We can store these mobile numbers into a Nested Table. Step 1:First we need to create a Nested Table Type Declare Type Mobile_Numbers_Type IS TABLE OF NUMBER; Mobile_Numbers Mobile_Numbers_Type; Step 2:Then we need to initialize it Declare Type Mobile_Numbers_Type IS TABLE OF NUMBER; Mobile_Numbers Mobile_Numbers_Type; Begin Mobile_Numbers := Mobile_Numbers_Type(1111111111,1000000000); End; Step 3: We can access the values as under SQL> Set ServerOutput On; SQL> Declare 2 3 Type Mobile_Numbers_Type IS TABLE OF NUMBER; 4 5 Mobile_Numbers Mobile_Numbers_Type; 6 7 Begin 8 9 Mobile_Numbers := Mobile_Numbers_Type(1111111111,1000000000); 10 11 DBMS_OUTPUT.PUT_LINE('First value is: ' || Mobile_Numbers(1)); 12 13 DBMS_OUTPUT.PUT_LINE('Second value is: ' || Mobile_Numbers(2)); 14 End; 15 / First value is: 1111111111 Second value is: 1000000000 PL/SQL procedure successfully completed.
32. What are the basic element of Base configuration of an oracle Database ?
Answer: It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the database consists of SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool) SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write) DBWR (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS
33. What is the purpose of Oracle's VSIZE function?
Answer: It returns the number of bytes in the internal representation of expression. If expression is null, it returns null. SQL> Select EMPNAME, VSize(EMPNAME) VSizeExample 2 From tblemployee; EMPNAME VSIZEEXAMPLE -------------------------------------------------- ------------ Deepak Kumar Goyal 18 Shashi Dayal 12 Amitav Mallik 13 Amit Ojha 9 Sumanta Manik 13 It is similar to DataLength function in
34. What are the options available to refresh snapshots ?
Answer: COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced. FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables. FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.
35. How does one connect to an administrative user? (for DBA )
Answer: If an administrative user belongs to the "dba" group on Unix, or the "ORA_DBA" (ORA_sid_DBA) group on NT, he/she can connect like this: connect / as sysdba No password is required. This is equivalent to the desupported "connect internal" method. A password is required for "non-secure" administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example: connect sys/password as sysdba
36. How do I find used/free space in a TEMPORARY tablespace? (for DBA )
Answer: Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view: SELECT tablespace_name, SUM (bytes used), SUM (bytes free) FROM V$temp_space_header GROUP BY tablespace_name;
37. What is Decompose function in Oracle?
Answer: This function of Oracle returns a Unicode string.It is the exact opposite of Compose function. Syntax Decompose(string) where "string" parameter can be a Char,Nchar,NClob,NCHAR, DUACLOB, VARCHAR2, or NVARCHAR2. SQL> Select 2 DeCompose(Compose('A' || UniStr('\0300'))) Example 3 From Dual; EX -- A`
38. How will you convert string to a date in oracle database?
Answer: This Oracle Interview questions is some time asked as follow up of previous Oracle Interview questions related to converting date to char in Oracle. By the way to_ date function is used to convert string to a date function. Syntax : to_date(string, format) Example: to_date('2012/06/12', 'yyyy/mm/dd') It will return June 12, 2012
39. What is Compose Function in Oracle?
Answer: The Compose function of Oracle accepts a string and returns a Unicode string.It also accepts an expression that can be resolve to a string. Syntax Compose(string) where "string" parameter can be a Char,Nchar,NClob,NCHAR, DUACLOB, VARCHAR2, or NVARCHAR2. SQL> Select 2 Compose('A' || UniStr('\0300')) GraveAccent 3 ,Compose('A' || UniStr('\0301')) AcuteAccent 4 ,Compose('A' || UniStr('\0302')) CircumFlex 5 ,Compose('A' || UniStr('\0303')) Tilde 6 ,Compose('A' || UniStr('\0308')) Umlaut 7 From Dual; G A C T U - - - - - ? ? ? ? ?
40. What is an administrative (privileged) user? (for DBA )
Answer: Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with the orapwd utility.
41. What is Associative Array?
Answer: Oracle 9i R2 has renamed the index-by tables available in earlier versions of Oracle to Associative Arrays.Like VArrays/Nested Tables, they are also 1D array and can be use either in a relational table or in functions. They can not exist in the database and rather are found only in PL/SQL memory structures.They are use for columns having multiple values.It is basically a two-Column table where the first column is the INDEX while the second column holds the DATA ELEMENT.They are UNBOUND since they don't have any upper limits to grow.
42. What is Save Points in Oracle database?
Answer: SAVE POINTS are used to divide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed. Whenever we encounter error we can rollback from the point where we set our SAVEPOINT.This is useful for multistage transaction and conditional transaction where commit and rollback depend on certain condition. This is another commonly asked Oracle Interview Question and since save points are also available in other database e.g. SQL Server, some time Interviewer follow up with differences with other database as well.