ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

Oracle 10g Interview Tips and Questions

Updated on June 26, 2016

Oracle RAC (Real Application Clusters)

Oracle Interview Questions

Oracle is strong in database management and data-warehousing for more than decades. It is leading the RDBMS world on top rank. Being a Oracle techie, one has lots of opportunities.

We posted our first resource on oracle-interview questions. We have posted Volume-I of oracle interview questions. You can expect couple of more volumes here.

Download oracle interview questions here.

We love your feedback on our articles which will help us to give more articles with more quality in near future. Please feel free to leave your comments. Thanks for staying with us.

If you have any problem downloading please visit my google site Free-career-guide.

Apart from these questions, if you know about Oracle Analytic functions, it will be added strength during a Oracle interview. Please see my page on Oracle Analytic Functions if you want to know about.

Oracle Interview Questions LEVEL - I (Novice)

(1) How do you generate file output from SQL?

Using SPOOL command.

Ex:

SPOOL C:\ScriptOutput.log;

SELECT * FROM SCOTT.EMP WHERE DEPT='SALES';

SPOOL OFF;

(2) What statement is used in Oracle SQL* Plus to enable/disable feedback/information after SQL Output?

Use the options FEEDBACK and VERIFY to OFF.

SET FEEDBACK OFF

SET VERIFY OFF

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

Use HOST (HO) command or exclamation mark "!" followed by OS command.

Ex: In windows

HOST dir, HO DIR, !DIR

Ex: In Unix

HO ls, HO cp sample.sql sample.bak

(4) How do you prevent output of a SQL from coming to the screen?

Set the TERMOUT option to off in SQL Plus.

SET TERMOUT OFF or SET TERM OFF

(5) How do you set the number of lines per page of output?

SET PAGESIZE n

This command sets the size of the page to n.

(6) How do you set the size of a line in a page?

SET LINESIZE 130

Sets the number of characters per line to 130.

(7) What SQLPlus command is used to format a column output of SELECT statements?

COLUMN command.

Ex: SET COLUMN EMP_NAME HEADING EMPLOYEE_NAME, SET COLUMN EMP_NAME FORMAT A40

(8) When will you use declare statements?

DECLARE statement must be the first line of anonymous PL/SQL blocks. However, if you want to declare a private section inside a procedure/function/package/anonymous block, you can use separate DECLARE..BEGIN..END;

(9) What is the default sorting order of ORDER BY clause in SELECT statement?

Ascending order

(10) What is a Cartesian product?

If join is undefined between two or more tables in a select statement, result will be a cartesian product, i.e., each row in a table will be associated with all rows in joined tables.

if table a,b & c were joined without any join condition, restant number of rows will product of rows in a, b & c. Say tables-a,b&c has 5 records each, resultant number of records will be 5*5*5 = 125.

(11) What is the difference between a procedure, function and anonymous block?

A function must return a value and a procedure doesn't have to. Anonymous blocks are not stored in database where procedure and function are stored.

(12) What would happens if constraint name is left out of a constraint clause?

Oracle will use default name of SYS_Cxxxx where xxxx is a system generated number.

(13) What is mutating table error and how can you get around it?

This will occurs when a trigger try to update a row it is currently using. The usual fix involves use of either a temporary table or a view, so that data is selected from one object while updating the other object.

(14) What is the use of %ROWTYPE and %TYPE in PL/SQL?

%ROWTYPE defines a row with all columns in a table. The %TYPE defines a variable with data type of a column.

Eg:

declare

emp_row emp%rowtype;

emp_id emp.emp_id%type;

....

(15) How will you find whether a cursor is open or closed?

Use the %ISOPEN cursor status variable.

(16) How will you find if a database schema is invalid?

Status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.

IBM Jobcast

Oracle Interview Quetions - Level 2 (For Developers)

(1) Mention few ORACLE supplied packages you have used in your code?

DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.

(2) What are PL/SQL tables?

PL/SQL tables are arrays which can be accessed using a index (integer). A PL/SQL table can be defined using %ROWTYPE or using TYPE, TABLE, RECORD statements in declaration.

(3) What happens to the table space definition when you disable a PK?

Table space definitions will be lost and we have to specify when we enable.

(4) Tell what do you know about SQLCODE and SQLERRM in PL/SQL?

SQLCODE - is a system variable which returns the value of the error number for last error encountered in a PL/SQL Block.

SQLERRM - is a system varaible which returns the value of the error message for last error encountered in a PL/SQL Block.

(5) What is exception in ORACLE?

Exceptions are error handlers in ORACLE PL/SQL.When an error occurred in a PL/SQL, oracle looks for a EXCEPTION block and a matching exception in WHEN clauses.

Eg:

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Data Error:'||SQLERRM);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Execution Error:'||SQLERRM);

END;

(6) Below query returned 3 rows. Justify is this correct?

SELECT TRUNC(SYSDATE)-1 yesterday, TRUNC(SYSDATE) today, TRUNC(SYSDATE)+1 tomorrow FROM DUAL;

In fact dual is a dummy table and expected to have single row. But here it looks like some user have inserted rows in to this table by mistake. It will be a problem when dual is used by any of stored procedures/other objects.

(7) Have you ever faced - Snapshot too old - issue? What is the cause and how can we prevent this?

This is caused by large transactions especially involving huge number of records (and running for a while) and is happening when their rollback space is overlapped by/to other transactions space (or even its own space).

Comments

    0 of 8192 characters used
    Post Comment

    • selvirajan profile image
      Author

      selvirajan 24 months ago from India

      Thanks all. Glad that this helped. Would try to add / update this content sooner.

    • profile image

      amanchmap 6 years ago

      Very nice post. I just stumbled upon your weblog and wished to say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

      http://www.oracleinterviewquestions.info/

    • Mazqura Akter profile image

      Mazqura Akter 6 years ago

      great post. Information that all need for us. I love it.

      Thanks.

      http://www.oracledbain.blogspot.com/

    • profile image

      Ella Holmes 7 years ago

      This is great interview and some questions are really good . thanks!!!!

    • profile image

      bipin 7 years ago

      hi,

      grt stuff regarding oracle

    • glassvisage profile image

      glassvisage 9 years ago from Northern California

      Wow! What a great resource for those interested in interviewing for Oracle! I wish there were pages like this for the jobs I applied for :)

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)