Oracle: Exceptions

Exception :
22. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are. CURSOR_ALREADY_OPEN. DUP_VAL_ON_INDEX. NO_DATA_FOUND. TOO_MANY_ROWS. INVALID_CURSOR. INVALID_NUMBER. LOGON_DENIED. NOT_LOGGED_ON. PROGRAM-ERROR. STORAGE_ERROR. TIMEOUT_ON_RESOURCE. VALUE_ERROR. ZERO_DIVIDE. OTHERS.
23. What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
24. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.
25. What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occured. SQLERRM returns the relevant error message of the SQLCODE.
26. Where the Pre_defined_exceptions are stored ?
In the standard package.

Read more »

Oracle: Cursor

Cursors
9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10. Explain the two type of Cursors ?
There are two types of cursors, Implict Cursor and Explicit Cursor. PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
12. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not. % ROWCOUNT - number of rows featched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are featched. % NOT FOUND - to check whether cursor has featched any row. True if no rows are featched. These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.
13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed. eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; END LOOP;
14. What will happen after commit statement ?
Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;-----commit; end loop; end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers
16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
17. How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete. Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the retruned boolean value.
18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
19. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.
20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.
21. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers. ii. done using Integarity Constraints. I & ii.

Read more »

Oracle :CONSTRAINTS

CONSTRAINTS
12. What is an Integrity Constraint ?
Integrity constraint is a rule that restricts values to a column in a table.
13. What is Referential Integrity ?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
14. What are the usage of SAVEPOINTS ?
SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.
15. What is ON DELETE CASCADE ?
When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
16. What are the data types allowed in a table ?
CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.
17. What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?
CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.
18. How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?
Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
19. What are the pre requisites ?
To modify datatype of a column. To add a column with NOT NULL constraint. To Modify the datatype of a column the column must be empty. to add a column with NOT NULL constrain, the table must be empty.
20. Where the integrity constrints are stored in Data Dictionary ?
The integrity constraints are stored in USER_CONSTRAINTS.
21. How will you a activate/deactivate integrity constraints ?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.
22. If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?
It won't, Because SYSDATE format contains time attached with it.
23. What is a database link ?
Database Link is a named path through which a remote database can be accessed.
24. How to access the current value and next value from a sequence ? Is it possible to access the current value in a session before accessing next value ?
Sequence name CURRVAL, Sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.
25. What is CYCLE/NO CYCLE in a Sequence ?
CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum. NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
26. What are the advantages of VIEW ?
To protect some of the columns of a table from other users. To hide complexity of a query. To hide complexity of calculations.
27. Can a view be updated/inserted/deleted? If Yes under what conditions ?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
28.If a View on a single base table is manipulated will the changes be reflected on the base table ?
If changes are made to the tables which are base tables of a view will the changes be reference on the view.

Read more »

Oracle SQL PLUS STATEMENTS

SQL PLUS STATEMENTS
1. What are the types of SQL Statement ?
Data Definition Language : CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language : INSERT,UPDATE,DELETE,LOCK TABLE,EXPLAIN PLAN & SELECT.
Transactional Control : COMMIT & ROLLBACK. Session Control : ALTERSESSION & SET ROLE. System Control : ALTER SYSTEM.
2. What is a transaction ?
Transaction is logical unit between two commits and commit and rollback.
3. What is difference between TRUNCATE & DELETE ?
TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATE, DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
4. What is a join ? Explain the different types of joins ?
Join is a query which retrieves related columns or rows from multiple tables. Self Join - Joining the table with itself. Equi Join - Joining two tables by equating two common columns. Non-Equi Join - Joining two tables by equating two common columns. Outer Join - Joining two tables in such a way that query can also retrive rows that do not have corresponding join value in the other table.
5. What is the Subquery ?
Subquery is a query whose return values are used in filtering conditions of the main query.
6. What is correlated sub-query ?
Correlated sub_query is a sub_query which has reference to the main query.
7. Explain Connect by Prior ?
Retrives rows in hierarchical order.
e.g. select empno, ename from emp where.
8. Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)), INSTR returns the position of the mth occurrence of the string 2 in
string1. The search begins from nth position of string1. SUBSTR (String1 n,m) SUBSTR returns a character string of size m in string1, starting from nth postion of string1.
9. Explain UNION,MINUS,UNION ALL, INTERSECT ?
INTERSECT returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query. UNION ALL - returns all rows selected by either query,including all duplicates.
10. What is ROWID ?
ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.
11. What is the fastest way of accessing a row in a table ?
Using ROWID.

Read more »

Oracle: MANAGING BACKUP & RECOVERY

MANAGING BACKUP & RECOVERY
73. What are the different methods of backing up oracle database ?
- Logical Backups - Cold Backups - Hot Backups (Archive log)
74. What is a logical backup ?
Logical backup involves reading a set of databse records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
75. What is cold backup ? What are the elements of it ?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files. - All Control files. - All on-line redo log files. - The init.ora file (Optional)
76. What are the different kind of export backups ?
Full back - Complete database. Incremental - Only affected tables from last incremental date/full backup date. Cumulative backup - Only affected table from the last cumulative date/full backup date.
77. What is hot backup and how it can be taken ?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
78. What is the use of FILE option in EXP command ?
To give the export file name.
79. What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.
80. What is the use of GRANT option in EXP command ?
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.
81. What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.
82. What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the databse objects will be created.
83. What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.

