ORACLE

Oracle Corporation

The Oracle Corporation is an American global computer technology corporation, headquartered in Redwood City, California. The company primarily specializes in developing and marketing computer hardware systems and enterprise software products – particularly its own brands of database management systems. In 2011 Oracle was the second-largest software maker by revenue, after Microsoft.

The company also develops and builds tools for database development and systems of middle-tier software, enterprise resource planning (ERP) software, customer relationship management (CRM) software and supply chain management (SCM) software.

Larry Ellison, a co-founder of Oracle, served as Oracle’s CEO from founding. On September 18, 2014, it was announced that he would be stepping down (with Mark Hurd and Safra Catz to become CEOs). Ellison became executive chairman and CTO. He also served as the Chairman of the Board until his replacement by Jeffrey O. Henley in 2004. On August 22, 2008, the Associated Press ranked Ellison as the top-paid chief executive in the world.

History

Ellison co-founded Oracle Corporation in 1977 with Bob Miner and Ed Oates under the name Software Development Laboratories (SDL). Ellison took inspiration from the 1970 paper written by Edgar F. Codd on relational database management systems (RDBMS) named “A Relational Model of Data for Large Shared Data Banks.” He heard about the IBM System R database from an article in the IBM Research Journal provided by Oates. Also derived from Codd’s theories, Ellison wanted to make Oracle’s product compatible with System R, but failed to do so as IBM kept the error codes for their DBMS a secret. SDL changed its name to Relational Software, Inc (RSI) in 1979, then again to Oracle Systems Corporation in 1982, to align itself more closely with its flagship product Oracle Database. At this stage Bob Miner served as the company’s senior programmer. In 1995, Oracle Systems Corporation changed its name to Oracle Corporation, officially named Oracle, but sometimes referred to as Oracle Corporation, the name of the holding company. Part of Oracle Corporation’s early success arose from using the C programming language to implement its products. This eased porting to different operating systems (most of which support C).

Technology timeline

  • 1979: offered the first commercial SQL RDBMS
  • 1983: offered a VAX-mode database
  • 1984: offered the first database with read-consistency
  • 1986: offered a client-server DBMS
  • 1987: introduced UNIX-based Oracle applications
  • 1988: introduced PL/SQL
  • 1992: offered full applications implementation methodology
  • 1995: offered the first 64-bit RDBMS
  • 1996: moved towards an open standards-based, web-enabled architecture
  • 1999: offered its first DBMS with XML support
  • 2001: became the first to complete 3 terabyte TPC-H world record
  • 2002: offered the first database to pass 15 industry standard security evaluations
  • 2003: introduced what it calls “Enterprise Grid Computing” with Oracle10g
  • 2005: released its first free database, Oracle Database 10g Express Edition (XE)
  • 2008: Smart scans in software improve query-response in HP Oracle Database Machine / Exadata storage
  • 2013: began use of Oracle 12C which is capable of providing cloud services with Oracle Database

Oracle products and services

Oracle designs, manufactures, and sells software and hardware products, as well as offers services complementing them (such as financing, training, consulting, and hosting services). Many of the products have been added to Oracle’s portfolio through acquisitions.

12

Oracle database as a career option

Oracle is the world’s largest enterprise software company and the jobs in the oracle field are on huge demand in the market. There are thousands of professionals, working in almost all countries around the world in this technology and create enterprise software that solves customers’ problems. Oracle professionals are in such high demand that they are the most likely to be hired without experience.

Oracle database professionals

Below are few of the Oracle Database Career options that one may select for his bright career.

Oracle Developer:

An Oracle developer is responsible for creating or maintaining the database components of an application that uses the Oracle technology stack. Oracle developers either develop new applications or convert existing applications to run in an Oracle Database environment. For this reason, developers work closely with the database administrators, sharing knowledge and information.

