1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

Oracle interview questions

  • What is Observer pattern ? Explain with Code Example ? view answer
  • Replication Troubleshooting using DBMS_REFRESH: view answer
  • How to work with Oracle Data Pump? view answer
  • dbv: DBVERIFY utility in oracle: view answer
  • Physical Standby Database Creation in Oracle view answer
  • Working with AWR - Oracle 10g for Performance tuning in Oracle view answer
  • DBMS_SCHEDULER in Detail: view answer
  • How to manually Create Oracle DataBase: view answer
  • How to create Oracle Database Version: 10.2.0.1.0 in Linux: view answer
  • Change DB Name in Oracle: view answer
  • ORA-06502: PL/SQL: numeric or value error in Database Version: 10.2.0.3 view answer
  • Subtracting values in PL/SQL and merging result into cursor or table ----------------------- I am trying to pull in values from two different dates on a table (start and end) and subtract one from the other and store it into my result set which I'll be passing back via a cursor with my PL/SQL procedure. I'm a little stuck on how to accomplish this. Here's what I have so far. The FOR loop doesn't work and it's obviously not the way to go but it represents what I'm trying to get to. CODE: create or replace PROCEDURE BLMROUTING (startDate IN DATE, endDate IN DATE, series IN VARCHAR, p_cursor OUT SYS_REFCURSOR) AS TYPE rec IS RECORD (integ diag.blm_acct.integ_over_val%TYPE, signal diag.PV_SGNL_ID_ASSC.sgnl_id%TYPE, sampleDate diag.blm_acct.smpl_dte%TYPE); TYPE tableResults IS TABLE OF rec; ResultTableStart tableResults; ResultTableEnd tableResults; ResultTable tableResults; BEGIN SELECT tc.integ_over_val, ta.sgnl_id, MAX(startDate) as MaxStartDate BULK COLLECT INTO ResultTableStart FROM diag.pv_sgnl_id_assc ta INNER JOIN diag.series_sgnl_rec_asgn tb ON ta.sgnl_id = tb.sgnl_id INNER JOIN diag.blm_acct tc on ta.pv_id = tc.pv_id WHERE tb.series_id = series ORDER BY disp_ord_nbr; SELECT tc.integ_over_val, ta.sgnl_id, MAX(endDate) as MaxEndDate BULK COLLECT INTO ResultTableEnd FROM diag.pv_sgnl_id_assc ta INNER JOIN diag.series_sgnl_rec_asgn tb ON ta.sgnl_id = tb.sgnl_id INNER JOIN diag.blm_acct tc on ta.pv_id = tc.pv_id WHERE tb.series_id = series ORDER BY disp_ord_nbr; FOR j IN ResultTableStart.FIRST..ResultTableStart.LAST LOOP INSERT INTO ResultTable VALUES (ResultTableStart(j).integ_over_val - ResultTableEnd(j).integ_over_val, ResultTableStart(j).sgnl_id, ResultTableStart(j).MaxStartDate); END LOOP; END BLMROUTING; view answer
  • What is an PL/SQL Block in Oracle ? view answer
  • How To Invoke the Original Export Import Utilities? view answer
  • Migrating a Database across platforms. view answer
  • Export and Import exp / imp in Oracle view answer
  • Renaming or Relocating Datafiles belonging to a Single Tablespace view answer
  • Procedure for transporting tablespaces view answer
  • Transporting Tablespaces view answer
  • Increasing or Decreasing the size of a Temporary Tablespace view answer
  • Temporary Tablespace view answer
  • Dropping Tablespaces view answer
  • Renaming Tablespaces view answer
  • Making a Tablespace Read only. view answer
  • Taking tablespaces Offline or Online view answer
  • How To Change System Global Area (SGA) in Oracle? view answer
  • How To Unlock the Sample User Account in Oracle? view answer
  • What Operating Systems Are Supported by Oracle Database 10g XE? view answer
  • What Are the Limitations Oracle Database 10g XE? view answer
  • What is Oracle Database 10g Express Edition? view answer
  • What Is Open Database Communication (ODBC) in Oracle? view answer
  • What Is Transport Network Substrate (TNS) in Oracle? view answer
  • What Is SQL*Plus? view answer
  • What Is a Recycle Bin in Oracle? view answer
  • What Is a Dynamic Performance View in Oracle? view answer
  • What Is a Static Data Dictionary in Oracle? view answer
  • What Is an Oracle Data File? view answer
  • What Is an Oracle Tablespace? view answer
  • What Is a Table Index in Oracle? view answer
  • What Is a Database Table in Oracle? view answer
  • What is a Database Schema in Oracle? view answer
  • What Is a User Role in Oracle? view answer
  • What Is the Relation of a User Account and a Schema in Oracle? view answer
  • What Is a User Account in Oracle? view answer
  • What is Program Global Area (PGA) in Oracle? view answer
  • What is System Global Area (SGA) in Oracle? view answer
  • What Is a Initialization Parameter File in Oracle? view answer
  • What Is a Server Parameter File in Oracle? view answer
  • What Is a Parameter File in Oracle? view answer
  • What Is an Oracle Instance? view answer
  • What Is an Oracle Database? view answer
  • What Is Oracle? view answer
  • What are various constraints used in SQL? view answer
  • What is pseudo columns ? Name them? view answer
  • What are the Various Master and Detail Relation ships. view answer
  • Can a view be updated/inserted/deleted? If Yes under what conditions ? view answer
  • What is CYCLE/NO CYCLE in a Sequence ? view answer
  • Explain UNION,MINUS,UNION ALL, INTERSECT ? view answer
  • What is difference between TRUNCATE & DELETE ? view answer
  • Which is more faster - IN or EXISTS? view answer
  • When do you use WHERE clause and when do you use HAVING clause? view answer
  • What is a join ? Explain the different types of joins ? view answer
  • What is ON DELETE CASCADE ? view answer
  • What is difference between UNIQUE and PRIMARY KEY constraints? view answer
  • What are the advantages of VIEW ? view answer
  • List few advantages of Stored Procedure. view answer
  • What is an execution plan? When would you use it? How would you view the execution plan? view answer
  • What are different normalization forms? view answer
  • What is normalization? view answer
  • What are the properties of the Relational tables? view answer
  • How to know the days or months or years between two employees in emp table? view answer
  • Please explain Outer Join in SQL view answer
  • Explain Concept of Self Join view answer
  • Explain Normal Join view answer
  • Explain the concept of NULL view answer
  • Please explain the concepts of transaction, commit and rollback view answer
  • Emp table had an employee with salary 2000. I issued an update statement to set the salary to 3000. Then I issued a create table statement. However the create table command errored out. I want to rollback the earlier update statement. Can I do that? view answer
  • What is the difference between Rule based optimization and Cost based optimization in Oracle SQL queries? view answer
  • What is a Shared Folder? view answer
  • You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do? view answer
  • Your users get the following error: ORA-00055 maximum number of DML locks exceeded What is the problem and how do you fix it? view answer
  • You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem? view answer
  • You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it? view answer
  • Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs? view answer
  • Users aren?t being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed What is the problem? view answer
  • What file will give you Oracle instance status information? Where is it located? view answer
  • How can you determine if the SQLNET process is running for SQLNET V1? How about V2? view answer
  • Users from the PC clients are getting the following error stack: view answer
  • Users from the PC clients are getting messages indicating : view answer
  • How can you determine if an Oracle instance is up from the operating system level? view answer
  • If you aren?t sure what command does a particular UNIX function what is the best way to determine the command? view answer
  • What does >> do? view answer
  • What is the easiest method to create a file on UNIX? view answer
  • What is the finger command? view answer
  • How can you find your id on a system? view answer
  • How can you find all the processes on your system? view answer
  • How can you find dead processes? view answer
  • What is redirection and how is it used? view answer
  • The system administrator tells you that the system hasn?t been rebooted in 6 months, should he be proud of this? view answer
  • What is an inode? view answer
  • The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program? view answer
  • What is the purpose of the grep command? view answer
  • How do you create a decision tree in a shell script? view answer
  • Can you remove an open file under UNIX? view answer
  • What command is used to remove a file? view answer
  • What command is used to type files to the screen? view answer
  • How can you determine the number of SQLNET users logged in to the UNIX system? view answer
  • How can you determine the space left in a file system? view answer
  • When should you consider denormalization? view answer
  • What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used? view answer
  • How should a many-to-many relationship be handled? view answer
  • What does a hard one-to-one relationship mean (one where the relationship on both ends is ?must?)? view answer
  • Why are recursive relationships bad? How do you resolve them? view answer
  • What is an ERD? view answer
  • Is the following statement true or false: ?All relational databases must be in third normal form? Why or why not? view answer
  • Describe third normal form? view answer
  • You have a simple application with no ?hot? tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces? view answer
  • How many redo logs should you have and how should they be configured for maximum recoverability? view answer
  • How many control files should you have? Where should they be located? view answer
  • When should the default values for Oracle initialization parameters be used as is? view answer
  • OS user should be used for the first part of an Oracle installation (on UNIX)? view answer
  • You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for? view answer
  • What must be installed with ODBC on the client in order for it to work with Oracle? view answer
  • When configuring SQLNET on the client what files need to be set up? view answer
  • When configuring SQLNET on the server what files must be set up? view answer
  • You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem? view answer
  • What should be done prior to installing Oracle (for the OS and the disks)? view answer
  • How do you set up your tablespace on installation? view answer
  • Define OFA. view answer
  • You see multiple extents in the temporary tablespace. Is this a problem? view answer
  • In a system with an average of 40 concurrent users you get the following from a query on rollback extents: ROLLBACK CUR EXTENTS --------------------- -------------------------- R01 11 R02 8 R03 12 R04 9 SYSTEM 4 You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action? view answer
  • You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem? view answer
  • You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem? view answer
  • If you see the value for reloads is high in the estat library cache report is this a matter for concern? view answer
  • you see the value for reloads is high in the estat library cache report is this a matter for concern? view answer
  • If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it? view answer
  • What can cause a high value for recursive calls? How can this be fixed? view answer
  • You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about? What if redo log space wait time is high? How can you fix this? view answer
  • How can you tell if a tablespace has excessive fragmentation? view answer
  • If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only) view answer
  • If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)? view answer
  • If you see statistics that deal with ?undo? what are they really talking about? view answer
  • you see contention for library caches how can you fix it? view answer
  • When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it? view answer
  • Discuss row chaining, how does it happen? How can you reduce it? How do you correct it? view answer
  • Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning? view answer
  • Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed? view answer
  • When should you increase copy latches? What parameters control copy latches? view answer
  • When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it? view answer
  • Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output? view answer
  • What is the fastest query method for a table? view answer
  • What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans? view answer
  • What are some indications that you need to increase the SHARED_POOL_SIZE parameter? view answer
  • You see multiple fragments in the SYSTEM tablespace, what should you check first? view answer
  • How do you set up tablespaces during an Oracle installation? view answer
  • A tablespace has a table with 30 extents in it. Is this bad? Why or why not. view answer
  • How do you generate file output from SQL? view answer
  • How do you prevent Oracle from giving you informational messages during and after a SQL statement execution? view answer
  • How do you prevent output from coming to the screen? view answer
  • How do you set the number of lines on a page of output? The width? view answer
  • What is explain plan and how is it used? view answer
  • What is tkprof and how is it used? view answer
  • What is the default ordering of an ORDER BY clause in a SELECT statement? view answer
  • You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic? view answer
  • What is a Cartesian product? view answer
  • 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? view answer
  • What special Oracle feature allows you to specify how the cost based system treats a SQL statement? view answer
  • 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 view answer
  • What SQLPlus command is used to format output from a select? view answer
  • You want to use SQL to build SQL, what is this called and give an example view answer
  • How do you execute a host operating system command from within SQL? view answer
  • can you call a PL/SQL procedure from SQL? view answer
  • You want to include a carriage return/linefeed in your output from a SQL script, how can you do this? view answer
  • How can variables be passed to a SQL routine? view answer
  • How can you determine if an index needs to be dropped and rebuilt? view answer
  • A user selects from a sequence and gets back two values, his select is: SELECT pk_seq.nextval FROM dual; What is the problem? view answer
  • How can you find out how many users are currently logged into the database? How can you find their operating system id? view answer
  • If you have an example table, what is the best way to get sizing data for the production table implementation? view answer
  • A developer is trying to create a view and the database won?t let him. He has the ?DEVELOPER? role which has the ?CREATE VIEW? system privilege and SELECT grants on the tables he is using, what is the problem? view answer
  • A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check? view answer
  • How can you tell if a database object is invalid? view answer
  • What causes the ?snapshot too old? error? How can this be prevented or mitigated? view answer
  • You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not? view answer
  • (On UNIX) When should more than one DB writer process be used? How many should be used? view answer
  • What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause? view answer
  • What is the proper method for disabling and re-enabling a primary key constraint? view answer
  • What happens if a tablespace clause is left off of a primary key constraint clause? view answer
  • What happens if the constraint name is left out of a constraint clause? view answer
  • What are some of the Oracle provided packages that DBAs should be aware of? view answer
  • If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why? view answer
  • You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal? view answer
  • What is the purpose of the IMPORT option IGNORE? What is it?s default setting? view answer
  • Give one method for transferring a table from one schema to another: view answer
  • What are the types of triggers? view answer
  • How can you generate debugging output from PL/SQL? view answer
  • How can you find within a PL/SQL block, if a cursor is open? view answer
  • What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? view answer
  • In what order should a 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? view answer
  • When is a declare statement needed ? view answer
  • Describe the use of PL/SQL tables view answer
  • What packages (if any) has Oracle provided for use by developers? view answer
  • Describe the use of %ROWTYPE and %TYPE in PL/SQL view answer
  • What is a mutating table error and how can you get around it? view answer
  • Describe the difference between a procedure, function and anonymous pl/sql block. view answer
  • What is a self join? Explain it with an example. view answer
  • What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand? view answer
  • What is the system function to get the current user?s user id? view answer
  • What is an extended stored procedure? Can you instantiate a COM object by using T-SQL? view answer
  • Can you have a nested transaction? view answer
  • What is a join and explain different types of joins? view answer
  • What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? view answer
  • How to determine the service pack currently installed on SQL Server? view answer
  • What is database replication? What are the different types of replication you can set up in SQL Server? view answer
  • Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan? view answer
  • What are the different ways of moving data/databases between servers and databases in SQL Server? view answer
  • What are statistics, under what circumstances they go out of date, how do you update them? view answer
  • How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode? view answer
  • What is blocking and how would you troubleshoot it? view answer
  • What is a deadlock and what is a live lock? How will you go about resolving deadlocks? view answer
  • What are the steps you will take, if you are tasked with securing an SQL Server? view answer
  • What are the steps you will take to improve performance of a poor performing query? view answer
  • What is RAID and what are different types of RAID configurations? view answer
  • What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach? view answer
  • What are constraints? Explain different types of constraints. view answer
  • What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version? view answer
  • Explain the storage models of OLAP ? view answer
  • What?s the difference between DELETE TABLE and TRUNCATE TABLE commands? view answer
  • What is lock escalation? view answer
  • What?s the maximum size of a row? view answer
  • What is a transaction and what are ACID properties? view answer
  • What are defaults? Is there a column to which a default can?t be bound? view answer
  • Define candidate key, alternate key, composite key. view answer
  • What is bit datatype and what?s the information that can be stored inside a bit column? view answer
  • What are user defined datatypes and when you should go for them? view answer
  • What?s the difference between a primary key and a unique key? view answer
  • How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? view answer
  • What is denormalization and when would you go for it? view answer
  • What is normalization? Explain different levels of normalization? view answer
  • Explain the difference between $ORACLE_HOME and $ORACLE_BASE. view answer
  • Explain an ORA-01555 (snapshot too old ) view answer
  • How would you go about increasing the buffer cache hit ratio? view answer
  • How would you go about generating an EXPLAIN plan? view answer
  • What column differentiates the V$ views to the GV$ views and how ? view answer
  • Give the stages of instance startup to a usable state where normal users may access it. view answer
  • What command would you use to create a backup control file? view answer
  • Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each. view answer
  • A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables? view answer
  • Give two examples of referential integrity constraints. view answer
  • What type of index should you use on a fact table? view answer
  • Give the two types of tables involved in producing a star schema and the type of data they hold. view answer
  • Give the reasoning behind using an index. view answer
  • Compare and contrast TRUNCATE and DELETE for a table. view answer
  • Where would you look for errors from the database engine? view answer
  • Give two examples of how you might determine the structure of the table DEPT. view answer
  • Explain the difference between a data block, an extent and a segment. view answer
  • How do you switch from an init.ora file to a spfile? view answer
  • You have just had to restore from backup and do not have any control files. How would you go about bringing up this database? view answer
  • Explain the difference between a hot backup and a cold backup and the benefits associated with each. view answer
  • How do you view version information in Oracle? view answer
  • Explain implicit cursor attributes. view answer
  • Explain explicit cursor attributes. view answer
  • The NVL function only allows the same data type. But here?s the task: if the commission field is null, then the text ?Not Applicable? should be displayed, instead of blank space. How do you write the query? view answer
  • How do you display the records within a given range? view answer
  • How do I display row number with records? view answer
  • How do I eliminate duplicate rows in an Oracle database? view answer
  • How do you switch to DOS prompt from SQL prompt? view answer
  • What?s the command to change the SQL prompt name? view answer
  • What?s the command to see the current user name? view answer
  • What are the pros and cons of using triggers? Expected answer: view answer
  • When a query is sent to the database and an index is not being used, what type of execution is taking place? view answer
  • What is the difference of a LEFT JOIN and an INNER JOIN statement? view answer
  • What is an advantage to using a stored procedure as opposed to passing an SQL query from an application. view answer
  • What is a Cartesian product? What causes it? view answer
  • Name the operators that cannot be overloaded. sizeof, ., .*, .->, ::, ?: view answer
  • How to use " in Oracle data ? Question -- We are encountering db values that contain both double quotes and apostrophes. Has anyone found a work-around for this situation? view answer
  • What Is Input Buffer in SQL*Plus? view answer
  • How To Save Query Output to a Local File? view answer
  • What Is Output Spooling in SQL*Plus? view answer
  • What Are SQL*Plus Environment Variables? view answer
  • How To Look at the Current SQL*Plus System Settings? view answer
  • How To Change SQL*Plus System Settings? view answer
  • What Information Is Needed to Connect SQL*Plus an Oracle Server? view answer
  • How To Get Help at the SQL Prompt? view answer
  • How To Start the Command-Line SQL*Plus? view answer
  • What Is SQL*Plus? view answer
  • What Is Open Database Communication (ODBC)? view answer
  • What Is Transport Network Substrate (TNS)? view answer
  • What Is SQL*Plus? view answer
  • What Is a Recycle Bin? view answer
  • What Is a Dynamic Performance View? view answer
  • What Is a Static Data Dictionary? view answer
  • What Is an Oracle Data File? view answer
  • What Is an Oracle Tablespace? view answer
  • What Is a Table Index? view answer
  • What Is a Database Table? view answer
  • What is a Database Schema? view answer
  • What Is a User Role? view answer
  • What Is the Relation of a User Account and a Schema? view answer
  • What Is a User Account? view answer
  • What is Program Global Area (PGA)? view answer
  • What is System Global Area (SGA)? view answer
  • What Is a Initialization Parameter File? view answer
  • What Is a Server Parameter File? view answer
  • What Is a Parameter File? view answer
  • What Is an Oracle Instance? view answer
  • What Is an Oracle Database? view answer
  • What Is Oracle? view answer
  • How to find out the third highest salary / nth highest salary ? view answer
  • Can one read/write files from PL/SQL? view answer
  • Is there a limit on the size of a PL/SQL block? view answer
  • What is PL/SQL? view answer
  • What is implicit cursor and how is it used by Oracle ? view answer
  • What is a transaction ? view answer
  • What are the various types of queries ? view answer
  • If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ? view answer
  • What is a package cursor ? view answer
  • What is the use of Data Dictionary ? view answer
  • What is the use of Control files ? view answer
  • Can you increase the size of a tablespace ? How ? view answer
  • Describe Oracle database's physical and logical structure ? view answer
  • What are constraining triggers ? view answer
  • What are mutating triggers ? view answer
  • What are cascading triggers? What is the maximum no of cascading triggers at a time? view answer
  • What are the values of :new and :old in Insert/Delete/Update Triggers ? view answer
  • Can views be specified in a trigger statement ? view answer
  • What is the maximum no. of statements that can be specified in a trigger statement ? view answer
  • What is the advantage of a stored procedure over a database trigger ? view answer
  • What are the various types of database triggers ? view answer
  • What are the parts of a database trigger ? view answer
  • Can you pass parameters in packages ? How ? view answer
  • Why Create or Replace, and not Drop and recreate procedures ? view answer
  • What are the constructs of a procedure, function or a package ? view answer
  • Can 2 functions have same name & input parameters but differ only by return data type view answer
  • Can functions be overloaded ? view answer
  • What is Over Loading and what are its restrictions ? view answer
  • What are the various types of parameter modes in a procedure ? view answer
  • Can you have two stored functions with the same name ? view answer
  • Can you have two functions with the same name in a PL/SQL block ? view answer
  • What is the difference between a procedure and a function ? view answer
  • Can we define exceptions twice in same block ? view answer
  • What are the various types of Exceptions ? view answer
  • PROCEDURE TO PICK RANDOM SET OF NUMBERS FROM A GIVEN RANGE OF NUMBERS view answer
  • FUNCTIONS AS STORED PROGRAM UNITS : insert a record into the table emp1 for job = 'MGR' and sal >5000 - if sal <5000 return false and don't insert view answer
  • Calling a stored procedure within the main procedure view answer
  • PROCEDURES AS STORED PROGRAM UNITS view answer
  • Procedures with FUNCTIONS view answer
  • Procedure Non-Stored Program Units with the "IN OUT PARAMETER" view answer
  • Procedure Non-Stored Program Units with the "OUT PARAMETER" view answer
  • Procedure Non-Stored Program Units with the "IN PARAMETER" ## IN parameter data cannot be manipulated ## IN parameter can only receive a constant value from the main program ## IN parameter does not return any value to the calling program view answer
  • PROCEDURES AS NON-STORED PROGRAM UNITS view answer
  • Procedure to delete rownum 6 to 9 and update 10 to end using the WHERE CURRENT OF CURSOR view answer
  • Procedure using WHERE CURRENT OF CURSOR view answer
  • parameterized cursor with DEFAULT OPTION another option view answer
  • parameterized cursor with DEFAULT OPTION view answer
  • Procedure with parameterized cursors view answer
  • Procedure to find the nth maximum salary use PL/SQL view answer
  • Procedure to select 1st 'n' higest persons & salaries using PL/SQL view answer
  • Procedure with EXCEPTIONS view answer
  • Procedure with RECORDS view answer
  • Procedure with USER-DEFINED EXCEPTIONS view answer
  • Procedure with TOO_MANY_ROWS error handling view answer
  • Procedure with GOTO statement view answer
  • Procedure for scope of variables view answer
  • Procedure to check for PALINDROME and to print the reverse of the string view answer
  • create user defined datatype (arrays) view answer
  • Program for Fibonacci Series view answer
  • Program to generate Prime Numbers view answer
  • Stripping part Numbers of unwanted characters view answer
  • Program to print reverse of values view answer
  • SIMPLE PROGRAM USING PARAMETERIZED CURSOR view answer
  • SIMPLE PROGRAM IMPLEMENT EXPLICIT CURSORS view answer
  • PROGRAM TO GENERATE MULTIPLICATION TABLE FOR A NUMBER 'n' view answer
  • PROGRAM TO GENERATE FIBONACCI SERIES UPTO 'n' NUMBERS view answer
  • PROGRAM TO CHECK A STRING FOR PALINDROME view answer
  • PROGRAM TO REVERSE A GIVEN STRING view answer
  • PROGRAM TO FIND ODD & EVEN NUMBERS view answer
  • Query to find the #week from the date given view answer
  • Query as follows /* Query*/ need to split a string into seperate values. eg. col1 col2 ---------- 100 - 'a,b,c' 200 - 'a,x,b,d,e' 300 - 'c' result: value count ------------- a - 2 b - 1 c - 2 etc. view answer
  • Query to display date in 3 different formats view answer
  • Query to delete duplicate records from a table view answer
  • Query as follows /* query*/ iam having data in column called col1 ------ 23.23456 456.7894 123.3456 324.67845 like this i have 20000 records here in data after the dot i want only 2 digits with out rounding view answer
  • Query to insert ?&? into a varchar2 datatype column view answer
  • Query as follows I have a query as below: select quantity from material_table. quantity --------- 20 40 10 I want the query to return only first two records (i.e. to stop the query when the sum of quantity is 60 in this case). view answer
  • Query as below /* query asked*/ If an employee taken continues leave from 25-Feb-2006 to 03-Apr-2006, we have to display the monthly leave breakup like Feb ? 4 Days, Mar ? 31 Days and Apr ? 3 Days. How to write a procedure, Please advice us. Record it will be store in Start Date : 25-Feb-2006 End Date : 03-Apr-2006 Notification Date : 25-Feb-2006 No of Days : 38 view answer
  • Query as follows /* query asked */ Say in Table ?T? I have two colums ?A?, ? B? I want to get the sum of A based on values of B. I should get the sum in two buckets For B=0 And B not equal zero. Example A B ___ ___ 1 0 2 1 3 0 4 3 5 2 6 0 So I should get the result set as SUM (A) B ______ __________ 10 For B=ZERO 11 For B=NONZERO view answer
  • Following is the query SQL> DESC PATH Name Null? Type ------------------------------- -------- ---- COL1 CHAR(1) COL2 CHAR(1) SQL> select * from path; Col1 Col2 ------ ------ A B B C B D C M D G M H R Y 7 rows selected. the result to be as follows B,C,C,M,M,H view answer
  • Query to show all running SQL?s view answer
  • SYS_CONNECT_BY_PATH implementation and examples in Oracle 9i view answer
  • Query to find the day of the week view answer
  • Query to find the current day of the week view answer
  • Query to find the last day of the given date (format as mm-yyyy) view answer
  • Partitions Queries details view answer
  • Write a Query to display the Lowest & Highest in Sal column with following Conditions 1. Display 'LOW' in column SAL If SAL < 4000 2. Display 'HIGH' in column SAL if SAL > 4000 view answer
  • Query to find the TOP N Analysis view answer
  • How to find only the duplicate records view answer
  • Following is the Query IF I HAVE TWO COLUMNS LIKE THAT ID COLUMN A COLUMN B 1 A 10000 2 Z 20000 3 I 30000 4 Z 40000 5 P 50000 6 D 60000 7 A 70000 8 P 80000 9 I 90000 Find the Query which will SUM THE COLUMN B, GROUP BY COLUMN A WITHOUT DISTRUBING THE SEQUENCE Output shud be as A 80000 Z 60000 I 12000 P 13000 D 6000 view answer
  • The Follwing is the Query id date --------------- 1 NULL 1 NULL 1 NULL 2 NULL 2 21.03.2004 3 22.04.2003 3 10.04.2003 Want the id when the date is never filled Output shud return as ID 1 view answer
  • Following is the Query?O/P Wanted ! Table A ------- SNo Sname 1 a 2 b 3 c Table B -------- SNo Course 1 x 1 y 1 k 1 z 2 x 2 z 2 d 3 y 3 z 3 e I need the output in the following format where course is x or y or z. 1 x y z 2 x z 3 y z view answer
  • . Following is the query Table 1 Col1 2 5 7 9 4 6 Query output to be as follows (row1+row2) 2+5=7 (row2+row3) 5+7 =12 (row3+row4) 7+9=16 etc etc view answer
  • Query to extract the given string 'a;b;c;d;e;f' as (a b c d e f) view answer
  • Query to get the number of days between two dates is easy to get view answer
  • sql query to retrieve the first day of the month... view answer
  • How to find the number of commas in the given String ? view answer
  • How to move a file from one folder to other folder using SQLPLUS? view answer
  • What are the different types of Data models? view answer
  • What is fact constellation? view answer
  • What is a Dimension table? view answer
  • What is a Fact table? view answer
  • What is a Junk Dimension? view answer
  • What is Data Profiling? view answer
  • What is the difference between star schema and snowflake schema? view answer
  • What is Virtual Data Warehousing? view answer
  • What is a Data source? view answer
  • What is a Surrogate key? view answer
  • What is slowly changing dimension? view answer
  • What is Factless Fact Table? view answer
  • What is Staging Area? view answer
  • What is Metadata? view answer
  • What are the various Reporting tools in the Market? view answer
  • What is the Difference between OLTP and OLAP? view answer
  • What is ETL? view answer
  • What is OLAP? view answer
  • 3. What are the various approaches of building a Data warehouse? view answer
  • 2. What is a Data mart? view answer
  • How you can Create a duplicate TABLE of an existing Table view answer
  • Using SQL*Plus how can I produce a flat file without headings, feedback, etc ? view answer
  • How can I see my PL/SQL output ???? view answer
  • How can I retrieve a random number ? view answer
  • What machine, server or instance am I using ?? view answer
  • What DB version is on my machine ? view answer
  • Question: What patches are loaded on my machine ? view answer
  • Question: What Applications/Versions/Patch Levels are loaded on my machine ? view answer
  • How to produce a delimited file from Oracle using SQL ? view answer
  • What versions are loaded on my machine ? view answer
  • SQL*Loader doesn't run after I click the MS-DOS prompt in Windows, why ? view answer
  • What causes SQL*Loader-625: Multibyte character error in control file when I run SQL*Loader? view answer
  • What causes SQL*Loader-625: Multibyte character error in control file when I run SQL*Loader? view answer
  • How can I load data into Oracle tables ? view answer
  • How can I clone (copy) a table without copying the data (just the schema)? view answer
  • How can I move table data from one database to another ? view answer
  • How to find out any difference in objects between two instances ? view answer
  • How to check datatype consistency between two environments ? view answer
  • How to Rebuild indexes to have correct storage parameters ? view answer
  • How to find tables with nonunique PK indexes ? view answer
  • How to find out which primary keys are disabled ? view answer
  • How to find tables without Primary Key constraint view answer
  • How to identify space-bound objects ? view answer
  • How to find out any object reaching threshold ? view answer
  • How to analyze tables and indexes quickly, using a 5% sample size view answer
  • How to check free, pct_free, and allocated space within a tablespace view answer
  • How to verify free space in tablespaces view answer
  • What is the difference between hibernate and spring JDBC template? List any advantages and disadvantages view answer
  • What is the difference between TRUNCATE between DELETE IN Oracle? view answer

