Oracle Interview Handbook

Schema: Logical container of data structures called schema objects

Schema Objects:

1. Table :

• Basic unit of data storage in oracle database.
• Holds user accessible date
• Stotred in rows and columns

2. Indexes :

• Provides direct and fast access to rows. Frequently asked questions on oracle indexes

3. Partitions: 
           Partitioning is  functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, so that these database objects can be managed effectively.

4. Views:
          A view is a virtual table, which provides access to a subset of column from one or more table. A view can derive its data from one or more table. An output of query can be stored as a view.A view in oracle is nothing but a stored sql scripts. Views itself contain no data.

5. Sequences:
        It is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

6. Synonyms:
       A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

7. Dimension:

8. Users : 

9. Roles : 

10. Contexts : 

11. Directory Objects : 

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



Tablespaces : Database is divided into logical storage units called tablespaces.
Used to group related logical structures together.

Materialized views : Basically used to improve the performance of query since it contains results of query.Used for reporting instead of a table for faster execution.

Rollback: 

Used to undo work done in the current transaction.
Syntax:
              Rollback;
              Rollback to savepoint s1;

NULL : Niether 0 nor it is a blank space.It is unknown value uses 4 bytes of space in SQL.

Flashback :  Rewinds the database to a target time, SCN or log sequence number.

Savepoint : 

• Used to subdivide the transaction into smaller parts.
• It enables rolling back part of transaction.
• Maximum of 5 savepoints are allowed.

Snapshot : Read only copy of the master table on a remote node which is referred periodically to reflect changes made to the master table 

• More useful in distributed computing environment
• To create snap shop use create snapshot command
• We can specify refresh interval while creating snapshot.

SCN: The system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.
Transaction :  Sequence of SQL statements that oracle database treats as single unit.

Difference between delete and truncate:

• We cannot rollback truncateO
• Delete from tablename; ->DML ->hence slower
• Truncate table tablename -> DDL ->just moves the high water mark - > hence faster

Correlated Query

It is a form of sub query, where the sub query uses the values from the outer query in its WHERE clause. It  is used for row by row processing. The sub query is executed for each row of the main query. Example queries

Private and public procedure

• Public procedure: In a package, the signature of the procedure is specified in the package specification. This procedure can be called outside of the package.
• Private procedure: For private procedure, there won’t be any signature in the package specification. So, these procedures can be called only inside the package and cannot be called outside of the package.

Sample delete trigger on employees table:

CREATE OR REPLACE TRIGGER EMPLOYEES_AD
 AFTER  DELETE ON EMPLOYEES
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
BEGIN
   INSERT INTO
   employees_changes (employee_id,
            change_date
             )
   VALUES (:OLD.photo_tag_id,
        SYSDATE
     );
END;

Difference between a procedure and a function

A function returns a value. However a procedure does not return a value. 

Order by :
          If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order (which is equivalent to ORDER BY expression ASC.

Cluster:

• It is the optional method of storing table data.
• Made up of group of table that shares same datablock
• Tables are grouped together because they share same column and often used  together.

e.g. EMP and DEPT table shares deptno column. When EMP and DEPT tables are clustered oracle database physically stores all rows of each department from both EMP and DEPT tables in the same data block .

PROS : Disk I/O reduced and access time improves for joins of clustered tables.

Cluster keys are columns that the clustered table have in common .Cluster keys are stored only once no matter how many rows of different tables contains the value.

CONS: Dont use cluster for tables that are frequently accessed individually.

Hash cluster: