Here are few interview questions with answers found on the internet.As I don't have time to format these questions to wiki I am just posting them hoping someone to format them. Explain the difference between a hot backup and a cold backup and the benefits associated with each.NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly. The %TYPE associates a variable with a single column type. What packages (if any) has Oracle provided for use by developers?You get this error when you get a snapshot too old within rollback. Expected answer: Oracle provides the DBMS_ series of packages.A value greater than 80-90% is good, less could indicate problems. It can be reduced by setting the storage parameters on the table to appropriate values. If it is specified, the error is ignored and the tables data will be inserted. The packages used by DBAs may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT. How many should be used If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? One is to look at the v$session or v$process views.If you simply take the ratio of existing parameters this will be a cumulative value since the database started. How do you correct it Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won? It can be corrected by export and import of the effected table. Give one method for transferring a table from one schema to another: There are several possible methods, export-import, CREATE TABLE... Another way is to check the current_logins parameter in the v$sysstat view.In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.A data block is the smallest unit of logical storage for a database object.
These are especially useful for the WHEN OTHERS exception. How can you find within a PL/SQL block, if a cursor is open? The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. This can also be used to generate explain plan output. When looking at v$sysstat you see that sorts (disk) is high. How about an indication if they are default settings or have been changed You can look in the file for an indication of manually set parameters. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems. What is the proper method for disabling and re-enabling a primary key constraint You use the ALTER TABLE command for both. A developer is trying to create a view and the database won? 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 You need to verify the developer has direct grants on all tables used in the view. t create a stored object with grants given through views. If you have an example table, what is the best way to get sizing data for the production table implementation The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation.The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time.The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process.ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. 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? It occurs because the trigger is trying to update a row it is currently using.NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. Where in the Oracle directory tree structure are audit traces placed? 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.
Expected answer: Use the %ISOPEN cursor status variable. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view. Describe hit ratio as it pertains to the database buffers. s default setting The IMPORT IGNORE option tells import to ignore "already exists" errors. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Why The user is assigned the SYSTEM tablespace as a default and temporary tablespace. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause The index is created in the user? The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows. How can you find out how many users are currently logged into the database?