Major responsibilities:

  • Implementing the data model required by the application
  • Creating schema objects and implementing rules for data integrity
  • Choosing a programming environment for a new development project
  • Writing server-side PL/SQL or Java subprograms and client-side procedural code that use SQL statements
  • Creating the application interface with the chosen development tool
  • Establishing a Globalization Support environment for developing globalized applications
  • Instantiating applications in different databases for development, testing, education, and deployment in a production environment.

Oracle Database Administrator:

Each database requires at least one database administrator (DBA) to administer it. Because an Oracle database system can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility.

Major responsibilities:

  • Installing and upgrading the Oracle server and application tools
  • Allocating system storage and planning future storage requirements for the database system
  • Creating primary database storage structures (tablespaces) after application developers have designed an application
  • Creating primary objects (tables, views, indexes) once application developers have designed an application
  • Modifying the database structure, as necessary, from information given by application developers
  • Enrolling users and maintaining system security
  • Ensuring compliance with your Oracle license agreement
  • Controlling and monitoring user access to the database
  • Monitoring and optimizing the performance of the database
  • Planning for backup and recovery of database information
  • Maintaining archived data on tape
  • Backing up and restoring the database
  • Contacting Oracle Corporation for technical support

Oracle Business Intelligence Developer:

This developer’s role will work directly with the core Business Intelligence team to design and develop world-class analytics. A successful candidate should be able to work with functional business analysts, business process owners and other technical team members for a given requirement or process. This includes taking part of the design, soliciting feedback and documenting the information associated with the integrations for support and change management purposes. Further, the candidate should be capable of executing requirement and design specification into development deliverables.

Major Responsibilities

  • Gathering the technical requirements, design and develop business analytical solutions, testing and implementing of Business Intelligence/ (ETL, Reporting, Analytics) requirements.
  • Design and develop ETL (Extract Transform and Load) maps using industry standard tools like Oracle Warehouse Builder.
  • Design and develop ER diagrams and map the source data into data warehouse objects.
  • Data model design and documentation (Facts and Dimensions, Business Users & Security Permissions, Stars with Hierarchies, Business Model Names for Source Columns, Reports and Dashboard Layouts).
  • Design and develop objects in Oracle database.
  • Developing Oracle Business Intelligence meta-data layer. Configure repository on all the three layers (Physical Layer, Business Logic and Mapping Layer and Presentation Layer).
  • Develop Dashboards / Reports with different Analytics Views (Pivot Table, Chart, Tabular and View Selector), Alerts, Guided Navigation, and Dynamic / Interactive Dashboards with drill-down capabilities using global and local Filters.
  • Support by fixing incidents and enhance the business solutions that are developed based on the functional and strategic requirements.
  • Support and enhance the transaction business applications as needed.
  • Develop and run the internal financial reporting for the organization.

Data Architect:

A data architect is a practitioner of data architecture, an information technology discipline concerned with designing, creating, deploying and managing an organization’s data architecture.

Data architects define how the data will be stored, consumed, integrated and managed by different data entities and IT systems, as well as any applications using or processing that data in some way. It is closely allied with business architecture and is considered to be one of the four domains of enterprise architecture.

Application Developers:

Application developers design and implement database applications.

 Major responsibilities:

  • Designing and developing the database application
  • Designing the database structure for an application
  • Estimating storage requirements for an application
  • Specifying modifications of the database structure for an application
  • Relaying the above information to a database administrator
  • Tuning the application during development
  • Establishing an application’s security measures during development

Application developers can perform some of these tasks in collaboration with DBAs.

Key points that would help a person to get an idea about the job opportunities and preparation for getting into oracle database field.

  • Salary trends in the US support good growth for these hot jobs:

 

 

3

 

 

 

  • Before taking training in Oracle database one must decide in which job profile he wants to make his career depending upon his area of interest.
  • After deciding area of interest he must undergo Oracle Database training from reputed and registered company which provides training first on dummy projects and then on live projects, and most importantly company must have placement services for the trainees.
  • After taking training one must get an oracle certification in a desired profile to improve his qualification and to make him worth in the job market. Because if two persons with roughly the same background and experience are vying for the same job, then the certified person will usually get to the top of HR pre-screening pile. “Certification gives an employee the edge in the market”.

