(Continued from previous part...)

123. What is a “trigger”?

Answer1
It’s a piece of sql that is activitied when a certain event happens.

Answer2
Trigger is also set of sql comments written together same as stored procedure but the difference is that trigger will be activated each & everytime addition or deletion takes place.

124. 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.

125. What cursor type do you use to retrieve multiple recordsets?

REF cursor in oracle

126. What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.

DROP…..It will drop the table completely.(all values& table structure)
TRUNCATE……All the values in the table will be deleted.(the structure wont be)
DELETE….Can be use to delete particular row or all values i table.(structure wont be deleted)

127. Difference between “ORACLE” and “MICROSOFT ACCESS” databases.

Answer1
ORACLE ….can be use with a no of platform eg unix,windown,solaris.SQL that is use for retriving data from oracle is known as Transact SQL.ORACLE is very big database compare with MSAcess.
MSAcess……Can be use with windows only.SQL use is known just as SQL.

Answer2
MSA support only in windows platforms. But oracle available in all platforms. No security and there is no conceprs of schema,users,privilages in access. Aceess is used in small and medium organizations. Oracle is a collection of large no database tools for mangaing data.

128. How to remove duplicate records from a table?

Answer1
Sol. DELETE FROM table_name WHERE id=1 LIMIT 1;

Answer2
in Oracle:-
delete from table_name
where id = 1
and rowid != (select max(rowid) from table_name where id = 1);

Note - min() can be used in place of max(). max() will keep the latest record and min() will keep the oldest record.

Answer3
in Oracle:-
delete from emp
where rowid not in (select max(rowid) from emp group by empno);

129. Are you a nuts-n-bolts DBA or a tools-n-props DBA

A nuts-n-bolts DBA is the type that likes to figure out every little item about how the database works. He/she is a DBA who typically hates a GUI environment and prefers the command line to execute commands and accomplish tasks. A nuts-n-bolts DBA like to feel in control of the database and only feels comfortable at the command line and vi as an editor. The tools-n-props DBA is mostly the opposite of a nuts-n-bolts DBA, they like the feel of a GUI, the ease at which things can be accomplished without knowing much about the database. They want to get the job done with the least amount of intervention from having to figure out what everything is doing behind the scenes. Now the answer, I would explain myself as a combination of the two. I, having been in this business for over 20 years, have grown up in a command line era where the GUIs never seemed to work. There was high complexity in systems and not much good documentation on how things worked. Thus, I had to learn everything about most aspects of the database environment I was working in and thus became a nuts-n-bolts DBA. I was a true command line and vi bigot. Times have changed and the GUIs are very reliable, understand the environment they are installed on, and can generally get the job done quicker for individuals new to database administration. I too am slowly slipping over to the dark side of GUI administration. If you find yourself as a tools-n-props DBA, try to convey that you are aware of some tasks that require you to be a nuts-n-bolts DBA.

130. How to create a database link?

A database link is an object in the local database that allows you to access objects on a remote database or to mount a secondary database in read-only mode.
CREATE [PUBLIC] DATABASE LINK dblink
[CONNECT TO user IDENTIFIED BY password]
[USING ‘connect_string’]

131. What is SQL*Loader?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility.

132. How to run SQL script from a Unix Shell?

The below example shows how to interact oracle from Shell script sqlplus /nolog

(Continued on next part...)