What is the difference between Stored procedure and function?

ANS:- Functions and stored procedure are almost same. The only difference between a function and a stored procedure is that function will return a value, but stored procedure does not return a value. But you can pass arguments to stored procedure useing IN and you can take argument from a stored procedure by using OUT.
 

What are the different types of Database triggers?

ANS:- There are 12 different types of triggers. The main division is Row level triggers(Fires for each row) and Statement level triggers(Fires once for the triggering statement). In each of this triggers you can have BEFORE or AFTER of INSERT UPDATE and DELETE. So it make a total of 12 triggers. You can have more than one same type of trigger for a table from oracle7.2 onwards.
 

What is the pctincrease of a rollback segment? What is the optimal parameter?

ANS:- PCTINCREASE for a rollback segment is zero. Optimal parameter is the optimal size of the rollback segment. If the rollback segment is subjected to frequent shrinks and expands it will affect the performance of the database. So we have to set a parameter called OPTIMAL so that it will shrink only to that size.
 

How to take warm backup?

ANS:- Warm backup is online backup. All datafiles of an individual online tablespace or specific datafiles of an online tablespace can be backed up while the tablespace and datafiles are currently online and in use for normal database operation. To perform an online backup of an entire tablespace,or a specific datafile of an online tablespace, perform the following tasks:

1. Identify the datafiles.

If you are backing up a specific datafile, use the fully specified filename of the datafile.

Before beginning a backup on an entire tablespace, identify all of the tablespace's datafiles using the DBA_DATA_FILES data dictionary view. For example, assume that the USERS tablespace is to be backed up. To identify the USERS tablespace's datafile, you can query the DBA_DATA_FILES view:

        SELECT tablespace_name, file_name

           FROM sys.dba_data_files

           WHERE tablespace_name = 'USERS';
 

        TABLESPACE_NAME    FILE_NAME

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

        USERS              filename1

        USERS              filename2

Here, filename1 and filename2 are fully specified filenames corresponding to the datafiles of the USERS tablespace. See the Oracle7 Server Reference for more information about the DBA_DATA_FILES data dictionary view.

2.  Mark the beginning of the online tablespace backup.

To prepare the datafiles of an online tablespace for backup, use either the Start Online Backup menu  item of Server Manager or the SQL command ALTER TABLESPACE with the BEGIN BACKUP option.

The following statement marks the start of an online backup for the tablespace USERS:

ALTER TABLESPACE users BEGIN BACKUP;

3. Back up the online datafiles.

At this point, you can back up the online datafiles of the online tablespace:

from within Server Manager, using the HOST command by exiting Server 
Manager and entering the operating system commands or starting the backup utility

4. Mark the end of the online tablespace backup.

After backing up the datafiles of the online tablespace, indicate the end of the online backup using either the End Online Tablespace Backup dialog box of Server Manager or the SQL command ALTER TABLESPACE with the END BACKUP option.

The following statement ends the online backup of the tablespace USERS:

ALTER TABLESPACE users END BACKUP;
For the backup of control file use Alter database backup control file to   (SELECT min(rowid)
                             FROM   emp b
                             WHERE b.empno = a.empno and  b.ename = a.ename);
 

What is rowid?

ANS:- Rowid is a unique identifier for a row. It is in hexadecimal format. It got 16 characters. First 8digits are block id. Next 4 row num. Next 4 file id.
 

Which is the trigger used in oracle8.0 for updating a view containing join statement?

ANS:- Oracle8.0 uses INSTEAD OF trigger to update views which cann't be updated otherwise.
 

How do I get Oracle to start automatically when my server boots up?

ANS:-Make sure the entry for your database SID in the /etc/oratab file ends in a capital Y. Eg: 

      #   $ORACLE_SID:$ORACLE_HOME:[N|Y]
      #
      ORAC:/u01/app/oracle/product/7.3.2:Y

The scripts for starting and stopping databases are:
   $ORACLE_HOME/bin/dbstart and dbshut. SQL*Net is started with the lsnrctl command. Add the
   following two entries to your /etc/rc.local (or equivalent) file: 

      su - oracle -c /path/to/$ORACLE_HOME/bin/dbstart
      su - oracle -c /path/to/$ORACLE_HOME/bin/lsnrctl

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips