Oracle: Oracle IQA 1

29. DB [Database]

A DB is a structured collection of data that is organized, so that its contents can easily be accessed, managed and updated. Oracle stores each data item in its own field. The fields relating to a particular person, thing, / event are bundled together to form a single, complete unit of data, called a record (a row / an occurrence). Each record is made up of a number of fields. No two fields in a record can have the same field name. Oracle stores records relating to each other in a table. A table consists of a number of records. Each field occupies one column and each record occupies one row.

32. SQL Compiler

Compiles SQL statements into a shared cursor. The SQL Compiler is made up of the parser, the optimizer, and the row source generator.

33. SQL Analyze

A tool in Oracle Tuning pack that analyzes and tunes problematic SQL statements that are causing the greatest impact on database performance.

34. SQL*Plus

Oracle tool used to execute SQL statements against an Oracle DB. It is an interactive SQL-based language for data manipulation, data definition and the definition of access rights for an Oracle DB. Often used as an end-user reporting tool.

35. SQL*Plus Worksheet

A tool with which administrators can execute SQL and PL/SQL commands and store them as files to reuse at a later time.

36. SQL Scratchpad

SQL Scratchpad is a low-overhead tool that simplifies SQL and PL/SQL development and general DB querying. Along with the Console Navigator, SQL Scratchpad offers a complete SQL development environment.

37. SQL buffer

The default buffer containing your most recently entered SQL command or PL/SQL block. SQL*Plus commands are not stored in the SQL buffer.

38. SQL script

It is a file containing SQL statements that you can run in SQL*Plus to perform DB administration quickly and easily.

39. SQL statement

It is a complete command/statement written in the SQL language. Portions of SQL statements/commands are called expressions, predicates, or clauses.

40. SQL*Loader

An Oracle tool used to load data from operating system files into Oracle DB tables. Reads and interprets input files. It is the most efficient way to load large amounts of data.

41. SQL*Net

Net8's precursor. An Oracle product that works with the Oracle Server and enables two or more computers that run the Oracle RDBMS or Oracle tools such as SQL*Forms to exchange data through a network. SQL*Net supports distributed processing and distributed database capability. SQL*Net runs over and interconnects many communications protocols.

42. SQL Trace

A basic performance diagnostic tool to help monitor and tune applications running against the Oracle server. SQL Trace lets you assess the efficiency of the SQL statements an application runs and generates statistics for each statement. The trace files produced by this tool are used as input for TKPROF.

43. SQL history

The complete set of application SQL data and statistics that are executed within the database environment. The statements can be added to the SQL History from a variety of sources, including the SQL cache, Oracle Trace, and .XDL files. The SQL History provides a consistent source of SQL data for all of the Tuning Pack applications that make tuning recommendations. The SQL History also prevents the Tuning Pack applications from impacting the production database environment when SQL information is needed for an analysis.

44. SQL object

SQL objects, located in the Navigator window, represent specific SQL statements that you can tune against the database session to which they are connected. SQL objects may be created, copied or deleted. The statements within them may be edited in a number of ways.

Note, however, that the objects become read-only after an explain plan is generated for the statement. To proceed with further editing, you must create a copy of the node using SQL=>Create Like command.

45. SQL reuse

One of the categories of application tuning. The Oracle Server maintains only one copy of a distinct SQL statement within the library cache to maximize memory and minimize redundant parsing and validating. To effectively use this feature, you must write duplicate SQL statements using identical structure and form (two statements are considered the same only when they match character for character, including spaces and punctuation). Oracle Expert compares your workload statements to determine if any can be rewritten to take advantage of the cache behavior and reports its findings.

46. SQLJ

Embedded SQL in Java. The standard that defines how SQL statements can be embedded in Java programs to access SQL data. A translator transforms the SQLJ programs to standard JDBC programs.

47. SQL apply mode

The mode in which log apply services automatically apply archived redo log information to a logical standby database by transforming transaction information into SQL statements and then executing the SQL statement to the logical standby database.

48. iSQL*Plus

It is a browser-based interface to SQL*Plus. It is a component of the SQL*Plus product. It enables you to use a web browser to connect to Oracle9i and perform the same actions as you would through the command-line version of SQL*Plus. You can use iSQL*Plus to write SQL*Plus, SQL and PL/SQL commands to:

Enter, edit, run and save SQL commands and PL/SQL blocks.
Calculate, and print query results.
List column definitions for any table.
Access and copy data between databases.
Perform database administration.

49. iSQL*Plus Server

The iSQL*Plus Server is located on the middle tier of the iSQL*Plus three-tier architecture. It comprises the SQL*Plus engine and the iSQL*Plus module. In combination with the web server provided by the Oracle HTTP Server, they provide the iSQL*Plus web enabled interface to Oracle9i.

60. Privileges

A privilege can be granted explicitly. For example, the privilege to insert records into the EMP table can be explicitly granted to the user SCOTT. Alternatively a privilege can be granted to a role (a named group of privileges), and then the role can be granted to one / more users. For example, the privilege to insert records into the EMP table can be granted to the role named CLERK, which in turn can be granted to the users SCOTT and BRIAN.

A schema object privilege is a privilege / right to perform a particular action on a specific schema object, different object privileges are available for different types of schema objects.

A system privilege is the right to perform a particular action, / to perform an action on any schema objects of a particular type. There are over 60 distinct system privileges.

61. Security

Three Schema Security Model

Typically all DB tables are stored in a single schema. Security options are then set to grant other schemas / users access to the data.

The 'Three Schema Security Model' for DB security:

Level 1 Schema - Owns all application data tables. e.g. APP_DATA

Level 2 Schema - Owns Views and Packages needed to access the application data e.g. APP_CODE

Level 3 Schema - Used for application users to connect to the DB. e.g. APP_USER

This arrangement makes it easy to do imports and exports.

Level 1 Schema - Grant the required SELECT, UPDATE, DELETE privileges to the Level 2 Schema.

Level 2 Schema - Objects in this schema use privileges granted by the first schema to access the data.

Level 3 Schema - Uses privileges granted and objects created by the second schema to access the data.

None of the 3 schemas should be given full DBA permissions. Application users should never be allowed to connect to the DB via a 'single' schema that owns all data and objects. Rather than the 'level 3 schema' being a single schema /logon /password, you may choose to implement multiple schemas, one for each user - in this case privileges should be granted to each level 3 schema using a ROLE.

To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base objects underlying the view. Views add two more levels of security for tables, column-level security and value-based security: A view can provide access to selected columns of base tables and also displays only selected rows (if a WHERE clause is used in the definition of the view)

62. Roles

It is a named group of related privileges. You can grant a role to users or other roles.

Any role can be granted to any DB user. GRANT role TO [user,] [role,]

The following default roles are provided only for backward compatibility with earlier versions of Oracle: CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE. It is recommended that you create new roles to suit the specific needs of your application.

A role can be granted system / schema object privileges.

Each role granted to a user is, at a given time, either enabled / disabled. This can be used to provide selective availability of privileges. A role can be granted to other roles (an indirect grant). However, a role cannot be granted to itself and cannot be granted circularly. For example, role A cannot be granted to role B if role B has previously been granted to role A. An indirectly granted role can be explicitly enabled / disabled for a user. However, by enabling a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

