Oracle Interview Questions: Level 1

Most often asked:

About Interview Questions on Oracle.

http://get-best.com/IT_Interview_Questions/dbaQA1.htm

http://get-best.com/IT_Interview_Questions/dbaQA2.htm

http://get-best.com/IT_Interview_Questions/dbaQA3.htm

http://get-best.com/IT_Interview_Questions/dbaQA4.htm


For more Technical Questions

http://blogs.ittoolbox.com/database/solutions/archives/006260.asp


Click the above link

Oracle Certification: OCP

Database Administrator: Database Administrators are responsible for administrative activities like installing database, taking backups, managing and fine-tuning performance over the network, and data recovery. They have the knowledge of basic architecture of the Oracle database. Visit Oracle DBA Certification Track page to gain detailed information about the exam, its details, types of questions, and how to prepare for the exam, if you are interested in taking the OCP-DBA certification.

http://www.whizlabs.com/ocp/oracle-dba.html


Oracle 9i DBA Path Details

Oracle Administrator track for Oracle 9i has been divided into the following three levels.

1.Associate Level (2 Exams). You need to clear the following exams:

Introduction to Oracle: SQL and PL/SQL OR
Introduction to Oracle 9i: SQL (online also)
Oracle 9i Database: Fundamentals I
Oracle 9i Database: Fundamentals II
Oracle 9i Database: Performance Tuning

*** To get OCP - Professional Level (2 Exams and one Oracle University hands-on course within the Oracle 9i DBA learning path). You need to clear the following exams:

How to check any deadlock and other locking issues

Normally deadlock generates dump file and automatically is released by oracle system process

1) check v$session

sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null

2) To kill a locked session, first need to find sid, serial and use

sqlplus>alter system kill session 'sid, serial#';

*** you need have dba priviledge to kill sessions

3. To find which SQL has lock wait

sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece

What a Oracle DBA can be?

This is the prime most where I visualized what a DBA is!

"DBA is a Database Administrator who logs onto the multi-user operating system as internal or system with the appropriate password is recognized by the operating system as the rightful owner of resources on the Hard disk drive. This person then becomes the Oracle DBA". --- IVAN BAYROSS

And the second question heave me is what a DBA can be!

For this I did not get right answer when I premeditated to take this line of work, but latter with perceptive of Oracle’s Workgroup I could able to learn that DBA’s are not only do backup, recover & tune the database but they design the database and more deeper I go I learned the depths of being a DBA. 10 months of expertise of mine and certification from Oracle made me to know the depths of being what a DBA holds.This is the first question always comes when we take up a new vocation – what I can, if I took up this line of work?So i put my solutions in this web log with the various works a DBA can handle.

Truly the job of DBA encompasses many roles. We will look at the roles which I learn in recent times.

There are DBAs who focus on logical design and DBAs who focus on physical design; DBAs who specialize in building systems and DBAs who specialize in maintaining and tuning systems; specialty DBAs and general-purpose DBAs.

System DBA:

A system DBA focuses on technical rather than business issues, primarily in the system administration area. Typical tasks center on the physical installation and performance of the DBMS software and can include the followingInstalling new DBMS versions and applying maintenance fixes supplied by the DBMS vendorSetting and tuning system parametersTuning the operating system, network, and transaction processors to work with the DBMSEnsuring appropriate storage for the DBMSEnabling the DBMS to work with storage devices and storage management softwareInterfacing with any other technologies required by database applicationsInstalling third-party DBA toolsSystem DBAs are rarely involved with actual implementation of databases and applications. They might get involved in application tuning when operating system parameters or complex DBMS parameters need to be altered.
Indeed, the job of system DBA usually exists only if the organization does not have an official
system administration or systems programming department.

Database Architect:

Some organizations create a separate position, database architect, for design and implementation of new databases. The database architect is involved in new design and development work only; he is not involved in maintenance, administration, or tuning of established databases and applications. The database architect designs new databases for new or existing applications.

The rationale for creating a separate position is that the skills required for designing new databases are different from the skills required to keep an existing database implementation up and running. A database architect is more likely than a general-purpose DBA to have data administration and modeling expertise.

