1. What is Log Switch? - The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
2. What is On-line Redo Log? - The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.
3. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? - All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.
4. What are the steps involved in Database Startup? - Start an instance, Mount the Database and Open the Database.
5. What are the steps involved in Instance Recovery? - Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
6. Can Full Backup be performed when the database is open? - No.
7. What are the different modes of mounting a Database with the Parallel Server? - Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
8. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
9. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.
10. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.
11. What is Restricted Mode of Instance Startup? - An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
12. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
13. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
14. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.
15. Can a View based on another View? - Yes.
16. Can a Tablespace hold objects from different Schemes? - Yes.
17. Can objects of the same Schema reside in different tablespaces? - Yes.
18. What is the use of Control File? - When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
19. Do View contain Data? - Views do not contain or store data.
20. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
21. What are the type of Synonyms? - There are two types of Synonyms Private and Public
22. What is a Redo Log? - The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
23. What is an Index Segment? - Each Index has an Index segment that stores all of its data.
24. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
25. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.
26. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
27. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.
28. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
29. What is an Extent? - An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
30. What is a View? - A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
31. What is Table? - A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
32. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.
33. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
34. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.
35. What is schema? - A schema is collection of database objects of a User.
36. Describe Referential Integrity? - A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.
37. What is Hash Cluster? - A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.
38. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.
39. What is Database Link? - A database link is a named object that describes a “path” from one database to another.
40. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together
41. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.
42. What are the Characteristics of Data Files? - A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.
43. How to define Data Block size? - A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.
44. What does a Control file Contain? - A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.
45. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? - A Cluster with an index on the Cluster Key 48.When does a Transaction end? - When it is committed or Rollbacked.
46. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? - What are the factors that affect OPTIMIZER in choosing an Optimization approach? - Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
47. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? - The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
48. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
49. What is Execution Plan? - The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
50. What are the different approaches used by Optimizer in choosing an execution plan? - Rule-based and Cost-based.
51. What does ROLLBACK do? - ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
52. What is SAVE POINT? - For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.
53. What are the values that can be specified for OPTIMIZER MODE Parameter? - COST and RULE.
54. What is COST-based approach to optimization? - Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
55. What does COMMIT do? - COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
56. What is RULE-based approach to optimization? - Choosing an executing plan based on the access paths available and the ranks of these access paths.
57. What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command? - CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.
58. Define Transaction? - A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
59. What is Read-Only Transaction? - A Read-Only transaction ensures that the results of each query executed in the transaction are consistent with respect to the same point in time.
60. What is a deadlock? - Explain . Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically. These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.
61. What is a Schema? - The set of objects owned by user account is called the schema.
62. What is a cluster Key? - The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.
63. What is Parallel Server? - Multiple instances accessing the same database (Only In Multi-CPU environments)
64. What are the basic element of Base configuration of an oracle Database? - It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (table, indexes, views etc.,) The server that access the database consists of SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool) SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write) DBWR (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS
65. What is clusters? - Group of tables physically stored together because they share common columns and are often used together is called Cluster.
66. What is an Index? - How it is implemented in Oracle Database? - An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command (Ver 7.0)
67. What is a Database instance? - Explain A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
68. What is the use of ANALYZE command? - To perform one of these function on an index, table, or cluster: - To collect statistics 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 off the table or cluster.
69. What is default tablespace? - The Tablespace to contain schema objects created without specifying a tablespace name.
70. What are the system resources that can be controlled through Profile? - The number of concurrent sessions the user can establish the CPU processing time available to the user’s session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user’s session the amount of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user’s session the allowed amount of connect time for the user’s session.
71. What is Tablespace Quota? - The collective amount of disk space available to the objects in a schema on a particular tablespace.
72. What are the different Levels of Auditing? - Statement Auditing, Privilege Auditing and Object Auditing.
73. What is Statement Auditing? - Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects
74. What are the database administrators utilities available? - 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.
75. 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 database.
76. 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 provides to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.
77. What are Roles? - Roles are named groups of related privileges that are granted to users or other roles.
78. What are the use of Roles? - REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group. DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group’s role automatically reflect the changes made to the role. SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user’s privileges in any given situation. APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.
79. What is Privilege Auditing? - Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
80. What is Object Auditing? - Object auditing is the auditing of accesses to specific schema objects without regard to user.
81. What is Auditing? - Monitoring of user access to aid in the investigation of database use.
82. What are the responsibilities of a Database Administrator?
1. Installing and upgrading the Oracle Server and application tools.
2. Allocating system storage and planning future storage requirements for the database system.
3. Managing primary database structures (tablespaces)
4. Managing primary objects (table, views, indexes)
5. Enrolling users and maintaining system security.
6. Ensuring compliance with Oracle license agreement
7. Controlling and monitoring user access to the database.
8. Monitoring and optimizing the performance of the database.
9. Planning for backup and recovery of database information.
10. Maintain archived data on tape
11. Backing up and restoring the database.
12. Contacting Oracle Corporation for technical support.
83. 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.
84. What is a profile? - Each database user is assigned a Profile that specifies limitations on various system resources available to the user.
85. 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.
86. What are the dictionary tables used to monitor a database spaces? - DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES.
87. 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 base tables 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.
88. 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.
89. 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 padded, %s - Log sequence number not padded. %T - Thread number left-zero- padded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.
90. 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. 95. 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).
91. What dynamic data replication? - Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
92. What is Two-Phase Commit? - Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.
93. How can you Enforce Referential 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.
94. What is a SQL * NET? - SQL *NET is ORACLE’s mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.
95. What is a SNAPSHOT? - Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
96. What is the mechanism provided by ORACLE for table replication? - Snapshots and SNAPSHOT LOGs
97. 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.
98. What are the various type of snapshots? - Simple and Complex.
99. Describe two phases of Two-phase commit? - Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to commit or rollback the transaction, even if there is a failure) Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.
100. 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.
101. 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.
102. What are the options available to refresh snapshots? - COMPLETE - Tables are completely regenerated using the snapshots 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.
103. What is a SNAPSHOT LOG? - A snapshot log is a table in the master database that is associated with the master table. ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.
104. What is Distributed database? - A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
105. How can we reduce the network traffic? - Replication of data in distributed environment. - Using snapshots to replicate data. - Using remote procedure calls.
106. Differentiate simple and complex, snapshots? - A simple snapshot is based on a query thaat does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snapshot of operations. - A complex snapshots contain at least any one of the above.
107. What are the Built-ins used for sending Parameters to forms? - You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
108. Can you have more than one content canvas view attached with a window? - Yes. Each window you create must have at least one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.
109. Is the After report trigger fired if the report execution fails? - Yes.
110. Does a Before form trigger fire when the parameter form is suppressed? - Yes.
111. Is it possible to split the print reviewer into more than one region? - Yes
112. Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size? - Yes
113. For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame? - Yes
114. Can a field be used in a report without it appearing in any data group? - Yes
115. The join defined by the default data link is an outer join yes or no? - Yes
116. Can a formula column referred to columns in higher group? - Yes
117. Can a formula column be obtained through a select statement? - Yes
118. Is it possible to insert comments into sql statements return in the data model editor? - Yes
119. Is it possible to disable the parameter from while running the report? - Yes
120. When a form is invoked with call_form, Does oracle forms issues a save point? - Yes
121. Can a property clause itself be based on a property clause? - Yes
122. If a parameter is used in a query without being previously defined, what diff. exist between report 2.0 and 2.5 when the query is applied? - While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.
123. What are the SQL clauses supported in the link property sheet? - Where start with having.
124. What is trigger associated with the timer? - When-timer-expired.
125. What are the trigger associated with image items? - When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks on an image item
126. What are the different windows events activated at runtimes? - When_window_activated When_window_closed When_window_deactivated When_window_resized Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.
127. When do you use data parameter type? - When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data parameters are used to pass data to produts invoked with the run_product built-in subprogram.
128. What is difference between open_form and call_form? - when one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. when one form invokes another form by executing call_form, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.
129. What is new_form built-in? - When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.
130. What is the “LOV of Validation” Property of an item? - What is the use of it? - When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV. Whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.
131. What is the diff. when Flex mode is mode on and when it is off? - When flex mode is on, reports automatically resizes the parent when the child is resized.
132. What is the diff. when confine mode is on and when it is off? - When confine mode is on, an object cannot be moved outside its parent in the layout.
133. What are visual attributes? - Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.
134. Which of the two views should objects according to possession? - view by structure.
135. What are the two types of views available in the object navigator (specific to report 2.5)? - View by structure and view by type .
136. What are the vbx controls? - Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.
137. What is the use of transactional triggers? - Using transactional triggers we can control or modify the default functionality of the oracle forms.
138. How do you create a new session while open a new form? - Using open_form built-in setting the session option Ex. Open_form (’Stocks ‘,active,session). when invoke the mulitiple forms with open form and call_form in the same application, state whether the following are true/False
139. What are the ways to monitor the performance of the report? - Use reports profile executable statement. Use SQL trace facility.
140. If two groups are not linked in the data model editor, What is the hierarchy between them? - Two group that is above are the left most rank higher than the group that is to right or below it.
141. An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form? - True
142. Explain about horizontal, Vertical tool bar canvas views? - Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window
143. What is the purpose of the product order option in the column property sheet? - To specify the order of individual group evaluation in a cross products.
144. What is the use of image_zoom built-in? - To manipulate images in image items.
145. How do you reference a parameter indirectly? - To indirectly reference a parameter use the NAME IN, COPY ‘built-ins to indirectly set and reference the parameters value’ Example name_in (’capital parameter my param’), Copy (’SURESH’,'Parameter my_param’)
146. What is a timer? - Timer is an “internal time clock” that you can programmatically create to perform an action each time the times.
147. What are the two phases of block coordination? - There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record. These operations are accomplished through the execution of triggers.
148. What are Most Common types of Complex master-detail relationships? - There are three most common types of complex master-detail relationships: master with dependent details master with independent details detail with two masters
149. What is a text list? - The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select values that are not displayed.
150. What is term? - The term is terminal definition file that describes the terminal form which you are using r20run.
151. What is use of term? - The term file which key is correspond to which oracle report functions.
152. What is pop list? - The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.
153. What is the maximum no of chars the parameter can store? - The maximum no of chars the parameter can store is only valid for char parameters, which can be up to 64K. No parameters default to 23 Bytes and Date parameter default to 7 Bytes.
154. What are the default extensions of the files created by library module? - The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary
155. What are the Coordination Properties in a Master-Detail relationship? - The coordination properties are Deferred Auto-Query These Properties determine when the population phase of block coordination should occur.
156. How do you display console on a window? - The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.
157. What are the different Parameter types? - Text ParametersData Parameters
158. State any three mouse events system variables? - System.mouse_button_pressedSystem.mouse_button_shift
159. What are the types of calculated columns available? - Summary, Formula, Placeholder column.
160. Explain about stacked canvas views? - Stacked canvas view is displayed in a window on top of, or “stacked” on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.
161. What are the built_ins used the display the LOV? - Show_lov List_values
162. What is the difference between SHOW_EDITOR and EDIT_TEXTITEM? - Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.
163. What are the built-ins that are used to Attach an LOV programmatically to an item? - set_item_property get_item_property (by setting the LOV_NAME property)
164. How do you call other Oracle Products from Oracle Forms? - Run_product is a built-in, Used to invoke one of the supported oracle tools products and specifies the name of the document or module to be run. If the called product is unavailable at the time of the call, Oracle Forms returns a message to the operator.
165. What is the main diff. bet. Reports 2.0 & Reports 2.5? - Report 2.5 is object oriented.
166. What are the different file extensions that are created by oracle reports? - Rep file and Rdf file.
167. What is strip sources generate options? - Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file
168. What is the basic data structure that is required for creating an LOV? - Record Group.
169. What is the Maximum allowed length of Record group Column? - Record group column names cannot exceed 30 characters.
170. Which parameter can be used to set read level consistency across multiple queries? - Read only
171. What are the different types of Record Groups? - Query Record Groups NonQuery Record Groups State Record Groups
172. From which designation is it preferred to send the output to the printed? - Previewer
173. what are difference between post database commit and post-form commit? - Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.
174. What are the different display styles of list items? - Pop_list Text_list Combo box
175. Which of the above methods is the faster method? - performing the calculation in the query is faster.
176. With which function of summary item is the compute at options required? - percentage of total functions.
177. What are parameters? - Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type char, number, date that you define at design time.
178. What are the three types of user exits available? - Oracle Precompiler exits, Oracle call interface, NonOracle user exits.
179. How many windows in a form can have console? - Only one window in a form can display the console, and you cannot change the console assignment at runtime
180. If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated? - Only for 10 records.
181. What are the two repeating frame always associated with matrix object? - One down repeating frame below one across repeating frame.
182. What are the master-detail triggers? - On-Check_delete_master, On_clear_details, On_populate_details
183. What are the different objects that you cannot copy or reference in object groups? - Objects of different modules Another object groups Individual block dependent items Program units.
184. What is an OLE? - Object Linking & Embedding provides you with the capability to integrate objects from many Windows applications into a single compound document creating integrated applications enables you to use the features form.
185. Is it possible to modify an external query in a report which contains it? - No.
186. Does a grouping done for objects in the layout editor affect the grouping done in the data model editor? - No.
187. Can a repeating frame be created without a data group as a base? - No
188. If a break order is set on a column would it affect columns which are under the column? - No
189. Is it possible to set a filter condition in a cross product group in matrix reports? - No
190. Do user parameters appear in the data modal editor in 2.5? - No
191. Can you pass data parameters to forms? - No
192. Is it possible to link two groups inside a cross products after the cross products group has been created? - no
193. What are the different modals of windows? - Modeless windows Modal windows
194. What are modal windows? - Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.
195. What are the different default triggers created when Master Deletes Property is set to Non-isolated? - Master Deletes Property Resulting Triggers: Non-Isolated (the default) On-Check-Delete-Master On-Clear-Details On-Populate-Details
196. What are the different default triggers created when Master Deletes Property is set to isolated? - Master Deletes Property Resulting Triggers: Isolated On-Clear-Details On-Populate-Details
197. What are the different default triggers created when Master Deletes Property is set to Cascade? - Master Deletes Property Resulting Triggers: Cascading On-Clear-Details On-Populate-Details Pre-delete
198. What is the diff. bet. setting up of parameters in reports 2.0 reports2.5? - LOVs can be attached to parameters in the reports 2.5 parameter form.
199. What are the difference between lov & list item? - Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.
200. What is the advantage of the library? - Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.
201. What is lexical reference? - How can it be created? - Lexical reference is place_holder for text that can be embedded in a SQL statements. A lexical reference can be created using & before the column or parameter name.
202. What is system.coordination_operation? - It represents the coordination causing event that occur on the master block in master-detail relation.
203. What is synchronize? - It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal representation of the screen.
204. What use of command line parameter cmd file? - It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.
205. What is a Text_io Package? - It allows you to read and write information to a file in the file system.
206. What is forms_DDL? - Issues dynamic Sql statements at run time, including server side pl/SQl and DDL
207. How is link tool operation different bet. reports 2 & 2.5? - In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field is selected and the link tool is then used to link the first field to the second field.
208. What are the different styles of activation of ole Objects? - In place activation, External activation
209. How do you reference a Parameter? - In Pl/SQL, You can reference and set the values of form parameters using bind variables syntax. Ex. PARAMETER name = ‘’ or :block.item = PARAMETER Parameter name
210. What is the difference between object embedding & linking in Oracle forms? - In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.
211. Name of the functions used to get/set canvas properties? - Get_view_property, Set_view_property
212. What are the built-ins that are used for setting the LOV properties at runtime? - get_lov_property set_lov_property
213. What are the built-ins used for processing rows? - Get_group_row_count(function) Get_group_selection_count(function) Get_group_selection(function) Reset_group_selection(procedure) Set_group_selection(procedure) Unset_group_selection(procedure)
214. What are built-ins used for Processing rows? - GET_GROUP_ROW_COUNT(function) GET_GROUP_SELECTION_COUNT(function) GET_GROUP_SELECTION(function) RESET_GROUP_SELECTION(procedure) SET_GROUP_SELECTION(procedure) UNSET_GROUP_SELECTION(procedure)
215. What are the built-in used for getting cell values? - Get_group_char_cell(function) Get_groupcell(function) Get_group_number_cell(function)
216. What are the built-ins used for Getting cell values? - GET_GROUP_CHAR_CELL (function) GET_GROUPCELL(function) GET_GROUP_NUMBET_CELL(function)
217. A tleast how many set of data must a data model have before a data model can be base on it? - Four
218. To execute row from being displayed that still use column in the row which property can be used? - Format trigger.
219. What are different types of modules available in oracle form? - Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application
220. What is the remove on exit property? - For a modeless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.
221. What is WHEN-Database-record trigger? - Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. Generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.
222. What is a difference between pre-select and pre-query? - Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode. Pre-query trigger fires before pre-select trigger.
223. What are built-ins associated with timers? - find_timer create_timer delete_timer
224. What are the built-ins used for finding object ID functions? - Find_group(function) Find_column(function)
225. What are the built-ins used for finding Object ID function? - FIND_GROUP(function) FIND_COLUMN(function)
226. Any attempt to navigate programmatically to disabled form in a call_form stack is allowed? - False
227. Use the Add_group_row procedure to add a row to a static record group 1. true or false? - False
228. Use the add_group_column function to add a column to record group that was created at a design time? - False
229. What are the various sub events a mouse double click event involves? - What are the various sub events a mouse double click event involves? - Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.
230. How can a break order be created on a column in an existing group? - What are the various sub events a mouse double click event involves? - By dragging the column outside the group.
231. What is the use of place holder column? - What are the various sub events a mouse double click event involves? - A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to appear.
232. What is the use of hidden column? - What are the various sub events a mouse double click event involves? - A hidden column is used to when a column has to embed into boilerplate text.
233. What is the use of break group? - What are the various sub events a mouse double click event involves? - A break group is used to display one record for one group ones. While multiple related records in other group can be displayed.
234. What is an anchoring object & what is its use? - What are the various sub events a mouse double click event involves? - An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.
235. What are the various sub events a mouse double click event involves? - What are the various sub events a mouse double click event involves? - Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.
236. What are the default parameter that appear at run time in the parameter screen? - What are the various sub events a mouse double click event involves? - Destype and Desname.
237. What are the built-ins used for Creating and deleting groups? - CREATE-GROUP (function) CREATE_GROUP_FROM_QUERY(function) DELETE_GROUP(procedure)
238. What are different types of canvas views? - Content canvas views Stacked canvas views Horizontal toolbar vertical toolbar.
239. What are the different types of Delete details we can establish in Master-Details? - Cascade Isolate Non-isolate
240. What is relation between the window and canvas views? - Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.
241. What is a User_exit? - Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.
242. How is it possible to select generate a select set for the query in the query property sheet? - By using the tables/columns button and then specifying the table and the column names.
243. How can values be passed between precompiler exits & Oracle call interface? - By using the statement EXECIAFGET & EXECIAFPUT.
244. How can a square be drawn in the layout editor of the report writer? - By using the rectangle tool while pressing the (Constraint) key.
245. How can a text file be attached to a report while creating in the report writer? - By using the link file property in the layout boiler plate property sheet.
246. How can I message to passed to the user from reports? - By using SRW.MESSAGE function.
247. How is possible to restrict the user to a list of values while entering values for parameters? - By setting the Restrict To List property to true in the parameter property sheet.
248. How can a button be used in a report to give a drill down facility? - By setting the action associated with button to Execute pl/SQL option and using the SRW.Run_report function.
249. How can a cross product be created? - By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.
250. What is a physical page? What is a logical page? - A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.
251. What does the term panel refer to with regarda to pages? - A panel is the number of physical pages needed to print one logical page.
252. What is a master detail relationship? - A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.
253. What is a library? - A library is a collection of subprograms including user named procedures, functions and packages.
254. How can a group in a cross products be visually distinguished from a group that does not form a cross product? - A group that forms part of a cross product will have a thicker border.
255. What is the frame & repeating frame? - A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the number of records that are to displayed is not known before.
256. What is a combo box? - A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.
257. What are three panes that appear in the run time pl/SQL interpreter? - Source pane, interpreter pane, navigator pane.
258. What are the two panes that Appear in the design time pl/SQL interpreter? - Source pane, interpreter pane
259. What are the two ways by which data can be generated for a parameters list of values? - Using static values, writing select statement.
260. What are the various methods of performing a calculation in a report? - Perform the calculation in the SQL statements itself, use a calculated / summary column in the data model.
261. What are the default extensions of the files created by menu module? - .mmb, .mmx
262. What are the default extensions of the files created by forms modules? - .fmb - form module binary .fmx - form module executable
263. To display the page number for each page on a report, what would be the source & logical page number or physical page number?
264. It is possible to use raw devices as data files and what is the advantages over file system files? - Yes. The advantages over file system files. I/O will be improved because Oracle is bypassing the kernel when writing to disk. Disk Corruption will decrease.
265. What are disadvantages of having raw devices? - We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
266. What is the significance of having storage clause? - We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.,
267. What is the use of INCTYPE option in EXP command? - Type export should be performed COMPLETE, CUMULATIVE, INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into a notion Completes. e. will be written.
268. What is the use of FILE option in IMP command? - The name of the file from which import should be performed.
269. What is a Shared SQL pool? - The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
270. 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.
271. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?
1. SYSTEM - Data dictionary tables.
2. DATA - Standard operational tables.
3. DATA2- Static tables used for standard operations
4. INDEXES - Indexes for Standard operational tables.
5. INDEXES1 - Indexes of static tables used for standard operations.
6. TOOLS - Tools table.
7. TOOLS1 - Indexes for tools table.
8. RBS - Standard Operations Rollback Segments,
9. RBS1,RBS2 - Additional/Special Rollback segments.
10. TEMP - Temporary purpose tablespace
11. TEMP_USER - Temporary tablespace for users.
12. USERS - User tablespace.
272. How to implement the multiple control files for an existing database?
1. Shutdown the database
2. Copy one of the existing control file to new location
3. Edit Config ora file by adding new control filename
4. Restart the database.
273. What is advantage of having disk shadowing/ Mirroring? - Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switches over to a working disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
274. How will you force database to use particular rollback segment? - SET TRANSACTION USE ROLLBACK S
Operating system questions
Hardware, Unix/Linux, Windows interview questions
1. What are the basic functions of an operating system? - Operating system controls and coordinates the use of the hardware among the various applications programs for various uses. Operating system acts as resource allocator and manager. Since there are many possibly conflicting requests for resources the operating system must decide which requests are allocated resources to operating the computer system efficiently and fairly. Also operating system is control program which controls the user programs to prevent errors and improper use of the computer. It is especially concerned with the operation and control of I/O devices.
2. Why paging is used? - Paging is solution to external fragmentation problem which is to permit the logical address space of a process to be noncontiguous, thus allowing a process to be allocating physical memory wherever the latter is available.
3. While running DOS on a PC, which command would be used to duplicate the entire diskette? diskcopy
4. What resources are used when a thread created? How do they differ from those when a process is created? - When a thread is created the threads does not require any new resources to execute the thread shares the resources like memory of the process to which they belong to. The benefit of code sharing is that it allows an application to have several different threads of activity all within the same address space. Whereas if a new process creation is very heavyweight because it always requires new address space to be created and even if they share the memory then the inter process communication is expensive when compared to the communication between the threads.
5. What is virtual memory? - Virtual memory is hardware technique where the system appears to have more memory that it actually does. This is done by time-sharing, the physical memory and storage parts of the memory one disk when they are not actively being used.
6. What is Throughput, Turnaround time, waiting time and Response time? - Throughput – number of processes that complete their execution per time unit. Turnaround time – amount of time to execute a particular process. Waiting time – amount of time a process has been waiting in the ready queue. Response time – amount of time it takes from when a request was submitted until the first response is produced, not output (for time-sharing environment).
7. What is the state of the processor, when a process is waiting for some event to occur? - Waiting state
8. What is the important aspect of a real-time system or Mission Critical Systems? - A real time operating system has well defined fixed time constraints. Process must be done within the defined constraints or the system will fail. An example is the operating system for a flight control computer or an advanced jet airplane. Often used as a control device in a dedicated application such as controlling scientific experiments, medical imaging systems, industrial control systems, and some display systems. Real-Time systems may be either hard or soft real-time. Hard real-time: Secondary storage limited or absent, data stored in short term memory, or read-only memory (ROM), Conflicts with time-sharing systems, not supported by general-purpose operating systems. Soft real-time: Limited utility in industrial control of robotics, Useful in applications (multimedia, virtual reality) requiring advanced operating-system features.
9. What is the difference between Hard and Soft real-time systems? - A hard real-time system guarantees that critical tasks complete on time. This goal requires that all delays in the system be bounded from the retrieval of the stored data to the time that it takes the operating system to finish any request made of it. A soft real time system where a critical real-time task gets priority over other tasks and retains that priority until it completes. As in hard real time systems kernel delays need to be bounded
10. What is the cause of thrashing? How does the system detect thrashing? Once it detects thrashing, what can the system do to eliminate this problem? - Thrashing is caused by under allocation of the minimum number of pages required by a process, forcing it to continuously page fault. The system can detect thrashing by evaluating the level of CPU utilization as compared to the level of multiprogramming. It can be eliminated by reducing the level of multiprogramming.
11. What is multi tasking, multi programming, multi threading? - Multi programming: Multiprogramming is the technique of running several programs at a time using timesharing. It allows a computer to do several things at the same time. Multiprogramming creates logical parallelism. The concept of multiprogramming is that the operating system keeps several jobs in memory simultaneously. The operating system selects a job from the job pool and starts executing a job, when that job needs to wait for any i/o operations the CPU is switched to another job. So the main idea here is that the CPU is never idle. Multi tasking: Multitasking is the logical extension of multiprogramming .The concept of multitasking is quite similar to multiprogramming but difference is that the switching between jobs occurs so frequently that the users can interact with each program while it is running. This concept is also known as time-sharing systems. A time-shared operating system uses CPU scheduling and multiprogramming to provide each user with a small portion of time-shared system. Multi threading: An application typically is implemented as a separate process with several threads of control. In some situations a single application may be required to perform several similar tasks for example a web server accepts client requests for web pages, images, sound, and so forth. A busy web server may have several of clients concurrently accessing it. If the web server ran as a traditional single-threaded process, it would be able to service only one client at a time. The amount of time that a client might have to wait for its request to be serviced could be enormous. So it is efficient to have one process that contains multiple threads to serve the same purpose. This approach would multithread the web-server process, the server would create a separate thread that would listen for client requests when a request was made rather than creating another process it would create another thread to service the request. To get the advantages like responsiveness, Resource sharing economy and utilization of multiprocessor architectures multithreading concept can be used.
12. What is hard disk and what is its purpose? - Hard disk is the secondary storage device, which holds the data in bulk, and it holds the data on the magnetic medium of the disk.Hard disks have a hard platter that holds the magnetic medium, the magnetic medium can be easily erased and rewritten, and a typical desktop machine will have a hard disk with a capacity of between 10 and 40 gigabytes. Data is stored onto the disk in the form of files.
13. What is fragmentation? Different types of fragmentation? - Fragmentation occurs in a dynamic memory allocation system when many of the free blocks are too small to satisfy any request. External Fragmentation: External Fragmentation happens when a dynamic memory allocation algorithm allocates some memory and a small piece is left over that cannot be effectively used. If too much external fragmentation occurs, the amount of usable memory is drastically reduced. Total memory space exists to satisfy a request, but it is not contiguous. Internal Fragmentation: Internal fragmentation is the space wasted inside of allocated memory blocks because of restriction on the allowed sizes of allocated blocks. Allocated memory may be slightly larger than requested memory; this size difference is memory internal to a partition, but not being used
14. What is DRAM? In which form does it store data? - DRAM is not the best, but it’s cheap, does the job, and is available almost everywhere you look. DRAM data resides in a cell made of a capacitor and a transistor. The capacitor tends to lose data unless it’s recharged every couple of milliseconds, and this recharging tends to slow down the performance of DRAM compared to speedier RAM types.
15. What is Dispatcher? - Dispatcher module gives control of the CPU to the process selected by the short-term scheduler; this involves: Switching context, Switching to user mode, Jumping to the proper location in the user program to restart that program, dispatch latency – time it takes for the dispatcher to stop one process and start another running.
16. What is CPU Scheduler? - Selects from among the processes in memory that are ready to execute, and allocates the CPU to one of them. CPU scheduling decisions may take place when a process: 1.Switches from running to waiting state. 2.Switches from running to ready state. 3.Switches from waiting to ready. 4.Terminates. Scheduling under 1 and 4 is non-preemptive. All other scheduling is preemptive.
17. What is Context Switch? - Switching the CPU to another process requires saving the state of the old process and loading the saved state for the new process. This task is known as a context switch. Context-switch time is pure overhead, because the system does no useful work while switching. Its speed varies from machine to machine, depending on the memory speed, the number of registers which must be copied, the existed of special instructions(such as a single instruction to load or store all registers).
18. What is cache memory? - Cache memory is random access memory (RAM) that a computer microprocessor can access more quickly than it can access regular RAM. As the microprocessor processes data, it looks first in the cache memory and if it finds the data there (from a previous reading of data), it does not have to do the more time-consuming reading of data from larger memory.
19. What is a Safe State and what is its use in deadlock avoidance? - When a process requests an available resource, system must decide if immediate allocation leaves the system in a safe state. System is in safe state if there exists a safe sequence of all processes. Deadlock Avoidance: ensure that a system will never enter an unsafe state.
20. What is a Real-Time System? - A real time process is a process that must respond to the events within a certain time period. A real time operating system is an operating system that can run real time processes successfully
######################################################################################
1) Which of the following file is read to start the instance?
a. Controlfile b. Initialization Parameter file
c. Data files d. None
Ans: B.
Explanation: It will read init.ora parameter file for starting the instance.
2) Which file is read when database is mounted?Ans: Control file.
Explanation: Control file is read while we are mounting the database.
3) What actions will occur if we issue command startup at SQL prompt immediately then?
Ans: Instance is started, Database is mounted & then Database is opened.
4) What do dirty buffers comprises of?
Ans: Buffers modified but nit yet written to disk.
Explanation: Modified buffers in database buffer cache (SGA), which has not written to disk.
5) Which init.ora parameter is used to size database buffer cache?
Ans: db_cache_size or db_block_buffers.
6) What do library cache consists of?
Ans: Hold parsed versions of executed sql statements & Hold parsed versions of pl/sql program unit.
Explanation: Consists of both parsed versions of sql & pl/sql.
7) How can we size shared pool?
Ans: shared_pool_size.
Explanation: we have to specify shared_pool_size= in initialization parameter file.
8) What does program global area (PGA) contains?
Ans: Users program variables, Users session information, & User defined cursors.
Explanation: PGA contains program variables, session information, and cursors. Not a SQL statement.
9) What happens during process of checkpoint?
Ans: It’s an event of recording modified blocks in database buffer cache onto data files.
Explanation: When checkpoint occurs it will invoke the DBWR to write dirty blocks from database buffer cache to database files.
10) What does SMON does?
Ans: Crash recovery, Clean up temporary segments & Coalescing free space
Explanation: SMON will do crash recovery; cleaning of temporary segments and coalescing free space. But it does not take care of background process.
11) Which file is read to start the instance?
Ans: Initialization parameter.
Explanation: It will read init.ora parameter file for starting the instance.
12) Which file is read when database is mounted?
Ans: Control file.
Explanation: control file is read while we are mounting the database.
13) What happens when we issue command startup at SQL prompt immediately?
Ans: Instance is started, Database is mounted & Database is opened.
14) Does PGA is a part of System Global Area?
Ans: NO.
Explanation: PGA (Program global area) is not a part of SGA. It is a separate memory structure.
15) What do dirty buffers comprises of?
Ans: Buffers modified but not yet written to disk.
Explanation: Modified buffers in database buffer cache (SGA), which has not written to disk.
16) Which init.ora parameter is used to size database buffer cache?
Ans: db_cache_size.
Explanation: If we want to change the size of database buffer cache we have to specify db_cache_size or db_block_buffers.
17) What do the library cache consists of?
Ans: Hold parsed versions of executed sql statements & Hold parsed versions of pl/sql program unit.
Explanation: Consists of both parsed versions of sql & pl/sql.
18) How can we size shared pool?
Ans: shared_pool_size.
Explanation: we have to specify shared_pool_size= in initialization parameter file.
19) What does program global area (PGA) contains?
Ans: Users program variables, Users session information, & User defined cursors.
Explanation: PGA contains program variables, session information, and cursors. Not a SQL statement.
20) What happens during process of checkpoint?
Ans: It’s an event of recording modified blocks in database buffer cache onto data files.
Explanation: When checkpoint occurs it will invoke the DBWR to write dirty blocks from database buffer cache to database files.
21) What does SMON does?
Ans: Crash recovery, Clean up temporary segments & Coalescing free space
Explanation: SMON will do crash recovery; cleaning of temporary segments and coalescing free space. But it does not take care of background process.
22) The total number of Base tables that get created into sys account?
Ans: 1762.
Explanation: 1762 base tables will get created in sys account.
23) What is the status of your database when we run the create database file (ex. Cr8demo.sql)?
Ans: nomount.
Explanation: Database status should be in nomount state. Because for mounting a database it requires a control file.
24) Who are the users that created automatically the moment the database is created?
Ans: sys, Scott.
Explanation: sys and Scott users will get created when we create a database.
25) What is the table space that accommodates base tables?
Ans: system.
Explanation: It creates base tables in system table space.
26) What is the default table space for sys user?
Ans: system.
Explanation: Default table space for sys user is system.
27) Data Dictionary Views are static?
Ans: True.
Explanation: Data dictionary views are nothing but DBA_, ALL_, and USER_.
28) Is the database creation successful with this command?
Ans: True.
Explanation: It will use OMF for creating control file and data files.
29) What happens when ‘SHUTDOWN NORMAL’ is issued?
Ans: Database and redo buffers are written to disk, Background processes are terminated & the next startup does not require any instance recovery.
Explanation: When we issue a command SHUTDOWN NORMAL it will wait for connected users to disconnect but it doesn’t allow to any user to logged into the database.
30) Does the names & locations of data files is present in parameter file?
Ans: NO.
Explanation: It doesn’t maintain the location and names of data files that will be maintained by control file.
31) Can we create a tablespace with multiple datafiles at a single stroke?
Ans: Yes.
Explanation: We can create tablespace in single stroke with command SQL>Create tablespacedatafile ‘’ size 2m,’’ size 3m; ----- Like this we can specify multiple datafiles for 1 tablespace.
32) Can a datafile be associated with two different tablespaces?
Ans: No.
Explanation: One datafile can associated to one tablespace not more than one tablespace.
33) Suppose your database has max_datafiles limit of 80 and we want to add files above this limit which file we need to modify?
Ans: Controlfile.
Explanation: In controlfile we have to change MAXDATAFILES= and we have to recreate controlfile then only it will change the limit of datafiles for the database.
34) Which view tells us all the tablespaces in your database?
Ans: v$tablespace.
Explanation: V$TABLESPACE view give the tablespace details in a database.
35) Can we bring system tablespace offline when the database is up?
Ans: No.
Explanation: We cannot make system tablespace offline because it contains base tables.
36) What is the dafault initial extent size when the tablespace is dictionary managed?
Ans: 5 blocks.
Explanation: When we create a dictionary managed tablespace it will give the initial extent as 5*.
37) Which parameter should be added in init.ora file for creating tablespace with multiple blocksizes?
Ans: db_nk_cache_size=n.
Explanation: We have to add db_Nk_cache_size= where N is 2,4,8,16,32. We are specifying value for that particular block size, oracle allocates buffers in database buffer cache for that block size. Whenever we perform any transaction on that particular block size tablespace it will use that buffers.
38) What is the value for storage clause pctincrease when the tablespace extent management is local (uniform)?
Ans: 0%.
Explanation: PCTINCREASE for locally managed tablespace is 0%.
39) What is the command that combines all the smaller contiguous free extents in the tablespace into one larger extent?
Ans: coalesce.
Explanation: Coalesce is used to combine all the smaller contiguous free extents in the tablespace into one larger extent. Merge and sum are SQL commands related to table and add extents is not valid.
40) If the system datafile to be renamed, the database must be in which mode?
Ans: mount.
Explanation: For renaming a datafile belonging to system tablespace our database should be in mount state because system contains all the base tables when we open a database it will continuosly update the base tables evenif we are not performing transactions.
41) After creating a tablespace what is the default value for segment space management in 9i?
Ans: manual.
Explanation: Its manual in 9i and Oracle 10g it’s AUTO.
42) A tablespace was created with extent management as local. After that the tablespace extent management was changed from local to dictionary. What would be the next extent size?
Ans: 1m.
Explanation: Its 1m after the change.
43) If we create a tablespace with extent management dictionary and block size 8k with default storage initial 10k. After creating this tablespace whatvalue it will show for initial_extent in dba_tablespaces?
Ans: 40k.
Explanation: If extent management is dictionary then database requires initial extent size atleast (block_size*5), here its 8k*5=40k.
44) Can we create a table with your own parameters like ( initial 300k next 300k minextents) on tablespace whose extent management is local?
Ans: YES.
Explanation: Yes we can create.
45) A locally managed tablespace is made offline what is the status of bytes column in dba_data_files?
Ans: It shows the null value.
46) Can we resize a datafile where the related tablespace is in offline mode?
Ans: NO.
Explanation: We cannot do it.
47) DBA changed a datafile’s autoextend value to on, what is the default value for increment_by (column) located in dba_data_files?
Ans: 1m.
Explanation: When we changed datafile to Autoextend on then value of increment_by column in DBA_DATA_FILES will be 1m (by default) means after filling of datafile complete it will increase the size of datafile by 1m everytime.
48) Can we drop a object when the tablespace is in read only mode?
Ans: YES.
Explanation: Yes we can do it.
49) We are trying to create a table with your own storage parameters in a locally managed tablespace. Guess what happens?
Ans: It will create table with default storage parameters at tablespace level.
50) Extent deallocation for a segment is done when ___________?
Ans: dropped, truncate.
Explanation: When we dropped or truncate a object it will deallocate the extents for that segment.
51) What type of data is available in rollback segments?
a. previous image b. post updated image
c. meta data d. no data
52) One of these is not the purpose of rollback segments?
a. Undo previous command b. read consistency
c. Crash recovery d. backup support
53) What is the default status of rollback segment the moment it is created?
a. offline b. online
c. deferred d. pending
54) What is the storage parameter that is unique to rollback segments?
a. initial b. dictionary
c. optimal d. shrink
55) Suppose a rollback segment is occupied by a transaction and in the mean time the rollback segment is brought offline at that moment what is the status of tat rollback segment?
a. offline b. deferred
c. pending offline d. cannot be made offline
56) What does high water mark size (hwm size) in rollback segment state?
a. the max size rollback segment b. the optimal size of rollback
as grown ever segment
c. the min size rollback segment d. None
has ever been
57) Suppose the users tablespace is bought offline which has some open transaction later the user said commit, what is the status of the rollback segment at this stage?
a. deffered b. optimal
c. pending offline d. offline
58) To make rollback segments online the moment the database is started what is the file we need to modify?
a. controlfile b. logfile
c. init.ora d. orapwd file
59) Can a rollback segment hold multiple entries?
a. No b. Yes
60) Can we drop an undo tablespace which currently in use?
a. Yes b. No
61) Can we create permanent objects in default temporary tablespace of a DB?
a. YES b. NO
62) Can we make a tempfile read only?
a. YES b. NO
63) Which of the following view, by which we can find out the default temporary tablespace of a DB?
a. dba_temp_files b. v$tempfile
c. database_properties d. db_properties
e. None
64) what is the extent_management value for the temporary tablespace created in 10g?
a. Local b. Dictionary
c. System d. User
e. None
65) What is the minimum size for a temporary file to be created?
a. 1030k b. 1040k
c. 1041k d. 1031k
e. 1050k
66) What is the value for allocation_type column in dba_tablespaces view for temporary tablespace?
a. SYSTEM b. LOCAL
c. USER d. UNIFORM
67) Which of the following cmd is used to make the temporary TS as default temporary TS of DB?
a. SQL>alter database default b. SQL>alter database default
temporary tablespace tablespace temporary
; ;
c. SQL> alter database temporary d. SQL>alter system set default
tablespace; temporary tablespace
;
68) Which of the following conditions should meet to convert permanent TS into Temporary?
a. Extent Management Local Auto b. Extent Management Local
and TS must be empty Uniform & TS must be empty
c. Extent Management Dictionary & d. None
TS must be empty
69) What is the command to convert a permanent TS into temporary?
a. SQL>alter database tablespace b. alter tablespace
temporary; temporary;
c. SQL>alter tablespace permanent d. SQL>alter database permanent
temporary: temporary;
70) Can we create temporary tablespace with “SEGMENT SPACE MANAGEMENT AUTO” ?
a. YES b. NO
71) Create user without mentioning default tablespace clause. Then by default which tablespace allocate for that user?
a. system b. user_data
c. temp d. SYSAUX
e. Default TS of DB
72) One user assigned select on
<><><><><>to another user with grant option after that this second user assigned same privilege to third user. After that first user revoked this privilege from second user. Then third user can e use that already assigned privilege?
a. NO b. YES
73) If DBA created one role with some privileges and assigned this role this users. After that he want revoke on privilege from that users how?
a. revoke fromb. revoke from < username,username,……>
c. revoke fromd. we can’t
74) DBA created one profile and assigned to users for applicable for that which parameter we need set in init.ora?
a. timed_statistics=true b.resource_limits=true
c. resource_limit=true d. none
75) Which privilege is necessary for a normal user to change his password?
a. create any table b. create session
c. alter user d. alter any user
76) How to manually lock user account?
a. user account lock b. alter useraccount lock
c. alter user identified d. none
by
77) From which view user can see his privilege?
a. user_role_privs b. dba_sys_privs
c. session_privs d. role_role_privs
78) One user as quota on two tablespaces. Can he create his tables other than default tablespace?
a. NO b. YES
79) System user granted DBA to normal user. Now can this user revoke DBA from system?
a. YES b. NO
80) For creating password file for sys in which directory we are created?
a. $HOME b. $ORACLE_HOME/rdbms/admin
c. $ORACLE_HOME/dbs d. $ORACLE_HOME/sqlplus/admin
81) What is the default location of listener.ora file?
a. $ORACLE_HOME/rdbms/admin b. $ORACLE_OME/dbs
c. $ORACLE_HOME/network/admin/ d. $ORACLE_HOME/network/tools/
samples samples
82) The listener service is stopped after giving connection to a client. What is the status of client?
a. connection will lost b. connection will be continued by
giving an error message
c. the client session hangs d. connection will be continued
without any messages
83) What is the command to start the listener for particular parameters set?
a. lsnrctl reload b. lsnrctl start
c. tnspingstart d. lsnrctl startall
84) Can we start the listener service for a database which is not yet started/opened?
a. YES b. NO (minimum the DB must be in
mount state)
b. NO (First the DB should be opened) d. None of the above
85) In which file do we set this parameter FAILOVER=ON to use failover server option of oracle?
a. init.ora file b. tnsnames.ora
c. listener.ora d. bot listener.ora & tnsnames.ora
c. controlfile
86) Can we start multiple database services with in one listener service?
a. NO b. YES
c. YES & its only in Oracle 9i
87) Can I have multiple listeners for a single database?
a. YES b. NO
c. YES & its only in Oracle 9i
88) What is the view do we query to find out the users who are connected using oracle networking?
a. DBA_USERS b. DBA_NET_INFO
c. V$SESSION d. DBA_CLIENT_INFO
89) After some modifications to listener file, How can I refresh the already running listener service without stopping it?
a. lsnrctl start b. lsnrctl reload
c. lsnrctl status d. lsnrctl restart
90) Which operations we can perform using network connections?
a. DML b. DDL
c. A & B d. Only DML’s
91) Which background process is needed to create materialized view?
a. ckpt & cjq0 b. lgrw & reco
c. dbw0 & reco d. reco & cjq0
92) Which parameter do we use to start to reco process?
a. job_queue_process b. reco_processes
c. distributed_transactions d. global_names
93) Is it mandatory to put the parameter global_names=true for creating database links?
a. YES b. NO
94) For creating database links is it necessary to put some value for distributed_transactions parameter?
a. No (Not required at client) b. Yes (Needed only at client)
c. Yes (Needed at both client & server) d. Yes (Needed only at server)
95) Which background process refresh the materialized view on a given refresh interval?
a. cjq0 b. reco
c. arc0 d. ckpt
96) Can we do any DML operations on materialized view?
a. Yes ( Only its not possible with b. Yes (Only with refresh fast option)
Refresh fast option)
c. No d. Yes
97) How many refresh options do we have for creating materialized view?
a. Yes b. No
98) What is the segment type for a materialized view?
a. view b. table
c. materialized view d. synonym
99) Can we manually refresh any materialized view?
a. Yes b. No
100) What is the syntax to drop materialized view?
a. SQL>DROP VIEW; b. SQL>DROP TABLE
cascade;
c.SQL>DROP MATERIALIZED VIEW d.SQL>DROPcascade;
;
ANSWERS TO THE QUESTIONS FROM Q51 – Q100
51) Ans: A (Previous image).
Explanation: The main purposes of rollback segments are to maintain before/previous image of data.
52) Ans: D.
Explanation: Backup support is not a function of rollback segment
53) Ans: A (offline).
Explanation: After creating rollback segment the default status will be offline.
54) Ans: C (optimal).
Explanation: For a rollback segment unique storage parameter is optimal for shrinking.
55) Ans: C (Pending offline).
Explanation: When rollback supporting one transaction and in mean time if you are making that rollback segment offline then the status of rollback segment will be pending offline because one active transaction was going on that rollback segment.
56) Ans: A (The max size rollback segment has grown ever).
Explanation: High water mark size indicates the max size of rollback segment has grown ever in his life time.
57) Ans: A (deferred).
Explanation: The status of rollback segment will be deferred.
58) Ans: C (init.ora).
Explanation: We have to modify init.ora parameter file to online any rollback segment when DB is started.
59) Ans: B (YES).
Explanation: YES rollback segments can hold multiple entries. It work on First in First serve basis.
60) Ans: B (NO).
Explanation: Oracle donot allow to drop undo tablespace which in use, because other than sys user system rollback segment cannot use any other users.
61) Ans: B.
Explanation: We cannot create an permanent object in any temporary tablespace.
62) Ans: B.
Explanation: DBA cannot make any tempfile read only
63) Ans: C.
Explanation: By database_properties we can find which TS is default temporary TS for a database.
64) Ans: A.
Explanation: It’s a Local, because extent information of dictionary managed tablespace will be stored in data dictionary and locally managed tablespace extent information will be stored in locally in same tablespace so it will reduce the burden on dictionary. If temporary tablespace is dictionary managed then its burden is on data dictionary.
65) Ans: C.
Explanation: We can create temporary file wit minimum 1041k.
66) Ans: D.
Explanation: Oracle will uniformly allocate extents for temporary tablespace.
67) Ans: A.
Explanation: SQL>alter database default emporary tablespace; to make a default temporary tablespace for database.
68) Ans: C.
Explanation: That tablespace should be dictionary managed and must be empty.
69) Ans: B.
Explanation: Alter tablespace temporary; it is possible only for dictionary managed TS and must be empty.
70) Ans: B.
Explanation: No we cannot create temporary tablespace with segment space management auto.
71) Ans: E.
Explanation: From 10g onwards which is the default tablespace for database that will be assign to the user. In 9i it is system tablespace.
72) Ans: A.
Explanation: No the user cannot
73) Ans: C.
Explanation: We have to revoke the privilege from the role. We cannot revoke directly from the user.
74) Ans: C.
Explanation: Resourse_limit=true we have set init.ora file so profile will effect on user.
75) Ans: B.
Explanation: User require create session privilege to change his own password because he is own that whole schema.
76) Ans: B.
Explanation: Alter useraccount lock;
77) Ans: C.
Explanation: session_privs will show the privileges for that user.
78) Ans: B.
Explanation: Yes if user is having quota on different tablespaces he can create is own objects on that tablespaces.
79) Ans: A.
Explanation: User can revoke a dba privilege from sysdba. Because Oracle database requires at any point of time one DBA only.
80) Ans: C.
Explanation: We have to create your password file in ORACLE_HOME/dbs directory only then only it will read that password file.
81) Ans: C.
Explanation: Bydeafult listener.ora file will be available in $ORACLE_HOME/network/admin/samples.
82) Ans: D.
Explanation: Connection will be continued without any messages because listener as already authenticated that user for database.
83) Ans: B.
Explanation: LSNRCTL START to start the listener. lsnrctl reload will be used when we add more services in listener.ora. lsnrctl start all doesnot work because all is not a listenername, every listener is having different names, tnsping utility is not for listener.
84) Ans: A.
Explanation: Listener is independent from database that’s why we can start/stop the listener without database also.
85) Ans: B.
Explanation: In TNSNAMES.ORA file we have to specify FAILOVER=ON to use failover server option of Oracle networking.
86) Ans: B.
Explanation: Yes we can start n number of services with one listener.
87) Ans: A.
Explanation: Yes we can configure n number of listener for one database.
88) Ans: C.
Explanation: We can query V$SESSION to find out all the information of users who are logged in to your database. We can find out from where user logged in, at what time he logged in, etc.
89) Ans: B.
Explanation: We can use reload option to refresh already running listener.
90) Ans: C.
Explanation: We can use DML and DDL operations using network connection because using oracle networking directly your login to user SCHEMA.
91) Ans: D.
Explanation: cjq0 process is required to refresh materialized views and reco is required for maintaining distributed transactions between database.
92) Ans: C.
Explanation: DISTRIBUTED_TRANSACTIONS parameter is responsible for distributed transactions. From 9i onwards this reco is mandatory backgroud process so Oracle deprecated this parameter.
93) Ans: B.
Explanation: It is not mandatory to put parameter global_names=true for creating database link because this parameter we have to set when we are creating global database links.
94) Ans: B.
Explanation: Its required only at client side.
95) Ans: A.
Explanation: CJQ0 background process will refresh the materialized view after every refresh interval.
96) Ans: C.
Explanation: No materialized view is only read only we cannot perform any DML operations on materialized views.
97) Ans: B.
Explanation: We have only three refresh options for creating materialized view (COMPLETE,FAST,FORCE).
98) Ans: A.
Explanation: Yes we can refresh materialized view manually using DBMS_MVIEW package.
99) Ans: B.
Explanation: When we create a materialized view that a local table in database so segment type of materialized view is TABLE.
100) Ans: C.
Explanation: SQL> DROP MATERIALIZED VIEW; other options are not valid for materialized views.
**************************************************************************************
Question : What are the components of physical database structure of Oracle database
totally there are 6 files(components) of physical database structure.3 mandatory & 3 optional. Three mandatory files are : 1> datafile : store actual data 2> control file : strores stuctural & status information of database. 3> redolog file : stores changed/committed data Three optional files are : 4> parameter file : strores all size related parameters note : this file is mandatory for a1st time u create a database, then it is optional. 5> archivelog file : its offline copy of redolog files 6> password file : used to make normal user to behave as a super user.
Question : What are the components of logical database structure of Oracle database
Tablespace, segments, extens, data Blocks.
A logical unit of storage of databse is called Tablespace.
Segments is a space alloocated for a specific logical storage structure within a tablespce.
Extents: Space allocated to a segments.
Datablocks: Oracle server manage the storage space in the datafiles in units is called data blocks or oracle blocks.
Question : What is a tablespace
An Oracle database consists of one or more logical storage units called tablespaces, which collectivley store all of the database's data.
Each tablespace in an ORACLE database consists of one or more files called datafiles, which are physical structures that conform with the operating system in which Oracle is running.
For Example::: A simple Oracle Database have one tablespace and one datafile
Question : What is SYSTEM tablespace and when is it created
System tablespace is the memory allocated by oracle for creation of objects,views,indexes.
this is created automatically by oracle when the Database is created
SYSTEM TABLESPACE USAGE NOTES:
Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects
Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.
select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME
Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:
Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME
Question : Explain the relationship among database, tablespace and data file. What is schema
Databases, tablespaces and datafiles are closely related, but they have important differences:
--- A Oracle Database consists of one or more tablespaces
--- Each Table space in an Oracle database consists of one or more files called datafiles.
--- A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
When a database user is created, a corresponding schema with the same name is created for that user. A schema is a named collection of objects that include Tables, Triggers, constraints, Indexes, Views etc. A user can only be associated with one schema, and that is the same name as the user's. Username and schema are often used interchangeably.
Question : What are Schema Objects
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users. Objects may define areas of the database to hold data, such as tables or indexes, or may consist just of a definition, such as a views or synonyms.
There is no relationship between a tablespace and a schema. Objects in the same schema can use storage in different tablespaces, and a tablespace can contain data from different schemas.
Schema objects can be created and manipulated using SQL. As an administrator, you can create and manipulate schema objects, just as you do with the logical and physical structures of your database using Oracle Enterprise Manager. The underlying SQL is generated for you by Oracle Enterprise Manager.
Question : Can objects of the same schema reside in different tablespaces
Yes , it can .
for example if you specify a different tablepace (B) for indexes, the indexes of the tables that the user create would be residing in B , and the table would reside in the user's default tablespace A.
Question : What is an Oracle view
A view is a logical table which makes a complex query easy.We can even create a complex view by joining two tables.
Question : What are the advantages of views
Views provide many advantages, like:
(a) They restrict access to the whole data, because they display only selective columns.
(b) They can be used to make complex queries easy. A user can use a simple query on a view to display data from multiple tables, without having the knowledge of how to join tables in queries.
(c) Different views can be created from the same data as per the requirements of different types of use groups.
Security
l Query Simplification
l Allows Different Perspective
l Schema Transparency / Location Transparency
l Schema Consistency
l Allows work-around for SQL limitations
Security
• to provide an additional level of table security by restricting access to a
predetermined set of rows and/or columns of a table
CREATE VIEW emp_sal_hist_v
AS
SELECT ratehist.employee, ratehist.beg_date, ratehist.pay_rate
FROM ratehist, employee
WHERE ratehist.company = employee.company
AND ratehist.employee = employee.employee
AND USER = employee.user_id;
Query Simplification
For example, a single view might be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables. Saving of complex queries also permits simplified commands for an end-user who does not know how to make joins and/or cryptic business rules governing a join.
Allows Different Perspective
For example, the columns of a view can be renamed without affecting the tables on which the view is based.
Columns cannot be dropped from tables in version 7.x, but you could recreate views without the unnecessary column.
Schema Transparency / Location Transparency
• Ability to hide the schema of data from the application, and therefore the user.
• For example, if a view's defining query references three columns of a four column table and a fifth column is added to the table, the view's definition is not affected and all applications using the view are not affected.
• Views can also be used to join tables across database schemas OR across databases (using remote links), thereby encapsulating schema names from the end user.
Schema Consistency
• If a web application is accessing legacy data and then we migrate over to a new system.
• Identify legacy tables accessed through the web.
• Create a view look-alike for each legacy table and have it return the samedata.
• Though not a long-term solution, will allow intermediate means of allowing the web application to run while the interface is rebuilt to the new system.
this is all about advantages of view.
Question : What is an Oracle sequence
A sequence is a database object created by a user that can be used to generate unique integers. A typical usage of sequences is to generate primary key values which are unique for each row.
It is generated and incremented (or decremented) by an internal Oracle routine. It can be used by multiple users and for multiple tables too. A sequence can be used instead of writing an application code for sequence-generating routine.
Question : What is a private synonym and public synonym
To create a private synonym in your own schema, you must have CREATE SYNONYM system privilege.
To create a private synonym in another user's schema, you must have CREATE ANY SYNONYM system privilege.
To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Such privileges must be granted to a user before the user can use the synonym. ‘
Question : What are synonyms used for
Synonyms are used to : 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.
Question : What is an Oracle index
An Index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation.
The Logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored
An index is a schema object that can speed up the retrieval of rows by using pointers. If you do not have an index, then a full table scan occurs. Its purpose is to reduce disk I/O by using an indexed path to locate data quickly. If a table is dropped, the corresponding indexes are also dropped
Question : How are the index updates
Indexes can be updated by either Dropping and recreating them, or Rebuilding them online
Question : What are clusters
A Cluster is made up of a table, or group of tables that share the same data blocks, which are grouped togather because they share common columns and are often used together
Question : What is cluster key
A cluster index is needed in support of a cluster. One is used to allow the other to exist. Much like we need an index in support of a primary key. They are not the same things.
Clusters are useful in the database to store related pieces of information from more then 1 table in the same physical database block. It in effect stores data "prejoined". I can use this technique to store all of the data from the DEPT
and EMP table for a given DEPTNO on the same block so that all employees of department 10 as well as the department 10 master record are all physically stored on the same exact block. When I go to "join" this data -- it is already
done for me -- in a very few IOs I can get all of the data I need. Using conventional tables, this data could be scattered onto many dozens of blocks.
read the oracle documentation, it gives examples of creating a cluster, the cluster key index, and the tables in the cluster.
Also, with a hash cluster there is no index by definition so no syntax to inspect. We hash the cluster key to determine where the data goes -- we do not index it. the data is the index.
Clusters are useful when you want data with the same cluster key values to be
physically stored near eachother.
Question : What is database link
A database link is a pointer in the local database that allows you to access on a remote database.
Question : What are the types of database links
Oracle allows you to create private, public, and global database links.
Private Database Link: You can create a private database link in a specific schema of a database. Only the owner of a private database link or PL/SQL subprograms in the schema can use a private database link to access data and database objects in the corresponding remote database.
Public Database Link : You can create a public database link for a database. All users and PL/SQL subprograms in the database can use a public database link to access data and database objects in the corresponding remote database.
Global Database Link - When an Oracle network uses Oracle Names, the names servers in the system automatically create and manage global database links for every Oracle database in the network. All users and PL/SQL subprograms in any database can use a global database link to access data and database objects in the corresponding remote database.
A private database link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the private link to access the specified remote database.
When many users require an access path to a remote Oracle database, an administrator can create a single public database link for all users in a database.
When an Oracle network uses Oracle Names, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.
Question : What is data block
Block is the smallest unit of storage in the
logical structure of the database where
actual table rows are stored.
Question : What is row chaining
if any of he empty row is not sufficient to hold the row. then row is placed in multiple blocks. it happenes when the block size is small and rows are of large size. then it cause chaining. Due to chaining performance degrades and will cause more IOs
Question : What is a data segment
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment.
Oracle databases use four types of segments, which are described in the following sections:
A single data segment in an Oracle database holds all of the data for one of the following:
A table that is not partitioned or clustered
A partition of a partitioned table
A cluster of tables
Question : What is an index segment
Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage
Question : What is rollback segment
ROLLBACK INFORMATION NOTES:
Segment Name - Name of the rollback segment.
Owner - Owner of the rollback segment.
Tablespace - Name of the tablespace containing the rollback segment.
Segment ID - ID number of the rollback segment.
File ID - ID number of the block containing the segment header.
Block ID - Starting block number of the extent.
Initial Extent - Initial extent size in bytes.
Next Extent - Secondary extent size in bytes.
Min Extents - Minimum number of extents.
Max Extents - Maximum number of extents.
PCT Increase - Percent increase for extent size.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .
select SEGMENT_NAME,
OWNER,
TABLESPACE_NAME,
SEGMENT_ID,
FILE_ID,
BLOCK_ID,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
INSTANCE_NUM
from dba_rollback_segs
order by SEGMENT_NAME
Question : What is the use of control file
Control file is binary file which is having all the information realted to database. db_name, maxlogfiles, maxdatafiles, tablespaces information. Without this u cannot open your database. Init.ora parameter file showing the location of the controlfile.
Question : What is a database instance and Explain
A instance is basically a set of memory and some background processes
Question : What is a deadlock and Explain
Answer : A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multitable deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock
What is a Database instance ? Explain
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
What is Parallel Server ?
Multiple instances accessing the same database (Only In Multi-CPU environments)
What is clusters ?
Group of tables physically stored together because they share common columns and are often used together is called Cluster
Question : How do you rename a database?
To change the name of the database
-- For this script to run properly do the following:
-- Backup the Control fiel to Trace
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
-- Shutdown the database to make the changes
SHUTDOWN IMMEDIATE;
-- Edit the trace file and change the CREATE CONTROLFILE command
-- CREATE CONTROLFILE REUSE SET DATABASE "NEW_SID_NAME" RESETLOGS
-- (note the SET keyword)
Change the name in Control file and Init.ora
The first line of Control file should be "CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS ARCHIVELOG"
- modify the db_name parameter in the init.ora
-- Startup the datbase nomount with changed PFile
STARTUP NOMOUNT;
-- Execute the create controlfile command.
@create_control.sql;
-- Cancel base recovery of the database
Recover database USING BACKUP CONTROLFILE until cancel;
CANCEL
-- Open resetlogs the database
ALTER DATABASE OPEN RESETLOGS;
-- Rename GLOBAL_NAME to
ALTER DATABASE RENAME GLOBAL_NAME TO ;
-- Create SPFile, IF required give NAME and PATH of the PFILE
CREATE SPFILE FROM PFILE;
select name from v$database;
Question : How do you pin an object
Use dbms_shared_pool procedure.
EXECUTE DBMS_SHARED_POOL.KEEP(OBJECTNAME);
Question : What are memory structures in Oracle?
The basic memory structures associated with Oracle include:
• System Global Area (SGA), which is shared by all server and background
processes and holds the following:
o Database buffer cache
o Redo log buffer
o Shared pool
o Large pool (if configured)
• Program Global Areas (PGA), which is private to each server and background
process; there is one PGA for each process. The PGA holds the following:
o Stack areas
o Data areas
Question : What does database do during mounting process?
oracle process are created 1 LGWR 2 DBWR 3 CKPT 4 LGWR 5 SMON 6 PMON etc.. control file is readed
Question : What is the correct sequence among FETCH, EXECUTE, And PARSE
the correct sequence is PARSE, EXECUTE and then FETCH.
Question : Explain Oracle memory structure.
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer’s RAM is called memory structure. Oracle has two memory structures in the computer’s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single, user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database
Question : How can you check which user has which Role.
desc dba_tab_privs
Question : How to DROP an Oracle Database?
Answer : You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:
1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc
Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is also removed.
Question : What is the view name where i can get the space in MB for tables or views?
Answer : select segment_name,sum(bytes) from dba_segments where segment_name='TABLE_NAME' AND OWNER='OWNER of the table' group by segment_name
Question : What is difference between Logical Standby Database and Physical Standby database?
Answer : The primary functional difference between logical and physical standby database setups is that logical standby permits you to add additional objects (tables, indexes, etc) to the database, while physical standby is always an exact structural duplicate of the master database. The downside, though, is that logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be generally regarded as more tempramental than physical standby.
Question : What is a latch?
Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.
A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies
while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).
Question : Latches vs Enqueues
Enqueues are another type of locking mechanism used in Oracle. An enqueue is a more sophisticated mechanism which permits several concurrent processes to have varying degree of sharing of "known" resources. Any object which can be concurrently used, can be protected with enqueues. A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes can lock a table in share mode or in share update mode
etc.
One difference is that the enqueue is obtained using an OS specific locking mechanism. An enqueue allows the user to store a value in the lock, i.e the mode in which we are requesting it. The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested
with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO.
Another difference between latches and enqueues is that in latches there is no ordered queue of waiters like in enqueues.
Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.
Question : How Materialized Views Work with Object Types and Collections
Answer : This is extract from Oracle10g Advanced Replication Book.
How Materialized Views Work with Object Types and Collections
Oracle object types are user-defined datatypes that make it possible to model complex real-world entities such as customers and orders as single entities, called objects, in the database. You create object types using the CREATE TYPE ... AS OBJECT statement. You can replicate object types and objects between master sites and materialized view sites in a replication environment.
An Oracle object that occupies a single column in a table is called a column object. Typically, tables that contain column objects also contain other columns, which may be built-in datatypes, such as VARCHAR2 and NUMBER. An object table is a special kind of table in which each row represents an object. Each row in an object table is a row object.
You can also replicate collections. Collections are user-defined datatypes that are based on VARRAY and nested table datatypes. You create varrays with the CREATE TYPE ... AS VARRAY statement, and you create nested tables with the CREATE TYPE ... AS TABLE statement.
Question : How do you find wheather the instance was started with pfile or spfile
Answer : Thhere are 3 different ways :-
1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will check in $oracle_home/dbs directory and on windows it will check in oracle_home/database directory, and the content of pfile is just text based, but spfile content is in binary format, that is understandable by oracle very well.
Also oracle server always check the spfile or pfile with these sequence :-
SPFILE.ORA
SPFILE.ORA
PFILE.ORA
PFILE.ORA
Question : What are the Advantages of Using DBCA
These are a few of the advantages of using DBCA:You can use its wizards to guide you through a selection of options providing an easy means of creating and tailoring your database. It allows you to provide varying levels of detail. You can provide a minimum of input and allow Oracle to make decisions for you, eliminating the need to spend time deciding how best to set parameters or structure the database. Optionally, it allows you to be very specific about parameter settings and file allocations.It builds efficient and effective databases that take advantage of Oracle's new features.It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files, including initialization files, follow standard naming and placement practicesTop of Form
Bottom of Form
Question : What is a cursor? Why do you need them? What are the different kinds of cursor?
oracle uses private sql area to execute sql queries and store the information.pl/sql uses cursors to name these private sql area and access its stored information
there r two types of cursors
implicit
explicit
implicit cursors are used for all dml and single row queries.these are system defined
explicit cursors are used for queries which return multiple row .these are user defined.
Question : What is SGA
SGA - Stands for System Global Area, its a part of Memory structure! SGA consists of
Shared Pool
Database Buffer Cache
Redo Log Buffer
Lock & Latch Management
& More Aditional Memory Structures
Large pool & Java Pool
another name of SGA is Shared Global Area. It is used to store database information that is shared by database processes. It contains Data and control information for the Oracle Server and is allocated in the Virtual Memory of the computer - where Oracle Resides
Question : What is a shared pool
The Shared Pool environment contains both fixed and variable structures. The Fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements.
Used To Store
Most Recently Executed SQL Statements
Most Recently used Data definitions
It Consists of two Key performance - related memory structures
Library Cache & Data Dictionary Cache
Shared Pool is sized by SHARED_POOL_SIZE
Question : What is mean by Program Global Area (PGA)
PGA - Program Global Area
or the Process Global Area is a memory region that contains data and control information for a single server process or a single background process.
The PGA is allocated when a process is created and deallocated when the process is terminated. PGA is an area that is used by only one process.
Question : What is redo log buffer
it is a memory location where the temporary changes are stored in a buffer
Question : How can you see the source code of the package
select text from dba_source where type='PACKAGE' and name=' ';
What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
What is the function of Optimizer ?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement
What is Execution Plan ?
The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
What is RULE-based approach to optimization ?
Choosing an executing planbased on the access paths available and the ranks of these access paths.
What is COST-based approach to optimization ?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
What is a Procedure ?
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.
What is a Package ?
A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database
What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in,update to, or delete from a table
What are the uses of Database Trigger ?
Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.
What are Roles
Roles are named groups of related privileges that are granted to users or other roles.
What is a profile ?
Each database user is assigned a Profile that specifies limitations on various system resources available to the user
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 base tables 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.
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.
What is the use of ANALYZE command ?
To perform one of these function on an index,table, or cluster:
- To collect statistics 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.
What is a SNAPSHOT ?
Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
How can we reduce the network traffic?
- Replication of data in distributed environment.
- Using snapshots to replicate data.
- Using remote procedure calls.
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.
What are the options available to refresh snapshots ?
COMPLETE - Tables are completely regenerated using the snapshots 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
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.
What is Restricted Mode of Instance Startup ?
An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
What are the different modes of mounting a Database with the Parallel Server ?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter
What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?
Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode
</></></></></>
-------------------------------------------------------------------------------------------------------------------------------
ORACLE FAQs :
1. WHAT IS DATA OR INFORMATION?
Ans: The Matter that we feed into the Computer is called Data or Information.
2. WHAT IS DATABASE?
Ans: The Collection of Interrelated Data is called Data Base.
3. WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some Programs to access the Data is Called Data Base Management System (DBMS).
4. WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
5. WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base Management System is one that can store data, the relationship of the data, and the behavior of the data (i.e., the way it interacts with other data).
6. NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it is to be considered truly relational. Out those, some are as follows
a) The rules stem from a single rule- the ‘zero rule’: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
b) Information Rule: Tabular Representation of Information.
c) Guaranteed Access Rule: Uniqueness of tuples for guaranteed accessibility.
d) Missing Information Rule: Systematic representation of missing information as NULL values.
e) Comprehensive Data Sub-Language Rule: QL to support Data definition, View definition, Data manipulation, Integrity, Authorization and Security.
7. WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE MODELS?
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in the Information Management System (IMS) developed by IBM in 1968. In this data is organized as a tree structure. Each tree is made of nodes and branches. The nodes of the tree represent the record types and it is a collection of data attributes entity at that point. The topmost node in the structure is called the root. Nodes succeeding lower levels are called children.
b) Network Model: The Network Model, also called as the CODSYL database structure, is an improvement over the Hierarchical mode, in this model concept of parent and child is expanded to have multiple parent-child relationships, i.e. any child can be subordinate to many different parents (or nodes). Data is represented by collection of records, and relationships among data are represented by links. A link is an association between precisely two records. Many-to-many relationships can exists between the parent and child.
c) Relational Model: The Relational Database Model eliminates the need for explicit parent-child relationships. In RDBMS, data is organized in two-dimensional tables consisting of relational, i.e. no pointers are maintained between tables.
8. WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between the data objects. The relationships between the collections of data in a system may be graphically represented using data modeling.
9. DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be easily identified. An entity is any object, place, person, concept or activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.
10. WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down Approach. Entity relationship is technique for analysis and logical modeling of a system’s data requirements. It is the most widely used and has gained acceptance as the ideal database design. It uses three basic units: entities, their attributes and the relationship that exists between the entities. It uses a graphical notation for representing these.
11. WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition of complex records into simple records.
12. WHAT ARE VARIOUS NORMAL FORMS OF DATA?
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
13. WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to a table to improve performance is called DENORMALIZATION.
14. WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on single system, which acts as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.
15. WHAT ARE A TABLE, COLUMN, AND RECORD?
Ans: Table: A Table is a database object that holds your data. It is made up of many columns. Each of these columns has a data type associated with it.
Column: A column, referred to as an attribute, is similar to a field in the file system.
Record: A row, usually referred to as tuple, is similar to record in the file system.
16. WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the problem. It contains what to do but not how to do
17.WHAT TYPE OF LANGUAGE "SQL" IS?
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts what to do rather than how to do any process.
18. CLASSIFICATION OF SQL COMMANDS?
Ans:
DDL (Data Definition Language) DML (Data Manipulating Language) DCL (Data Control Language) DTL(Data Transaction Language)
Create Alter Drop Select Insert Update Delete Rollback Commit Grant Revoke
19. WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML commands autocommit is to be turned ON explicitly.
20. WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where as Query is a single line request for the information from the database.
21. WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records where as Delete Command will delete specified or all the records depending only on the condition given.
22. WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database objects where as the Update command is used to modify the values of a data base objects.
23. WHAT ARE COMMANDS OF TCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the TCL Category.
24. WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can delete only those records that are not really required.
25. WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database character set and the characters $ and #. But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name space must have different names.
7) A name can be enclosed in double quotes.
26. HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.
27. WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values,
Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.
28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type. 1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table
29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of characters and while querying the table varchar2 trims the extra spaces from the column and fetches the rows that exactly match the criteria.
30. HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT DATE FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory.
Default Date Format is: DD-MON-YY.
31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans:
Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects (LOB’s) 2000 bytes 4000 bytes Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary precision 2 GB 4GB
32. HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly. So we follow the following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.
33. HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same name and data type but new size.
34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.
36. WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.
37. WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called Composite Primary Key.
38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE POSSIBLE PER COMPOSITE PRIMARY KEY?
Ans:
39. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero nor a space and any mathematical calculation with NULL is always NULL.
40. WHAT IS NULL? A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using
References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest value from the set and compares a value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query produces any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column value used in inner query refers to the column value present in the outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an 'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE ()
FUNCTIONS?
Ans: Translate() is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas
Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be
Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers
to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTEOF [break] ON etc are SQL*PLUS REPORTING COMMANDS.
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges.
Create
2. What is On-line Redo Log? - The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.
3. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? - All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.
4. What are the steps involved in Database Startup? - Start an instance, Mount the Database and Open the Database.
5. What are the steps involved in Instance Recovery? - Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
6. Can Full Backup be performed when the database is open? - No.
7. What are the different modes of mounting a Database with the Parallel Server? - Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
8. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
9. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.
10. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.
11. What is Restricted Mode of Instance Startup? - An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
12. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
13. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
14. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.
15. Can a View based on another View? - Yes.
16. Can a Tablespace hold objects from different Schemes? - Yes.
17. Can objects of the same Schema reside in different tablespaces? - Yes.
18. What is the use of Control File? - When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
19. Do View contain Data? - Views do not contain or store data.
20. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
21. What are the type of Synonyms? - There are two types of Synonyms Private and Public
22. What is a Redo Log? - The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
23. What is an Index Segment? - Each Index has an Index segment that stores all of its data.
24. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
25. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.
26. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
27. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.
28. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
29. What is an Extent? - An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
30. What is a View? - A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
31. What is Table? - A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
32. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.
33. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
34. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.
35. What is schema? - A schema is collection of database objects of a User.
36. Describe Referential Integrity? - A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.
37. What is Hash Cluster? - A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.
38. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.
39. What is Database Link? - A database link is a named object that describes a “path” from one database to another.
40. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together
41. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.
42. What are the Characteristics of Data Files? - A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.
43. How to define Data Block size? - A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.
44. What does a Control file Contain? - A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.
45. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? - A Cluster with an index on the Cluster Key 48.When does a Transaction end? - When it is committed or Rollbacked.
46. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? - What are the factors that affect OPTIMIZER in choosing an Optimization approach? - Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
47. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? - The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
48. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
49. What is Execution Plan? - The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
50. What are the different approaches used by Optimizer in choosing an execution plan? - Rule-based and Cost-based.
51. What does ROLLBACK do? - ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
52. What is SAVE POINT? - For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.
53. What are the values that can be specified for OPTIMIZER MODE Parameter? - COST and RULE.
54. What is COST-based approach to optimization? - Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
55. What does COMMIT do? - COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
56. What is RULE-based approach to optimization? - Choosing an executing plan based on the access paths available and the ranks of these access paths.
57. What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command? - CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.
58. Define Transaction? - A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
59. What is Read-Only Transaction? - A Read-Only transaction ensures that the results of each query executed in the transaction are consistent with respect to the same point in time.
60. What is a deadlock? - Explain . Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically. These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.
61. What is a Schema? - The set of objects owned by user account is called the schema.
62. What is a cluster Key? - The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.
63. What is Parallel Server? - Multiple instances accessing the same database (Only In Multi-CPU environments)
64. What are the basic element of Base configuration of an oracle Database? - It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (table, indexes, views etc.,) The server that access the database consists of SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool) SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write) DBWR (Data Base Write) ARCH (ARCHiver) CKPT (Check Point) RECO Dispatcher User Process with associated PGS
65. What is clusters? - Group of tables physically stored together because they share common columns and are often used together is called Cluster.
66. What is an Index? - How it is implemented in Oracle Database? - An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command (Ver 7.0)
67. What is a Database instance? - Explain A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
68. What is the use of ANALYZE command? - To perform one of these function on an index, table, or cluster: - To collect statistics 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 off the table or cluster.
69. What is default tablespace? - The Tablespace to contain schema objects created without specifying a tablespace name.
70. What are the system resources that can be controlled through Profile? - The number of concurrent sessions the user can establish the CPU processing time available to the user’s session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user’s session the amount of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user’s session the allowed amount of connect time for the user’s session.
71. What is Tablespace Quota? - The collective amount of disk space available to the objects in a schema on a particular tablespace.
72. What are the different Levels of Auditing? - Statement Auditing, Privilege Auditing and Object Auditing.
73. What is Statement Auditing? - Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects
74. What are the database administrators utilities available? - 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.
75. 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 database.
76. 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 provides to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.
77. What are Roles? - Roles are named groups of related privileges that are granted to users or other roles.
78. What are the use of Roles? - REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group. DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group’s role automatically reflect the changes made to the role. SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user’s privileges in any given situation. APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.
79. What is Privilege Auditing? - Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
80. What is Object Auditing? - Object auditing is the auditing of accesses to specific schema objects without regard to user.
81. What is Auditing? - Monitoring of user access to aid in the investigation of database use.
82. What are the responsibilities of a Database Administrator?
1. Installing and upgrading the Oracle Server and application tools.
2. Allocating system storage and planning future storage requirements for the database system.
3. Managing primary database structures (tablespaces)
4. Managing primary objects (table, views, indexes)
5. Enrolling users and maintaining system security.
6. Ensuring compliance with Oracle license agreement
7. Controlling and monitoring user access to the database.
8. Monitoring and optimizing the performance of the database.
9. Planning for backup and recovery of database information.
10. Maintain archived data on tape
11. Backing up and restoring the database.
12. Contacting Oracle Corporation for technical support.
83. 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.
84. What is a profile? - Each database user is assigned a Profile that specifies limitations on various system resources available to the user.
85. 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.
86. What are the dictionary tables used to monitor a database spaces? - DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES.
87. 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 base tables 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.
88. 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.
89. 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 padded, %s - Log sequence number not padded. %T - Thread number left-zero- padded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.
90. 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. 95. 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).
91. What dynamic data replication? - Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
92. What is Two-Phase Commit? - Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.
93. How can you Enforce Referential 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.
94. What is a SQL * NET? - SQL *NET is ORACLE’s mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.
95. What is a SNAPSHOT? - Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
96. What is the mechanism provided by ORACLE for table replication? - Snapshots and SNAPSHOT LOGs
97. 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.
98. What are the various type of snapshots? - Simple and Complex.
99. Describe two phases of Two-phase commit? - Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to commit or rollback the transaction, even if there is a failure) Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.
100. 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.
101. 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.
102. What are the options available to refresh snapshots? - COMPLETE - Tables are completely regenerated using the snapshots 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.
103. What is a SNAPSHOT LOG? - A snapshot log is a table in the master database that is associated with the master table. ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.
104. What is Distributed database? - A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
105. How can we reduce the network traffic? - Replication of data in distributed environment. - Using snapshots to replicate data. - Using remote procedure calls.
106. Differentiate simple and complex, snapshots? - A simple snapshot is based on a query thaat does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snapshot of operations. - A complex snapshots contain at least any one of the above.
107. What are the Built-ins used for sending Parameters to forms? - You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
108. Can you have more than one content canvas view attached with a window? - Yes. Each window you create must have at least one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.
109. Is the After report trigger fired if the report execution fails? - Yes.
110. Does a Before form trigger fire when the parameter form is suppressed? - Yes.
111. Is it possible to split the print reviewer into more than one region? - Yes
112. Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size? - Yes
113. For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame? - Yes
114. Can a field be used in a report without it appearing in any data group? - Yes
115. The join defined by the default data link is an outer join yes or no? - Yes
116. Can a formula column referred to columns in higher group? - Yes
117. Can a formula column be obtained through a select statement? - Yes
118. Is it possible to insert comments into sql statements return in the data model editor? - Yes
119. Is it possible to disable the parameter from while running the report? - Yes
120. When a form is invoked with call_form, Does oracle forms issues a save point? - Yes
121. Can a property clause itself be based on a property clause? - Yes
122. If a parameter is used in a query without being previously defined, what diff. exist between report 2.0 and 2.5 when the query is applied? - While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.
123. What are the SQL clauses supported in the link property sheet? - Where start with having.
124. What is trigger associated with the timer? - When-timer-expired.
125. What are the trigger associated with image items? - When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks on an image item
126. What are the different windows events activated at runtimes? - When_window_activated When_window_closed When_window_deactivated When_window_resized Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.
127. When do you use data parameter type? - When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data parameters are used to pass data to produts invoked with the run_product built-in subprogram.
128. What is difference between open_form and call_form? - when one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. when one form invokes another form by executing call_form, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.
129. What is new_form built-in? - When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.
130. What is the “LOV of Validation” Property of an item? - What is the use of it? - When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV. Whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.
131. What is the diff. when Flex mode is mode on and when it is off? - When flex mode is on, reports automatically resizes the parent when the child is resized.
132. What is the diff. when confine mode is on and when it is off? - When confine mode is on, an object cannot be moved outside its parent in the layout.
133. What are visual attributes? - Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.
134. Which of the two views should objects according to possession? - view by structure.
135. What are the two types of views available in the object navigator (specific to report 2.5)? - View by structure and view by type .
136. What are the vbx controls? - Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.
137. What is the use of transactional triggers? - Using transactional triggers we can control or modify the default functionality of the oracle forms.
138. How do you create a new session while open a new form? - Using open_form built-in setting the session option Ex. Open_form (’Stocks ‘,active,session). when invoke the mulitiple forms with open form and call_form in the same application, state whether the following are true/False
139. What are the ways to monitor the performance of the report? - Use reports profile executable statement. Use SQL trace facility.
140. If two groups are not linked in the data model editor, What is the hierarchy between them? - Two group that is above are the left most rank higher than the group that is to right or below it.
141. An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form? - True
142. Explain about horizontal, Vertical tool bar canvas views? - Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window
143. What is the purpose of the product order option in the column property sheet? - To specify the order of individual group evaluation in a cross products.
144. What is the use of image_zoom built-in? - To manipulate images in image items.
145. How do you reference a parameter indirectly? - To indirectly reference a parameter use the NAME IN, COPY ‘built-ins to indirectly set and reference the parameters value’ Example name_in (’capital parameter my param’), Copy (’SURESH’,'Parameter my_param’)
146. What is a timer? - Timer is an “internal time clock” that you can programmatically create to perform an action each time the times.
147. What are the two phases of block coordination? - There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record. These operations are accomplished through the execution of triggers.
148. What are Most Common types of Complex master-detail relationships? - There are three most common types of complex master-detail relationships: master with dependent details master with independent details detail with two masters
149. What is a text list? - The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select values that are not displayed.
150. What is term? - The term is terminal definition file that describes the terminal form which you are using r20run.
151. What is use of term? - The term file which key is correspond to which oracle report functions.
152. What is pop list? - The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.
153. What is the maximum no of chars the parameter can store? - The maximum no of chars the parameter can store is only valid for char parameters, which can be up to 64K. No parameters default to 23 Bytes and Date parameter default to 7 Bytes.
154. What are the default extensions of the files created by library module? - The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary
155. What are the Coordination Properties in a Master-Detail relationship? - The coordination properties are Deferred Auto-Query These Properties determine when the population phase of block coordination should occur.
156. How do you display console on a window? - The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.
157. What are the different Parameter types? - Text ParametersData Parameters
158. State any three mouse events system variables? - System.mouse_button_pressedSystem.mouse_button_shift
159. What are the types of calculated columns available? - Summary, Formula, Placeholder column.
160. Explain about stacked canvas views? - Stacked canvas view is displayed in a window on top of, or “stacked” on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.
161. What are the built_ins used the display the LOV? - Show_lov List_values
162. What is the difference between SHOW_EDITOR and EDIT_TEXTITEM? - Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.
163. What are the built-ins that are used to Attach an LOV programmatically to an item? - set_item_property get_item_property (by setting the LOV_NAME property)
164. How do you call other Oracle Products from Oracle Forms? - Run_product is a built-in, Used to invoke one of the supported oracle tools products and specifies the name of the document or module to be run. If the called product is unavailable at the time of the call, Oracle Forms returns a message to the operator.
165. What is the main diff. bet. Reports 2.0 & Reports 2.5? - Report 2.5 is object oriented.
166. What are the different file extensions that are created by oracle reports? - Rep file and Rdf file.
167. What is strip sources generate options? - Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file
168. What is the basic data structure that is required for creating an LOV? - Record Group.
169. What is the Maximum allowed length of Record group Column? - Record group column names cannot exceed 30 characters.
170. Which parameter can be used to set read level consistency across multiple queries? - Read only
171. What are the different types of Record Groups? - Query Record Groups NonQuery Record Groups State Record Groups
172. From which designation is it preferred to send the output to the printed? - Previewer
173. what are difference between post database commit and post-form commit? - Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.
174. What are the different display styles of list items? - Pop_list Text_list Combo box
175. Which of the above methods is the faster method? - performing the calculation in the query is faster.
176. With which function of summary item is the compute at options required? - percentage of total functions.
177. What are parameters? - Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type char, number, date that you define at design time.
178. What are the three types of user exits available? - Oracle Precompiler exits, Oracle call interface, NonOracle user exits.
179. How many windows in a form can have console? - Only one window in a form can display the console, and you cannot change the console assignment at runtime
180. If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated? - Only for 10 records.
181. What are the two repeating frame always associated with matrix object? - One down repeating frame below one across repeating frame.
182. What are the master-detail triggers? - On-Check_delete_master, On_clear_details, On_populate_details
183. What are the different objects that you cannot copy or reference in object groups? - Objects of different modules Another object groups Individual block dependent items Program units.
184. What is an OLE? - Object Linking & Embedding provides you with the capability to integrate objects from many Windows applications into a single compound document creating integrated applications enables you to use the features form.
185. Is it possible to modify an external query in a report which contains it? - No.
186. Does a grouping done for objects in the layout editor affect the grouping done in the data model editor? - No.
187. Can a repeating frame be created without a data group as a base? - No
188. If a break order is set on a column would it affect columns which are under the column? - No
189. Is it possible to set a filter condition in a cross product group in matrix reports? - No
190. Do user parameters appear in the data modal editor in 2.5? - No
191. Can you pass data parameters to forms? - No
192. Is it possible to link two groups inside a cross products after the cross products group has been created? - no
193. What are the different modals of windows? - Modeless windows Modal windows
194. What are modal windows? - Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.
195. What are the different default triggers created when Master Deletes Property is set to Non-isolated? - Master Deletes Property Resulting Triggers: Non-Isolated (the default) On-Check-Delete-Master On-Clear-Details On-Populate-Details
196. What are the different default triggers created when Master Deletes Property is set to isolated? - Master Deletes Property Resulting Triggers: Isolated On-Clear-Details On-Populate-Details
197. What are the different default triggers created when Master Deletes Property is set to Cascade? - Master Deletes Property Resulting Triggers: Cascading On-Clear-Details On-Populate-Details Pre-delete
198. What is the diff. bet. setting up of parameters in reports 2.0 reports2.5? - LOVs can be attached to parameters in the reports 2.5 parameter form.
199. What are the difference between lov & list item? - Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.
200. What is the advantage of the library? - Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.
201. What is lexical reference? - How can it be created? - Lexical reference is place_holder for text that can be embedded in a SQL statements. A lexical reference can be created using & before the column or parameter name.
202. What is system.coordination_operation? - It represents the coordination causing event that occur on the master block in master-detail relation.
203. What is synchronize? - It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal representation of the screen.
204. What use of command line parameter cmd file? - It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.
205. What is a Text_io Package? - It allows you to read and write information to a file in the file system.
206. What is forms_DDL? - Issues dynamic Sql statements at run time, including server side pl/SQl and DDL
207. How is link tool operation different bet. reports 2 & 2.5? - In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field is selected and the link tool is then used to link the first field to the second field.
208. What are the different styles of activation of ole Objects? - In place activation, External activation
209. How do you reference a Parameter? - In Pl/SQL, You can reference and set the values of form parameters using bind variables syntax. Ex. PARAMETER name = ‘’ or :block.item = PARAMETER Parameter name
210. What is the difference between object embedding & linking in Oracle forms? - In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.
211. Name of the functions used to get/set canvas properties? - Get_view_property, Set_view_property
212. What are the built-ins that are used for setting the LOV properties at runtime? - get_lov_property set_lov_property
213. What are the built-ins used for processing rows? - Get_group_row_count(function) Get_group_selection_count(function) Get_group_selection(function) Reset_group_selection(procedure) Set_group_selection(procedure) Unset_group_selection(procedure)
214. What are built-ins used for Processing rows? - GET_GROUP_ROW_COUNT(function) GET_GROUP_SELECTION_COUNT(function) GET_GROUP_SELECTION(function) RESET_GROUP_SELECTION(procedure) SET_GROUP_SELECTION(procedure) UNSET_GROUP_SELECTION(procedure)
215. What are the built-in used for getting cell values? - Get_group_char_cell(function) Get_groupcell(function) Get_group_number_cell(function)
216. What are the built-ins used for Getting cell values? - GET_GROUP_CHAR_CELL (function) GET_GROUPCELL(function) GET_GROUP_NUMBET_CELL(function)
217. A tleast how many set of data must a data model have before a data model can be base on it? - Four
218. To execute row from being displayed that still use column in the row which property can be used? - Format trigger.
219. What are different types of modules available in oracle form? - Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application
220. What is the remove on exit property? - For a modeless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.
221. What is WHEN-Database-record trigger? - Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. Generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.
222. What is a difference between pre-select and pre-query? - Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode. Pre-query trigger fires before pre-select trigger.
223. What are built-ins associated with timers? - find_timer create_timer delete_timer
224. What are the built-ins used for finding object ID functions? - Find_group(function) Find_column(function)
225. What are the built-ins used for finding Object ID function? - FIND_GROUP(function) FIND_COLUMN(function)
226. Any attempt to navigate programmatically to disabled form in a call_form stack is allowed? - False
227. Use the Add_group_row procedure to add a row to a static record group 1. true or false? - False
228. Use the add_group_column function to add a column to record group that was created at a design time? - False
229. What are the various sub events a mouse double click event involves? - What are the various sub events a mouse double click event involves? - Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.
230. How can a break order be created on a column in an existing group? - What are the various sub events a mouse double click event involves? - By dragging the column outside the group.
231. What is the use of place holder column? - What are the various sub events a mouse double click event involves? - A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to appear.
232. What is the use of hidden column? - What are the various sub events a mouse double click event involves? - A hidden column is used to when a column has to embed into boilerplate text.
233. What is the use of break group? - What are the various sub events a mouse double click event involves? - A break group is used to display one record for one group ones. While multiple related records in other group can be displayed.
234. What is an anchoring object & what is its use? - What are the various sub events a mouse double click event involves? - An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.
235. What are the various sub events a mouse double click event involves? - What are the various sub events a mouse double click event involves? - Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.
236. What are the default parameter that appear at run time in the parameter screen? - What are the various sub events a mouse double click event involves? - Destype and Desname.
237. What are the built-ins used for Creating and deleting groups? - CREATE-GROUP (function) CREATE_GROUP_FROM_QUERY(function) DELETE_GROUP(procedure)
238. What are different types of canvas views? - Content canvas views Stacked canvas views Horizontal toolbar vertical toolbar.
239. What are the different types of Delete details we can establish in Master-Details? - Cascade Isolate Non-isolate
240. What is relation between the window and canvas views? - Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.
241. What is a User_exit? - Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.
242. How is it possible to select generate a select set for the query in the query property sheet? - By using the tables/columns button and then specifying the table and the column names.
243. How can values be passed between precompiler exits & Oracle call interface? - By using the statement EXECIAFGET & EXECIAFPUT.
244. How can a square be drawn in the layout editor of the report writer? - By using the rectangle tool while pressing the (Constraint) key.
245. How can a text file be attached to a report while creating in the report writer? - By using the link file property in the layout boiler plate property sheet.
246. How can I message to passed to the user from reports? - By using SRW.MESSAGE function.
247. How is possible to restrict the user to a list of values while entering values for parameters? - By setting the Restrict To List property to true in the parameter property sheet.
248. How can a button be used in a report to give a drill down facility? - By setting the action associated with button to Execute pl/SQL option and using the SRW.Run_report function.
249. How can a cross product be created? - By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.
250. What is a physical page? What is a logical page? - A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.
251. What does the term panel refer to with regarda to pages? - A panel is the number of physical pages needed to print one logical page.
252. What is a master detail relationship? - A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.
253. What is a library? - A library is a collection of subprograms including user named procedures, functions and packages.
254. How can a group in a cross products be visually distinguished from a group that does not form a cross product? - A group that forms part of a cross product will have a thicker border.
255. What is the frame & repeating frame? - A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the number of records that are to displayed is not known before.
256. What is a combo box? - A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.
257. What are three panes that appear in the run time pl/SQL interpreter? - Source pane, interpreter pane, navigator pane.
258. What are the two panes that Appear in the design time pl/SQL interpreter? - Source pane, interpreter pane
259. What are the two ways by which data can be generated for a parameters list of values? - Using static values, writing select statement.
260. What are the various methods of performing a calculation in a report? - Perform the calculation in the SQL statements itself, use a calculated / summary column in the data model.
261. What are the default extensions of the files created by menu module? - .mmb, .mmx
262. What are the default extensions of the files created by forms modules? - .fmb - form module binary .fmx - form module executable
263. To display the page number for each page on a report, what would be the source & logical page number or physical page number?
264. It is possible to use raw devices as data files and what is the advantages over file system files? - Yes. The advantages over file system files. I/O will be improved because Oracle is bypassing the kernel when writing to disk. Disk Corruption will decrease.
265. What are disadvantages of having raw devices? - We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
266. What is the significance of having storage clause? - We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.,
267. What is the use of INCTYPE option in EXP command? - Type export should be performed COMPLETE, CUMULATIVE, INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into a notion Completes. e. will be written.
268. What is the use of FILE option in IMP command? - The name of the file from which import should be performed.
269. What is a Shared SQL pool? - The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
270. 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.
271. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?
1. SYSTEM - Data dictionary tables.
2. DATA - Standard operational tables.
3. DATA2- Static tables used for standard operations
4. INDEXES - Indexes for Standard operational tables.
5. INDEXES1 - Indexes of static tables used for standard operations.
6. TOOLS - Tools table.
7. TOOLS1 - Indexes for tools table.
8. RBS - Standard Operations Rollback Segments,
9. RBS1,RBS2 - Additional/Special Rollback segments.
10. TEMP - Temporary purpose tablespace
11. TEMP_USER - Temporary tablespace for users.
12. USERS - User tablespace.
272. How to implement the multiple control files for an existing database?
1. Shutdown the database
2. Copy one of the existing control file to new location
3. Edit Config ora file by adding new control filename
4. Restart the database.
273. What is advantage of having disk shadowing/ Mirroring? - Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switches over to a working disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
274. How will you force database to use particular rollback segment? - SET TRANSACTION USE ROLLBACK S
Operating system questions
Hardware, Unix/Linux, Windows interview questions
1. What are the basic functions of an operating system? - Operating system controls and coordinates the use of the hardware among the various applications programs for various uses. Operating system acts as resource allocator and manager. Since there are many possibly conflicting requests for resources the operating system must decide which requests are allocated resources to operating the computer system efficiently and fairly. Also operating system is control program which controls the user programs to prevent errors and improper use of the computer. It is especially concerned with the operation and control of I/O devices.
2. Why paging is used? - Paging is solution to external fragmentation problem which is to permit the logical address space of a process to be noncontiguous, thus allowing a process to be allocating physical memory wherever the latter is available.
3. While running DOS on a PC, which command would be used to duplicate the entire diskette? diskcopy
4. What resources are used when a thread created? How do they differ from those when a process is created? - When a thread is created the threads does not require any new resources to execute the thread shares the resources like memory of the process to which they belong to. The benefit of code sharing is that it allows an application to have several different threads of activity all within the same address space. Whereas if a new process creation is very heavyweight because it always requires new address space to be created and even if they share the memory then the inter process communication is expensive when compared to the communication between the threads.
5. What is virtual memory? - Virtual memory is hardware technique where the system appears to have more memory that it actually does. This is done by time-sharing, the physical memory and storage parts of the memory one disk when they are not actively being used.
6. What is Throughput, Turnaround time, waiting time and Response time? - Throughput – number of processes that complete their execution per time unit. Turnaround time – amount of time to execute a particular process. Waiting time – amount of time a process has been waiting in the ready queue. Response time – amount of time it takes from when a request was submitted until the first response is produced, not output (for time-sharing environment).
7. What is the state of the processor, when a process is waiting for some event to occur? - Waiting state
8. What is the important aspect of a real-time system or Mission Critical Systems? - A real time operating system has well defined fixed time constraints. Process must be done within the defined constraints or the system will fail. An example is the operating system for a flight control computer or an advanced jet airplane. Often used as a control device in a dedicated application such as controlling scientific experiments, medical imaging systems, industrial control systems, and some display systems. Real-Time systems may be either hard or soft real-time. Hard real-time: Secondary storage limited or absent, data stored in short term memory, or read-only memory (ROM), Conflicts with time-sharing systems, not supported by general-purpose operating systems. Soft real-time: Limited utility in industrial control of robotics, Useful in applications (multimedia, virtual reality) requiring advanced operating-system features.
9. What is the difference between Hard and Soft real-time systems? - A hard real-time system guarantees that critical tasks complete on time. This goal requires that all delays in the system be bounded from the retrieval of the stored data to the time that it takes the operating system to finish any request made of it. A soft real time system where a critical real-time task gets priority over other tasks and retains that priority until it completes. As in hard real time systems kernel delays need to be bounded
10. What is the cause of thrashing? How does the system detect thrashing? Once it detects thrashing, what can the system do to eliminate this problem? - Thrashing is caused by under allocation of the minimum number of pages required by a process, forcing it to continuously page fault. The system can detect thrashing by evaluating the level of CPU utilization as compared to the level of multiprogramming. It can be eliminated by reducing the level of multiprogramming.
11. What is multi tasking, multi programming, multi threading? - Multi programming: Multiprogramming is the technique of running several programs at a time using timesharing. It allows a computer to do several things at the same time. Multiprogramming creates logical parallelism. The concept of multiprogramming is that the operating system keeps several jobs in memory simultaneously. The operating system selects a job from the job pool and starts executing a job, when that job needs to wait for any i/o operations the CPU is switched to another job. So the main idea here is that the CPU is never idle. Multi tasking: Multitasking is the logical extension of multiprogramming .The concept of multitasking is quite similar to multiprogramming but difference is that the switching between jobs occurs so frequently that the users can interact with each program while it is running. This concept is also known as time-sharing systems. A time-shared operating system uses CPU scheduling and multiprogramming to provide each user with a small portion of time-shared system. Multi threading: An application typically is implemented as a separate process with several threads of control. In some situations a single application may be required to perform several similar tasks for example a web server accepts client requests for web pages, images, sound, and so forth. A busy web server may have several of clients concurrently accessing it. If the web server ran as a traditional single-threaded process, it would be able to service only one client at a time. The amount of time that a client might have to wait for its request to be serviced could be enormous. So it is efficient to have one process that contains multiple threads to serve the same purpose. This approach would multithread the web-server process, the server would create a separate thread that would listen for client requests when a request was made rather than creating another process it would create another thread to service the request. To get the advantages like responsiveness, Resource sharing economy and utilization of multiprocessor architectures multithreading concept can be used.
12. What is hard disk and what is its purpose? - Hard disk is the secondary storage device, which holds the data in bulk, and it holds the data on the magnetic medium of the disk.Hard disks have a hard platter that holds the magnetic medium, the magnetic medium can be easily erased and rewritten, and a typical desktop machine will have a hard disk with a capacity of between 10 and 40 gigabytes. Data is stored onto the disk in the form of files.
13. What is fragmentation? Different types of fragmentation? - Fragmentation occurs in a dynamic memory allocation system when many of the free blocks are too small to satisfy any request. External Fragmentation: External Fragmentation happens when a dynamic memory allocation algorithm allocates some memory and a small piece is left over that cannot be effectively used. If too much external fragmentation occurs, the amount of usable memory is drastically reduced. Total memory space exists to satisfy a request, but it is not contiguous. Internal Fragmentation: Internal fragmentation is the space wasted inside of allocated memory blocks because of restriction on the allowed sizes of allocated blocks. Allocated memory may be slightly larger than requested memory; this size difference is memory internal to a partition, but not being used
14. What is DRAM? In which form does it store data? - DRAM is not the best, but it’s cheap, does the job, and is available almost everywhere you look. DRAM data resides in a cell made of a capacitor and a transistor. The capacitor tends to lose data unless it’s recharged every couple of milliseconds, and this recharging tends to slow down the performance of DRAM compared to speedier RAM types.
15. What is Dispatcher? - Dispatcher module gives control of the CPU to the process selected by the short-term scheduler; this involves: Switching context, Switching to user mode, Jumping to the proper location in the user program to restart that program, dispatch latency – time it takes for the dispatcher to stop one process and start another running.
16. What is CPU Scheduler? - Selects from among the processes in memory that are ready to execute, and allocates the CPU to one of them. CPU scheduling decisions may take place when a process: 1.Switches from running to waiting state. 2.Switches from running to ready state. 3.Switches from waiting to ready. 4.Terminates. Scheduling under 1 and 4 is non-preemptive. All other scheduling is preemptive.
17. What is Context Switch? - Switching the CPU to another process requires saving the state of the old process and loading the saved state for the new process. This task is known as a context switch. Context-switch time is pure overhead, because the system does no useful work while switching. Its speed varies from machine to machine, depending on the memory speed, the number of registers which must be copied, the existed of special instructions(such as a single instruction to load or store all registers).
18. What is cache memory? - Cache memory is random access memory (RAM) that a computer microprocessor can access more quickly than it can access regular RAM. As the microprocessor processes data, it looks first in the cache memory and if it finds the data there (from a previous reading of data), it does not have to do the more time-consuming reading of data from larger memory.
19. What is a Safe State and what is its use in deadlock avoidance? - When a process requests an available resource, system must decide if immediate allocation leaves the system in a safe state. System is in safe state if there exists a safe sequence of all processes. Deadlock Avoidance: ensure that a system will never enter an unsafe state.
20. What is a Real-Time System? - A real time process is a process that must respond to the events within a certain time period. A real time operating system is an operating system that can run real time processes successfully
######################################################################################
1) Which of the following file is read to start the instance?
a. Controlfile b. Initialization Parameter file
c. Data files d. None
Ans: B.
Explanation: It will read init.ora parameter file for starting the instance.
2) Which file is read when database is mounted?Ans: Control file.
Explanation: Control file is read while we are mounting the database.
3) What actions will occur if we issue command startup at SQL prompt immediately then?
Ans: Instance is started, Database is mounted & then Database is opened.
4) What do dirty buffers comprises of?
Ans: Buffers modified but nit yet written to disk.
Explanation: Modified buffers in database buffer cache (SGA), which has not written to disk.
5) Which init.ora parameter is used to size database buffer cache?
Ans: db_cache_size or db_block_buffers.
6) What do library cache consists of?
Ans: Hold parsed versions of executed sql statements & Hold parsed versions of pl/sql program unit.
Explanation: Consists of both parsed versions of sql & pl/sql.
7) How can we size shared pool?
Ans: shared_pool_size.
Explanation: we have to specify shared_pool_size=
8) What does program global area (PGA) contains?
Ans: Users program variables, Users session information, & User defined cursors.
Explanation: PGA contains program variables, session information, and cursors. Not a SQL statement.
9) What happens during process of checkpoint?
Ans: It’s an event of recording modified blocks in database buffer cache onto data files.
Explanation: When checkpoint occurs it will invoke the DBWR to write dirty blocks from database buffer cache to database files.
10) What does SMON does?
Ans: Crash recovery, Clean up temporary segments & Coalescing free space
Explanation: SMON will do crash recovery; cleaning of temporary segments and coalescing free space. But it does not take care of background process.
11) Which file is read to start the instance?
Ans: Initialization parameter.
Explanation: It will read init.ora parameter file for starting the instance.
12) Which file is read when database is mounted?
Ans: Control file.
Explanation: control file is read while we are mounting the database.
13) What happens when we issue command startup at SQL prompt immediately?
Ans: Instance is started, Database is mounted & Database is opened.
14) Does PGA is a part of System Global Area?
Ans: NO.
Explanation: PGA (Program global area) is not a part of SGA. It is a separate memory structure.
15) What do dirty buffers comprises of?
Ans: Buffers modified but not yet written to disk.
Explanation: Modified buffers in database buffer cache (SGA), which has not written to disk.
16) Which init.ora parameter is used to size database buffer cache?
Ans: db_cache_size.
Explanation: If we want to change the size of database buffer cache we have to specify db_cache_size or db_block_buffers.
17) What do the library cache consists of?
Ans: Hold parsed versions of executed sql statements & Hold parsed versions of pl/sql program unit.
Explanation: Consists of both parsed versions of sql & pl/sql.
18) How can we size shared pool?
Ans: shared_pool_size.
Explanation: we have to specify shared_pool_size=
19) What does program global area (PGA) contains?
Ans: Users program variables, Users session information, & User defined cursors.
Explanation: PGA contains program variables, session information, and cursors. Not a SQL statement.
20) What happens during process of checkpoint?
Ans: It’s an event of recording modified blocks in database buffer cache onto data files.
Explanation: When checkpoint occurs it will invoke the DBWR to write dirty blocks from database buffer cache to database files.
21) What does SMON does?
Ans: Crash recovery, Clean up temporary segments & Coalescing free space
Explanation: SMON will do crash recovery; cleaning of temporary segments and coalescing free space. But it does not take care of background process.
22) The total number of Base tables that get created into sys account?
Ans: 1762.
Explanation: 1762 base tables will get created in sys account.
23) What is the status of your database when we run the create database file (ex. Cr8demo.sql)?
Ans: nomount.
Explanation: Database status should be in nomount state. Because for mounting a database it requires a control file.
24) Who are the users that created automatically the moment the database is created?
Ans: sys, Scott.
Explanation: sys and Scott users will get created when we create a database.
25) What is the table space that accommodates base tables?
Ans: system.
Explanation: It creates base tables in system table space.
26) What is the default table space for sys user?
Ans: system.
Explanation: Default table space for sys user is system.
27) Data Dictionary Views are static?
Ans: True.
Explanation: Data dictionary views are nothing but DBA_, ALL_, and USER_.
28) Is the database creation successful with this command?
Ans: True.
Explanation: It will use OMF for creating control file and data files.
29) What happens when ‘SHUTDOWN NORMAL’ is issued?
Ans: Database and redo buffers are written to disk, Background processes are terminated & the next startup does not require any instance recovery.
Explanation: When we issue a command SHUTDOWN NORMAL it will wait for connected users to disconnect but it doesn’t allow to any user to logged into the database.
30) Does the names & locations of data files is present in parameter file?
Ans: NO.
Explanation: It doesn’t maintain the location and names of data files that will be maintained by control file.
31) Can we create a tablespace with multiple datafiles at a single stroke?
Ans: Yes.
Explanation: We can create tablespace in single stroke with command SQL>Create tablespace
32) Can a datafile be associated with two different tablespaces?
Ans: No.
Explanation: One datafile can associated to one tablespace not more than one tablespace.
33) Suppose your database has max_datafiles limit of 80 and we want to add files above this limit which file we need to modify?
Ans: Controlfile.
Explanation: In controlfile we have to change MAXDATAFILES=
34) Which view tells us all the tablespaces in your database?
Ans: v$tablespace.
Explanation: V$TABLESPACE view give the tablespace details in a database.
35) Can we bring system tablespace offline when the database is up?
Ans: No.
Explanation: We cannot make system tablespace offline because it contains base tables.
36) What is the dafault initial extent size when the tablespace is dictionary managed?
Ans: 5 blocks.
Explanation: When we create a dictionary managed tablespace it will give the initial extent as 5*
37) Which parameter should be added in init.ora file for creating tablespace with multiple blocksizes?
Ans: db_nk_cache_size=n.
Explanation: We have to add db_Nk_cache_size=
38) What is the value for storage clause pctincrease when the tablespace extent management is local (uniform)?
Ans: 0%.
Explanation: PCTINCREASE for locally managed tablespace is 0%.
39) What is the command that combines all the smaller contiguous free extents in the tablespace into one larger extent?
Ans: coalesce.
Explanation: Coalesce is used to combine all the smaller contiguous free extents in the tablespace into one larger extent. Merge and sum are SQL commands related to table and add extents is not valid.
40) If the system datafile to be renamed, the database must be in which mode?
Ans: mount.
Explanation: For renaming a datafile belonging to system tablespace our database should be in mount state because system contains all the base tables when we open a database it will continuosly update the base tables evenif we are not performing transactions.
41) After creating a tablespace what is the default value for segment space management in 9i?
Ans: manual.
Explanation: Its manual in 9i and Oracle 10g it’s AUTO.
42) A tablespace was created with extent management as local. After that the tablespace extent management was changed from local to dictionary. What would be the next extent size?
Ans: 1m.
Explanation: Its 1m after the change.
43) If we create a tablespace with extent management dictionary and block size 8k with default storage initial 10k. After creating this tablespace whatvalue it will show for initial_extent in dba_tablespaces?
Ans: 40k.
Explanation: If extent management is dictionary then database requires initial extent size atleast (block_size*5), here its 8k*5=40k.
44) Can we create a table with your own parameters like ( initial 300k next 300k minextents) on tablespace whose extent management is local?
Ans: YES.
Explanation: Yes we can create.
45) A locally managed tablespace is made offline what is the status of bytes column in dba_data_files?
Ans: It shows the null value.
46) Can we resize a datafile where the related tablespace is in offline mode?
Ans: NO.
Explanation: We cannot do it.
47) DBA changed a datafile’s autoextend value to on, what is the default value for increment_by (column) located in dba_data_files?
Ans: 1m.
Explanation: When we changed datafile to Autoextend on then value of increment_by column in DBA_DATA_FILES will be 1m (by default) means after filling of datafile complete it will increase the size of datafile by 1m everytime.
48) Can we drop a object when the tablespace is in read only mode?
Ans: YES.
Explanation: Yes we can do it.
49) We are trying to create a table with your own storage parameters in a locally managed tablespace. Guess what happens?
Ans: It will create table with default storage parameters at tablespace level.
50) Extent deallocation for a segment is done when ___________?
Ans: dropped, truncate.
Explanation: When we dropped or truncate a object it will deallocate the extents for that segment.
51) What type of data is available in rollback segments?
a. previous image b. post updated image
c. meta data d. no data
52) One of these is not the purpose of rollback segments?
a. Undo previous command b. read consistency
c. Crash recovery d. backup support
53) What is the default status of rollback segment the moment it is created?
a. offline b. online
c. deferred d. pending
54) What is the storage parameter that is unique to rollback segments?
a. initial b. dictionary
c. optimal d. shrink
55) Suppose a rollback segment is occupied by a transaction and in the mean time the rollback segment is brought offline at that moment what is the status of tat rollback segment?
a. offline b. deferred
c. pending offline d. cannot be made offline
56) What does high water mark size (hwm size) in rollback segment state?
a. the max size rollback segment b. the optimal size of rollback
as grown ever segment
c. the min size rollback segment d. None
has ever been
57) Suppose the users tablespace is bought offline which has some open transaction later the user said commit, what is the status of the rollback segment at this stage?
a. deffered b. optimal
c. pending offline d. offline
58) To make rollback segments online the moment the database is started what is the file we need to modify?
a. controlfile b. logfile
c. init.ora d. orapwd file
59) Can a rollback segment hold multiple entries?
a. No b. Yes
60) Can we drop an undo tablespace which currently in use?
a. Yes b. No
61) Can we create permanent objects in default temporary tablespace of a DB?
a. YES b. NO
62) Can we make a tempfile read only?
a. YES b. NO
63) Which of the following view, by which we can find out the default temporary tablespace of a DB?
a. dba_temp_files b. v$tempfile
c. database_properties d. db_properties
e. None
64) what is the extent_management value for the temporary tablespace created in 10g?
a. Local b. Dictionary
c. System d. User
e. None
65) What is the minimum size for a temporary file to be created?
a. 1030k b. 1040k
c. 1041k d. 1031k
e. 1050k
66) What is the value for allocation_type column in dba_tablespaces view for temporary tablespace?
a. SYSTEM b. LOCAL
c. USER d. UNIFORM
67) Which of the following cmd is used to make the temporary TS as default temporary TS of DB?
a. SQL>alter database default b. SQL>alter database default
temporary tablespace tablespace temporary
c. SQL> alter database temporary d. SQL>alter system set default
tablespace
68) Which of the following conditions should meet to convert permanent TS into Temporary?
a. Extent Management Local Auto b. Extent Management Local
and TS must be empty Uniform & TS must be empty
c. Extent Management Dictionary & d. None
TS must be empty
69) What is the command to convert a permanent TS into temporary?
a. SQL>alter database tablespace b. alter tablespace
c. SQL>alter tablespace permanent d. SQL>alter database permanent
70) Can we create temporary tablespace with “SEGMENT SPACE MANAGEMENT AUTO” ?
a. YES b. NO
71) Create user without mentioning default tablespace clause. Then by default which tablespace allocate for that user?
a. system b. user_data
c. temp d. SYSAUX
e. Default TS of DB
72) One user assigned select on
<><><><><>to another user with grant option after that this second user assigned same privilege to third user. After that first user revoked this privilege from second user. Then third user can e use that already assigned privilege?
a. NO b. YES
73) If DBA created one role with some privileges and assigned this role this users. After that he want revoke on privilege from that users how?
a. revoke from
c. revoke from
74) DBA created one profile and assigned to users for applicable for that which parameter we need set in init.ora?
a. timed_statistics=true b.resource_limits=true
c. resource_limit=true d. none
75) Which privilege is necessary for a normal user to change his password?
a. create any table b. create session
c. alter user d. alter any user
76) How to manually lock user account?
a. user
c. alter user
by
77) From which view user can see his privilege?
a. user_role_privs b. dba_sys_privs
c. session_privs d. role_role_privs
78) One user as quota on two tablespaces. Can he create his tables other than default tablespace?
a. NO b. YES
79) System user granted DBA to normal user. Now can this user revoke DBA from system?
a. YES b. NO
80) For creating password file for sys in which directory we are created?
a. $HOME b. $ORACLE_HOME/rdbms/admin
c. $ORACLE_HOME/dbs d. $ORACLE_HOME/sqlplus/admin
81) What is the default location of listener.ora file?
a. $ORACLE_HOME/rdbms/admin b. $ORACLE_OME/dbs
c. $ORACLE_HOME/network/admin/ d. $ORACLE_HOME/network/tools/
samples samples
82) The listener service is stopped after giving connection to a client. What is the status of client?
a. connection will lost b. connection will be continued by
giving an error message
c. the client session hangs d. connection will be continued
without any messages
83) What is the command to start the listener for particular parameters set?
a. lsnrctl reload
c. tnsping
84) Can we start the listener service for a database which is not yet started/opened?
a. YES b. NO (minimum the DB must be in
mount state)
b. NO (First the DB should be opened) d. None of the above
85) In which file do we set this parameter FAILOVER=ON to use failover server option of oracle?
a. init.ora file b. tnsnames.ora
c. listener.ora d. bot listener.ora & tnsnames.ora
c. controlfile
86) Can we start multiple database services with in one listener service?
a. NO b. YES
c. YES & its only in Oracle 9i
87) Can I have multiple listeners for a single database?
a. YES b. NO
c. YES & its only in Oracle 9i
88) What is the view do we query to find out the users who are connected using oracle networking?
a. DBA_USERS b. DBA_NET_INFO
c. V$SESSION d. DBA_CLIENT_INFO
89) After some modifications to listener file, How can I refresh the already running listener service without stopping it?
a. lsnrctl start
c. lsnrctl status
90) Which operations we can perform using network connections?
a. DML b. DDL
c. A & B d. Only DML’s
91) Which background process is needed to create materialized view?
a. ckpt & cjq0 b. lgrw & reco
c. dbw0 & reco d. reco & cjq0
92) Which parameter do we use to start to reco process?
a. job_queue_process b. reco_processes
c. distributed_transactions d. global_names
93) Is it mandatory to put the parameter global_names=true for creating database links?
a. YES b. NO
94) For creating database links is it necessary to put some value for distributed_transactions parameter?
a. No (Not required at client) b. Yes (Needed only at client)
c. Yes (Needed at both client & server) d. Yes (Needed only at server)
95) Which background process refresh the materialized view on a given refresh interval?
a. cjq0 b. reco
c. arc0 d. ckpt
96) Can we do any DML operations on materialized view?
a. Yes ( Only its not possible with b. Yes (Only with refresh fast option)
Refresh fast option)
c. No d. Yes
97) How many refresh options do we have for creating materialized view?
a. Yes b. No
98) What is the segment type for a materialized view?
a. view b. table
c. materialized view d. synonym
99) Can we manually refresh any materialized view?
a. Yes b. No
100) What is the syntax to drop materialized view?
a. SQL>DROP VIEW
cascade;
c.SQL>DROP MATERIALIZED VIEW d.SQL>DROP
ANSWERS TO THE QUESTIONS FROM Q51 – Q100
51) Ans: A (Previous image).
Explanation: The main purposes of rollback segments are to maintain before/previous image of data.
52) Ans: D.
Explanation: Backup support is not a function of rollback segment
53) Ans: A (offline).
Explanation: After creating rollback segment the default status will be offline.
54) Ans: C (optimal).
Explanation: For a rollback segment unique storage parameter is optimal for shrinking.
55) Ans: C (Pending offline).
Explanation: When rollback supporting one transaction and in mean time if you are making that rollback segment offline then the status of rollback segment will be pending offline because one active transaction was going on that rollback segment.
56) Ans: A (The max size rollback segment has grown ever).
Explanation: High water mark size indicates the max size of rollback segment has grown ever in his life time.
57) Ans: A (deferred).
Explanation: The status of rollback segment will be deferred.
58) Ans: C (init.ora).
Explanation: We have to modify init.ora parameter file to online any rollback segment when DB is started.
59) Ans: B (YES).
Explanation: YES rollback segments can hold multiple entries. It work on First in First serve basis.
60) Ans: B (NO).
Explanation: Oracle donot allow to drop undo tablespace which in use, because other than sys user system rollback segment cannot use any other users.
61) Ans: B.
Explanation: We cannot create an permanent object in any temporary tablespace.
62) Ans: B.
Explanation: DBA cannot make any tempfile read only
63) Ans: C.
Explanation: By database_properties we can find which TS is default temporary TS for a database.
64) Ans: A.
Explanation: It’s a Local, because extent information of dictionary managed tablespace will be stored in data dictionary and locally managed tablespace extent information will be stored in locally in same tablespace so it will reduce the burden on dictionary. If temporary tablespace is dictionary managed then its burden is on data dictionary.
65) Ans: C.
Explanation: We can create temporary file wit minimum 1041k.
66) Ans: D.
Explanation: Oracle will uniformly allocate extents for temporary tablespace.
67) Ans: A.
Explanation: SQL>alter database default emporary tablespace
68) Ans: C.
Explanation: That tablespace should be dictionary managed and must be empty.
69) Ans: B.
Explanation: Alter tablespace
70) Ans: B.
Explanation: No we cannot create temporary tablespace with segment space management auto.
71) Ans: E.
Explanation: From 10g onwards which is the default tablespace for database that will be assign to the user. In 9i it is system tablespace.
72) Ans: A.
Explanation: No the user cannot
73) Ans: C.
Explanation: We have to revoke the privilege from the role. We cannot revoke directly from the user.
74) Ans: C.
Explanation: Resourse_limit=true we have set init.ora file so profile will effect on user.
75) Ans: B.
Explanation: User require create session privilege to change his own password because he is own that whole schema.
76) Ans: B.
Explanation: Alter user
77) Ans: C.
Explanation: session_privs will show the privileges for that user.
78) Ans: B.
Explanation: Yes if user is having quota on different tablespaces he can create is own objects on that tablespaces.
79) Ans: A.
Explanation: User can revoke a dba privilege from sysdba. Because Oracle database requires at any point of time one DBA only.
80) Ans: C.
Explanation: We have to create your password file in ORACLE_HOME/dbs directory only then only it will read that password file.
81) Ans: C.
Explanation: Bydeafult listener.ora file will be available in $ORACLE_HOME/network/admin/samples.
82) Ans: D.
Explanation: Connection will be continued without any messages because listener as already authenticated that user for database.
83) Ans: B.
Explanation: LSNRCTL START
84) Ans: A.
Explanation: Listener is independent from database that’s why we can start/stop the listener without database also.
85) Ans: B.
Explanation: In TNSNAMES.ORA file we have to specify FAILOVER=ON to use failover server option of Oracle networking.
86) Ans: B.
Explanation: Yes we can start n number of services with one listener.
87) Ans: A.
Explanation: Yes we can configure n number of listener for one database.
88) Ans: C.
Explanation: We can query V$SESSION to find out all the information of users who are logged in to your database. We can find out from where user logged in, at what time he logged in, etc.
89) Ans: B.
Explanation: We can use reload option to refresh already running listener.
90) Ans: C.
Explanation: We can use DML and DDL operations using network connection because using oracle networking directly your login to user SCHEMA.
91) Ans: D.
Explanation: cjq0 process is required to refresh materialized views and reco is required for maintaining distributed transactions between database.
92) Ans: C.
Explanation: DISTRIBUTED_TRANSACTIONS parameter is responsible for distributed transactions. From 9i onwards this reco is mandatory backgroud process so Oracle deprecated this parameter.
93) Ans: B.
Explanation: It is not mandatory to put parameter global_names=true for creating database link because this parameter we have to set when we are creating global database links.
94) Ans: B.
Explanation: Its required only at client side.
95) Ans: A.
Explanation: CJQ0 background process will refresh the materialized view after every refresh interval.
96) Ans: C.
Explanation: No materialized view is only read only we cannot perform any DML operations on materialized views.
97) Ans: B.
Explanation: We have only three refresh options for creating materialized view (COMPLETE,FAST,FORCE).
98) Ans: A.
Explanation: Yes we can refresh materialized view manually using DBMS_MVIEW package.
99) Ans: B.
Explanation: When we create a materialized view that a local table in database so segment type of materialized view is TABLE.
100) Ans: C.
Explanation: SQL> DROP MATERIALIZED VIEW
**************************************************************************************
Question : What are the components of physical database structure of Oracle database
totally there are 6 files(components) of physical database structure.3 mandatory & 3 optional. Three mandatory files are : 1> datafile : store actual data 2> control file : strores stuctural & status information of database. 3> redolog file : stores changed/committed data Three optional files are : 4> parameter file : strores all size related parameters note : this file is mandatory for a1st time u create a database, then it is optional. 5> archivelog file : its offline copy of redolog files 6> password file : used to make normal user to behave as a super user.
Question : What are the components of logical database structure of Oracle database
Tablespace, segments, extens, data Blocks.
A logical unit of storage of databse is called Tablespace.
Segments is a space alloocated for a specific logical storage structure within a tablespce.
Extents: Space allocated to a segments.
Datablocks: Oracle server manage the storage space in the datafiles in units is called data blocks or oracle blocks.
Question : What is a tablespace
An Oracle database consists of one or more logical storage units called tablespaces, which collectivley store all of the database's data.
Each tablespace in an ORACLE database consists of one or more files called datafiles, which are physical structures that conform with the operating system in which Oracle is running.
For Example::: A simple Oracle Database have one tablespace and one datafile
Question : What is SYSTEM tablespace and when is it created
System tablespace is the memory allocated by oracle for creation of objects,views,indexes.
this is created automatically by oracle when the Database is created
SYSTEM TABLESPACE USAGE NOTES:
Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects
Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.
select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME
Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:
Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME
Question : Explain the relationship among database, tablespace and data file. What is schema
Databases, tablespaces and datafiles are closely related, but they have important differences:
--- A Oracle Database consists of one or more tablespaces
--- Each Table space in an Oracle database consists of one or more files called datafiles.
--- A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
When a database user is created, a corresponding schema with the same name is created for that user. A schema is a named collection of objects that include Tables, Triggers, constraints, Indexes, Views etc. A user can only be associated with one schema, and that is the same name as the user's. Username and schema are often used interchangeably.
Question : What are Schema Objects
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users. Objects may define areas of the database to hold data, such as tables or indexes, or may consist just of a definition, such as a views or synonyms.
There is no relationship between a tablespace and a schema. Objects in the same schema can use storage in different tablespaces, and a tablespace can contain data from different schemas.
Schema objects can be created and manipulated using SQL. As an administrator, you can create and manipulate schema objects, just as you do with the logical and physical structures of your database using Oracle Enterprise Manager. The underlying SQL is generated for you by Oracle Enterprise Manager.
Question : Can objects of the same schema reside in different tablespaces
Yes , it can .
for example if you specify a different tablepace (B) for indexes, the indexes of the tables that the user create would be residing in B , and the table would reside in the user's default tablespace A.
Question : What is an Oracle view
A view is a logical table which makes a complex query easy.We can even create a complex view by joining two tables.
Question : What are the advantages of views
Views provide many advantages, like:
(a) They restrict access to the whole data, because they display only selective columns.
(b) They can be used to make complex queries easy. A user can use a simple query on a view to display data from multiple tables, without having the knowledge of how to join tables in queries.
(c) Different views can be created from the same data as per the requirements of different types of use groups.
Security
l Query Simplification
l Allows Different Perspective
l Schema Transparency / Location Transparency
l Schema Consistency
l Allows work-around for SQL limitations
Security
• to provide an additional level of table security by restricting access to a
predetermined set of rows and/or columns of a table
CREATE VIEW emp_sal_hist_v
AS
SELECT ratehist.employee, ratehist.beg_date, ratehist.pay_rate
FROM ratehist, employee
WHERE ratehist.company = employee.company
AND ratehist.employee = employee.employee
AND USER = employee.user_id;
Query Simplification
For example, a single view might be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables. Saving of complex queries also permits simplified commands for an end-user who does not know how to make joins and/or cryptic business rules governing a join.
Allows Different Perspective
For example, the columns of a view can be renamed without affecting the tables on which the view is based.
Columns cannot be dropped from tables in version 7.x, but you could recreate views without the unnecessary column.
Schema Transparency / Location Transparency
• Ability to hide the schema of data from the application, and therefore the user.
• For example, if a view's defining query references three columns of a four column table and a fifth column is added to the table, the view's definition is not affected and all applications using the view are not affected.
• Views can also be used to join tables across database schemas OR across databases (using remote links), thereby encapsulating schema names from the end user.
Schema Consistency
• If a web application is accessing legacy data and then we migrate over to a new system.
• Identify legacy tables accessed through the web.
• Create a view look-alike for each legacy table and have it return the samedata.
• Though not a long-term solution, will allow intermediate means of allowing the web application to run while the interface is rebuilt to the new system.
this is all about advantages of view.
Question : What is an Oracle sequence
A sequence is a database object created by a user that can be used to generate unique integers. A typical usage of sequences is to generate primary key values which are unique for each row.
It is generated and incremented (or decremented) by an internal Oracle routine. It can be used by multiple users and for multiple tables too. A sequence can be used instead of writing an application code for sequence-generating routine.
Question : What is a private synonym and public synonym
To create a private synonym in your own schema, you must have CREATE SYNONYM system privilege.
To create a private synonym in another user's schema, you must have CREATE ANY SYNONYM system privilege.
To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Such privileges must be granted to a user before the user can use the synonym. ‘
Question : What are synonyms used for
Synonyms are used to : 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.
Question : What is an Oracle index
An Index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation.
The Logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored
An index is a schema object that can speed up the retrieval of rows by using pointers. If you do not have an index, then a full table scan occurs. Its purpose is to reduce disk I/O by using an indexed path to locate data quickly. If a table is dropped, the corresponding indexes are also dropped
Question : How are the index updates
Indexes can be updated by either Dropping and recreating them, or Rebuilding them online
Question : What are clusters
A Cluster is made up of a table, or group of tables that share the same data blocks, which are grouped togather because they share common columns and are often used together
Question : What is cluster key
A cluster index is needed in support of a cluster. One is used to allow the other to exist. Much like we need an index in support of a primary key. They are not the same things.
Clusters are useful in the database to store related pieces of information from more then 1 table in the same physical database block. It in effect stores data "prejoined". I can use this technique to store all of the data from the DEPT
and EMP table for a given DEPTNO on the same block so that all employees of department 10 as well as the department 10 master record are all physically stored on the same exact block. When I go to "join" this data -- it is already
done for me -- in a very few IOs I can get all of the data I need. Using conventional tables, this data could be scattered onto many dozens of blocks.
read the oracle documentation, it gives examples of creating a cluster, the cluster key index, and the tables in the cluster.
Also, with a hash cluster there is no index by definition so no syntax to inspect. We hash the cluster key to determine where the data goes -- we do not index it. the data is the index.
Clusters are useful when you want data with the same cluster key values to be
physically stored near eachother.
Question : What is database link
A database link is a pointer in the local database that allows you to access on a remote database.
Question : What are the types of database links
Oracle allows you to create private, public, and global database links.
Private Database Link: You can create a private database link in a specific schema of a database. Only the owner of a private database link or PL/SQL subprograms in the schema can use a private database link to access data and database objects in the corresponding remote database.
Public Database Link : You can create a public database link for a database. All users and PL/SQL subprograms in the database can use a public database link to access data and database objects in the corresponding remote database.
Global Database Link - When an Oracle network uses Oracle Names, the names servers in the system automatically create and manage global database links for every Oracle database in the network. All users and PL/SQL subprograms in any database can use a global database link to access data and database objects in the corresponding remote database.
A private database link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the private link to access the specified remote database.
When many users require an access path to a remote Oracle database, an administrator can create a single public database link for all users in a database.
When an Oracle network uses Oracle Names, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.
Question : What is data block
Block is the smallest unit of storage in the
logical structure of the database where
actual table rows are stored.
Question : What is row chaining
if any of he empty row is not sufficient to hold the row. then row is placed in multiple blocks. it happenes when the block size is small and rows are of large size. then it cause chaining. Due to chaining performance degrades and will cause more IOs
Question : What is a data segment
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment.
Oracle databases use four types of segments, which are described in the following sections:
A single data segment in an Oracle database holds all of the data for one of the following:
A table that is not partitioned or clustered
A partition of a partitioned table
A cluster of tables
Question : What is an index segment
Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage
Question : What is rollback segment
ROLLBACK INFORMATION NOTES:
Segment Name - Name of the rollback segment.
Owner - Owner of the rollback segment.
Tablespace - Name of the tablespace containing the rollback segment.
Segment ID - ID number of the rollback segment.
File ID - ID number of the block containing the segment header.
Block ID - Starting block number of the extent.
Initial Extent - Initial extent size in bytes.
Next Extent - Secondary extent size in bytes.
Min Extents - Minimum number of extents.
Max Extents - Maximum number of extents.
PCT Increase - Percent increase for extent size.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .
select SEGMENT_NAME,
OWNER,
TABLESPACE_NAME,
SEGMENT_ID,
FILE_ID,
BLOCK_ID,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
INSTANCE_NUM
from dba_rollback_segs
order by SEGMENT_NAME
Question : What is the use of control file
Control file is binary file which is having all the information realted to database. db_name, maxlogfiles, maxdatafiles, tablespaces information. Without this u cannot open your database. Init.ora parameter file showing the location of the controlfile.
Question : What is a database instance and Explain
A instance is basically a set of memory and some background processes
Question : What is a deadlock and Explain
Answer : A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multitable deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock
What is a Database instance ? Explain
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
What is Parallel Server ?
Multiple instances accessing the same database (Only In Multi-CPU environments)
What is clusters ?
Group of tables physically stored together because they share common columns and are often used together is called Cluster
Question : How do you rename a database?
To change the name of the database
-- For this script to run properly do the following:
-- Backup the Control fiel to Trace
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
-- Shutdown the database to make the changes
SHUTDOWN IMMEDIATE;
-- Edit the trace file and change the CREATE CONTROLFILE command
-- CREATE CONTROLFILE REUSE SET DATABASE "NEW_SID_NAME" RESETLOGS
-- (note the SET keyword)
Change the name in Control file and Init.ora
The first line of Control file should be "CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS ARCHIVELOG"
- modify the db_name parameter in the init.ora
-- Startup the datbase nomount with changed PFile
STARTUP NOMOUNT;
-- Execute the create controlfile command.
@create_control.sql;
-- Cancel base recovery of the database
Recover database USING BACKUP CONTROLFILE until cancel;
CANCEL
-- Open resetlogs the database
ALTER DATABASE OPEN RESETLOGS;
-- Rename GLOBAL_NAME to
ALTER DATABASE RENAME GLOBAL_NAME TO ;
-- Create SPFile, IF required give NAME and PATH of the PFILE
CREATE SPFILE FROM PFILE;
select name from v$database;
Question : How do you pin an object
Use dbms_shared_pool procedure.
EXECUTE DBMS_SHARED_POOL.KEEP(OBJECTNAME);
Question : What are memory structures in Oracle?
The basic memory structures associated with Oracle include:
• System Global Area (SGA), which is shared by all server and background
processes and holds the following:
o Database buffer cache
o Redo log buffer
o Shared pool
o Large pool (if configured)
• Program Global Areas (PGA), which is private to each server and background
process; there is one PGA for each process. The PGA holds the following:
o Stack areas
o Data areas
Question : What does database do during mounting process?
oracle process are created 1 LGWR 2 DBWR 3 CKPT 4 LGWR 5 SMON 6 PMON etc.. control file is readed
Question : What is the correct sequence among FETCH, EXECUTE, And PARSE
the correct sequence is PARSE, EXECUTE and then FETCH.
Question : Explain Oracle memory structure.
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer’s RAM is called memory structure. Oracle has two memory structures in the computer’s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single, user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database
Question : How can you check which user has which Role.
desc dba_tab_privs
Question : How to DROP an Oracle Database?
Answer : You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:
1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc
Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is also removed.
Question : What is the view name where i can get the space in MB for tables or views?
Answer : select segment_name,sum(bytes) from dba_segments where segment_name='TABLE_NAME' AND OWNER='OWNER of the table' group by segment_name
Question : What is difference between Logical Standby Database and Physical Standby database?
Answer : The primary functional difference between logical and physical standby database setups is that logical standby permits you to add additional objects (tables, indexes, etc) to the database, while physical standby is always an exact structural duplicate of the master database. The downside, though, is that logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be generally regarded as more tempramental than physical standby.
Question : What is a latch?
Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.
A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies
while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).
Question : Latches vs Enqueues
Enqueues are another type of locking mechanism used in Oracle. An enqueue is a more sophisticated mechanism which permits several concurrent processes to have varying degree of sharing of "known" resources. Any object which can be concurrently used, can be protected with enqueues. A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes can lock a table in share mode or in share update mode
etc.
One difference is that the enqueue is obtained using an OS specific locking mechanism. An enqueue allows the user to store a value in the lock, i.e the mode in which we are requesting it. The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested
with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO.
Another difference between latches and enqueues is that in latches there is no ordered queue of waiters like in enqueues.
Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.
Question : How Materialized Views Work with Object Types and Collections
Answer : This is extract from Oracle10g Advanced Replication Book.
How Materialized Views Work with Object Types and Collections
Oracle object types are user-defined datatypes that make it possible to model complex real-world entities such as customers and orders as single entities, called objects, in the database. You create object types using the CREATE TYPE ... AS OBJECT statement. You can replicate object types and objects between master sites and materialized view sites in a replication environment.
An Oracle object that occupies a single column in a table is called a column object. Typically, tables that contain column objects also contain other columns, which may be built-in datatypes, such as VARCHAR2 and NUMBER. An object table is a special kind of table in which each row represents an object. Each row in an object table is a row object.
You can also replicate collections. Collections are user-defined datatypes that are based on VARRAY and nested table datatypes. You create varrays with the CREATE TYPE ... AS VARRAY statement, and you create nested tables with the CREATE TYPE ... AS TABLE statement.
Question : How do you find wheather the instance was started with pfile or spfile
Answer : Thhere are 3 different ways :-
1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will check in $oracle_home/dbs directory and on windows it will check in oracle_home/database directory, and the content of pfile is just text based, but spfile content is in binary format, that is understandable by oracle very well.
Also oracle server always check the spfile or pfile with these sequence :-
SPFILE
SPFILE.ORA
PFILE
PFILE.ORA
Question : What are the Advantages of Using DBCA
These are a few of the advantages of using DBCA:You can use its wizards to guide you through a selection of options providing an easy means of creating and tailoring your database. It allows you to provide varying levels of detail. You can provide a minimum of input and allow Oracle to make decisions for you, eliminating the need to spend time deciding how best to set parameters or structure the database. Optionally, it allows you to be very specific about parameter settings and file allocations.It builds efficient and effective databases that take advantage of Oracle's new features.It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files, including initialization files, follow standard naming and placement practicesTop of Form
Bottom of Form
Question : What is a cursor? Why do you need them? What are the different kinds of cursor?
oracle uses private sql area to execute sql queries and store the information.pl/sql uses cursors to name these private sql area and access its stored information
there r two types of cursors
implicit
explicit
implicit cursors are used for all dml and single row queries.these are system defined
explicit cursors are used for queries which return multiple row .these are user defined.
Question : What is SGA
SGA - Stands for System Global Area, its a part of Memory structure! SGA consists of
Shared Pool
Database Buffer Cache
Redo Log Buffer
Lock & Latch Management
& More Aditional Memory Structures
Large pool & Java Pool
another name of SGA is Shared Global Area. It is used to store database information that is shared by database processes. It contains Data and control information for the Oracle Server and is allocated in the Virtual Memory of the computer - where Oracle Resides
Question : What is a shared pool
The Shared Pool environment contains both fixed and variable structures. The Fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements.
Used To Store
Most Recently Executed SQL Statements
Most Recently used Data definitions
It Consists of two Key performance - related memory structures
Library Cache & Data Dictionary Cache
Shared Pool is sized by SHARED_POOL_SIZE
Question : What is mean by Program Global Area (PGA)
PGA - Program Global Area
or the Process Global Area is a memory region that contains data and control information for a single server process or a single background process.
The PGA is allocated when a process is created and deallocated when the process is terminated. PGA is an area that is used by only one process.
Question : What is redo log buffer
it is a memory location where the temporary changes are stored in a buffer
Question : How can you see the source code of the package
select text from dba_source where type='PACKAGE' and name=' ';
What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
What is the function of Optimizer ?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement
What is Execution Plan ?
The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.
What is RULE-based approach to optimization ?
Choosing an executing planbased on the access paths available and the ranks of these access paths.
What is COST-based approach to optimization ?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
What is a Procedure ?
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.
What is a Package ?
A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database
What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in,update to, or delete from a table
What are the uses of Database Trigger ?
Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.
What are Roles
Roles are named groups of related privileges that are granted to users or other roles.
What is a profile ?
Each database user is assigned a Profile that specifies limitations on various system resources available to the user
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 base tables 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.
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.
What is the use of ANALYZE command ?
To perform one of these function on an index,table, or cluster:
- To collect statistics 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.
What is a SNAPSHOT ?
Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
How can we reduce the network traffic?
- Replication of data in distributed environment.
- Using snapshots to replicate data.
- Using remote procedure calls.
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.
What are the options available to refresh snapshots ?
COMPLETE - Tables are completely regenerated using the snapshots 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
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.
What is Restricted Mode of Instance Startup ?
An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
What are the different modes of mounting a Database with the Parallel Server ?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter
What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?
Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode
-------------------------------------------------------------------------------------------------------------------------------
ORACLE FAQs :
1. WHAT IS DATA OR INFORMATION?
Ans: The Matter that we feed into the Computer is called Data or Information.
2. WHAT IS DATABASE?
Ans: The Collection of Interrelated Data is called Data Base.
3. WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some Programs to access the Data is Called Data Base Management System (DBMS).
4. WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
5. WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base Management System is one that can store data, the relationship of the data, and the behavior of the data (i.e., the way it interacts with other data).
6. NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it is to be considered truly relational. Out those, some are as follows
a) The rules stem from a single rule- the ‘zero rule’: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities to MANAGE the DATABASE.
b) Information Rule: Tabular Representation of Information.
c) Guaranteed Access Rule: Uniqueness of tuples for guaranteed accessibility.
d) Missing Information Rule: Systematic representation of missing information as NULL values.
e) Comprehensive Data Sub-Language Rule: QL to support Data definition, View definition, Data manipulation, Integrity, Authorization and Security.
7. WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE MODELS?
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in the Information Management System (IMS) developed by IBM in 1968. In this data is organized as a tree structure. Each tree is made of nodes and branches. The nodes of the tree represent the record types and it is a collection of data attributes entity at that point. The topmost node in the structure is called the root. Nodes succeeding lower levels are called children.
b) Network Model: The Network Model, also called as the CODSYL database structure, is an improvement over the Hierarchical mode, in this model concept of parent and child is expanded to have multiple parent-child relationships, i.e. any child can be subordinate to many different parents (or nodes). Data is represented by collection of records, and relationships among data are represented by links. A link is an association between precisely two records. Many-to-many relationships can exists between the parent and child.
c) Relational Model: The Relational Database Model eliminates the need for explicit parent-child relationships. In RDBMS, data is organized in two-dimensional tables consisting of relational, i.e. no pointers are maintained between tables.
8. WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between the data objects. The relationships between the collections of data in a system may be graphically represented using data modeling.
9. DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be easily identified. An entity is any object, place, person, concept or activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.
10. WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down Approach. Entity relationship is technique for analysis and logical modeling of a system’s data requirements. It is the most widely used and has gained acceptance as the ideal database design. It uses three basic units: entities, their attributes and the relationship that exists between the entities. It uses a graphical notation for representing these.
11. WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition of complex records into simple records.
12. WHAT ARE VARIOUS NORMAL FORMS OF DATA?
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
13. WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to a table to improve performance is called DENORMALIZATION.
14. WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on single system, which acts as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.
15. WHAT ARE A TABLE, COLUMN, AND RECORD?
Ans: Table: A Table is a database object that holds your data. It is made up of many columns. Each of these columns has a data type associated with it.
Column: A column, referred to as an attribute, is similar to a field in the file system.
Record: A row, usually referred to as tuple, is similar to record in the file system.
16. WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the problem. It contains what to do but not how to do
17.WHAT TYPE OF LANGUAGE "SQL" IS?
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts what to do rather than how to do any process.
18. CLASSIFICATION OF SQL COMMANDS?
Ans:
DDL (Data Definition Language) DML (Data Manipulating Language) DCL (Data Control Language) DTL(Data Transaction Language)
Create Alter Drop Select Insert Update Delete Rollback Commit Grant Revoke
19. WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML commands autocommit is to be turned ON explicitly.
20. WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where as Query is a single line request for the information from the database.
21. WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records where as Delete Command will delete specified or all the records depending only on the condition given.
22. WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database objects where as the Update command is used to modify the values of a data base objects.
23. WHAT ARE COMMANDS OF TCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the TCL Category.
24. WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can delete only those records that are not really required.
25. WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database character set and the characters $ and #. But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name space must have different names.
7) A name can be enclosed in double quotes.
26. HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.
27. WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values,
Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.
28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB. 2) LOBs (except NCLOB) can be attributes of an object type. 3) LOBs support random access to data. 4) Multiple LOB columns per table or LOB attributes in an object type. 1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only sequential access. 4) Only one LONG column was allowed in a table
29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of characters and while querying the table varchar2 trims the extra spaces from the column and fetches the rows that exactly match the criteria.
30. HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT DATE FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory.
Default Date Format is: DD-MON-YY.
31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans:
Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects (LOB’s) 2000 bytes 4000 bytes Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary precision 2 GB 4GB
32. HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly. So we follow the following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.
33. HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same name and data type but new size.
34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to enforce data entity and consistency.There r two levels
1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.
36. WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.
37. WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called Composite Primary Key.
38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE POSSIBLE PER COMPOSITE PRIMARY KEY?
Ans:
39. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero nor a space and any mathematical calculation with NULL is always NULL.
40. WHAT IS NULL? A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using
References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest value from the set and compares a value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query produces any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column value used in inner query refers to the column value present in the outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an 'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE ()
FUNCTIONS?
Ans: Translate() is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas
Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be
Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers
to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges.
Create
No comments:
Post a Comment