Secure Application Roles:

Secure application roles can only be enabled by authorized PL/SQL packages. This mechanism restricts the enabling of roles to the invoking application. Application developers no longer need to secure a role by embedding passwords inside applications. Instead, they create a secure application role and specify which PL/SQL package is authorized to enable the role.

Every user automatically belongs to the PUBLIC user group. As members of PUBLIC, users can see (select from) all data dictionary tables prefixed with USER and ALL. Additionally, a user can grant a privilege / a role to PUBLIC. All users can use the privileges granted to PUBLIC. You can grant / revoke any system privilege, object privilege, / role to PUBLIC. However, to maintain tight security over access rights, grant only privileges and roles that are of interest to all users to PUBLIC. Granting and revoking some system and object privileges to and from PUBLIC can cause every view, procedure, function, package, and trigger in the DB to be recompiled. PUBLIC has the following restrictions: You cannot assign tablespace quotas to PUBLIC, although you can assign the UNLIMITED TABLESPACE system privilege to PUBLIC. You can create DB links and synonyms as PUBLIC (using CREATE PUBLIC DATABASE LINK/SYNONYM), but no other schema object can be owned by PUBLIC.

63. What are rights?

Invoker Rights: An invoker-rights procedure executes with all of the invoker's roles and privileges. This is also the default for anonymous PL/SQL blocks. When using Invoker Rights the invoker needs privileges at runtime to access program references embedded in DML statements / dynamic SQL statements.

Definer Rights: A user of a definer-rights procedure requires only the privilege to execute the procedure; not on the underlying objects that the procedure accesses, because a definer-rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. When using Definer Rights it's essential that the procedure's owner has all the necessary object privileges for all referenced objects.

64. What is the starting "oracle error number"? What is meant by forward declaration in functions?

One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also

71. How many Integrity Rules are there and what are they?

There are Three Integrity Rules. They are as follows:

Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null

Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.

Business Integrity Rule: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

72. Physical Storage of the Data

The finest level of granularity of DB are the data blocks.

Data Block: One Data Block corresponds to specific number of physical DB space

Extent: Extent is the number of specific number of contiguous data blocks.

Segments: Set of Extents allocated for Extents. There are three types of Segments

Data Segment: Non Clustered Table has data segment data of every table is stored in cluster data segment

Index Segment: Each Index has index segment that stores data

Roll Back Segment: Temporarily store 'undo' information

12. What is a Transaction in Oracle?

A transaction is a Logical unit of work that compromises one / more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed / rolled back.

75. What is tablespace?

A tablespace is a logical storage unit. Multiple application objects (e.g. tables) can be stored in one tablespace. A DB can contain multiple tablespaces. A tablespace can be online / offline (not accessible), and can contain one / more datafiles, each of which can be online / offline. There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas. A tablespace can be set to read-only to eliminate the need to perform backup and recovery of large, static portions of the DB. A tablespace can be set to temporary to optimize the storage of temporary objects. Tablespaces can use one of two methods to keep track of free and used space: Dictionary-managed [A tablespace that uses the data dictionary to manage its extents has incremental extent sizes, which are determined by the storage parameters] / Locally managed [this is chosen when the tablespace is created and cannot be altered at a later date].

It is logical because a tablespace is not visible in the file system of the machine on which the DB resides. Each table, index and so on that is stored in an Oracle DB belongs to a tablespace. The tablespace builds the bridge between the Oracle DB and the file system in which the table's / index' data is stored. It is created with the create tablespace sql command.

There are three types of tablespaces in Oracle:

· Permanent tablespaces

· Undo tablespaces

· Temporary tablespaces

76. What are automatic locking and manual locking?

Automatic Locking

Oracle's automatically locks table data at the row level to minimize data contention. Oracle doesn't ever escalate row locks to block / table locks. Oracle may maintain several different types of row lock:

Exclusive lock mode: Prevents the resource from being shared. This lock mode is obtained to modify data. Only one exclusive lock can be placed on a resource (such as a row / a table).

Share lock mode: Allows the resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding a share lock to prevent concurrent access by a writer (who needs an exclusive lock). Many share locks can be placed on a single resource.

Manual Locking

Oracle's automatic locking can be overridden at two levels:

Per Session: As described above, the ALTER SESSION statement can set the transaction isolation level.

Per Transaction: Transactions that include the following SQL statements override Oracle's default locking

78. Tablespace, Data Files, Parameter File, Control Files

Tablespace: The table space is useful for storing the data in the DB. When a DB is created 2 tablespaces are created.

System Tablespace: This data file stores all the tables related to the system and DBA tables

User Tablespace: This data file stores all the user related tables, we should have separate table spaces for storing the tables and indexes so that the access is fast.

Data Files: Every Oracle Data Base has one / more physical data files. They store the data for the DB. Every datafile is associated with only one DB. Once the Data file is created the size cannot change. To increase the size of the DB to store more data we have to add data file.

Parameter Files: Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g.::

db_block_buffers = 500

db_name = ORA7

db_domain = u.s.acme lang

Control Files: Control files record the physical structure of the data files and redo log files. They contain the Db name, name and location of DB’s, data files, redo log files and time stamp.

79. What are the datatypes available in PL/SQL?

Every constant, variable, and parameter has a datatype/type, which specifies a storage format, constraints, and valid range of values. PL/SQL provides a variety of predefined datatypes.

Predefined Datatypes
User-Defined Subtypes
Datatype Conversion

Predefined Datatypes

A scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers that designate other program items. A LOB type holds values, called lob locators that specify the location of large objects (graphic images for example) stored out-of-line.

The scalar types fall into 4 families, which store number, character, Boolean, and date/time data, respectively.

User-Defined Subtypes

Each PL/SQL base type specifies a set of values and a set of operations applicable to items of that type. Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type.

Subtypes can increase reliability, provide compatibility with ANSI/ISO types, and improve readability by indicating the intended use of constants and variables. PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefines the subtypes CHARACTER and INTEGER as follows:

SUBTYPE CHARACTER IS CHAR;

SUBTYPE INTEGER IS NUMBER(38,0); -- allows only whole numbers

The subtype CHARACTER specifies the same set of values as its base type CHAR, so CHARACTER is an unconstrained subtype. But, the subtype INTEGER specifies only a subset of the values of its base type NUMBER, so INTEGER is a constrained subtype.

Datatype Conversion

Sometimes it is necessary to convert a value from one datatype to another. For example, if you want to examine a rowid, you must convert it to a character string. PL/SQL supports both explicit and implicit (automatic) datatype conversion.

Explicit Conversion: To convert values from one datatype to another, you use built-in functions. For example, to convert a CHAR value to a DATE or NUMBER value, you use the function TO_DATE or TO_NUMBER, respectively. Conversely, to convert a DATE or NUMBER value to a CHAR value, you use the function TO_CHAR.

