About a DBA and more...

Reference: http://trailfire.com/frank/marks/39915

What is a DBA?
A DBA is a Database Administrator, and this is the most common job that you find a database specialist doing. There are Development DBAs and Production DBAs. A Development DBA usually works closely with a team of developers and gets more involved in design decisions, giving advice on performance and writing good SQL. That can be satisfying at a human level because you are part of a team and you share the satisfaction of the team's accomplishments. A Production DBA (on the other hand) is responsible for maintaining Databases within an organisation, so it is a very difficult and demanding job. He or she, often gets involved when all the design decisions have been made, and has simply to keep things up and running. Therefore, of course, it is also a rewarding job, both financially and in terms of job satisfaction. But it's a more 'lonely' job than being a Development DBA.

1. What DBA activities did you to do today?
This is a loaded question and almost begs for you to answer it with "What DBA activities do you LIKE to do on a daily basis?." And that is how I would answer this question. Again, do not get caught up in the "typical" day-to-day operational issues of database administration. Sure, you can talk about the index you rebuilt, the monitoring of system and session waits that were occurring, or the space you added to a data file, these are all good and great and you should convey that you understand the day-to-day operational issues. What you should also throw into this answer are the meetings that you attend to provide direction in the database arena, the people that you meet and talk with daily to answer adhoc questions about database use, the modeling of business needs within the database, and the extra time you spend early in the morning or late at night to get the job done. Just because the question stipulates "today" do not take "today" to mean "today." Make sure you wrap up a few good days into "today" and talk about them. This question also begs you to ask the question of "What typical DBA activities are performed day to day within X Corporation?"

2. What are the different modes of mounting a Database with the Parallel Server?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

3. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode?
Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

4. Do you consider yourself a development DBA or a production DBA and why?
You take this as a trick question and explain it that way. Never in my database carrier have I distinguished between "development" and "production." Just ask your development staff or VP of engineering how much time and money is lost if development systems are down. Explain to the interviewer that both systems are equally important to the operation of the company and both should be considered as production systems because there are people relying on them and money is lost if either one of them is down. Ok you may be saying, and I know you are, that we lose more money if the production system is down. Ok, convey that to the interviewer and you won't get anyone to disagree with you unless your company sells software or there are million dollar deals on the table that are expecting the next release of your product or service.

5. What are the Large object types suported by Oracle?
Answer11)bfile - Up to 4 gigabytes �> File locators that point to a read-only binary object outside of the database2)blob - Up to 4 gigabytes. �> LOB locators that point to a large binary object within the database3)clob - Up to 4 gigabytes. �> LOB locators that point to a large character object within the database4)nclob - Up to 4 gigabytes. �>LOB locators that point to a large NLS character object within the databaseAnswer2These are the large object type supported bye oracle� CLOB and LONG for large fixed-width character data� NCLOB for large fixed-width national character set data� BLOB and LONG RAW for storing unstructured data� BFILE for storing unstructured data in operating system files

6. Diffrence between a �where� clause and a �having� claus
Answer1The order of the clauses in a query syntax using a GROUP BY clause is as follows:select �where..group by�having�order by�Where filters, group by arranges into groups, having applies based on group by clause. Having is applied with group by clause. Answer2In SQL Server, procedures and functions can return values. (In Oracle, procedures cannot directly return a value). The major difference with a function is that it can be used in a value assignment. Such as: �system functionDeclare @mydate datetimeSet @mydate = getdate()�user function (where the user has already coded the function)Declare @My_areaSet @My_area = dbo.fn_getMy_area(15,20) Answer31.�where� is used to filter records returned by �Select�2.�where� appears before group by clause3.In �where� we cannot use aggrigate functions like where count(*) >2 etc4.�having� appears after group by clause5.�having� is used to filter records returned by �Group by�<>2 etc there are two more