84. What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.
85. What is the use of OWNER option in EXP command ?
List of table accounts should be exported.
86. What is the use of TABLES option in EXP command ?
List of tables should be exported.
87. What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.
88. What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL.
89. What is the use of RECORD option in EXP command ?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
90. What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
91. What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
92. What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
93. What is the use of CONSISTENT (Ver 7) option in EXP command ?
A flag to indicate whether a read consistent version of all the exported objects should be maintained.
94. What is use of LOG (Ver 7) option in EXP command ?
The name of the file which log of the export will be written.
95.What is the use of FILE option in IMP command ?
The name of the file from which import should be performed.
96. What is the use of SHOW option in IMP command ?
A flag to indicate whether file content should be displayed or not.
97. What is the use of IGNORE option in IMP command ?
A flag to indicate whether the import should ignore errors encounter when issuing CREATE commands.
98. What is the use of GRANT option in IMP command ?
A flag to indicate whether grants on database objects will be imported.
99. What is the use of INDEXES option in IMP command ?
A flag to indicate whether import should import index on tables or not.
100. What is the use of ROWS option in IMP command ?
A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be exectued.

Read more »

Oracle: MANAGING DISTRIBUTED DATABASES.

MANAGING DISTRIBUTED DATABASES.
62. How can we reduce the network traffic ?
Replictaion of data in distributed environment. Using snapshots to replicate data. Using remote procedure calls.
63. What is snapshots ?
Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.
64. What are the various type of snapshots ?
Simple and Complex.
65. Differentiate simple and complex, snapshots ?
A simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations. A complex snapshots contain atleast any one of the above.
66. What dynamic data replication ?
Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
67. How can you Enforce Refrencial Integrity in snapshots ?
Time the references to occur when master tables are not in use. Peform the reference the manually immdiately locking the master tables. We can join tables in snopshots by creating a complex snapshots that will based on the master tables.
68. What are the options available to refresh snapshots ?
COMPLETE - Tables are completly regenerated using the snapshot's query and the master tables every time the snapshot referenced. FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables. FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.
69. what is snapshot log ?
It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.
70. When will the data in the snapshot log be used ?
We must be able to create a after row trigger on table (i.e., it should be not be already available )
After giving table privileges. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects that support its snapshot log. The master table name should be less than or equal to 23 characters. (The table name created will be MLOGS_tablename, and trigger name will be TLOGS name).
72. What are the benefits of distributed options in databases ?
Database on other servers can be updated and those transactions can be grouped together with others in a logical unit. Database uses a two phase commit.

Read more »

Oracle: DATABASE SECURITY & ADMINISTRATION

DATABASE SECURITY & ADMINISTRATION
48. What is user Account in Oracle database ?
An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.
49. How will you enforce security using stored procedures ?
Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
50. What are the dictionary tables used to monitor a database spaces ?
DBA_FREE_SPACE, DBA_SEGMENTS, DBA_DATA_FILES.
51. What are the responsibilities of a Database Administrator ?
Installing and upgrading the Oracle Server and application tools. Allocating system storage and planning future storage requirements for the database system. Managing primary database structures (tablespaces). Managing primary objects (table,views,indexes). Enrolling users and maintaining system security. Ensuring compliance with Oralce license agreement. Controlling and monitoring user access to the database. Monitoring and optimising the performance of the database. Planning for backup and recovery of database information. Maintain archived data on tape. Backing up and restoring the database. Contacting Oracle Corporation for technical support.
52. What are the roles and user accounts created automatically with the database ?
DBA - role Contains all database system privileges. SYS user account - The DBA role will be assigned to this account. All of the basetables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE. SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.
54. What are the database administrators utilities avaliable ?
SQL * DBA - This allows DBA to monitor and control an ORACLE database. SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables. Export (EXP) and Import (imp) utilities allow you to move existing data in ORACLE format to and from ORACLE database.
55. What are the minimum parameters should exist in the parameter file (init.ora) ?
DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation. DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters (DB_NAME & DB_DOMAIN)
CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used. DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA. PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user. ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup. Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.
56. What is a trace file and how is it created ?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
57. What are roles ? How can we implement roles ?
Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provies to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.
58. What are the steps to switch a database's archiving mode between NO ARCHIVELOG and ARCHIVELOG mode ?
1. Shutdown the database instance. 2. Backup the databse. 3. Perform any operating system specific steps (optional). 4. Start up a new instance and mount but do not open the databse. 5. Switch the databse's archiving mode.
59. How can you enable automatic archiving ?
Shut the database. Backup the database. Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file. Start up the databse.
60. How can we specify the Archived log file name format and destination ?
By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left paded, %s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.
61. What is the use of ANALYZE command ?
To perform one of these function on an index,table, or cluster: - to collect statisties about object used by the optimizer and store them in the data dictionary. - to delete statistics about the object used by object from the data dictionary. - to validate the structure of the object. - to identify migrated and chained rows of the table or cluster.

Read more »