Implicit Conversion: When it makes sense, PL/SQL can convert the datatype of a value implicitly. This lets you use literals, variables, and parameters of one type where another type is expected. In the example below, the CHAR variables start_time and finish_time hold string values representing the number of seconds past midnight. The difference between those values must be assigned to the NUMBER variable elapsed_time. So, PL/SQL converts the CHAR values to NUMBER values automatically.

Before assigning a selected column value to a variable, PL/SQL will, if necessary, convert the value from the datatype of the source column to the datatype of the variable. This happens, for example, when you select a DATE column value into a VARCHAR2 variable.

Likewise, before assigning the value of a variable to a DB column, PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column. If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use a datatype conversion function. PL/SQL cannot convert a VARCHAR2 value containing alphabetic characters to a NUMBER value.

84. What are blocks, extents and segments?

Blocks: Oracle DB data is stored in data blocks. One data block corresponds to a specific number of bytes of physical DB space on disk. (e.g. 1 block = 16 Kbytes)

Block size: The block size of the SYSTEM tablespace is termed the standard block size. This is set when the DB is created and can be any valid size. Early versions of Oracle supported just one block size set at DB creation, you can now specify up to four nonstandard block sizes, in addition to a standard block size. In the initialization file, you can configure subcaches within the buffer cache for each of these blocks sizes. Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block sizes.

The standard block size is used for the system tablespace and most other tablespaces. All partitions of a partitioned object must reside in tablespaces of a single block size. Multiple block sizes are useful primarily when transporting a tablespace from an OLTP DB to an enterprise DW.
Extents: An extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information. A small table may fit in a single extent; a large table may require multiple extents.

Segments: The level of logical DB storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure: Data /Index /Rollback/ Temporary segment. The allocation of extents for a given segment is handled different for Locally Managed Tablespaces and Dictionary Managed Tablespaces.

87. What is Redo log file?

It is an Oracle DB file that records information about each change to any block and are stored in order in which these changes were made. The set of redo log files for a DB is collectively known as the DBs redo log. Before Oracle changes data in a datafile it writes these changes to the redo log. If something happens to one of the datafiles, a backed up datafile can be restored and the redo that was written since replied, which brings the datafile to the state it had before it became unavailable.

Archived redo logs: When a DB is running in archive log modus, Oracle requires that online redo logs be archived before they're overwritten by a log switch. The archiving happens either manually / automatically by the archive process.

Starting the arch process

An online redo log is usually archived by arch. There are two ways to start arch:

First by the init parameter, this requires a restart of the DB log_archive_start.

The second way is to manually start the archive: alter system archive log start

88. What is the result of comparing NULL with NULL?

NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL.

98. What happens if a procedure that updates a column of table X is called in a DB trigger of the same table? Mutation of table occurs

99. What is a mutating and constraining table?

"Mutating" means "changing". A mutating table is a table that is currently being modified by an update / delete / insert a table that might need to be updated by the effects of a declarative delete cascade referential integrity constraint. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign / unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

There are several restrictions in Oracle regarding triggers:

1. A row-level trigger can’t query / modify a mutating table. (New and Old still can be accessed by the trigger).

2. A statement-level trigger cannot query / modify a mutating table if the trigger is fired as the result of a CASCADE delete, Etc.

108. What are external procedures? Why and when they are used?

External procedures are extended stored procedures only. They let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

109. What is Overloading of procedures?

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

e.g. DBMS_OUTPUT put_line

110. What are the modes of parameters that can be passed to a procedure?

IN, OUT, IN-OUT parameters

111. What are the components of a PL/SQL Block?

Declarative part, Executable part and Exception part Datatypes PL/SQL (/)

A set of related declarations and procedural statements is called block.

114. Is there a PL/SQL Engine in SQL*Plus?

No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the DB engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the DB individually.

115. Where the Pre_defined_exceptions are stored?

In the standard package: Procedures, Functions and Packages

116. What is Over Loading and what are its restrictions?

Overloading means an object performing different functions depending upon the no. of parameters / the data type of the parameters passed to it.

118. How many rows will the following SQL return: Select * from emp Where rownum < 10; 9 rows

119. How many rows will the following SQL return: Select * from emp Where rownum = 10; No rows

120. Which symbol precedes the path to the table in the remote DB? @

121. What is the parameter substitution symbol used with INSERT INTO command? and

122. What are the min. extents allocated to a rollback extent? 2

123. How many minimum groups are required for a matrix report? 4

124. A table has the following data: [[5, Null, 10]]. What will the average function return? 7.5

125. What is the maximum no. of statements that can be specified in a trigger statement? Once

126. Output of the following query: SELECT TRUNC(1234.5678,-2) FROM DUAL; 1200

127. What is the effect of synonym and table name used in same Select statement? Valid

128. What is the length of SQL integer? 32 bit length

129. Is Sysdate a system variable / a system function? System Function

130. What are various types of joins? Equijoin, Nonequijoin, self join, outer join

131. What are the types of Notation? Position, Named, Mixed and Restrictions

132. Why ‘Create or Replace’ and not Drop and recreate procedures? So that Grants are not dropped

133. What are the advantages of clusters? Access time reduced for joins

134. What are the disadvantages of clusters? The time for Insert increases

135. What are the datatypes supported by oracle (INTERNAL)? Varchar2, Number, Char, MLSLABEL

137. Which date function is used to find the difference between two dates? MONTHS_BETWEEN

138. What operator performs pattern matching? LIKE operator

139. What operator tests column for the absence of data? IS NULL operator

140. How many number of columns can table have? Range from 1 to 254

141. Which command displays the SQL command in the SQL buffer, and then executes it? RUN

142. What command is used to get back the privileges offered by the GRANT command? REVOKE

143. Is space acquired in blocks / extents? Extents

144. Which function is used to find the largest integer less than / equal to a specific value? FLOOR

145. What is subset of SQL commands used to manipulate Oracle DB structures, including tables? DDL

146. !=, <>, ^ = all denote the same operation. State true / false True

147. EXISTS, SOME, ANY are operators in SQL. State true / false True

148. Can you use select in FROM clause of SQL select? Yes

149. Can you have two functions with the same name in a PL/SQL block? Yes

150. Can you have two stored functions with the same name? Yes

151. Can null keys be entered in cluster index, normal index? Yes

152. Can you define multiple savepoint? Yes

153. Can you Rollback to any savepoint? Yes

154. Can you use %RowCount as a parameter to a cursor? Yes

155. Is the assignment given below allowed: ABC = PQR (Where ABC and PQR are records) Yes

156. Is this for loop allowed: For x in andStart .. andEnd Loop Yes

157. Can dual table be deleted, dropped / altered / updated / inserted? Yes

158. If content of dual is updated to some value computation takes place / not? Yes

159. If any other table same as dual is created would it act similar to dual? Yes

160. Are views automatically updated when base tables are updated? Yes

161. Can functions be overloaded? Yes

162. Can 2 functions have same name and input parameters but differ only by return datatype No

163. Can views be specified in a trigger statement? No

164. Can we define exceptions twice in same block? No

165. Can you call a stored function in the constraint of a table? No

166. Can Long/Long RAW be clustered? No

167. Can you alter synonym of view / view? No

168. Can you create index on view? No

169. Can a trigger written for a view? No

170. Can you increase the size of a tablespace? How? Yes, by adding datafiles to it

171. If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure? Yes

172. Is the query below allowed: Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15)) Yes

173. Can DB trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed. Yes, DB trigger would fire

174. Insert statement followed by create table statement followed by rollback? Will the rows be inserted? No

175. Which of the following is not a schema object: Index, table, public synonyms, trigger / package? Public synonyms

176. If all the values from a cursor have been fetched and another fetch is issued, the output will be: error, last record / first record? Last Record

177. Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be? 3

178. Assume that there are multiple DBs running on one machine. How can you switch from one to another? Changing the ORACLE_SID

179. What is the result of the following SQL: Select 1 from dual; UNION

Select 'A' from dual; Error

181. What are the wildcards used for pattern matching.

For single character substitution and % for multi-character substitution

182. What is the use of the DROP option in the ALTER TABLE command?

It is used to drop constraints specified on the table.

183. Why does the following command give a compilation error? DROP TABLE andTABLE_NAME;

Variable names should start with an alphabet. Here the table name starts with an 'and' symbol.

184. Which command displays the SQL command in the SQL buffer, and then executes it

Use the LIST / L command to get the recent one from SQL Buffer

185. How do I write a code which will run a SQL query and mail the results to a group?

Use DBMS_JOB for scheduling a code job and DBMS_MAIL to send the results through email.

186. For which relational operators in where clause, index is not used?

, like '% ...' is NOT functions, field +constant, field || ''

187. Can you increase the size of datafiles? How?

No (for Oracle 7.0), and Yes (for Oracle 7.3 by using the Resize clause ----- Confirm!!).

188. Can you pass parameters in packages? How?

Yes. You can pass parameters to procedures / functions in a package.

189. While designing in client/server what are the 2 important things to be considered?

Network Overhead (traffic), Speed and Load of client server

190. What is clustered index?

In an indexed cluster, rows are stored together based on their cluster key values.

191. Can Check constraint be used for self referential integrity? How?

Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

192. If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments / will it terminate?

It will terminate (Please check).

193. When are indexes created?

Index are created when table is queried for less than 2% / 4% to 25% of the table rows

194. What is the use of Control files?

Contains pointers to locations of various data files, redo log files, etc

195. What are the various types of RollBack Segments?

Public Available to all instances

Private Available to specific instance

196. Describe Oracle DB's physical and logical structure?

Physical: Data files, Redo Log files, Control file.

Logical: Tables, Views, Tablespaces, etc.

197. What are the inline and the precompiler directives?

The inline and precompiler directives detect the values directly

198. How do you use the same lov for 2 columns?

We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code

199. What is the use of Data Dictionary?

Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc

200. What are states of a rollback segment?

The various states of a rollback segment are:

ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID

201. What is the significance of ‘and’ and ‘andand’ operators in PL SQL?

‘and’ operator means that the PL SQL blocks requires user input for a variable.

‘andand’ operator means that the value of this variable should be the same as inputted by the user previously for this same variable.

204. 3 Steps in creating DB

Creating physical location for data in tables and indexes to be stored in DB

To create the file that still store log entries

To create logical structure of data dictionary

This is accomplished by create DB

1. Back up existing DB

2. Create / Edit the init.ora file

3. Verify the instance name

4. Start Application management DB tool

5. Start instance

6. Create and Backup the new DB.

206. Can we declare a column having number data type and its scale is larger than precision

Ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84)?

Yes. We can declare a column with above condition. Table created successfully.

207. In a Distributed DB System can we execute two queries simultaneously? Justify?

As Distributed DB system based on 2 phase commit, one query is independent of 2^nd query so of course we can run.

208. "IS" and "AS" while creating procedure.

Both are equivalent. Just replacement of DECLARE keyword in declarative section.

209. What is the use of CASCADE Constraints?

When this clause is used with the Drop command, a parent table can be dropped even when a child table exists.

210. Why u need indexing? Where that is stored and what you mean by schema object? for what purpose we use views?

We can’t create an Index on Index. Index is stored in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table...So that is a view.

211. What are master and detail Relationships?

The various Master and Detail Relationship are

NonIsolated: The Master cannot be deleted when a child is existing

Isolated: The Master can be deleted when the child is existing

Cascading: The child gets deleted when the Master is deleted.

212. What are the Various Block Coordination Properties?

The various Block Coordination Properties are

Immediate: Default Setting. The Detail records are shown when the Master Record is shown.

Differed with Auto Query: Oracle Forms defer fetching the detail records until the operator navigates to the detail block.

Differed with No Auto Query: The operator must navigate to the detail block and explicitly execute a query

214. What is concurrency?

Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the DB are Exclusive: The exclusive lock is useful for locking the row when an insert, update / delete are being done. This lock should not be applied when we do only select from the row. Share lock: we can do the table as Share_Lock as many share_locks can be put on the same resource.

215. What are the Pct Free and Pct Used

Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40

216. What is Row Chaining?

The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks.

217. What is a 2 Phase Commit?

2 Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the DB so that all the users see the same values. It contains DML statements / Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.

Prepare Phase: Global coordinator asks participants to prepare

Commit Phase: Commit all participants to coordinator to Prepared, Read only / abort Reply

219. What are Codd Rules?

Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 Codd rules and Oracle Satisfies 11 of the 12 rules and is the only RDBMS to satisfy the maximum number of rules.

221. Deleting the Duplicate rows in the table

We can delete the duplicate rows in the table by using the Rowid

222. What is a pseudocolumn? Name them?

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, / delete their values. CURRVAL, NEXTVAL, LEVEL, ROWID, ROWNUM

223. How to store directory structure in a DB?

We can do it by the following command: create or replace directory as 'c:\tmp'

26. What is Multi Threaded Server (MTA)?

In a Single Threaded Architecture (/ a dedicated server configuration) the DB manager creates a separate process for each DB user. But in MTA the DB manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the DBs memory requirement and resources.

227. What is Functional Dependency

Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R

228. What is Auditing?

The DB has the ability to audit all actions that take place within it.

a) Login attempts, b) Object Access, c) DB Action Result of Greatest(1,NULL) / Least (1,NULL) NULL

229. How can you avoid indexes?

To make index access path unavailable

Use FULL hint to optimizer for full table scan

Use INDEX / AND-EQUAL hint to optimizer to use one index / set to indexes instead of another.

Use an expression in the Where Clause of the SQL.

230. What is a forward declaration? What is its use?

PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.

231. What are actual and formal parameters?

Actual Parameters: Subprograms pass information using parameters. The variables / expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount.

Eg. raise_salary(emp_num, amount);

Formal Parameters: The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and increase.

Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

232. What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size?

In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 and 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 and 9MB) open_cursors was changed from 200 to 300 (std values are 200 and 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of DB creation}. The initial SGA was around 4MB when the server RAM was 32MB and the new SGA was around 13MB when the server RAM was increased to 128MB.

233. What is a package cursor?

A package cursor is a cursor which you declare in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

234. If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?

Yes, because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.

235. What are the various types of queries?

Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries

236. What is a transaction?

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

237. Is there a limit on the size of a PL/SQL block?

Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package / procedure.
SQL> select * from dba_object_size where name = 'procedure_name'

