|
Explain: Integrity Constraints, Index, Extenta.) Integrity Constraints
It can be called as a declarative way in order to define a business rule for a table's column
b.) Index
- It can be called as an optional structure which is associated with a table for direct access to the rows
- Index can be created for one or more columns in a table
c.) Extent
- It can be defined as a specific number of contiguous data blocks in single allocation.
- It is used to store a specific type of information.
|
|
What is ANALYZE command used for?ANALYZE command is used to perform various functions on index, table, or cluster, as listed below:
- It helps in dentifying migrated and chained rows of the table or cluster.
- It helps in validating the structure of the object.
- It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
- It helps in deleting statistics used by object from the data dictionary.Video: Oracle Interview Questions and Answers for Freshers
|
|
a.) List the types of joins used in writing SUBQUERIES.- Self join
- Outer Join
- Equi-joinb.) List the various Oracle database objects.- TABLES
- VIEWS
- INDEXES
- SYNONYMS
- SEQUENCES
- TABLESPACES
|
|
a. Explain i.) Rename ii.) Alias.- Rename - It is a permanent name provided to a table or column.
- Alias - It is a temporary name provided to a table or column which gets over after the execution of SQL statement.b.) What is a view?- It is virtual table which is defined as a stored procedure based on one or more tables.
|
|
a.) What are the varoius components of physical database structure of Oracle database?Oracle database comprises of three kinds of files:
- Datafiles,
- Redo log files,
- Control files.b.) List out the components of logical database structure of Oracle database.- Tablespaces
- Database's schema objects.
|
|
a.) What do you mean by a tablespace?- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.b.) What is Control File used for?Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.
|
|
a.) What is a synonym? What are its various types?A synonym can be called as an alias for a table, view, sequence or program unit.
It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.b.) What are the uses of synonyms?- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.
|
|
What do you mean by a deadlock?- When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.
|
|
a.) What suggestions do you have to reduce the network traffic?Following are some of the actions which can be taken to reduce the network traffic:
- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.b.) What are the various types of snapshots ?There are two types of snapshots:
- Simple snapshots - Based on a query that does not contain GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
- Complex snapshots- which contains atleast any one of the above.
|
|
What is a sub query? What are its various types?- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.
There can be two types of subqueries:
a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .
b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query
|
|
Will you be able to store pictures in the database?Explain.- Yes, pictures can be stored in the database using Long Raw Data type.
- This datatype is used to store binary data for 2 gigabytes of length.
- However, the table can have only one Long Raw data type.
|
|
Explain: a.) Integrity constraint. b.) COALESCE functiona.) Integrity constraint.
- It is a declaration defined in a business rule for a table column.
- Integrity constraint ensures the accuracy and consistency of data in a database.
- It is of three types - namely - Domain Integrity, Referential Integrity and Domain Integrity.
b.) COALESCE function
- This function is used to return the value which is set not to be null in the list.
- Incase all values in the list are null the coalesce function will return NULL.
- Its representation:
Coalesce(value1, value2, value3,…)
|
|
Explain the following:a.) BLOB datatype.
- It is a data type with varying length binary string, used to store two gigabytes memory.
- For BLOB, the length needs to be specified in bytes.
b.) DML.
- DML - it is also termed as Data Manipulation Language (DML).
- It is used to access and manipulate data in the existing objects.
- DML statements are insert, select, update and delete.
|
|
Differentiate between:a.) TRANSLATE and REPLACE.
- Translate is used to substitute character by character.
- Replace is used to substitute a single character with a word.
b.) What is Merge Statement used for?
- Merge statement is used to select rows from one or more data source to updating and insert into a table or a view.
|
|
What are the various Oracle Database objects?Various database objects are as follows:
- Tables – This is a set of elements organized in vertical and horizontal fashion.
- Tablespaces – This is a logical storage unit in Oracle.
- Views – It is virtual table derived from one or more tables.
- Indexes – This is a performance tuning method to process the records.
- Synonyms – This is a name for tables.
- Sequences.
|
|
What is the purpose of Save Points in Oracle database?- Save Points are used to divide a transaction into smaller phases.
- It enables rolling back part of a transaction.
- Maximum 5 save points are allowed in Oracle Database.
- Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.
|
|
a.) What is an ALERT?- It a window which appears on the screen overlaying a portion of the current display.b.) Differentiate between post-database commit and post-form commit.- The post-database commit trigger is fired after oracle forms issue the commit to finalized transactions.
- The post-form commit is fired during the post and commit transactions process, after the database commit occurs.
|
|
Differentiate between pre-select and pre-query.- Once oracle forms construct the select statement to be issued Pre-select is fired during the execute query and count query processing. All this happens before the statement is actually issued.
- The pre-query trigger is fired just before oracle forms issue the select statement to the database.
|
|
What is hot backup and logical backup?Hot backup
- Backing up the archive log files when database is open is called Hot backup.
- To do this, the ARCHIVELOG mode is enabled.
- Following files are backed up - All data files, Archive log, redo log files and control files.
Logical backup
- Logical back ip is reading a set of database records and writing them into a file.
- An Export utility is required to take the backup while an Import utility is required to recover from the backup.
|
|
What do you mean by Redo Log file mirroring?- The process of having a copy of redo log files is called mirroring.
- It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group.
- In case a group fails, the database automatically switches over to the next group. It diminishes the performance.
|
|
Hot backup vs. cold backupA database backup while it is still up and running is a Hot backup and it must be in archive log mode.
A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode.
The benefit of a hot backup is that the database is still available for use while the backup is occurring.
A cold backup is easier to administer the backup and recovery process.
Cold backups does not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.
|
|
What are actual and formal parameters?Actual ParametersThe variables or expressions referenced in the parameter list of a subprogram call are actual parameters. Following procedure call lists two actual parameters named empno and amt: raise_sal(empno, amt); Formal ParametersThe variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. Following procedure declares two formal parameters named empid and amt: PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;
|
|
Can we protect our PL/SQL source code?Yes, we can protect. PL/SQL V2.2 implements a binary wrapper for PL/SQL programs to protect the source code.
The utility available in this version transforms the PL/SQL source code into portable binary object code and thus protect from exposing your proprietary algorithms and methods.
|
|
Explain an Exception and its types.Exception is the error handling part of PL/SQL block.
Exception is of two types: Predefined and user defined.
Some of Predefined exceptions are
- ZERO_DIVIDE
- NO_DATA_FOUND
- TOO_MANY_ROWS
- LOGON_DENIED
- CURSOR_ALREADY_OPEN
- INVALID_NUMBER
- NOT_LOGGED_ON
- STORAGE_ERROR
- PROGRAM-ERROR
- TIMEOUT_ON_RESOURCE
- VALUE_ERROR
- DUP_VAL_ON_INDEX
- INVALID_CURSOR.
|
|
When do you get a .PLL extension in oracle? Explain its importance.PLL extension is created when we save a library module. It contains both source code and platform specific complied executable code.What is the use of SYSTEM.EFFECTIVE.DATE variable in oracle?It represents the effective database date.Use of an integrity constraint is better to validate data. ExplainUse of an integrity constraint is better to validate data because it prevents invalid data entry at the basic level into the database tables.
|
|
Explain the function of optimizer in oracle.The optimizer determines the most efficient way to execute a SQL statement based on the kind of data in the table and the statements fired to fetch that data like indexes, full table scans, loops, joins etc. Optimizers are also used to avoid excessive I/O resources.
|
|
What is meant by recursive hints in oracle?Number of times a dictionary table is repeatedly called by various processes is known as recursive hint. It occurs because of the small size of data dictionary cache.What are the limitations of a CHECK Constraint?The limitation of CHECK is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub queries.
|
|
Explain the use of ROWS option in IMP command.It indicates whether or not the table rows should be imported.Explain the use of INDEXES option in IMP command.It determines whether indexes are imported.Explain the use of GRANT option in IMP command.GRANT specifies to import object grants.Explain the use of IGNORE option in IMP command.IGNORE defines how object creation errors should be handled.
|
|
Explain the use of SHOW option in IMP command.When the value of show=y, the DDL within the export file is displayed.What is the use of FILE option in IMP command?FILE param defines the name of the export file to import. Multiple files can be listed, separated by commas.Explain the use of LOG option in EXP command.LOG specifies the log file to write messages.
|
|
What is hot backup and how it can be taken?Hot backup is taking backup of archived log files when database is open. He ARCHIVELOG switch should be enabled for this to happen. Hot backup happens while the database is still being accessed by users and tables are being updated. It does not need the database to have a downtime.
It includes the following:
- Data files
- Archived log files
- Redo log files
- Control files
Steps to take a hot backup:
- Enable ARCHIVE Log
- Change tablespace mode to begin backup
- Now create backup of your control file in Human Readable format
- Copy all your datafiles, redo logs and control file from your database server to backup location
- Copy database software $ORACLE_HOME from server to backup location
- Copy Apps Middle tier all TOPs (APPL_TOP, COMMON_TOP, ORA_TOP)
|
|
What are the different kind of export backups? Following are the different kinds of export backups:
- Full/Complete backup: Backup of the whole database
- Incremental backup: Only backup the data that has been modified since last incremental backup
- Cumulative backup: Only affected tables from the last cumulative backup date
|
|
Difference between pre-select and pre-queryPre-select
This fires during the execute and count query processing after an oracle form builds the select statement to be executed, but before it’s execution
Pre-query
This fires before an oracle form issues the select statement. It executes before the pre-select trigger.
|
|
Difference between open_form and call_form in oracle.CALL_FORM: This runs a specified form while keeping the parent form active. This allows moving between the forms. Oracle forms run the new form with Run form preferences based on the parent form. When the called form exits, oracle forms resume the parent form along with its last state. Either of the forms can be hidden or displayed based on logic.
OPEN_FORM: This executes the current form and executes the new specified form as a modal form. Oracle release the memory for the parent form and the new form is executed using the same Run form preferences as the parent form.
|
|
What are the different types of Record groups in oracle? Explain each of themRecord group is an internal oracle forms data structure having a similar column-row structure and relationship as a database table.
They are logical groups and never displayed to the user as such. Various types of Record groups in oracle are:
- Query record group: This record group is based on an attached SELECT query. Columns in this record group derive their default names, data types, length etc from the tables referred I the SELECT query.
- Static record group: This record group is not associated with any query. Their structure and values are defined at design time and thus remain fixed at run time.
- Non query record group: This record group also does not have an associated query. However, its structure and values can be defined during run time programmatically.
|
|
What is a trace file and how is it created in oracle?Trace files are files used to store details of exceptions thrown by Oracle background processes i.e. dbwr, lgwr, pmon, smon etc. They are usually created for diagnostic dumps as well and help in debugging and solving exceptions in Oracle. To create a Trace file in oracle:Set sql_trace=true with alter session command. This will generate a trace file for all sql commands issued by your user session. This is known as a level-1 trace file. One can also create super detailed level-4 trace files with additional details if the need be. These files are stored in the form $ORACLE_SID_ora_xxx.trc in the trace directory, where xxx is sequential number. To create a lelevl-4 detailed trace file, we need to know the SID and SERIAL# for the session to trace. Eg: to trace for session for SID 5: Connect system as sysdba;
ORADEBUG SETOSPID 5;
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
|
|
Explain the use of online redo log files in oracle.Every Oracle database has a redo log, which records all changes that have been made in the data files. These files then enable us to replay the SQL Statements. Oracle writes all the changes to the redo log before making any changes in the data files. If something happens to any data file, a backed up data file is restored and the redo log helps in doing so. However, a database can only be recovered if it runs under Archive log mode.
|
|
Explain ENABLE NOVALIDATE constraint.When a constraint has ENABLE NOVALIDATE state, all subsequent statements are checked for conformity to this constraint. A Table with this constraint can contain invalid data, however, it does not allow any further invalid data to be added to that table. Enabling this constraint is much faster than enabling and validating any other constraint. Also, it does not need any DML locks during validation with this constraint in place. Eg:ALTER TABLE table1 ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (table2_id)
REFERENCES table2 (id)
ENABLE NOVALIDATE;
|
|
How to create LOV dynamically at runtime & attach to text field?Steps to create a dynamic LOV:1. Create a record group, eg: RG 2. Create RG Sql query as Select col1,col2,col3 from dual; Keep in mind to adjust data types accordingly 3. Create an LOV and attach this RG to it Use SET_ITEM_PROPERTY to attach text field, dynamic LOV, and value, eg: SET_ITEM_PROPERTY(item_id, LOV_NAME, value);
|
|
How can we force the database to use the user specified rollback segment?We can do so by using the following SQL statement SET TRANSACTION USE ROLLBACK SEGMENT User_Rollback_Segment_Name Explain the use of CONSISTENT option in EXP command.It specifies the read only statement for export to ensure data consistency. Explain the use of ANALYSE option in EXP command.It is a flag to indicate if the statistical information about the exported objects should be written to export dump file or not.
|
|
Explain the use of PARFILE option in EXP command.It specifies the file that contains the export parameters.Explain the use of PARFILE option in EXP command.It specifies the file that contains the export parameters. Explain the use of RECORD option in EXP command.It is the flag that indicates if a record will be stored in data dictionary tables recording the export.Explain the use of INCTYPE option in EXP command.It specifies the type of export to be performed. It can be either COMPLETE, CUMULATIVE, INCREMENTAL.
|
|
Explain the use of RECORD LENGTH option in EXP command.Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk.Explain the use of TABLES option in EXP command.Indicates that the type of export is table-mode and lists the tables to be exported.Explain the use of OWNER option in EXP command.It tells that only the owner’s objects will be exported.
|
|
Explain the use of FULL option in EXP command.It tells that the entire database is to be exported.Explain the use of CONSTRAINTS option in EXP command.It specifies whether table constraints should be exported with table data.Explain the use of ROWS option in EXP command.It is the condition to decide if the table rows should be exported or not.Explain the use of INDEXES option in EXP command.It determines whether index definitions are exported.
|
|
Explain the use of GRANT option in EXP command.It specifies the object grants to export.Explain the use of COMPRESS option in EXP command.When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be used.Explain the use of FILE option in EXP command.File parameter takes the name of the export file. Multiple files can be listed, separated by commas.
|