Oracle certifications

Oracle Certification Program Categories

 

Oracle Certified Associate

The Oracle Certified Associate (OCA) credential is typically the first step toward achieving the flagship Oracle Certified Professional certification. The OCA credential ensures that the individual is equipped with fundamental skills, providing a strong foundation for supporting Oracle products. An OCA credential is available for several of today’s most in-demand technology job roles.

Oracle Certified Professional

The Oracle Certified Professional (OCP) credential is the benchmark of professional skill and technical expertise required to manage, develop, or implement enterprise-wide databases, middleware, or applications. Increasingly, IT managers use the OCP credential to evaluate the qualifications of employees and job candidates.

Oracle Certified Master

The Oracle Certified Master (OCM) credential recognizes the ultimate advanced level of skills, knowledge, and proven ability. These professionals are qualified to answer the most difficult questions and solve the most complex problems.

Oracle Certified Expert Program

The Certified Expert program is a part of Oracle’s Certification Program which grants credentials that recognize competency in specific technologies, architectures or domains not currently covered in the path-based Certified Associate and Certified Professional certifications.

Some of the key features of Expert certifications are:

  1. Niche oriented – based upon specific products or technologies rather than broad job roles.
  2. Credentials are independent of the current OCA, OCP, OCM hierarchy.
  3. Typically comprises a single comprehensive exam, rather than a series of exams.
  4. Competencies falling under the umbrella of the Expert program may range from foundational skills to mastery of advanced technologies.
  5. May have prerequisites such as an online exam, previous certification or training course attendance.

Topics may include areas identified for certifications that do not fall within Oracle’s previously defined path-based certification model, but are valuable or essential skills that can and should be tested. Successful candidates will be awarded a special Expert Certification designation.

Oracle Certified Specialist

The Oracle Certified Specialist branded credentials are typically foundation-level implementation-oriented certifications targeted at employees of current Oracle partners, employees of integrators seeking to become Oracle partners and individuals who plan to apply for work with Oracle-authorized partners. Additionally these certifications would be applicable to anyone who wishes to add credibility and broaden their skill sets on specific Oracle products.

Oracle Certified Specialist certification credentials validated essential knowledge and skills working with key Oracle products and can be important assets to individuals working on client implementations. In addition to the credibility that these certifications provide in some cases they may lead to higher-level or broader-level certifications from Oracle University.

After getting certification in a particular or multiple field(s), a candidate becomes eligible to get placed in a top ranked company, but before placement he needs to prepare well to crack the interview of that company. So few of the important questions that are asked in company’s interviews are as follows:

Oracle Developer interview Questions

Describe the difference between a procedure, function and anonymous pl/sql block. Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn’t have to.

What is a mutating table error and how can you get around it?

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

Describe the use of %ROWTYPE and %TYPE in PL/SQL.

Expected answer: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

What packages (if any) has Oracle provided for use by developers?

Expected answer: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

Describe the use of PL/SQL tables.

Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

When is a declare statement needed?

The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

In what order should an open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

How can you find within a PL/SQL block, if a cursor is open?

Expected answer: Use the %ISOPEN cursor status variable.

How can you generate debugging output from PL/SQL?

Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

What are the types of triggers?

Expected Answer: There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words: BEFORE ALL ROW INSERT, AFTER ALL ROW INSERT, BEFORE INSERT, AFTER INSERT etc.

How can variables be passed to a SQL routine?

By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,…,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersand variable in the code itself: “select * from dba_tables where owner=&owner_name;”. Use of double ampersands tells SQLPLUS to re-substitute the value for each subsequent use of the variable, a single ampersand will cause a re-prompt for the value unless an ACCEPT statement is used to get the value from the user.

You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?

The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function “||”. Another method, although it is hard to document and isn’t always portable is to use the return/linefeed as a part of a quoted string.

How can you call a PL/SQL procedure from SQL?

By use of the EXECUTE (short form EXEC) command.

How do you execute a host operating system command from within SQL?

