All Job Interview Questions and Answers << Job Questions and Answers for Oracle
Questions and answers taken from real job interviews.
Find interview questions and answers on this website:
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.
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.
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.
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.
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 /
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.
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;
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.
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
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);
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;
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
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
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
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
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
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.
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
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
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.
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.
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.
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
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.
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.
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)
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.
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.
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 - - - - - ? ? ? ? ?
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`
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.
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.
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.
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.
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.
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
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
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.
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.
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).
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
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.