238. What operator performs pattern matching?

Pattern matching operator is LIKE and it has to used with two attributes

% % means matches zero/ more characters

_ (underscore) _ means matching exactly one character

239. What are the privileges that can be granted on a table by a user to others?

Insert, update, delete, select, references, index, execute, alter, all.

240. What is the use of DESC in SQL?

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.

Explanation: The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

241. What is cluster? Cluster index and non cluster index

Clustered Index: - A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.

Non-Clustered Index: - A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consists of the data pages. Instead the leaf node contains index rows.

242. What’s the back end processes when we type "Select * from Table"?

First it will look into the System Global Area (SGA) weather the query is been executed earlier.

If it exists, it would retrieve the same output present in memory.

If not the query we typed is complied and the resulting parse tree and execution plan is been stored in SGA. Then query gets executed and output is given to the application.

243. What is FORALL keyword?

The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not for loop.

244. What is Pragma?

It is compiler directive which serve as instructions to PL/SQL compiler. The compiler will act on Pragma during the compilation of the block.

Type of Pragma

• Pragma Autonomous_transaction

• Serially reusable

• Exception init

• Restrict_references

• interface

Pragma Autonomous_transaction: It is a transaction that is started within the context of another transaction known as the parent transaction but it is independent of it. Autonomous_transaction can be committed / rollback independent of the state of the parent transaction.

Note: The Autonomous_transaction Pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit / roll back those operations, then resume the main transaction.

Restrict_References: they place restrictions on what kinds of SQL statements and packages variables can be in a function. In addition to compiling the function as normal, the compiler needs to verify that the restrictions are met. This Pragma specifies the purity level of a given function.

Exception init: with the help of this u can associate a named exception with a particular oracle error. By this u can trap the error specifically rather than using the when others handler.

245. What is Dynamic SQL?

It is only possible to execute Data Manipulation Language (DML) statements (select, insert, update) directly in PL/SQL. These DML statements have to be fixed statements that mean the complete statement, containing all the names of the DB objects (etc.), have to be known at runtime. The reason for this is that PL/SQL uses early (static) binding. Binding is done at compile time. The definitions of the DB objects are looked up in the data dictionary at this time, not at runtime. Without Dynamic SQL, users would not be able to execute Data Definition Language (DDL) statements (create, drop, grant, revoke), session control language (SCL) statements (alter session, set role) / statements, where the whole statement / parts of the statement are unknown until runtime.

Methods: In PL/SQL 2.1 (Oracle Server Enterprise edition 7.1.X) Oracle introduced the DBMS_SQL package to execute dynamic SQL statements in PL/SQL. It is a PL/SQL built-in, which offers a programmatic API and allows developers to include DDL and SCL statements in there code. In Oracle 8i Native Dynamic SQL (dynamic SQL for short) has been introduced. Dynamic statements can be placed directly into PL/SQL code using the EXECUTE IMMEDIATE statement.

246. What is Bulk binding?

A new feature called "bulk binds" was added to PL/SQL block in Oracle 8i. Bulk binds enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time. Bulk binds also allow many similar DML statements to be executed with one call instead of requiring a separate call for each. For certain types of PL/SQL programs, using bulk binds will reduce CPU usage and make the code run faster. A context switch occurs every time the PL/SQL engine calls the SQL engine to parse, execute, / fetch from a cursor. Since context switches use CPU time, reducing the number of context switches will reduce the amount of CPU time used. In addition, the SQL engine can often reduce the number of logical reads required when multiple rows are fetched in one call. Reducing logical reads also saves CPU time.

248. What are the Back ground processes in Oracle and what are they?

There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system. The various background processes in oracle are

DB Writer (DBWR): DB Writer Writes Modified blocks from DB buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
LogWriter (LGWR): LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into an online redo log file.
System Monitor (SMON): The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
Process Monitor (PMON): The Process Monitor performs process recovery when user Process fails. PMON clears and frees resources that process was using.
Checkpoint (CKPT) :: At Specified times, all modified DB buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of DB to indicate the
most recent checkpoint
Archieves (ARCH): The Archiever copies online redo log files to archival storage when they are busy.
Recoveror (RECO): The Recoveror is used to resolve the distributed transaction in network
Dispatcher (Dnnn): The Dispatcher is useful in Multi Threaded Architecture
Lckn: We can have up to 10 lock processes for inter instance locking in parallel SQL.

249. Oracle DB uses three types of file structure

Data files: store actual data for tablespace, which is a logical unit of storage. Every tablespace has one / more data file to store actual data for tables, indexes, and clusters. Data is read and write to data file as needed.
Redo log file: Two / more redo log file make up a logical redo log, which is used to recover modifications that have not been written to data files in event of power outage.
Control file: Used at start up to identify the DB and determine which redo log files and data file are created.

250. Parametric Cursor: The cursor in which we can pass value when it is being opened

251. What are object group?

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy / reference them in another module.

252. What are referenced objects?

Referencing allows you to create objects that inherit their functionality and appearance from other objects. Referencing an object is similar to copying an object, except that the resulting reference object maintains a link to its source object. A reference object automatically inherits any changes that have been made to the source object when you open / regenerate the module that contains the reference object.

253. Can you store objects in library?

254. SQL Statement Execution:

Reserves area in memory called Private SQL Area.
Populate this area with application data.
Process data in memory area
Free the, memory area when exec is complete.

Which command executes the contents of a specified file?

START / @.

255. What is Pragma EXECPTION_INIT? Explain the usage?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

256. Explain the usage of WHERE CURRENT OF clause in cursors?

WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor. DB Triggers

258. Explian rowid, rownum? What are the pseudo columns we have?

ROWID - Hexa decimal number each and every row having unique. Used in searching ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysis. Other Pseudo Column is

NEXTVAL, CURRVAL Of sequence are some examples

259. What is Raise_application_error?

Raise_application_error is a procedure of package DBMS_STANDARD which allows issuing a user_defined error messages from stored sub-program / DB trigger.

260. What is an Exception? What are types of Exception? How many types of Exceptions are there?

Exception is the error handling part of PL/SQL block. Exception is an error situation, which arises during program execution. When an error occurs exception is raised, normal execution is stopped and control transfers to exception-handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally defined (system-defined / pre-defined) / User-defined exception.

An Exception cannot be declared twice in the same block.
Exceptions declared in a block are considered as local to that block and global to its sub-blocks.
An enclosing block cannot access Exceptions declared in its sub-block where as it possible for a sub-block to refer its enclosing Exceptions.

There are 2 types of exceptions. They are

System Exceptions e.g. When no_data_found, When too_many_rows
User Defined Exceptions e.g. My_exception exception When My_exception then

Some of Predefined exceptions are.

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

INVALID_NUMBER

LOGON_DENIED

NOT_LOGGED_ON

PROGRAM-ERROR

STORAGE_ERROR

TIMEOUT_ON_RESOURCE

VALUE_ERROR

ZERO_DIVIDE, etc

261. How to disable multiple triggers of a table at a time?

ALTER TABLE

DISABLE ALL TRIGGER