7. Shall we create procedures to fetch more than one record?
Yes. We can create procedures to fetch more than a row. By using CURSOR commands we could able to do that. Ex: CREATE OR REPLACE PROCEDURE myprocedure ISCURSOR mycur IS select id from mytable;new_id mytable.id%type;BEGINOPEN mycur;LOOPFETCH mycur INTO new_id;exit when mycur%NOTFOUND;�do some manipulations�END LOOP;CLOSE mycur;END myprocedure;In this example iam trying to fetch id from the table mytable. So it fetches the id from each record until EOF. (EXIT when mycur%NOTFOUND-is used to check EOF.For further informations, refer CURSORS.

8. Do View contain Data?
Views do not contain or store data.

9. What are the Referential actions supported by FOREIGN KEY integrity constraint?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

10. What are the type of Synonyms?
There are two types of Synonyms Private and Public

11. Where would you look for errors from the database engine?
In the alert log.

12. Compare and contrast TRUNCATE and DELETE for a table.
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 a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

13. Give the reasoning behind using an index.
Faster access to data blocks in a table.

14. Give the two types of tables involved in producing a star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

15. What type of index should you use on a fact table?
A Bitmap index.

16. What is a Segment? A segment is a set of extents allocated for a certain logical structure.

17. 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?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

18. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
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.

19. What is a Sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

20. Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startupSTARTUP MOUNT - The database is mountedSTARTUP OPEN - The database is opened

21. What is a Synonym?
A synonym is an alias for a table, view, sequence or program unit.

22. How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql. Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statementLook at the explain plan with utlxplp.sql or utlxpls.sql

23. How would you go about increasing the buffer cache hit ratio?
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.

24. Explain an ORA-01555
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.

25. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

26. How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;

27. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

28. What command would you use to encrypt a PL/SQL application?
WRAP

29. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

30. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

31. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

32. Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

33. Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

34. What does a Control file Contain?
A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.

35. What is difference between UNIQUE constraint and PRIMARY KEY constraint?
A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls

36. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command?
What are the factors that affect OPTIMIZER in choosing an Optimization approach? - Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

37. Describe what redo logs are.
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

38. How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;

39. What are the different Parameter types?
Text ParametersData Parameters

40. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

41. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

42. Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.

43. When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.

44. How do you add a data file to a tablespace?
ALTER TABLESPACE ADD DATAFILE SIZE

45. How do you resize a data file?
ALTER DATABASE DATAFILE RESIZE ;

46. What view would you use to look at the size of a data file?
DBA_DATA_FILES

47. What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE

48. How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.

49. How can you rebuild an index?
ALTER INDEX REBUILD;.

50. Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

51. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS

52. How can you gather statistics on a table?
The ANALYZE command.

53. How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE orUse ALTER SESSION SET SQL_TRACE = TRUE;

54. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

55. What is a Schema?
The set of objects owned by user account is called the schema.

56. What is a cluster Key?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

55. What?s the command to change the SQL prompt name?
SQL> set sqlprompt ?database-1 > ?database-1 >database-1 >

56. What is Parallel Server?
Multiple instances accessing the same database (Only In Multi-CPU environments)

57. How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv); Example. Table Emp Empno Ename 101 Scott 102 Jiyo 103 Millor 104 Jiyo 105 Smith delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);The output like, Empno Ename 101 Scott 102 Millor 103 Jiyo 104 Smith 58. How do I display row number with records? Answer1To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;

Output: 1 Scott

2 Millor

3 Jiyo

4 Smith

Answer2

select rownum ,ename from emp;

Output:

1 smith

2 john

3 king

4 scott

59. Display the records between two range
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);

Enter value for upto: 10

Enter value for Start: 7

ROWNUM EMPNO ENAME

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

1 7782 CLARK

2 7788 SCOTT

3 7839 KING

4 7844 TURNER

60. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text ?Not Applicable? want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;

Output :

NVL(TO_CHAR(COMM),'NA')

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

NA

300

500

NA

1400

NA

NA

61. Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

62. Explicit Cursor attributes
There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

63. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL :

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips :1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.

2. All are Boolean attributes.

64. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2

SAL

---------

3700

65. To view installed Oracle version information
SQL> select banner from v$version;

66. Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp; the output like,

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

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

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like, Rs. Three Thousand only. SQL> select sal "Salary ", (' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.')) "Sal in Words" from emp

/

Salary Sal in Words

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

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.
67. Display Odd/ Even number of records
Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

1 3 5

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

2 4 6

68. Which date function returns number value?
months_between

69. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

70. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor

71. Other way to replace query result null value with a text
SQL> Set NULL ?N/A? to reset SQL> Set NULL ??

72. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

73. What is the output of SIGN function?
1 for positive value, 0 for Zero, -1 for Negative value.

74. What is the maximum number of triggers, can apply to a single table?
12 triggers.

75. What is a database instance? Explain.
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users. The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

76. What is Parallel Server?
Multiple instances accessing the same database (only in multi-CPU environments)

More on:http://dba.fyicenter.com/interview/DBA_9.html

About 50 pages of interviews