!!! Oracle interview questions !!!

Export and Import exp / imp in Oracle



Export and Import

You can run Export and Import tool in two modes

Command Line Mode

Interactive Mode

When you just type exp or imp at o/s prompt it will run in interactive mode i.e. these tools will prompt you for all the necessary input. If you supply command line arguments when calling exp or imp then it will run in command line mode

Command Line Parameters of Export tool

You can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table





Keyword Description (Default)

--------------------------------------------------------------

USERID username/password

BUFFER size of data buffer

FILE output files (EXPDAT.DMP)

COMPRESS import into one extent (Y)

GRANTS export grants (Y)

INDEXES export indexes (Y)

DIRECT direct path (N)

LOG log file of screen output

ROWS export data rows (Y)

CONSISTENT cross-table consistency(N)

FULL export entire file (N)

OWNER list of owner usernames

TABLES list of table names

RECORDLENGTH length of IO record

INCTYPE incremental export type

RECORD track incr. export (Y)

TRIGGERS export triggers (Y)

STATISTICS analyze objects (ESTIMATE)

PARFILE parameter filename

CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export


The Export and Import tools support four modes of operation

FULL :Exports all the objects in all schemas
OWNER :Exports objects only belonging to the given OWNER
TABLES :Exports Individual Tables
TABLESPACE :Export all objects located in a given TABLESPACE.


The information you are posting should be related to java and ORACLE technology. Not political.