Oracle Interview Questions1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
- A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
- I would create a text based backup control file, stipulating where on disk all the data files were and then issue the recover command with the using backup control file clause.
- Issue the create spfile from pfile command.
- A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
- Use the describe command or use the dbms_metadata.get_ddl package.
- In the alert log.
- Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.
- Faster access to data blocks in a table.
- Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
- A Bitmap index.
- A Primary or Unique Key can be used to enforce uniqueness on one or more columns.
- A Referential Integrity Contraint can be used to enforce a Foreign Key relationship between two tables.
- A Not Null constraint - to ensure a value is entered in a column
- A Value Constraint - to check a column value against a specific set of values.
- Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
- 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. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. 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.
- Alter database backup control file to trace.
- STARTUP NOMOUNT - Instance startup
- STARTUP MOUNT - The database is mounted
- STARTUP OPEN - The database is opened
- The INST_ID column which indicates the instance in a RAC environment the information came from.
- Create a plan table with utlxplan.sql.
- Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
- Look at the explain plan with utlxplp.sql or utlxpls.sql
- Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
- You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
- ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Oracle Interview Questions1. How would you determine the time zone under which a database was operating?
- SELECT dbtimezone FROM DUAL;
- It ensure the use of consistent naming conventions for databases and links in a networked environment.
- They are all named PL/SQL blocks.
- Function must return a value. Can be called inside a query.
- Procedure may or may not return value.
- Package is the collection of functions, procedures, variables which can be logically grouped together.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
- Job Queue Process (CJQ)
13. How would you force a log switch?
- alter system switch logfile;
15. What does coalescing a tablespace do?
- Coalesce simply takes contigous free extents and makes them into a single bigger free extent.
- TEMP tablespace gets cleared once the transaction is done where as PERMANENT tablespace retails the data.
- Grant create session to username;
- alter tablespace USERS add datafile '/ora01/oradata/users02.dbf' size 50M;
- alter database datafile '/ora01/oradata/users02.dbf' resize 100M;
- By implementing an INSERT trigger for logging details during each INSERT operation on the table
- ALTER INDEX index_name REBUILD;
- A table partition is also a table segment, and by using partitioning technique we can enhance performance of table access.
- show errors
- exec dbms_stats.gather_table_stats
- Also, remember to analyze all associated indexes on that table using dbms_stats.gather_index_stats
- alter session set sql_trace='TRUE';
- SQL*LOADER loads external data which is in OS files to oracle database tables while IMPORT utility imports data only which is exported by EXPORT utility of oracle database.
- TNSNAMES.ORA and SQLNET.ORA