Oracle Interview Question

What is schema?

Once a USER gets created in the ORACLE database a schema is created.

How to create a user in oracle?

CREATE USER gopal IDENTIFIED BY me;

What is a control file?

The control file records the name of the database, the date and time it was created, the location of the database and redo logs, and the synchronization information. Any database must have at least one control file.

What is redo log files?

 Ans: Any database must have at least two redo logs. These are the journals for the database; the redo logs record all changes to the user objects or system objects. If any type of failure occurs, the changes recorded in the redo logs can be used to bring the database to a consistent state without losing any committed transactions.

What is temporary table space?

Ans: To improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, create temporary
tablespaces. A temporary tablespace can be shared by multiple users and can be
assigned to users with the CREATE USER statement when you create users in the
Database.

What is SGA?

Oracle database-wide system memory is known as the SGA, the system global area or shared global area. The data and control structures in the SGA are shareable, and all the Oracle background processes and user processes can use them.

What do you mean by commit and rollback?

Ans: commit - This statement is used to make all pending changes permanent in the database. After commit is executed the local changes are reflected to database and other user.

Example - Suppose there is an employee table. We delete a row from this table and then give the commit statement it will make changes permanent. That means we can't rollback it.
Rollback- This statement is used to discard(undo) all pending changes.
Example- Suppose there is an employee table. We wanted delete a row from this table but accidentally we delete all the rows. Then if we use the rollback statement we can go to the previous state.

What are database objects available in oracle?

Ans: There are five database objects available in oracle. These are table, view, sequence, index, and synonym

What is a view?

Ans: A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table.
There is no stored file that direct represents the view instead a definition of view is stored in data dictionary.

What is sequence?

Ans: It is a database object .it is used to generate unique integers.

What are DML & DDL statements?

Ans: DML is data manipulation language like -insert, update, delete. These are all non auto-committed statement.
DDL is data definition language like- create table, alter table. These are all auto-committed statement.

What are stored-procedures? And what are the advantages of using them.

Ans: Stored procedures are database objects that perform a user-defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

What is ROWID?

Ans: The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

What is normalization?

  It is a process of analyzing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
Normalization is done using database constraint.
_ Minimizing redundancy (Avoid storing the same data in the database)
_ Minimizing insertion, deletion and update anomalies.