262. Is there any query which is use to find the case sensitivity in each records in DB through visual basic?

For case sensitive string comparison in SQL one has to use substring() and ascii() functions in the following way.

Get first character of both strings using substring function as substring(str1, 1, 1)

Find ascii value of both characters and compare.

Put statements 1 and 2 in loop to advance to next character

For example if (ascii(substring(str1, @pos, 1)) = ascii(substring(str2, @pos, 1)) then @pos = @pos + 1 ....

263. Which system tables contain information on privileges granted and privileges obtained?

USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

264. Which system table contains information on constraints on all the tables created?

USER_CONSTRAINTS, system table contains information on constraints on all the tables created

265. How can I hide a particular table name of our schema?

You can hide the table name by creating synonyms. E.g. you can create a synonym y for table x

Create synonym y for x;

266. How can we backup the SQL files and what is SAP?

You can backup the SQL files through backup utilities / some backup command in SQL. SAP is ERP software for the organization to integrate the software.

267. When we give SELECT * FROM EMP; How does oracle respond?

When u give SELECT * FROM EMP; the server check all the data in the EMP file and it displays the data of the EMP file

268. Write query Max 5 salaries?

Select * from emp e where 5 > (select count(*) from emp where sal>e.sal)

269. Write the query nth max sal

Select distinct (a.sal) from emp a where andn=select count(distinct(b.sal) from emp b where a.sal<=b.sal

270. Write the query odd and even numbers?

Select * from emp where (rowed, 1) in (select rowed, mod(rownum,2) from emp)

271. How write a SQL statement to query the result set and display row as columns and columns as row?

TRANSFORM Count(Roll_no) AS CountOfRoll_no

SELECT Academic_Status

FROM tbl_enr_status

GROUP BY Academic_Status

PIVOT Curnt_status;

272. Output of the following query? Select Replace (Translate (Ltrim (Rtrim ('!!ATHEN!!','!'),'!'), 'AN', '**'), '*',' TROUBLE') from dual;

TROUBLETHETROUBLE

273. What does the following query do? SELECT SAL + NVL(COMM,0) FROM EMP;

This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.

274. How to write a SQL statement to find the first occurrence of a non-zero value?

There is a slight chance the column "a" has a value of 0 which is not null. In that case, you’ll loose the information. There is another way of searching the first not null value of a column:
select column_name from table_name where column_name is not null and rownum<2;

275. Given an un-normalized table with columns:

Delete from where rowid not in (select max(rowid) from group by name).

276. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?

The privilege receiver can further grant privileges he/she has obtained from the owner to any other user.

277. How to find out the 10^th highest salary in SQL query?

Table - Tbl_Test_Salary

Column - int_salary

select max(int_salary)

from Tbl_Test_Salary

where int_salary in

(select top 10 int_Salary from Tbl_Test_Salary order by int_salary)

278. What does the following query do?

SELECT SAL + NVL(COMM,0) FROM EMP;

It gives the added value of sal and comm for each employee in the emp table.

NVL(null value) replaces null with 0.

279. How to find 2^ND maximum value from a table?

select max(field1) from tname1 where field1=(select max(field1) from tname1 where field1<(select max(field1) from tname1);

Field1- Salary field

Tname= Table name.

280. I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.

Select col1 from tab1 where col1 in (select max(col1) from tab1 group by col1 having count(col1) > 1 )

281. How to find out the DB name from SQL*PLUS command prompt?

Select * from global_name;

This will give the DB name which you are currently connected to

282. How to retrieving the data from 11th column to a n^th column in a table

Select * from EMP where rowid in (Select rowid from EMP where rownum <=andupto minus Select rowid from EMP where rownum

283. How to display duplicate rows in a table?

Select * from emp group by (empid) having count(empid)>1

284. What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000?

UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;

sal = 11000, comm = 1000.

285. What will be the output of the following query?

SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );

NO. Explanation: The query checks whether a given string is a numerical digit.

286. Given an employee and manager table, write a SQL syntax that could be used to find out an employee's manager's manager, assuming all managers are in the employee table.

It is assumed that u have created a single table for populating data of Employee and Manager (Manager is also an Employee so she/he will be residing in the same table)

The sample data would be like this

EmployeeID ----- EmployeeName ------- ManagerID

1 Akhtar 0

2 Bilal 1

3 Faheem 2

** This query will return "Akhtar" for given EmployeeID = 3.

SELECT m.EmployeeName FROM #EmpTemp m WHERE m.EmloyeeID = (SELECT e.ManagerID FROM #EmpTemp e WHERE e.EmloyeeID = (Select t.ManagerID FROM #EmpTemp t WHERE t.EmloyeeID = 3))

** This is solution is workable in case of 3-levels only not even to 2-levels of hierarchy. An n-level solution can be achieved by writing a Recursive stored procedure and that will also be given soon.

287. When using a count(distinct) is it better to use a self-join / temp table to find redundant data, and provide an example?

Instead of this we can use GROUP BY Clause with HAVING condition.

For ex, Select count(*),lastname from tblUsers group by lastname having count(*)>1

This query returns the duplicated lastnames values in the lastname column from tblUsers table.

288. Why do I get "Invalid Cursor State" errors when I insert/update/delete data with executeQuery()?

IF u get this error, then there is a problem with the Java program, U may open the cursor with the Readonly mode.Change the Resultset type then you will get it.This is not problem with the Oracle

Can we call user defined packages in SQL statements?

I do not agree that we can't call user defined packages in the SQL statements.

Instead we can call user defined packaged functions in the SELECT statement.

e.g. SELECT pkg.test(10) FROM DUAL;

289. What command is used to create a table by copying the structure of another table?

CREATE TABLE.. AS SELECT command

Explanation: To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.

CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;

If the WHERE condition is true, then all the rows / rows satisfying the condition will be copied to the new table.

290. How to copy sql table?

COPY FROM DB TO DB action -

destination_table (column_name, column_name...) USING query

eg: copy from scott/tiger@ORCL92 - to scott/tiger@ORCL92-

create new_emp – using select * from emp;

291. There is an eno and gender in a table. Eno has primary key and gender has a check constraints for the values 'M' and 'F'. While inserting the data into the table M was misspelled as F and F as M. What is the update statement to replace F with M and M with F?

update set gender=

case where gender='F' Then 'M'

where gender='M' Then 'F'

292. There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected columns of the right table. E.g. in this query which uses the key-word LEFT OUTER JOIN. Syntax though varies across DBs. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id

SQL Server/Sybase: Employee_table.Dept_id(+) = Dept_table.Dept_id

293. DBMS_OUTPUT package à to write information à Output Buffer à this buffer à displayed on screen à from SQL*Plus à if SET SERVEROUTPUT ON; command is issued

DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow.

In that case, set the buffer size to a larger value, Ex: SET SERVEROUTPUT ON SIZE 200000

If you haven't cleared the DBMS_OUTPUT buffer with the disable/enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.

Note that DBMS_OUTPUT doesn't print blank/NULL lines.

To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP;

294. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?

PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. Choose one of the following solutions:

1. Grant direct access on the tables to your user. Do not use roles!

2. GRANT select ON scott.emp TO my_user;

3. Define your procedures with invoker rights (Oracle 8i and higher);

4. Move all the tables to one user/schema.

295. How can I protect my PL/SQL source code?

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available. Syntax: wrap iname=myscript.sql oname=xxxx.plb

296. How can one see if somebody modified any code?

Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:

SELECT OBJECT_NAME, TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,

TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,

STATUS FROM USER_OBJECTS WHERE LAST_DDL_TIME > 'andCHECK_FROM_DATE';

297. How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where a certain table, field / expression is referenced in your PL/SQL source code.

Select type, name, line from USER_SOURCE where upper(text) LIKE '%andKEYWORD%';

298. How can one keep a history of PL/SQL code changes?

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema/DB level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code, should someone make any catastrophic changes. Look at this example:

CREATE TABLE SOURCE_HIST -- Create history table

AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE * FROM USER_SOURCE WHERE 1=2;

CREATE or REPLACE TRIGGER change_hist -- Store code in history table

AFTER CREATE ON SCOTT.SCHEMA -- Change Scott to your schema name

DECLARE

BEGIN

IF DICTIONARY_OBJ_TYPE in ('Procedure', 'Function', 'Package', 'Package Body', 'Type') then

-- Store old code in SOURCE_HIST table

INSERT INTO SOURCE_HIST

Select SYSDATE, USER_SOURCE * from USER_SOURCE where

TYPE = DICTIONARY_OBJ_TYPE and NAME = DICTIONARY_OBJ_NAME;

End if;

EXCEPTION WHEN OTHERS THEN raise_application_error(-20000, SQLERRM);

END; /

299. Can one read/write files from PL/SQL? Can one execute an operating system command from PL/SQL?

Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command. Copy this example to get started:

Declare

fileHandler UTL_FILE.FILE_TYPE;

Begin

fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');

UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');

UTL_FILE.FCLOSE(fileHandler);

Exception

When utl_file.invalid_path then

raise_application_error(-20000, 'ERROR: Invalid path for file / path not in INIT.ORA.');

End; /

There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the pre-compiler languages, OCI / Perl with Oracle access modules) to act as a listener on a DB pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different DB pipe. In Oracle8 one can call external 3GL code in a dynamically linked library (DLL / shared object). One just writes a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.

300. Can one call DDL statements from PL/SQL?

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “Execute Immediate” statement. Users running Oracle versions below 8i can look at the “DBMS_SQL” package.

Begin

Execute Immediate 'CREATE TABLE X(A DATE)';

End;

Note: The DDL statement in quotes should not be terminated with a semicolon.

301. Can one use dynamic SQL statements from PL/SQL?

Starting from Oracle8i one can use the “Execute Immediate" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples.

Execute Immediate 'Create table

( )';

-- Using bind variables...

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';

Execute Immediate sql_stmt USING dept_id, dept_name, location;

-- Returning a cursor...

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';

Execute Immediate sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 andabove) to execute dynamic statements.

CREATE or REPLACE PROCEDURE DYNSQL AS

cur integer;

rc integer;

BEGIN

cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);