By use of exclamation mark “!” (in UNIX and some other OS) or the HOST (HO) command.

 

You want to use SQL to build SQL, what is this called and give an example?

This is called dynamic SQL. An example would be: set lines 90 pages 0 termout off feedback off verify off spool drop_all.sql select ?drop user ?||username||? cascade;? from dba_users where username not in (“SYS?,?SYSTEM?); spool off Essentially you are looking to see that they know to include a command (in this case DROP USER…CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.

What SQLPlus command is used to format output from a select?

This is best done with the COLUMN command.

You want to group the following set of select returns, what can you group on Max (sum_of_cost), min(sum_of_cost), count(item_no), item_no?

The only column that can be grouped on is the “item_no” column, the rest have aggregate functions associated with them.

What special Oracle feature allows you to specify how the cost based system treats a SQL statement?

The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.

You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?

Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.

What is a Cartesian product?

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?

Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.

What is the default ordering of an ORDER BY clause in a SELECT statement?

Ascending

What is tkprof and how is it used?

The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

What is EXPLAIN PLAN and how is it used?

The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

How do you set the number of lines on a page of output?

The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.

How do you prevent output from coming to the screen?

The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.

How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?

The SET options FEEDBACK and VERIFY can be set to OFF.

How do you generate file output from SQL?

By use of the SPOOL command.

What is a CO-RELATED SUBQUERY?

A 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.

Example: SELECT  field1 from table1 X WHERE  field2>(select avg (field2) from table1 Y where field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

What are various joins used while writing SUBQUERIES?

Self join– It’s a join foreign key of a table references the same table.

Outer Join– It’s 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– It’s 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.

What are various constraints used in SQL?

NULL, NOT NULL, CHECK, DEFAULT.

What are different Oracle database objects?

TABLES, VIEWS, INDEXES, SYNONYMS, SEQUENCES, TABLESPACES etc.

What is difference between Rename and Alias?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which does not exist once the SQL statement is executed.

 

What is a view?

A view is stored procedure based on one or more tables, it’s a virtual table.

What are various privileges that a user can grant to another user?

SELECT CONNECT RESOURCE

What is difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Can a primary key contain more than one column?

Yes

How you will avoid duplicating records in a query?

By using DISTINCT

What is difference between SQL and SQL*PLUS?

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. It’s a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database (DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Which datatype is used for storing graphics and images?

LONG RAW data type is used for storing BLOB’s (binary large objects).

How will you delete duplicating rows from a base table?

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);

What is difference between SUBSTR and INSTR?

SUBSTR returns a specified portion of a string e.g. SUBSTR(‘BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string.

Example: INSTR(‘ABC-DC-F’,’-‘,2) output 7 (2nd occurrence of ‘-‘)

There is a string ‘120000 12 0 .125’, how you will find the position of the decimal point?

INSTR(‘120000 12 0 .125′,’.’,1) output 13

There is a ‘%’ sign in one field of a column. What will be the query to find it?

‘\’ should be used before ‘%’.

When you use WHERE clause and when you use HAVING clause?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

 

Which is faster – IN or EXISTS?

EXISTS is faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Appropriate answer will be….

If result of the subquery is small, then “IN” is typically more appropriate and if result of the subquery is big/large/long then “EXIST” is more appropriate.

What is an OUTER JOIN?

Outer Join- It’s a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

How you will avoid your query from using indexes?

SELECT * FROM emp Where emp_no+’ ‘=12345;

i.e. you have to concatenate the column name with space within codes in the where condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

i.e. using HINTS

What is a pseudo column? Give some examples.

It is a column that is not an actual column in the table. E.g. USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments?

For top N queries, see http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post

What is the purpose of a cluster?

Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

Difference between an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR…IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

What are cursor attributes?

%ROWCOUNT %NOTFOUND %FOUND %ISOPEN

What is a cursor for loop?

Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor’s record.

Difference between NO DATA FOUND and %NOTFOUND.

NO DATA FOUND is an exception raised only for the SELECT….INTO statements when the where clause of the query does not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

What a SELECT FOR UPDATE cursor represent?

SELECT……FROM……FOR……UPDATE [OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.

What ‘WHERE CURRENT OF ‘ clause does in a cursor?

LOOP

SELECT  num_credits  INTO  v_numcredits  FROM classes

WHERE  dept=123 and course=101;

UPDATE  students

SET current_credits=current_credits+v_numcredits

WHERE  CURRENT OF  X;

END  LOOP

COMMIT;

 

END;

What is use of a cursor variable? How is it defined?

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.

What should be the return type for a cursor variable? Can we use a scalar data type as return type?

The return type for a cursor must be a record type. It can be declared explicitly as a user-defined or %ROWTYPE can be used. E.g. TYPE t_studentsref IS REF CURSOR RETURN students %ROWTYPE.

How you open and close a cursor variable? Why it is required?

OPEN cursor variable FOR SELECT…Statement CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

How you were passing cursor variables in PL/SQL 2.2.

In PL/SQL 2.2 cursor variables cannot be declared in a package. This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Can cursor variables be stored in PL/SQL tables? If yes how? If not why?

No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

Difference between procedure and function.

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

What are different modes of parameters used in functions and procedures?

IN OUT IN OUT

What is difference between a formal and an actual parameter?

The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

Can the default values be assigned to actual parameters?

Yes

Can a function take OUT parameters? If not why?

Yes. A function returns a value, but can also have one or more OUT parameters. it is best practice, however to use a procedure rather than a function if you have multiple values to return.

What is syntax for dropping a procedure and a function? Are these operations possible?

Drop Procedure procedure_name

Drop Function function_name

What are ORACLE PRECOMPILERS?

Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,… This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The precompiler translates the embedded SQL and pl/sql statements into calls to the precompiler runtime library. The output must be compiled and linked with this library to creater an executable.

What is OCI? What are its uses?

Oracle Call Interface is a method of accessing database from a 3GL program. Uses:- No precompiler is required, PL/SQL blocks are executed like other DML statements.

The OCI library provides functions to

– parse SQL statements

– bind input variables

– bind output variables

– execute statements

– fetch the results

Difference between database triggers and form triggers.

Data base trigger (DBT) fires when a DML operation is performed on a data base table. Form trigger (FT) Fires when user presses a key or navigates between fields on the screen b) Can be row level or statement level No distinction between row level and statement level. c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that defines the trigger. e) Can cause other database triggers to fire. Can cause other database triggers to fire, but not other form of triggers.

What is an UTL_FILE? What are different procedures and functions associated with it?

UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

Can you use a commit statement within a database trigger?

No (unless you make the trigger code an AT [AUTONOMOUS TRANSACTION] something you should never do)

What is the maximum buffer size that can be specified using the DBMS_OUTPUT. ENABLE function?

1,000,000

Oracle DBA Interview Questions

  1. What is an Oracle Instance?
  2. What information is stored in Control File?
  3. When you start an Oracle DB which file is accessed first?
  4. What is the Job of  SMON, PMON processes?
  5. What is Instance Recovery?
  6. What is written in Redo Log Files?
  7. How do you control number of Datafiles one can have in an Oracle database?
  8. How many Maximum Datafiles can there be in an Oracle Database?
  9. What is a Tablespace?
  10. What is the purpose of Redo Log files?
  11. Which default Database roles are created when you create a Database?
  12. What is a Checkpoint?
  13. Which Process reads data from Datafiles?
  14. Which Process writes data in Datafiles?
  15. Can you make a Datafile auto extendible? If yes, how?
  16. What is a Shared Pool?
  17. What is kept in the Database Buffer Cache?
  18. How many maximum Redo Logfiles one can have in a Database?
  19. What is difference between PFile and SPFile?
  20. What is PGA_AGGREGRATE_TARGET parameter?
  21. Large Pool is used for what?
  22. What is PCT Increase setting?
  23. What is PCTFREE and PCTUSED Setting?
  24. What is Row Migration and Row Chaining?
  25. What is 01555 – Snapshot Too Old error and how do you avoid it?
  26. What is a Locally Managed Tablespace?
  27. Can you audit SELECT statements?
  28. What does DBMS_FGA package do?
  29. What is Cost Based Optimization?
  30. How often you should collect statistics for a table?
  31. How do you collect statistics for a table, schema and Database?
  32. Can you make collection of Statistics for tables automatic?
  33. On which columns you should create Indexes?
  34. What types of Indexes are available in Oracle?
  35. What is B-Tree Index?
  36. A table is having few rows, should you create indexes on this table?
  37. A Column is having many repeated values which type of index you should create on this column, if you have to?
  38. When should you rebuild indexes?
  39. Can you built indexes online?
  40. Can you see Execution Plan of a statement?
  41. A table is created with the following setting
    storage (initial 200k
    next 200k
    minextents 2
    maxextents 100
    pctincrease 40)

What will be the size of 4th extent?

  1. What is DB Buffer Cache Advisor?
  2. What is STATSPACK tool?
  3. Can you change SHARED_POOL_SIZE online?
  4. Can you redefine a table Online?
  5. Can you assign Priority to users?
  6. You want users to change their passwords every 2 months. How do you enforce this?
  7. How do you delete duplicate rows in a table?
  8. What is Automatic Management of Segment Space setting?
  9. What is the difference between DELETE and TRUNCATE statements?
  10. What is COMPRESS and CONSISTENT setting in EXPORT utility?
  11. What is the difference between Direct Path and Convention Path loading?
  12. Can you disable and enable Primary key?
  13. What is an Index Organized Table?
  14. What is a Global Index and Local Index?
  15. What is the difference between Range Partitioning and Hash Partitioning?
  16. What is difference between Multithreaded/Shared Server and Dedicated Server?
  17. Can you import objects from Oracle ver. 7.3 to 9i?
  18. How do you move tables from one tablespace to another tablespace?
  19. How do see how much space is used and free in a tablespace?
  20. Which types of backups you can take in Oracle?
  21. A database is running in NOARCHIVELOG mode then which type of backups you can take?
  22. Can you take partial backups if the Database is running in NOARCHIVELOG mode?
  23. Can you take Online Backups if the database is running in NOARCHIVELOG mode?
  24. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
  25. You cannot shutdown the database for even some minutes, then in which mode you should run the database?
  26. Where should you place Archive logfiles, in the same disk where DB is or another disk?
  27. Can you take online backup of a Control file if yes, how?
  28. What is a Logical Backup?
  29. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
  30. Why do you take tablespaces in Backup mode?
  31. What is the advantage of RMAN utility?
  32. How RMAN improves backup time?
  33. Can you take Offline backups using RMAN?
  34. How do you see information about backups in RMAN?
  35. What is a Recovery Catalog?
  36. Should you place Recovery Catalog in the Same DB?
  37. Can you use RMAN without Recovery catalog?
  38. Can you take Image Backups using RMAN?
  39. Can you use Backupsets created by RMAN with any other utility?
  40. Where RMAN keeps information of backups if you are using RMAN without Catalog?
  41. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
  42. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
  43. Which is more efficient Incremental Backups using RMAN or Incremental Export?
  44. Can you start and shutdown DB using RMAN?
  45. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
  46. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
  47. You lost some datafiles and you don’t have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?
  48. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?
  49. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?
  50. You loss controlfile how do you recover from this?
  51. The current logfile gets damaged. What you can do now?
  52. What is a Complete Recovery?
  53. What is Cancel Based, Time based and Change Based Recovery?
  54. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?
  55. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
  56. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don’t have any backups?
  57. How do you recover from the loss of a controlfile if you have backup of controlfile?
  58. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
  59. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?
  60. Have you faced any emergency situation? Tell us how you resolved it?
  61. At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.

 

 

 

Few of the sites which can be of your use for job searches in oracle field are as follows:

 

 

*************************

 

 

Advertisements