Typical tasks performed by the database architect include:

Creating a logical data model (if no DA or data modeler position exists)Translating logical data models into physical database designsImplementing efficient databases, including specifying physical characteristics, designing efficient indexes, and mapping database objects to physical storage devicesAnalyzing data access and modification requirements to ensure efficient SQL and optimal database designCreating backup and recovery strategies for new databasesMost organizations do not staff a separate database architect position, instead requiring DBAs to work on both new and established database projects.

Database Analyst:

Another common staff position is the database analyst. There is really no set definition for this position. Sometimes junior DBAs are referred to as database analysts. Sometimes a database analyst performs a role similar to that of the database architect. Sometimes the data administrator is referred to as the database analyst or perhaps as the data analyst. And sometimes a database analyst is just another term used by some companies instead of database administratorData Modeler: A data modeler is usually responsible for a subset of the DA’s responsibilities.

Data modeling tasks include the following:

Collecting data requirements for development projectsAnalyzing the data requirementsDesigning project-based conceptual and logical data modelsCreating and updating a corporate data modelEnsuring that the DBAs have a sound understanding of the data models

Application DBA:

In direct contrast to the system DBA is the application DBA. The application DBA focuses on database design and the ongoing support and administration of databases for a specific application or applications. The application DBA is likely to be an expert at writing and debugging complex SQL and understands the best ways to incorporate database requests into application programs. The application DBA must also be capable of performing database change management, performance tuning, and most of the other roles of the DBA. The difference is the focus of the application DBA—it is on a specific subset of applications rather than the overall DBMS implementation and database environment

Task-oriented DBA

Larger organizations sometimes create very specialized DBAs that focus on a specific DBA task. However, task-oriented DBAs are quite rare outside of very large IT shops. One example of a task-oriented DBA is a backup-and-recovery DBA who devotes his entire day to ensuring the recoverability of the organization’s databases.Most organizations cannot afford this level of specialization, but when possible, task-oriented DBAs can ensure that very knowledgeable specialists tackle very important DBA tasks.

Performance Analyst:

Performance analysts are a specific type of task-oriented DBA. The performance analyst, more common than other task-oriented DBAs, focuses solely on the performance of database applications.

A performance analyst must understand the details and nuances of SQL coding for performance and be able to design databases for performance. A performance analyst will have very detailed technical knowledge of the DBMS so that he can make appropriate changes to DBMS and system parameters when required.

The performance analyst is usually the most skilled, senior member of the DBA staff, a role that he has grown into due to his experience and the respect he has gained in past tuning endeavors.

Data Warehouse Administrator:

Organizations that implement data warehouses for performing in-depth data analysis often staff DBAs specifically to monitor and support the data warehouse environment. Data warehouse administrators must be capable DBAs, but with a thorough understanding of the differences between a database that supports OLTP and a data warehouse.

Data warehouse administration requires experience with the following:

Business intelligence, query, and reporting toolsDatabase design for read-only accessData warehousing design issues such as star schemaData warehousing technologies such as OLAP (including ROLAP, MOLAP, and HOLAP)Data transformation and conversionData quality issuesData formats for loading and unloading of dataMiddleware

Want to Become an Oracle DBA

Often I come up with this query? How can I be an Oracle DBA and I always like to allocate my time on this. I hope the links help you if you are looking to be Oracle DBA and the measures you need to consider being a DBA.

http://www.databasejournal.com/features/

oracle/article.php/3335581

10 way's to keep the DBA Job!

http://searchoracle.techtarget.com/originalContent/

0,289142,sid41_gci918447,00.html

*** cut & paste the URL in your Browser

You can become an Oracle DBA, but here is the truth of the matter: there are practically zero jobs or job postings looking for DBA's with zero years of experience. "Entry level" in the Oracle DBA job world typically means 3-5 years of experience. Your IT certificate from the local community college means practically nothing. Your Oracle Certified Professional certificate may mean even less to some hiring managers (they don't know what it is, they've been burned by the paper MCSE, they value experience over a piece of paper, and so on). In this case, a certificate's true value lies within helping you get an interview with a company that uses Oracle.