rc := DBMS_SQL.EXECUTE(cur);

DBMS_SQL.CLOSE_CURSOR(cur);

END; /

302. How does one get the value of a sequence into a PL/SQL variable?

One can’t use sequences directly from PL/SQL. Oracle prohibits this:

i := sq_sequence.NEXTVAL;

However, one can use embedded SQL statements to obtain sequence values:

select sq_sequence.NEXTVAL into :i from dual;

303. How does one loop through tables in PL/SQL?

Look at the following nested loop code example.

DECLARE

CURSOR dept_cur IS SELECT deptno FROM dept ORDER BY deptno;

-- Employee cursor all employees for a dept number

CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS SELECT ename FROM emp WHERE deptno = v_dept_no;

BEGIN

FOR dept_rec IN dept_cur LOOP

dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));

FOR emp_rec in emp_cur(dept_rec.deptno) LOOP

dbms_output.put_line('...Employee is '||emp_rec.ename);

END LOOP;

END; /

304. How often should one ‘Commit’ in a PL/SQL loop? What is the best commit strategy?

One should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 errors (Snapshot too old). The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors. To fix this problem one can easily rewrite code like this:

FOR records IN my_cursor LOOP

...do some stuff...

COMMIT;

END LOOP;

COMMIT;

... to ...

FOR records IN my_cursor LOOP

...do some stuff...

i := i+1;

If mod(i, 10000) = 0 Then -- Commit every 10000 records

COMMIT;

END IF;

END LOOP;

COMMIT;

If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.

NOTE: Although fetching across COMMIT works with Oracle, is not supported by the ANSI standard.

305. Is there a limit on the size of a PL/SQL block?

Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package / procedure:

SQL> select * from dba_object_size where name = 'procedure_name';

306. Can one pass an object / table as an argument to a Remote Procedure?

The only way to reference an object type between DBs is via a DB link. Note that it is not enough to just use "similar" type definitions. Look at this example:

-- DB A receives a PL/SQL table from DB B

CREATE or REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS

BEGIN

-- do something with TabX from database B

null;

END; /

-- DB B sends a PL/SQL table to DB A

CREATE orREPLACE PROCEDURE pcalling IS TabX DBMS_SQL.VARCHAR2S@DBLINK2;

BEGIN

pcalled@DBLINK2(TabX);

END; /

307. Sysdate

Select sysdate from dual; è displays system date

Select add_months(sysdate,4) from dual; è adds 4 months to the system date

Select add_months(sysdate,-4) from dual; è adds -4 months (i.e.) subtracts 4 from the system date

308. Parameter NOCOPY

CREATE or REPLACE PROCEDURE ( NOCOPY )

BEGIN

;

END;                         /

Note: If a subprogram exists with an unhandled exception, the value assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, the changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."

309. How to avoid using cursors? What to use instead of cursor and in what cases to do so?

just use subquery in for clause

ex:

for emprec in (select * from emp)

loop

dbms_output.put_line(emprec.empno);

end loop;

no exit statement needed

implicit open,fetch,close occurs

310. Write the order of precedence for validation of a column in a table? i. Done using DB triggers. ii. Done using Integrity Constraints.

i and ii

311. Give the structure of the function?

FUNCTION name (argument list .....) Return datatype is

Local variable declarations

Begin

Executable statements

Exception

Execution handlers

End;

312. Explain how procedures and functions are called in a PL/SQL block?

Function is called as part of an expression.

sal := calculate_sal ('a822');

calculate_bonus ('A822');

313. What will happen after commit statement?

Cursor C1 is Select empno, ename from EMP;

Begin

Open C1;

Loop

Fetch C1 into

eno.ename;

Exit When C1 %notfound;

Commit;

End loop;

End;

The cursor having query as SELECT.... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

314. How we can create a table in PL/SQL block. Insert records into it? Is it possible by some procedure / function? Please give example...

CREATE or REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)

AS l_stmt VARCHAR2(200);

BEGIN

DBMS_OUTPUT.put_line('STARTING ');

l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';

execute IMMEDIATE l_stmt;

DBMS_OUTPUT.put_line('end ');

EXCEPTION

WHEN OTHERS THEN DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);

END;

315. How packaged procedures and functions are called from the following? a. Stored procedure / anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS

a) PACKAGE NAME.PROCEDURE NAME (parameters);

variable := PACKAGE NAME.FUNCTION NAME (arguments);

EXEC SQL EXECUTE

b) BEGIN

PACKAGE NAME.PROCEDURE NAME (parameters)

variable := PACKAGE NAME.FUNCTION NAME (arguments);

END;

END EXEC;

c) EXECUTE PACKAGE NAME.PROCEDURE

If the procedures don’t have any out / in-out parameters a function can’t be called.

Active set: A set of rows return by a multi-row query.

Export: Putting data of tables in file, which can be, handles by OS.

Auditing: It is used for noting down user's activity and statistics about the operations in data objects. The auditing are Statement, Privilege, and Object. It is done to audit statement activity .The auditing information about date and time of information, nature of operation is stored in table AUD$ which is used by user system.

Audit select on itemmaster;

Then application auditing is done and stored in table:

· To record the usage of privilege

· To record the activity on object

Nature of Auditing: Auditing is done on per session basis-one record is generated / per statement basis per session/statement. Audit any allows user to audit any schema object in the DB.

Table partitioning: Table partitioning divides table data between two / more tablespaces and physical data file on separate disk. We can use it to improve transaction throughout and certain type of queries for large tables.

Restriction:

· A table that is a part of cluster can't be partitioned.

· A table can be partitioned based on ranges column values only.

· Attribute of partitioned table can't include long, long raw / any lob data type.

· Bitmap indexes can't be defined on partitioned tables.

Rolling Forward: To reapply to Data file all changes that are recorded in Redo log file due to which datafile contains committed and uncommitted data

Shared Pool Performance

The shared pool is one of the memory structures in SGA .It is comprised of the data dictionary and the library cache. Check v$sgastat. The data dictionary cache buffers data dictionary objects that contain data about tables, indexes, users and all other objects. The Library Cache/SQL Cache buffers previously executed queries, so that they need not be reloaded and reparsed if user calls them again. Otherwise if the information is not in the buffer then oracle must get it from disk. The V$LIBRAY CACHE View stores performance data for library cache and V$ROWCACHE view stores performance data for the data dictionary cache. Sometime we may have to increase the value of initialization parameter SHARED_POOL_SIZE to improve the performance.

Oracle Naming Conventions

When designing a DB it's a good idea to follow some sort of naming convention. This will involve a little thought in the early design stages but will save significant time when maintaining the finished system. It's less important which exact conventions you choose to follow. The benefits of using a naming convention are more to do with human factors than any system limitations, but this does not make them any less important.

Table Names                Table names are plural, field name is singular

Field Names                 Fields should be unique within the DB schema.

Primary Key Fields       Indicate by appending _pk

Index names Name the Primary Key index as idx__pk

Constraints                  Primary and Unique constraints will be explicitly named.

Name the Primary Key Constraint as pk_

Name a Foreign Key Constraint as fk_

View Names View names are plural, field name is singular

PL/SQL

Prefix scalar variable names with v_

Prefix global variables (including host / bind variables) with g_

Prefix constants with c_

Prefix procedure / function call parameters (including sql*plus substitution parameters) with p_

Prefix record collections with r_ (alternatively suffix with _record)

Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)

Prefix pl/sql tables with t_ (alternatively suffix with _table)

Prefix table types with tt_ (alternatively suffix with _table_type)

Suffix cursors with _cursor

Prefix exceptions with e_

Prefix package names with PKG_

To run this SQL*Loader file: [SQLLOAD.CTL]

SQLLDR user/pass@sid control=sqlload.ctl LOG=MyData.log

Notes: Drop indexes (and recreate after load), disable referential integrity. This script inserts Comma Separated data into an Oracle table. To append rather than replace data change TRUNCATE to INSERT/APPEND

Also consider using External Tables

OPTIONS (BINDSIZE=2000000, ROWS=1000, ERRORS=1000, DISCARDMAX=1000)

LOAD DATA

INFILE 'MyData.csv'

BADFILE MyData.bad

DISCARDFILE MyData.dsc

TRUNCATE INTO TABLE MY_DATA_TABLE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY’

(org_code NULLIF org_code=BLANKS, org_name NULLIF org_name=BLANKS,

org_address1 NULLIF org_address1=BLANKS, org_address2 NULLIF org_address2=BLANKS,

org_address3 NULLIF org_address3=BLANKS, org_address4 NULLIF org_address4=BLANKS,

org_zipcode NULLIF org_zipcode=BLANKS,

org_start_date DATE(8)"yyyymmdd" NULLIF org_start_date=BLANKS,

org_status NULLIF org_status=BLANKS "nvl(:org_status,'Valid')",

org_telephone NULLIF org_telephone=BLANKS)

Forward Declaration: To declare variable and procedures before using it

Data Binding: Dividing the cursor in application as per select statement

Modularity: PL/SQL allows creating program module to improve software reliability and to hide complexity 

Positional and Named Notation: The actual arguments are associated with formal arguments by position keys.

A Trigger doesn't accept argument and have same name as table / procedure as it exist in separate namespace.

PL/SQL Signature Method: To determine when remote dependant object get invalid.

Object Privilege: On a particular object- I/U/D/Exec

System Privilege: Entire collection object -C/A/D

SGA Comprises: Data Buffer, Redo Log Buffer, and Shared pool Buffer.

Shared Pool: Required to process unique SQL stmt submitted to DB. It contains information such as parse tree and execution plan.

PGA: A memory buffer that contains data and control information for a server process.

Dedicated server: Handles request for single user.

Multithread Server: Handles request for multiple user.

Background process -DBWR, LGWR, PMON, SMON, CKPT

DBWR: Writes modified data blocks from DB buffer to data file.

CKPT: Responsible to check DBWR and update control file to indicate most recent CKPT.

SMON: Instance recovery at start up, Clean Temporary Segment.

PMON: Responsible for process recovery and user process fails, Cleaning up cache,freeing resources which was using process.

Data Dictionary: V$SESSION, information about integrity constraints, space allocated for schema object.

USER_TAB_COLUMNS: gives you a list of tables as per Column.

Index: are used for row selection in where and order by only if indexing on column

DBMS_ALERT is a Transaction Processing Package

DBMS_PIPE is an Application Development package

SQL Language Extension: Oracle * provide new built-in datatype, object datatypes, nested tables, and a no of other features that require new DDL extension.

The UTLBSTAT and UTLESTAT script: to get general overview of DB’s performance over a certain period of time. UTLBSTAT creates table and views containing cumulative DB performance summary information at the time when the script runs .All the objects create by UTLBSTAT contain word login.

Utlbstat.sql

UTLESTAT creates table and views containing cumulative DB performance summary information at the time when the script runs .All the objects create by UTLESTAT contain word end. UTLESTAT spools the results of these SQL statements to a file called REPORT.TXT

Utlestat.sql

Oracle

Pages

Sunday, August 7, 2011

Oracle IQA 1

Starting the arch process

No comments:

Post a Comment