File System re-organization: Oracle Internal Error

File System re-organization: Targeted: Internal Error 600 (For Below Post)

Oracle Internal Error:

As a Oracle database administrator, we must keep the file system perfect, to make accessible for end users. Well this augments my issue for the internal error which kept me on toe. So I started to work on this to try with this and almost absolute and reasonable.

Unused columns (Why disorganized & what creates them to drop):- Refer my previous post

SQL> Alter table set unused column
SQL>Alter table drop unused columns
Before SQL Statement sets the columns and the next statement drops the columns, but make sure about the constraints on those columns – well why don’t we use cascade option to do this.
SQL>Alter table drop unused column/columns cascade constraints
And one more important thing must me noted - when you are doing these things, the table is not available to the users.

To avoid this, use continue option

SQL>Alter table drop unused column/columns continue

***And one more thing is to note that when you drop the columns the table will be marked as invalid in general. Because it may have reference to other objects like stored procedures, triggers etc. So, it marks as invalid.

So, if use the CONTINUE option for the drop columns, (even during the dropping before completion, if instance crashes, the table will be marked as invalid in general. But when you use the continue option, u will be get rid of this problem and marked as valid) then the table will be marked as valid upon completion.

You can try this to monitor index usage – may be it will help you
SQL>Alter index index_name monitoring usage;

And to stop this – why to stop – well it causes performance degradation
SQL>Alter index index_name nomonitoring usage;

The check with the view
SQL>Select * from v$object_usage

Well its not an end – I need to check all the Index usage, the unused columns etc etc lots of stuff.

Oracle 9.2 on Microsoft Windows 2003 (32 Bit OS)

Internal Error ORA-600[QXIDMACLOB]

Internal Error ORA-600[QXIDMACLOB]

Export/Import Errors:

We have automated script running daily to do export database for the development server, but to my strange I find Internal Error ORA-600[QXIDMACLOB].

Well this case is repeating from couple of days and I could not able to identify this but latter followed up to metal ink where I could find the correct solution for this


Cause
The error is related to Bug:1805146 and is caused by a data dictionary inconsistency left by the ALTER TABLE SET UNUSED statement.This statement mark/delete entries in the col$ table related to that object but, miss or fail to mark/delete rows in some tables called coltype$, lob$ and partlog$.Causing the inconsistency.

Solution
Use the following workaround:After drop a column, this is marked as UNUSED.In order to avoid this error, customer must drop al unused columns executing the following command:ALTER TABLE DROP UNUSED COLUMNS;If the table is really big, this statement can potentially use a lot of UNDO space.In order to avoid UNDO space errors, customer must use the following statement:ALTER TABLE DROP UNUSED COLUMNS checkpoint 100;Checkpointing cuts down the amount of undo logs accumulated during the drop column operation to avoid running out of rollback segment space.If you do not specify a number (integer) , Oracle sets the default of 512.

*** I tried working on this and could able to solve the problem.

Statspack Installation - Steps

Detailed Steps to Configure Statspack!

Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

Space Requirements for Statspack!

Approximately 64MB is required to install Statspack.
If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
*** If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.


Start SQL*Plus.

Connect as a user with SYSDBA privilege.

For example: SQL> CONNECT / AS SYSDBA

Run the SPCREATE.SQL script.

On UNIX platforms, enter the following:

SQL>@?/rdbms/admin/spcreate
On Windows platforms, enter the following:

SQL>@%ORACLE_HOME%\rdbms\admin\spcreate

Once you run this script it will prompt you enter password for perfstat user.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.

*** Once I learn using Statspack I get back with the analysis report.

PERSONALITY TRAITS OF A DBA

Many times managers concentrate on technical qualities and overlook personality. Virtually every category shown above means a DBA will have to interface with other personnel, vendors, users, developers or managers.

This indicates the a DBA should exhibit the following traits:

Self Confidence
Curiosity
Tenacity
Tactful
Self-starter
Detail oriented