ORACLE DEVELOPMENT TIPS
Package : A package is a group of related program objects stored together as a unit in the database.
A package is an encapsulated collection of related program objects stored together in the database. Program objects are: procedures, functions, variables, constants, cursors, and exceptions.
Oracle Built-in Packages : Some examples are
DBMS_OUTPUT
DBMS_ALERT
DBMS_SQL
DBMS_DDL
Advantages of Packages over stand-alone procedures and functions:
1. Better performance : Packages allow ORACLE to read multiple package objects into memory at once.
2. Global variables : Packages can contain global variables and cursors that are available to all procedures and functions in the package.
3. Function Overloading : Packages allow you to overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.
4. Encapsulation : Packages allow you to organize your application development more efficiently : encapsulation of related procedures and variables
5. Privileges : Packages allow you to grant privileges more efficiently.
6. Recompiling dependent schema objects : Packages allow you to modify package objects without recompiling dependent schema objects. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.
(Procedure bodies contained within the package body can be altered separately from their publicly declared specifications in the package specification. As long as the procedure specification does not change, objects that reference the altered procedures of the package are never marked invalid; that is, they are never marked as needing recompilation.)
Tablespace : A tablespace is a logical storage unit which is used to group related logical structures together.
Schema : A schema is a collection of objects. Schema objects include such structures as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
Table : A table is the data structure that holds data in a relational database. A table is the basic unit of data storage in an ORACLE database.
View : A view can also be thought of as a "stored query." A view is a custom-tailored presentation of the data in one or more tables.
· Like tables, views can be queried, updated, inserted into, and deleted from, with restrictions. All operations performed on a view actually affect the base tables of the view.
Restrictions on DML operations for views use the following criteria in the following order:
1. If a view is defined by a query that contains a join operation, SET or DISTINCT operators, a GROUP BY clause, or a group function, rows cannot be inserted into, updated in, or deleted from the base tables using the view.
2. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, a row cannot be inserted into the base table using the view.
3. If the view was created by using an expression, such as DECODE(deptno, 10, 'SALES', ...), rows cannot be inserted into or updated in the base table using the view.
4. If a view is defined with the WITH CHECK OPTION, a row cannot be inserted into, or updated in, the base table (using the view) if the view cannot select the row from the base table.
Sequence : A sequence generates a serial list of unique numbers for numeric columns of a database's tables.
Q. Can you define a sequence which gives numbers in a descending order ?
A Yes
Procedure/Function : A procedure or function is a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task.
Exceptions : Exceptions are essentially interrupts that can halt processing when an error or an exception condition is raised.
Exception handlers : Exception handlers is the one which traps the interrupt and can execute user defined action.
Synonym : A synonym is an alias for a table, view, sequence, or program unit.
Index : Index is a logical structure created on one or more columns of a table for faster data access. (You can create a composite index on multiple columns up to a maximum of 16 columns)
· By default, an index is created on the primary key of the table, when a table is created. The index name is the same as the primary key constraint name. However, if the table is created without a primary key then no index is created automatically.
· If you disable the primary key constraint, the index on primary key is dropped automatically. If you enable the primary key constraint, the index on primary key is created automatically.
Clusters : Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. The related columns of the tables in a cluster is called the cluster key.
Hash Clusters : Hash clusters also cluster table data in a manner similar to normal, index clusters (clusters keyed with an index rather than a hash function). However, 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 stored together on disk.
Database links : A database link is a named object that describes a "path" from one database to another. Database links are implicitly used when a reference is made to a global object name in a distributed database.
Data Blocks : Data Block is the smallest unit of I/O used by database.
Extent : An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
Segment : A segment is a set of extents allocated for a certain logical structure. For example, the different types of segments include: Data Segment, Index Segment, Rollback Segment, Temporary Segment.
Redo Log : The primary function of the redo log is to record all changes made to data.
Mirrored Redo Log : To protect against a failure involving the redo log itself, ORACLE allows a mirrored redo log so that two or more copies of the redo log can be maintained on different disks.
Control File : A control file records the physical structure of the database. For example, it contains the following types of information:
· database name
· names and locations of a database's data files and redo log files
· time stamp of database creation
Data Dictionary : The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database. A data dictionary is created when a database is created.
Oracle System Architecture contains :
· Memory Structures
· Processes.
All memory structures exist in the main memory of the computers (sometimes called virtual memory or random access memory) that constitute the database system.
Processes : Processes are jobs or tasks that work in the memory of these computers.
Several basic memory structures are associated with ORACLE:
· the system global area
· the program global areas
System Global Area : The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance.
ORACLE instance : An SGA and the ORACLE background processes constitute an ORACLE instance.
The information stored within the SGA is divided into several types of memory structures :
· the database buffers
· the redo log buffer
· the shared pool
Database Buffers : Database buffers of the SGA store the most recently used blocks of database data; the set of database buffers in an instance is the database buffer cache.
Redo Log Buffer : The redo log buffer of the SGA stores redo entries - a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log file.
Shared Pool : The shared pool is a portion of the SGA that contains shared memory constructs such as shared SQL areas.
Shared SQL Area : A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement.
Cursor : Cursor is a work area in which SQL result is placed. A cursor is a handle (a name or pointer) for the memory associated with a specific statement.
Program Global Area : The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.
Process : A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task.
An ORACLE database system has two general types of processes:
· User Processes
· ORACLE processes
User Processes : A user process is created and maintained to execute the software code of an application program (such as a Pro*C program) or an ORACLE tool (such as SQL*DBA).
ORACLE Processes : ORACLE processes are called by other processes to perform functions on behalf of the invoking process.
The different types of ORACLE processes are :
· Server Processes
· Background Processes
Server Processes : ORACLE creates server processes to handle requests from connected user processes. A server process is in charge of communicating with the user process and interacting with ORACLE to carry out requests of the associated user process.
Background Processes : ORACLE creates a set of background processes for each instance. The background processes asynchronously perform I/O and monitor other ORACLE processes to provide increased parallelism for better performance and reliability.
Oracle has 9 background processes :
1. Database Writer (DBWR) : The Database Writer writes modified blocks from the database buffer cache to the data files.
2. Log Writer (LGWR) : The Log Writer writes redo log entries to disk.
3. Checkpoint (CKPT) : At specific times, all modified database buffers in the SGA are written to the data files by DBWR; this event is called a checkpoint. The Checkpoint process is responsible for signalling DBWR at checkpoints and updating all the data files and control files of the database to indicate the most recent checkpoint.
4. System Monitor (SMON) : The system monitor performs instance recovery at instance startup. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors.
5. Process Monitor (PMON) : The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using.
6. Archiver (ARCH) : The archiver copies the online redo log files to archival storage when they are full. ARCH is active only when a database's redo log is used in ARCHIVELOG mode.
7. Recoverer (RECO) : The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.
8. Dispatcher (Dnnn) : Dispatchers are optional background processes, present only when a multi-threaded server configuration is used. Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes. (This is the only Background Process which was not present in Oracle 6.0 - to be confirmed ?)
9. Lock (LCKn) : Up to ten lock processes (LCK0, . . ., LCK9) are used for inter-instance locking when the ORACLE Parallel Server is used.
Program Interface: The program interface is the mechanism by which a user process communicates with a server process.
SQL*Net: SQL*Net is Oracle’s interface to standard communications protocols that allows for the proper transmission of data between computers.
ORACLE Parallel Server: ORACLE with the Parallel Server option runs multiple instances that "share" a single physical database.
DUAL: The table named DUAL is a small table referenced by ORACLE and user-written programs to guarantee a known result. This table has one column and one row.
The Dynamic Performance Tables: Throughout its operation, ORACLE maintains a set of "virtual" tables that record current database activity. These tables are called dynamic performance tables.
Because dynamic performance tables are not true tables, they should not be accessed by most users. However, database administrators can query these tables and can create views on the tables and grant access to those views to other users.
The dynamic performance tables are owned by SYS and their names all begin with V_$. Views are created on these tables, and then synonyms are created for the views. The synonym names begin with V$.
Types of SQL Statements :
1. Data Definition Statements (DDL) : DDL statements define and maintain objects, and drop objects when they are no longer needed. Eg DROP TABLE, GRANT, etc.
2. Data Manipulation Statements (DML) : DML statements manipulate the database's data. Eg INSERT, DELETE, UPDATE, SELECT, LOCK, EXPLAIN PLAN, etc.
3. Transaction Control Statements : Transaction control statements manage the changes made by DML statements. Eg COMMIT, ROLLBACK, and SAVEPOINT.
4. Session Control Statements : Session control statements allow a user to control the properties of his current session. The two session control statements are ALTER SESSION and SET ROLE.
5. System Control Statements : System control commands change the properties of the ORACLE Server instance. The only system control command is ALTER SYSTEM.
6. Embedded SQL Statements : Embedded SQL statements incorporate DDL, DML, and transaction control statements in a procedural language program (such as those used with the Oracle Precompilers). Examples include OPEN, CLOSE, FETCH, and EXECUTE.
Transaction : A transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
Savepoint : Savepoints are intermediate markers that can be declared in long transactions that contain many SQL statements. By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.
PL/SQL : PL/SQL is Oracle's procedural language extension to SQL. PL/SQL combines the ease and flexibility of SQL with the procedural functionality of a structured programming language, such as IF ... THEN, WHILE, and LOOP.
Advantages of PL/SQL :
1. Because PL/SQL code can be stored centrally in a database, network traffic between applications and the database is reduced, so application and system performance increases.
2. Data can be easily manipulated by PL/SQL code. Eg. IF ... THEN, WHILE, and LOOP, Cursors, etc.
Trigger : A database trigger is a stored procedure that is fired (implicitly executed) when an INSERT, UPDATE, or DELETE statement is issued against the associated table.
Q. Which trigger is more efficient : AFTER or BEFORE ?
A. AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks need only be read once for both the triggering statement and the trigger.
Integrity Constraint : An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.
Types of integrity constraints :
The following integrity constraints are supported by ORACLE :
1. NOT NULL : disallows nulls (empty entries) in a table's column
2. UNIQUE : disallows duplicate values in a column or set of columns
3. PRIMARY KEY : disallows duplicate values and nulls in a column or set of columns
4. FOREIGN KEY : requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.
5. CHECK : disallows values that do not satisfy the logical expression of the constraint
Rules of Data Integrity in Relational Database Model :
1. Domain Integrity : Domain Integrity ensures that a value in a column is a member of the column’s domain (eg int, char, etc.) or a legal set of values.
2. Entity Integrity : Entity integrity means that every row in a table must be unique. To ensure entity integrity, a developer designates a column or a set of columns in a table as its primary key.
3. Referential Integrity : Referential integrity defines the relationships among different columns and tables in a relational database. It’s called referential integrity because the values in one column or set of columns refer to or must match the values in a related column or set of columns.
Referential Integrity Constraint : A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
Self Referential Integrity Constraint : A self referential integrity constraint references the parent key of the same table. It requires that for each row of a table, the value in the foreign key matches a value in a parent key.
Q. Can a foreign key reference a non-primary key which is unique ?
A Yes.
Q. Can a foreign key reference a field which is not a unique or a primary key ?
A No.
Q. In a CHECK integrity constraint for a column, can you refer to another column of the same table ?
A Yes.
Q. Can you define CHECK constraint at table level ?
A Yes.
Outer Joins : An outer join returns all rows that satisfy the join condition as well as those rows from one table for which no rows from the other satisfy the join condition. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition.
Restrictions on Outer Joins : Outer join queries are subject to these rules and restrictions:
1. The (+) operator can only appear in the WHERE clause, not in the select list, and can only be applied to a column of a table or view.
2. If A and B are joined by multiple join conditions, the (+) operator must be used in all of these conditions.
3. The (+) operator can only be applied to a column, rather than to an arbitrary expression, although an arbitrary expression can contain a column marked with the (+) operator.
4. A condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
5. A condition cannot use the IN comparison operator to compare a column marked with the (+) operator to another expression.
6. A condition cannot compare a column marked with the (+) operator to a subquery.
Locks : Locks are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. ORACLE uses locks to control concurrent access to data.
Locks are used to achieve two important database goals :
Consistency : Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data.
Integrity : Ensures that the database's data and structures reflect all changes made to them in the correct sequence.
Types of Locks :
1. Data Locks (DML)
2. Dictionary Locks (DDL)
3. Internal Locks and Latches
4. Distributed Locks
5. Parallel Cache Management Locks
Data Locks : Row Level and Table Level
Row Level : Exclusive Locks
Table Level : 1. Row Share Table Locks (RS)
2. Row Exclusive Table Locks (RX)
3. Share Table Locks (S)
4. Share Row Exclusive Table Locks (SRX)
5. Exclusive Table Locks (X)
Dictionary Locks :
1. Exclusive DDL Locks
2. Share DDL Locks
3. Breakable Parse Locks
Restrictiveness of Locks :
In general, two levels of locking can be used in a multi-user database:
· Exclusive Locks : An exclusive lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released.
· Share Locks : A share lock allows the associated resource to be shared, depending on the operations involved (e.g., several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks allow a higher degree of data concurrency than exclusive locks.
Deadlocks : A deadlock is a situation that can occur in multi-user systems that causes some number of transactions to be unable to continue work. A deadlock can occur when two or more users are waiting for data locked by each other. It typically happens when each of two or more users are waiting to access a resource that another user has already locked. This creates a deadlock situation because each user is waiting for resources held by the other user.
Eg
Transaction 1 Time Point Transaction 2
UPDATE emp 1 UPDATE emp
SET sal = sal*1.1 SET sal = 1342
WHERE empno = 1000; WHERE empno = 2000;
UPDATE emp 2 UPDATE emp
SET sal = sal*1.1 SET sal = 1342
WHERE empno = 2000; WHERE empno = 1000;
ORA-00060 3
deadlock detected while
waiting for resource
Lock Escalation : (ORACLE never escalates locks) In some database systems, lock escalation occurs when numerous locks are held at one level and the database automatically changes the locks to different locks at a higher level. For example, if a single user locks many rows in a table, the database might automatically escalate the user's row locks to a single table lock. With this plan, the number of locks has been reduced, but the restrictiveness of what is being locked has increased.
Lock escalation greatly increases the likelihood of deadlocks.
Types of Database Security :
· System Security
· Data Security
System Security: System security includes the mechanisms that control the access and use of the database at the system level. For example, system security includes:
· Valid username/password combinations
· Whether or not a user is authorized to connect to the database
· The amount of disk space available to the objects of a user
· The resource limits for a user
· Whether or not database auditing is active
· Which system operations a user can perform
Data Security: Data security includes the mechanisms that control the access and use of the database at the object level. For example, data security includes:
· Which users have access to a specific schema object and the specific types of actions allowed for each user on the object (e.g., user SCOTT can issue SELECT and INSERT statements but not DELETE statements using the EMP table)
· The actions, if any, that are audited for each schema object
Privilege : A privilege is a right to execute a particular type of SQL statement or to access another user's object.
Types of privileges :
· system privileges
· object privileges
System Privileges : System privileges allow users to perform a particular systemwide action, or to perform a particular action on a particular type of object. E.g. Create Tablespace, Delete the row of any table, etc.
Object Privileges : Object privileges allow users to perform a particular action on a specific object. E.g. delete row of specific table, etc.
Roles : Roles are named groups of related privileges that are granted to users or other roles.
Advantages of Roles :
1. reduced granting of privileges
2. dynamic privilege management (Changing of privileges)
3. selective availability of privileges (Enalbling/Disabling roles)
4. application awareness (Enalbling/Disabling of roles by application)
Profile : A profile is a named set of specified resource limits that can be assigned to valid usernames of an ORACLE database. Profiles provide for easy management of resource limits.
The various resource limits are :
· the number of concurrent sessions the user can establish
· the CPU processing time:
· available to the user's session
· available to a single call to ORACLE made by a SQL statement
· the amount of logical I/O:
· available to the user's session
· 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
Auditing : Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to:
· investigate suspicious activity.
· monitor and gather data about specific database activities.
Types of Auditing :
· Statement Auditing
· Privilege Auditing
· Object Auditing
Types of Failures :
· user error
· statement or process failure
· instance failure
· media (disk) failure
Structures Used for Recovery :
· The Online Redo Log
· The Archived Redo Log
· Control Files
· Rollback Segments
· Database Backups (of data files, control files, redo log files, etc)
Basic Recovery Steps :
Two separate steps are always used by ORACLE during recovery from an instance or media failure :
1. rolling forward
2. rolling back
· Rolling Forward : The first step of recovery is to roll forward, or reapply to the data files all of the changes recorded in the redo log. After roll forward, the data files contain all committed changes as well as any uncommitted changes that were recorded in the redo log.
· Rolling Back : The roll forward is only half of recovery. After the roll forward, any changes that were not committed must be undone. This process is called rolling back.
Distributed processing : Distributed processing uses more than one processor to divide the processing for a set of related jobs. Distributed processing reduces the processing load on a single processor by allowing different processors to concentrate on a subset of related tasks, thus improving the performance and capabilities of the system as a whole. E.g. Client-Server architecture.
Distributed Database : A distributed database is a network of databases managed by multiple database servers that appears to a user as a single logical database.
Two Phase Commit : Two Phase Commit is a mechanism wherein ORACLE automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database.
The Phases of the Two-Phase Commit Mechanism :
· Prepare phase : The global co-ordinator (initiating node) asks 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 co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.
Table Replication : Replication is a mechanism wherein distributed database systems make read-only copies of heavily accessed data on several nodes. ORACLE provides an automatic method for table replication and update called snapshots.
Snapshots : Snapshots are read-only copies of a master table located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table.
Global database : Global database is the collection of distributed databases participating in the transaction.
ROWID : ROWID is a pseudo column. It is used by Oracle for its internal references (row addresses). Its datatype is ROWID and it occupies 6 bytes in index data block.
Each row's address can be retrieved with a SQL query using the reserved word ROWID as a column name :
SELECT ROWID, ename FROM emp;
ROWIDs use a binary representation of the physical address for each row selected. When queried using SQL*Plus or SQL*DBA, the binary representation is converted to a VARCHAR2/hexadecimal representation and the above query might return the following row information:
ROWID ENAME
00000DD5.0000.0001 SMITH
00000DD5.0001.0001 ALLEN
00000DD5.0002.0001 WARD
As shown above, a ROWID's VARCHAR2/hexadecimal representation is divided into three pieces: block.row.file
· The data block that contains the row (block DD5 in the example). Block numbers are relative to their data file, not tablespace. Therefore, two rows with identical block numbers could reside in two different data files of the same tablespace.
· The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.
· The data file that contains the row (file 1 in the example). The first data file of every database is always 1, and file numbers are unique within a database.
Q. What are pseudo columns in Oracle ?
A Each table in an ORACLE database internally has a pseudocolumn named ROWID
Advantages of TRUNCATE Command over DELETE/DROP TABLE Command :
The TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster.
1. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back.
2. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations (grants).
3. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
4. As a TRUNCATE statement deletes rows from a table (or clustered table), triggers associated with the table are not fired.
5. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.
Types of Optimization :
Cost Based Optimization : Cost-based optimization is an optimization method which determine the most efficient execution plan for a DML statement by using the statistics for the referenced objects.
Rule Based Optimization : An optimization approach based on a set of rules. This optimization approach was used in ORACLE 6.0
A table, index, or cluster can be analyzed to collect or update statistics about the object.
The statistics can be queried using the following data dictionary views:
· USER_INDEXES, ALL_INDEXES, DBA_INDEXES
· USER_TABLES, ALL_TABLES, DBA_TABLES
· USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS
You can gather the following statistics on a table:
· number of rows
· number of blocks that have been used (computed only)
· number of blocks never used
· average available free space
· number of chained rows
· average row length
· number of distinct values per column
· the second smallest value per column (computed only)
· the second largest value per column (computed only)
Note: Statistics for all indexes associated with a table are automatically gathered when the table is analyzed for statistics.
Analyzing Statistics for the Data Dictionary : To use cost-based optimization on internal and recursive SQL calls, you have to ensure that ORACLE has gathered statistics on the data dictionary, and that these statistics are up-to-date. If no statistics are gathered on the data dictionary, ORACLE uses rule-based optimization on these calls.
· When a database is created, a single rollback segment named SYSTEM is created in the SYSTEM tablespace. If a database is to have other tablespaces, it must have two or more rollback segments in the SYSTEM tablespace. You cannot create any objects in non-SYSTEM tablespaces (not even rollback segments) until you have created and brought online at least one additional rollback segment in the SYSTEM tablespace.
Various Parameters to Analyze Command : (For Table, Index or Cluster)
(Topic : Chap 8 - Oracle Administrators Guide)
For index we have, ANALYZE INDEX
For clusters we have, ANALYZE CLUSTER
The syntax below applies to indexes and clusters as well.
· ANALYZE TABLE ABC COMPUTE STATISTICS
· ANALYZE TABLE ABC ESTIMATE STATISTICS [SAMPLE integer ROWS/PERCENT]
· ANALYZE TABLE ABC DELETE STATISTICS
· ANALYZE TABLE ABC VALIDATE STRUCTURE
· ANALYZE TABLE ABC VALIDATE STRUCTURE CASCADE
· ANALYZE TABLE ABC LIST CHAINED ROWS INTO chained_rows;
Reporting Constraint Exceptions : (Topic : Chap 8 - Oracle Administrators Guide)
If exceptions exist when a constraint is enabled, an error is returned and the integrity constraint remains disabled. To determine which rows violate the integrity constraint, include the EXCEPTIONS option in the ENABLE clause of a CREATE TABLE or ALTER TABLE statement. The EXCEPTIONS option places the ROWID, table owner, table name, and constraint name of all exception rows into a specified table. For example, the following statement attempts to enable the primary key of the DEPT table, and if exceptions exist, information is inserted into a table named EXCEPTIONS:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;
ORACLE Datatypes :
1. CHAR (1 to 255)
2. VARCHAR2 (1 to 2000)
3. NUMBER (precision : 1 to 38, scale : -84 to 127)
4. DATE (7 bytes)
5. LONG (1 to 2GB)
6. RAW (1 to 255)
7. LONG RAW (1 to 2GB)
8. ROWID (6 bytes)
9. MLSLABEL (1 to 255)
· Nulls are stored in the database if they fall between columns with data values. In these cases they require one byte. Trailing nulls in a row require no storage. In tables with many columns, the columns more likely to contain nulls should be defined last to conserve disk space.
· If you want ORACLE to ignore trailing blanks when comparing character values, you must store these values in CHAR columns rather than VARRCHAR2 columns.
Q. What is blank-padded comparison semantics ?
A. If two values have different lengths, ORACLE adds blanks to the shorter value, until the two values are the same length. Two values that differ only in the number of trailing blanks are considered equal. (ORACLE compares CHAR values using blank-padded comparison semantics)
Q. What is non-padded comparison semantics ?
A. Two values are only considered equal if they have the same characters and are of equal length. (ORACLE compares VARCHAR values using non-padded comparison semantics).
Restrictions on LONG and LONG RAW Data :
1. Only one LONG column is allowed per table.
2. LONG columns cannot be indexed.
3. LONG columns cannot appear in integrity constraints.
4. LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses, or with the DISTINCT operator in SELECT statements.
5. LONG columns cannot be referenced by SQL functions (such as SUBSTR or INSTR).
6. LONG columns cannot be used in the SELECT list of a subquery or queries combined by set operators (
7. LONG columns cannot be used in SQL expressions.
8. LONG columns cannot be referenced when creating a table with a query (CREATE TABLE . . . AS SELECT . . .) or when inserting into a table (or view) with a query
9. (INSERT INTO . . . SELECT . . .).
10. A variable or argument of a PL/SQL program unit cannot be declared using the LONG datatype.
RAW and LONG RAW Datatypes :
Use the RAW and LONG RAW datatypes for data that is not to be interpreted (not to be converted when moving data between different systems) by ORACLE. These datatypes are intended for binary data and byte strings. For example, LONG RAW can be used to store graphics, sound, documents, and arrays of binary data; the interpretation is dependent on the use.
· LONG and LONG RAW columns cannot be indexed, but RAW data can be indexed.
· PCTUSED cannot be specified for indexes.
· PCTINCREASE cannot be specified for rollback segments and defaults to 0.
%TYPE : The %TYPE attribute provides the datatype of a variable, constant, or column. This attribute is particularly useful when declaring a variable or procedure argument that refers to a column in a database table.
%ROWTYPE : The %ROWTYPE attribute is useful if you want to declare a variable to be a record that has the same structure as a row in a table or view, or a row that is returned by a fetch from a cursor.
STEPS IN EXECUTION OF SQL STATEMENTS :
1. Create a cursor
2. Parse the statement
3. Describe Results
4. Defining outputs
5. Bind any variables
6. Execute the statement
7. Fetch rows of a query result
Parsing :
Parsing is the process of:
1. translating a SQL statement, verifying it to be a valid statement
2. performing data dictionary lookups to check table and column definitions
3. acquiring parse locks on required objects so that their definitions do not change
during the statement's parsing
4. checking privileges to access referenced schema objects
5. determining the execution plan to be used when executing the statement
6. loading it into a shared SQL area
7. for distributed statements, routing all or part of the statement to remote nodes that contain referenced data
Parse Tree : A parse tree is a parsed representation of the source code produced by the PL/SQL compiler.
Cursor Attributes :
1. %Found
2. %NotFound
3. %RowCount
4. %IsOpen
Types of Cursors :
· Implicit Cursors - Defined automatically for each SQL
· Explicit Cursors - Defined explicitly by the user.
Default Roles Created by Oracle :
1. Connect
2. Resource
3. DBA
4. Exp_Full_Database
5. Imp_Full_Database
HINTS : Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer.
TYPES OF HINTS :
ALL_ROWS : The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput.
FIRST_ROWS : The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time.
FULL : The FULL hint explicitly chooses a full table scan for the specified table.
ROWID : The ROWID hint explicitly chooses a table scan by ROWID for the specified table.
CLUSTER : The CLUSTER hint explicitly chooses a cluster scan to access the specified table.
HASH : The HASH hint explicitly chooses a hash scan to access the specified table.
INDEX : The INDEX hint explicitly chooses an index scan for the specified table.
INDEX_ASC : The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in ascending order of their indexed values.
INDEX_DESC : The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, ORACLE scans the index entries in descending order of their indexed values.
ORDERED : The ORDERED hint causes ORACLE to join tables in the order in which they appear in the FROM clause.
USE_NL : The USE_NL hint causes ORACLE to join each specified table to another row source with a nested loops join using the specified table as the inner table.
USE_MERGE : The USE_MERGE hint causes ORACLE to join each specified table with another row source with a sort-merge join.
GENERAL INTERVIEW QUESTIONS
1. What are the various types of Exceptions ?
A User defined and Predefined Exceptions.
2. Can we define exceptions twice in same block?
A No.
3. What is the difference between a procedure and a function?
A Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
4. Can you have two functions with the same name in a PL/SQL block ?
A Yes.
5. Can you have two stored functions with the same name ?
A Yes.
6. Can you call a stored function in the constraint of a table ?
A No.
7. What are the various types of parameter modes in a procedure ?
A IN, OUT AND INOUT.
8. What is OverLoading and what are its restrictions ?
A OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
9. Can functions be overloaded ?
A Yes.
10. Can 2 functions have same name & input parameters but differ only by return datatype
A No.
11. What are the constructs of a procedure, function or a package ?
A The constructs of a procedure, function or a package are :
· variables and constants
· cursors
· exceptions
12. Why Create or Replace and not Drop and recreate procedures ?
A So that Grants are not dropped.
13. Can you pass parameters in packages ? How ?
A Yes. You can pass parameters to procedures or functions in a package.
14. What are the parts of a database trigger ?
A The parts of a trigger are:
· A triggering event or statement
· A trigger restriction
· A trigger action
15. What are the various types of database triggers ?
A There are 12 types of triggers :
· Insert, Delete and Update Triggers.
· Before and After Triggers.
· Row and Statement Triggers.
16. What is the advantage of a stored procedure over a database trigger ?
A We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
17. What are the maximum no. of statements that can be specified in a trigger statement ?
A One.
18. Can views be specified in a trigger statement ?
A No
19. What are the values of :new and :old in Insert/Delete/Update Triggers ?
A INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
20. What are cascading triggers? What is the maximum no of cascading triggers at a time?
A When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32
21. What are mutating triggers ?
A A trigger giving a SELECT on the table on which the trigger is written.
22. What are constraining triggers ?
A A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.
23. Describe Oracle database’s physical and logical structure ?
A Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
24. Can you increase the size of a tablespace ? How ?
A Yes, by adding datafiles to it.
25. Can you increase the size of datafiles ? How ?
A No.
26. What is the use of Control files ?
A Contains pointers to locations of various data files, redo log files, etc.
27. What is the use of Data Dictionary ?
A Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
28. What are the advantages of clusters ?
A Access time reduced for joins.
29. What are the disadvantages of clusters ?
A The time for Insert increases.
30. Can Long/Long RAW be clustered ?
A No.
31. Can null keys be entered in cluster index, normal index ?
A Yes.
32. Can Check constraint be used for self referential integrity ? How ?
A 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.
33. What are the min. extents allocated to a rollback extent ?
A Two
34. What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
A The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35. What is the difference between unique key and primary key ?
A Unique key can be null; Primary key cannot be null.
36. An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
A Yes.
37. Can you define multiple savepoints ?
A Yes.
38. Can you Rollback to any savepoint ?
A Yes.
39. What is the maximum no. of columns a table can have ?
A 254.
40. What is the significance of the & and && operators in PL SQL ?
A The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.
41. 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 or will it terminate?
A It will terminate (Please check).
42. Can you pass a parameter to a cursor?
A Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
43. What are the various types of RollBack Segments ?
A Public – Available to all instances
Private – Available to specific instance
44. What are the statuses of Rollback segments ?
A Online and Offline
45. Can you use %RowCount as a parameter to a cursor ?
A Yes
46. 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))
A Yes
47. Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
A Yes
48. Is this for loop allowed :
For x in &Start..&End Loop
A Yes
49. How many rows will the following SQL return :
Select * from emp
Where rownum <>
A 9 rows
50. How many rows will the following SQL return :
Select * from emp
Where rownum = 10;
A No rows
51. Which symbol preceeds the path to the table in the remote database ?
A @
52. Are views automatically updated when base tables are updated ?
A Yes
53. Can a trigger written for a view ?
A No
54. If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
A Last Record
55. A table has the following data : [5, Null, 10]. What will the average function return ?
A 7.5
56. Is Sysdate a system variable or a system function
A System Function
57. 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 ?
58. 3
59. Definition of relation Data. by Dr. Codd (IBM)?
A A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
60. What is Multi Threaded Server (MTA)
A In a single threaded Arch. the database manager creates a separate process for each database user. But in MTA can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
61. Which are initial RDBMs, Hierarchial & N/w database.
A RDBMS - R system
Hierarchial - IMS
N/W - DBTG
62.Diff. bet Oracle 6 and Oracle 7
| ORACLE 7 | ORACLE 6 |
| · Cost based optimizer | · Rule based optimizer |
| · Shared SQL Area | · SQL area allocated for each user |
| · Multi Threaded Server | · Single Threaded Server |
| · Hash Clusters | · Only B-Tree indexing |
| · Roll back Size Adjustment | · No provision |
| · Truncate command | · No provision |
| · Database Integrity Constraints | · Provision Appli. Level |
| · Stored procedures, functions packages & triggers | · No provision |
| · Resource profile limit. It prevents user from running away with system resources | · No provision |
| · Distributed Database | · Distributed Query |
| · Table replication & snapshots | · No provision |
| · Client/Server Tech. | · No provision |
63. What is Functional Dependency
A 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
2.64.What is Auditing ?
A The database has the ability to audit all actions that take place within it.
a). Login attempts b). Object Access c). Database Action
3.65.Result of Greatest(1,NULL) or Least(1,NULL)
A NULL
4.66.While designing in client/server what are the 2 imp. things to be considered ?
A Network Overhead (traffic), Speed and Load of client server
5.67.What are the disadvantages of SQL ?
A Disadvantages of SQL are :
· Cannot drop a field
· Cannot rename a field
· Cannot manage memory
· Procedural Language option not provided
· Index on view or index on index not provided
· View updation problem
6.68.When to create indexes?
A To be created when table is queried for less than 2% or 4% to 25% of the table rows.
7.69.How can you avoid indexes?
A TO make index access path unavailable
· Use FULL hint to optimiser for full table scan
· Use INDEX or AND-EQUAL hint to optimiser to use one index or set to indexes instead of another.
8.70.What is the result of the following SQL :
Select 1 from dual
UNION
Select ‘A’ from dual;
A Error
9.71.Can a database trigger be written on synonym of a table and if it can be then what would be the effect if original table were accessed.
A Yes, the database trigger would fire.
10.72.Can you alter synonym of view or view?
A No
11.73.Can you create index on view
A No.
12.74.What is the difference between a view and a synonym?
A Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
13.75.What is the difference between alias and synonym?
A Alias is temporary and used with one query.
Synonym is permanent and not used as alias.
14.76.What is the effect of synonym and table name used in same select statement?
A Valid
15.77.What’s the length of SQL integer
A 32 bit length
16.78.Diff. between foreign key and reference key
A Foreign key is the key i.e. attribute which refers to another table primary key.
Reference key is the primary key of table referred by another table.
17.79.Can dual table be deleted, dropped or altered or updated or inserted
A Yes but by the owner
18.80.If content of dual is updated to some value computation takes place or not.
A Yes
19.81.If any other table same as dual is created would it act similar to dual.
A Yes
20.82.For which relator in where clause, index is not used
A <>, like ‘% ...’ is NOT functions, field +constant, field || ‘’
21.83.Assume that there are multiple databases running on one machine. How can you switch from one to another?
A Changing the ORACLE_SID
22.84.What are the advantages of Oracle?
A Portability: Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence: Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue. This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery: Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerance to disk failure. You can also do point-in-time recovery.
Performance: Speed of a ‘tuned’ Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support: Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
23.85.What is a forward declaration? What is its use?
A 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.
Uses: You can use forward declarations to define subprograms in logical or alphabetical order define mutually recursive subprograms group subprograms in a package
24.86.What are actual and formal parameters?
A Actual Parameters: Subprograms pass information using parameters. The variables or 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;
25.87.What are the types of Notation?
A Position, Named, Mixed and Restrictions.
26.88.What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size?
A In our case,
db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500)
shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB)
open_cursors was changed from 200 to 300 (std values are 200 & 300)
db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database 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.
27.89.Is there a PL/SQL Engine in SQL*Plus?
A No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
28.90.Is there a limit on the size of a PL/SQL block?
A 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 or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
29.91.Can one read/write files from PL/SQL?
A Included in Oracle 7.3 is a UTL_FILE package that can read and write 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.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput', 'W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %s\n', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
30.92.How can I protect my PL/SQL source code?
A 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.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyy
31.93.Can one use dynamic SQL within PL/SQL? OR
Can you use a DDL in a procedure? How?
A From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg:
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;
ORACLE ADMINISTRATION TIPS
Various Parameters to STARTUP command:
· STARTUP NOMOUNT
· STARTUP MOUNT
· STARTUP OPEN
· STARTUP RESTRICT
· STARTUP EXCLUSIVE
· STARTUP PARALLEL
· STARTUP FORCE
· STARTUP RECOVER
Various Types of database SHUTDOWN:
· SHUTDOWN NORMAL
· SHUTDOWN IMMEDIATE
· SHUTDOWN ABORT
SHUTDOWN NORMAL:
· No new connections are allowed after the statement is issued.
· Before the database is shut down, ORACLE waits for all currently connected users to disconnect from the database.
SHUTDOWN IMMEDIATE:
· Current client SQL statements being processed by ORACLE are terminated immediately.
· Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
· ORACLE does not wait for users currently connected to the database to disconnect; ORACLE implicitly rolls back active transactions and disconnects all connected users.
SHUTDOWN ABORT:
· Current client SQL statements being processed by ORACLE are immediately terminated.
· Uncommitted transactions are not rolled back.
· ORACLE does not wait for users currently connected to the database to disconnect; ORACLE implicitly disconnects all connected users.
Types of Tablespace Offline :
Normal Offline : A tablespace can be taken offline normally if no error conditions exist for any of the data files of the tablespace; no data file in the tablespace can be currently offline as the result of a write error. In this case, ORACLE takes a checkpoint for all data files of the tablespace as it takes them offline.
Temporary Offline: A tablespace can be taken offline temporarily, even if error conditions are indicated for one or more files of the tablespace. In this case, ORACLE takes offline the data files that are not already offline, checkpointing them as it does so.
If no files are offline but you use the temporary option anyway, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors and you take the tablespace offline temporarily, the tablespace will require recovery before you can bring it back online.
Immediate Offline: A tablespace can be taken offline immediately, without ORACLE's taking a checkpoint on any of the data files. In this case, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.
· Take a tablespace offline temporarily only when you cannot take it offline normally; in this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online.
· Take a tablespace offline immediately only after trying both the normal and temporary options.
Data Block Size: For efficiency, all databases created should have a data block size that is larger than the operating system block size.
Each database's block size is set during database creation by the initialization parameter DB_BLOCK_SIZE. The block size cannot be changed after database creation except by re-creating the database.
Effects of CREATE DATABASE statement:
When executed, a CREATE DATABASE statement performs the following operations:
· Creates the data files for the database
· Creates the control files for the database
· Creates the redo log files for the database
· Creates the SYSTEM tablespace and the SYSTEM rollback segment
· Creates the data dictionary
· Creates the users SYS and SYSTEM
· Specifies the character set used to store data in the database
· Mounts and opens the database for use
You can start an instance and database in a variety of ways:
· Start the instance without mounting a database
· Start the instance and mount the database, but leave it closed
· Start the instance, and mount and open the database in:
· Unrestricted mode (accessible to all users)
· RESTRICTED mode (accessible to DBAs only)
Location of various Files:
· Data files and online redo log files should also be on different disks to reduce contention in writing data blocks and redo entries.
· If you are archiving the redo log, spread online redo log members across disks to eliminate contention between the LGWR and ARCH background processes.
· Data files should not be stored on the same disk drive that stores the database's redo log files. If the data files and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures.
· If several disk drives are available to store the database, it might be helpful to store table data in a tablespace on one disk drive, and index data in a tablespace on another disk drive. This way, when users query table information, both disk drives can work simultaneously, retrieving table and index data at the same time.
Size of online Redo Log Files: Online redo log files should be relatively small. If large online redo log files are used and checkpoints only occur at log switches, instance recovery takes a long time because of the large number of redo log entries that must be applied.
Checkpoint: A checkpoint is the event during which the Database Writer process (DBWR) writes all modified database buffers in the SGA to the appropriate data files.
Log Switch: A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another.
The above two events are often connected: an instance takes a checkpoint at each log switch by default. A log switch, by default, takes place automatically when the current online redo log file group fills.
Size of Control File: Typical control files are small. The main determinants of a control file's size are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters of the CREATE DATABASE statement that created the associated database. Increasing the values of these parameters increases the size of a control file of the associated database.
PCTFREE and PCTUSED:
PCTFREE and PCTUSED are used for the following purposes:
· To increase the performance of writing and retrieving a data or index segment
· To decrease the amount of unused space in data blocks
· To decrease the amount of row chaining between data blocks
PCTFREE : PCTFREE is a percentage of the space in a data block which is reserved for future updates of the existing rows in a data block. (Default is 10%)
PCTUSED : PCTUSED is a sort of threshold percentage of the space in a data block. Once the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED. ORACLE tries to keep a data block at least PCTUSED full. (Default is 40%)
· PCTUSED cannot be specified for indexes.
Free List: A Free List is a list of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE.
Effects of a lower PCTFREE:
· Reserves less room for updates to existing table rows
· Allows inserts to fill the block more completely
· May save space, because the total data for a table or index is stored in fewer blocks (more rows or entries per block)
· Increases processing costs because ORACLE must frequently reorganize blocks as their free space area becomes filled with new or updated data
· Potentially increases processing costs and space required if updates to rows or index entries cause rows to grow and span blocks (because UPDATE, DELETE, and SELECT statements might need to read more blocks for a given row and because chained row pieces contain references to other pieces).
A low PCTFREE might be suitable, for example, for a segment that is rarely changed.
Effects of a higher PCTFREE:
· Reserves more room for future updates to existing table rows
· May require more blocks for the same amount of inserted data (inserting fewer rows per block)
· Lessens processing costs because blocks infrequently need reorganization of their free space area
· May improve update performance, because ORACLE does not need to chain row pieces as frequently, if ever
A high PCTFREE is suitable, for example, for segments that are frequently INSERTed.
Q. What value of PCTFREE is to be used - higher or lower?
A. If there are many updates in which data values get longer, increase PCTFREE; if updates to rows do not affect the total row width, then PCTFREE can be low.
· The PCTFREE value for index segment data blocks is normally very low because indexes infrequently require updates.
Effects of a lower PCTUSED :
· on the average, keeps blocks less full than a higher PCTUSED
· reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when it has fallen below that percentage of usage
· increases the unused space in a database
Effects of a higher PCTUSED :
· on the average keeps blocks fuller than a lower PCTUSED
· improves space efficiency
· increases processing cost during INSERTs and UPDATEs
Q. What value of PCTUSED is to be used - higher or lower ?
A. If most activity of the table consists of read-only transactions OR if there are lots of INSERT/DELETE but no UPDATE or update does not increase the size of the rows, then a high value of PCTUSED must be used so that space freed by DELETE statements is used relatively soon OR less number of data blocks are required to store the table's data and there is a decrease in the average number of data blocks to scan for queries.
If there is high update activity on a table which increases the size of the rows, then a low value of PCTUSED must be used so that less processing is done during high update activity.
Choosing Associated PCTUSED and PCTFREE Values :
· The sum of PCTFREE and PCTUSED must be equal to or less than 100.
· If the sum is less than 100, the ideal compromise of space utilization and I/O performance is a sum of PCTFREE and PCTUSED that differs from 100 by the percentage of space in the available block that an average row occupies.
INITIAL : The size, in bytes, of the first extent allocated when a segment is created.
Default : 5 data blocks
Minimum : 2 data blocks
NEXT : The size, in bytes, of the next incremental extent to be allocated for a
segment.
Default : 5 data blocks
Minimum : 1 data block
PCTINCREASE : The percent by which each incremental extent grows over the last incremental extent allocated for a segment.
Default: 50 (%)
Minimum: 0 (%)
· PCTINCREASE is always 0 for rollback segments. PCTINCREASE cannot be specified for rollback segments.
· The default and minimum values of MINEXTENTS for a rollback segment are always 2.
Steps to estimate a table's size :
1. Calculate the total block header size.
2. Calculate the available data space per data block.
3. Calculate the combined column lengths of the average row.
4. Calculate the total average row size.
5. Calculate the average number of rows that can fit in a data block.
6. Calculate the number of blocks and bytes required for the table.
If a new column is added to a table, the column is initially null. You can add a column with a NOT NULL constraint to a table only if the table does not contain any rows.
Suggestion : If the index is extremely large, it may be beneficial to perform the following steps:
1. Create a new temporary segment tablespace.
2. Alter the index creator's temporary segment tablespace.
3. Create the index.
4. Remove the temporary segment tablespace and respecify the creator's temporary segment tablespace, if desired.
1. Can I update through a view ?
2. What is CHECK OPTION for a view ?
3. Are views updated when I update base tables ?
4. Should we use complex views that cruel performance ?
5. How can I get the definition of a view ?
Can I update through a view ?
You can do this if
1.Your view is a simple subset of a single table.
2.All "not null" columns for the table must be in the view.
3.The primary key is in the view.
The typical example is the view on EMP limited to a department and not including salary. Also see CHECK
OPTION discussion.
What is CHECK OPTION for a view ?
Imagine we have created a view of EMP limited to a department, (where DEPTNO = 10). Now, that is fine for querying, but you can still write records through this view (either by update or insert) with a value of 20 for DEPTNO. (Next time you query the view, such records will be invisible.)
Now if you want to stop someone doing this (and consider whether you want them to be able to do this or not very carefully) use the "check option" when creating the view:
create view DEPT_TEN
as
select EMPNO, DEPTNO, ENAME
from EMP
where DEPTNO = 10
with check option;
Are views updated when I update base tables ?
Yes, that is the whole idea of views. The only thing Oracle stores for a view is the text of the definition. When
you select from a view, Oracle looks up the text used to define the view and then executes that query.
Should we use complex views that cruel performance ?
Because view queries that involve sorting, grouping, etc can lead to a high performance overhead, it might be better to write some reports with a procedural component that fills up a temporary table and then does a number of queries from it.
While this is non-relational, it can be justified for some cases. Nevertheless, it is useful to have the view
definition in the database. You can then test the output from the view against the output from your procedural manipulations. The view definition can also be used as the unambiguous gospel.
How can I get the definition of a view ?
There are a number of dictionary views that include the text of views. You can select these quite happily, but
remember, if using SQL*Plus to use the SET command to fiddle with ARRAYSIZE, MAXDATA and LONG parameters.
1. Can I Update From Another Table ?
2. Can I Remove Duplicate Rows ?
3. What is ROWID good for ?
4. What is ROWNUM good for ?
5. Can I implement Tree Structured Queries ?
6. How can I get information on the row based on group information ?
7. How can I get a name for a temporary table that will not clash ?
8. How can I discover what tables, columns, etc are there ?
9. How can I rename a column ?
10. Is there a formatter for SQL or PL/SQL ?
11. How come records for the date I want are missing ?
12. How can I interpret a two digit year after 2000 ?
13. What is a cluster ?
14. How do I control which rollback segment I use ?
15. How do order a union ?
16. Who are SCOTT, SYSTEM and SYS ?
17. How do I avoid blowing rollback segments ?
18. How can I restore passwords ?
Can I Update From Another Table ?
Yes. For example, if we had a table DEPT_SUMMARY, we could update the number of employees field as
follows:
update DEPT_SUMMARY s
set NUM_EMPS = (
select count(1)
from EMP E
where E.DEPTNO = S.DEPTNO );
Can I remove duplicate rows ?
Yes, using the ROWID field. The ROWID is guaranteed unique. There are many variations on this theme, but the logic is to delete all but one record for each key value.
delete from EMP E
where not E.ROWID = (
select min(F.ROWID)
from EMP F
where F.EMP_ID = E.EMP_ID );
Can I implement Tree Structured Queries ?
Yes! This is commonly asked by those migrating from non-RDBMS apps. This is definitely non-relational
(enough to kill Codd and then make him roll in his grave) and is a feature I have not seen in the competition.
The definitive example is in the example SCOTT/TIGER database, when looking at the EMP table (EMPNO and MGR columns). The MGR column contains the employee number of the "current" employee's boss.
You have available an extra pseudo-column, LEVEL, that says how deep in the tree you are. Oracle can handle queries with a depth up to 255.
select LEVEL, EMPNO, ENAME, MGR
from EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
You can get an "indented" report by using the level number to substring or lpad a series of spaces and
concatenate that to the string.
select lpad(' ', LEVEL * 2) || ENAME ........
You use the start with clause to specify the start of the tree(s). More than one record can match the starting
condition.
One disadvantage of a "connect by prior" is that you cannot perform a join to other tables. Still, I have not
managed to see anything else like the "connect by prior" in the other vendor offerings and I like trees. Even
trying to doing this programmatically in embedded SQL is difficult as you have to do the top level query, for each of them open a cursor to look for lower level rows, for each of these.......
soon you blow the cursor limit for your installation.
The way around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the
select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
Note that you can't trick Oracle by using CONNECT BY PRIOR on a view that does the join.
How can I get information on the row based on group information ?
Imagine we have the EMP table and want details on the employee who has the highest salary. You need to use a subquery.
select e.ENAME, e.EMPNO, e.SAL
from EMP e
where e.SAL in (
select max (e2.SAL)
from EMP e2
);
You could get similar info on employees with the highest salary in their departments as follows
select e.ENAME, e.DEPTNO, e.SAL
from EMP e
where e.SAL = (
select max (e2.SAL)
from EMP e2
where e2.DEPTNO = e.DEPTNO
);
How can I get a name for a temporary table that will not clash ?
Use a sequence, and use the number to help you build the temporary table name. Note that SQL-92 is developing specific constructs for using temporary tables.
How can I discover what tables, columns, etc are there ?
Oracle maintains a live set of views that you can query to tell you what you have available. In V6, the first two to look at are DICT and DICT_COLUMNS which act as a directory of the other dictionary views. It is a good idea to be familiar with these.
Not all of these views are accessible by all users. If you are a DBA you should also create private DBA
synonyms by running $ORACLE_HOME/rdbms/admin/dba_syn.sql in your account.
How can I rename a column ?
There is no way a column can be renamed using normal SQL. It can be done carefully by the DBA playing around with internal SYS dictionary tables and bouncing the database, but this is not supported. (I have successfully done it in V4 thru V7). Do backup the database first unless you feel brave. I've written a quick and dirty script rncol.sql to do this. If you can't figure out how to use it from the source you definitely should not run it.
You can use a similar dirty trick for changing ownership of tables if storage space is limited.
Is there a formatter for SQL or PL/SQL ?
There are a number of "beautifiers" for various program languages. The cb and indent programs for the C
language spring to mind (although they have slightly different conventions). As far as I know there is no PD
formatter for SQL available.
Given that there are PD general SQL parsers and that the SQL standards are drafted in something close to BNF, maybe someone could base a reformatter based on the grammar.
Note that you CANNOT use cb and indent with Pro*C as both these programs will screw up the embedded SQL code.
I have recently heard that Kumaran Systems (see Vendor list) have a Forms PL/SQL and SQL formatter, but I do not now if they have unbundled it.
How come records for the date I want are missing ?
You are trying to retrieve data based on something like:
SELECT fld1, fld2 FROM tbl WHERE date_field = '18-jun-60'
You *know* there are records for that day - but none of them are coming back to you.
What has happened is that your records are not set to midnight (which is the default value if time of day not
specified).
You can either use to_char and to_date functions, which can be a bad move regarding SQL performance, or you can say
WHERE date_field >= '18-jun-60' AND date_field < '19-jun-60'
An alternative could be something like
WHERE date_field between '18-jun-1960'
AND to_date('23:59:59 18-jun-60', 'HH24:......YY') ;
How can I interpret a two digit year after 2000 ?
When converting to dates from characters when you only have two characters for the year, the picture format "RR" will be interpreted as the year based on a guess that that date is between 1950 and 2049.
What are these V$ tables?
There are a number of tables/views beginning with V$ that hold gory details for performance monitoring. These are not guaranteed to be stable from minor release to minor release and are for DBAs only.
There are usually no real underlying tables (unlike SYS.OBJ$) and are dummied up by the RDBMS kernel
software in much the same way that UNIX System V.4 dummies up the files in the /proc or /dev/proc directories.
If you have any code depending on these (and the widely used tools supplied by Oracle but unsupported are in this category) then you need to verify that everything works each time you upgrade your database. And when a major revision changes, all bets are off.
How do I get a top ten ?
This question often gets the response WHERE ROWNUM <= 10 but this will not work (except accidentally)
because the ROWNUM pseudocolumn is generated before the ORDER or WHERE clauses come into effect.
One elegant SQL-only approach (although it will be a bitch on a large table) was suggested by
Stowe@aol.com
select a.ordered_column, a.other_stuff
from table_name a
where 10 > (
select count(1)
from table_name b
where b.ordered_column
> a.ordered_column )
order by a.ordered_columnl;
I do not believe that straight SQL is the way to go for such problems when you have PL/SQL available.
My approach is to use PL/SQL instead (in SQL*Plus):
variable tenthsal number
declare
n number;
cursor c1 is
select SAL from EMP order BY SAL desc;
begin
open c1;
for n in 1..10 loop
fetch c1 into :tenthsal;
end loop;
close c1;
end:
/
select * from EMP
where SAL <= :tenthsal
order by SAL desc;
Late news: index descending hint to SQL works if you use a dummy restriction to force use of the index. Needs V7, etc.
How do control which rollback segment I use ?
In SQL, you may need to control the rollback segment used as the default rollback segment may be too small for the required transaction, or you may want to ensure that your transaction runs in a special rollback segment, unaffected by others. The statement is as follows:
SET TRANSACTION USE ROLLBACK SEGMENT segment_name;
On a related note, if all you are doing are SELECTS, it is worth telling the database of this using the following:
SET TRANSACTION READ ONLY;
Both these statements must be the first statement of the transaction.
How do I order a union ?
(Governments around the world have been trying to figure this one out).
Use the column number.
Say we are getting a list of names and codes and want it ordered by the name, using both EMP and DEPT tables:
select DEPTNO, DNAME from DEPT
union
select EMPNO, ENAME from EMP
order by 2;
Who are SCOTT, SYSTEM and SYS ?
These three users are common in many databases. See the glossary entries under SCOTT, SCOTT and SYS. Another common user/password is PLSQL/SUPERSECRET used for PL/SQL demo stuff.
How can I avoid blowing rollback segments ?
The simple answer is make sure you have them big enough and keep your transactions small, but that is being a smartarse.
More recent versions of Oracle have an option for the session that you can set that commits every so many DML statements. This is OK except for where you are doing your work in a single statement rather than using PL/SQL and a loop construct.
Imagine you have a HUGE table and need to update it, possibly updating the key. You cannot update it in one go because your rollback segments are too small. You cannot open a cursor and commit every n records, because usually the cursor will close. You cannot have a number of updates of a few records each because the keys may change - causing you to visit records more than once.
The solution I have used was to have one process select ROWID from the appropriate rows and pump these (via standard I/O) to another process that looped around reading ROWIDs from standard input, updating the
appropriate record and committing every 10 records or so. This was very easy to program and also was quite fast in execution. The number of locks and size of rollback segments required was minimal.
If you are writing in Pro*C and use MODE=ORACLE, there are ways around it too, but not if you are using
MODE=ANSI.
How can I restore passwords ?
OK, so this is really a DBA question, but it is worth putting in here because it involves SQL regardless of
interface.
First, look at the PASSWORD column in DBA_USERS. It looks like gobbledygook because it is an encrypted password. However you can use this if you have saved it somewhere else. Say you want to impersonate a user in a batch run overnight. First stash the gobbledygook password away somewhere, grant connect to the user identified by some password you know and then run your batches using the new known password.
To restore the password to what it was use the following syntax (which I think is undocumented).
grant connect to SCOTT identified by passwords GOBBLEDYGOOK;
Note especially the S on the end of PASSWORDS.
Avoiding Mutating Tables
Ok, so you've just recieved the error:
ORA-04091: table XXXX is mutating, trigger/function may not see it
and you want to get around that. This short article will describe and demonstrate the various methods of getting around the mutating table error.
If you are interested in why you are getting it and in what cases you will get it, please see the Oracle Server Application Developers Guide (click here to read it right now -- this link is to technet.oracle.com. You need a password to access this site but you can get one right away for free).
Avoiding the mutating table error is fairly easy. We must defer processing against the mutating or constrainng table until an AFTER trigger. We will consider two cases:
Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values
Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the :old values
Case 1 - you only need to access the :new values
This case is the simplest. What we will do is capture the ROWIDS of the inserted or udpated rows. We can then use these ROWIDS in an AFTER trigger to query up the affected rows.
It always takes 3 triggers to work around the mutating table error. They are:
A before trigger to set the package state to a known, consistent state
An after, row level trigger to capture each rows changes
An after trigger to actually process the change.
As an example -- to show how to do this, we will attempt to answer the following question:
I have a table containing a key/status/effective date combination. When status
changes, the values are propagated by trigger to a log table recording the
status history. When no RI constraint is in place everything works fine.
When an RI trigger enforces a parent-child relationship, the status change
logging trigger fails because the parent table is mutating. Propagating the
values to the log table implicitly generates a lookup back to the parent table
to ensure the RI constraint is satisfied.
I do not want to drop the RI constraint. I realize that the status is
denormalized. I want it that way. What is a good way to maintain the log?
Here is the implementation:
SQL> create table parent
2 ( theKey int primary key,
3 status varchar2(1),
4 effDate date
5 )
6 /
Table created.
SQL> create table log_table
2 ( theKey int references parent(theKey),
3 status varchar2(1),
4 effDate date
5 )
6 /
Table created.
SQL> REM this package is used to maintain our state. We will save the rowids of newly
SQL> REM inserted / updated rows in this package. We declare 2 arrays -- one will
SQL> REM hold our new rows rowids (newRows). The other is used to reset this array,
SQL> REM it is an 'empty' array
SQL> create or replace package state_pkg
2 as
3 type ridArray is table of rowid index by binary_integer;
4
4 newRows ridArray;
5 empty ridArray;
6 end;
7 /
Package created.
SQL> REM We must set the state of the above package to some known, consistent state
SQL> REM before we being processing the row triggers. This trigger is mandatory,
SQL> REM we *cannot* rely on the AFTER trigger to reset the package state. This
SQL> REM is because during a multi-row insert or update, the ROW trigger may fire
SQL> REM but the AFTER tirgger does not have to fire -- if the second row in an update
SQL> REM fails due to some constraint error -- the row trigger will have fired 2 times
SQL> REM but the AFTER trigger (which we relied on to reset the package) will never fire.
SQL> REM That would leave 2 erroneous rowids in the newRows array for the next insert/update
SQL> REM to see. Therefore, before the insert / update takes place, we 'reset'
SQL> create or replace trigger parent_bi
2 before insert or update on parent
3 begin
4 state_pkg.newRows := state_pkg.empty;
5 end;
6 /
Trigger created.
SQL> REM This trigger simply captures the rowid of the affected row and
SQL> REM saves it in the newRows array.
SQL> create or replace trigger parent_aifer
2 after insert or update of status on parent for each row
3 begin
4 state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid;
5 end;
6 /
Trigger created.
SQL> REM this trigger processes the new rows. We simply loop over the newRows
SQL> REM array processing each newly inserted/modified row in turn.
SQL> create or replace trigger parent_ai
2 after insert or update of status on parent
3 begin
4 for i in 1 .. state_pkg.newRows.count loop
5 insert into log_table
6 select theKey, status, effDate
7 from parent where rowid = state_pkg.newRows(i);
8 end loop;
9 end;
10 /
Trigger created.
SQL> REM this demonstrates that we can process single and multi-row inserts/updates
SQL> REM without failure (and can do it correctly)
SQL> insert into parent values ( 1, 'A', sysdate-5 );
1 row created.
SQL> insert into parent values ( 2, 'B', sysdate-4 );
1 row created.
SQL> insert into parent values ( 3, 'C', sysdate-3 );
1 row created.
SQL> insert into parent select theKey+6, status, effDate+1 from parent;
3 rows created.
SQL> select * from log_table;
THEKEY S EFFDATE
---------- - ---------
1 A 04-AUG-99
2 B 05-AUG-99
3 C 06-AUG-99
7 A 05-AUG-99
8 B 06-AUG-99
9 C 07-AUG-99
6 rows selected.
SQL> update parent set status = chr( ascii(status)+1 ), effDate = sysdate;
6 rows updated.
SQL> select * from log_table;
THEKEY S EFFDATE
---------- - ---------
1 A 04-AUG-99
2 B 05-AUG-99
3 C 06-AUG-99
7 A 05-AUG-99
8 B 06-AUG-99
9 C 07-AUG-99
1 B 09-AUG-99
2 C 09-AUG-99
3 D 09-AUG-99
7 B 09-AUG-99
8 C 09-AUG-99
9 D 09-AUG-99
12 rows selected.
Case 2 - you need to access the :old values
This one is a little more involved but the concept is the same. We'll save the actual OLD values in an array (as opposed to just the rowids of the new rows). Using tables of records this is fairly straightforward. Lets say we wanted to implement a flag delete of data -- that is, instead of actually deleting the record, you would like to set a date field to SYSDATE and keep the record in the table (but hide it from queries). We need to 'undo' the delete.
In Oracle8.0 and up, we could use "INSTEAD OF" triggers on a view to do this, but in 7.3 the implementation would look like this:
SQL> REM this is the table we will be flag deleting from.
SQL> REM No one will ever access this table directly, rather,
SQL> REM they will perform all insert/update/delete/selects against
SQL> REM a view on this table..
SQL> create table delete_demo ( a int,
2 b date,
3 c varchar2(10),
4 hidden_date date default to_date( '01-01-0001', 'DD-MM-YYYY' ),
5 primary key(a,hidden_date) )
6 /
Table created.
SQL> REM this is our view. All DML will take place on the view, the table
SQL> REM will not be touched.
SQL> create or replace view delete_demo_view as
2 select a, b, c from delete_demo where hidden_date = to_date( '01-01-0001', 'DD-MM-YYYY' )
3 /
View created.
SQL> grant all on delete_demo_view to public
2 /
Grant succeeded.
SQL> REM here is the state package again. This time the array is of
SQL> REM TABLE%ROWTYPE -- not just a rowid
SQL> create or replace package delete_demo_pkg
2 as
3 type array is table of delete_demo%rowtype index by binary_integer;
4
4 oldvals array;
5 empty array;
6 end;
7 /
Package created.
SQL> REM the reset trigger...
SQL> create or replace trigger delete_demo_bd
2 before delete on delete_demo
3 begin
4 delete_demo_pkg.oldvals := delete_demo_pkg.empty;
5 end;
6 /
Trigger created.
SQL> REM Here, instead of capturing the rowid, we must capture the before image
SQL> REM of the row.
SQL> REM We cannot really undo the delete here, we are just capturing the deleted
SQL> REM data
SQL> create or replace trigger delete_demo_bdfer
2 before delete on delete_demo
3 for each row
4 declare
5 i number default delete_demo_pkg.oldvals.count+1;
6 begin
7 delete_demo_pkg.oldvals(i).a := :old.a;
8 delete_demo_pkg.oldvals(i).b := :old.b;
9 delete_demo_pkg.oldvals(i).c := :old.c;
10 end;
11 /
Trigger created.
SQL> REM Now, we can put the deleted data back into the table. We put SYSDATE
SQL> REM in as the hidden_date field -- that shows us when the record was deleted.
SQL> create or replace trigger delete_demo_ad
2 after delete on delete_demo
3 begin
4 for i in 1 .. delete_demo_pkg.oldvals.count loop
5 insert into delete_demo ( a, b, c, hidden_date )
6 values
7 ( delete_demo_pkg.oldvals(i).a, delete_demo_pkg.oldvals(i).b,
8 delete_demo_pkg.oldvals(i).c, sysdate );
9 end loop;
10 end;
11 /
Trigger created.
SQL> REM Now, to show it at work...
SQL> insert into delete_demo_view values ( 1, sysdate, 'Hello' );
1 row created.
SQL> insert into delete_demo_view values ( 2, sysdate, 'Goodbye' );
1 row created.
SQL> select * from delete_demo_view;
A B C
---------- --------- ----------
1 09-AUG-99 Hello
2 09-AUG-99 Goodbye
SQL> delete from delete_demo_view;
2 rows deleted.
SQL> select * from delete_demo_view;
no rows selected
SQL> select * from delete_demo;
A B C HIDDEN_DA
---------- --------- ---------- ---------
1 09-AUG-99 Hello 09-AUG-99
2 09-AUG-99 Goodbye 09-AUG-99
Interview Questions for Oracle, DBA, Developer Candidates
Start---------------
PL/SQL Questions:
1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn’t have to.
2. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
4. What packages (if any) has Oracle provided for use by developers?
Level: Intermediate to high
Expected answer: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.
5. Describe the use of PL/SQL tables
Level: Intermediate
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
6. When is a declare statement needed ?
Level: Low
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?
Level: Intermediate
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Level: Intermediate
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
9. How can you find within a PL/SQL block, if a cursor is open?
Level: Low
Expected answer: Use the %ISOPEN cursor status variable.
10. How can you generate debugging output from PL/SQL?
Level:Intermediate to high
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
11. What are the types of triggers?
Level:Intermediate to high
Expected Answer: There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
End-------------------------------------------------
DBA:
1. Give one method for transferring a table from one schema to another:
Level:Intermediate
Expected Answer: There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.
2. What is the purpose of the IMPORT option IGNORE? What is it’s default setting?
Level: Low
Expected Answer: The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N.
3. You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal?
Level: Low
Expected answer: Use the ALTER TABLESPACE ..... SHRINK command.
4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?
Level: Low
Expected answer: The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
5. What are some of the Oracle provided packages that DBAs should be aware of?
Level: Intermediate to High
Expected answer: Oracle provides a number of packages in the form of the DBMS_ packages owned by the SYS user. The packages used by DBAs may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT. They may also try to answer with the UTL*.SQL or CAT*.SQL series of SQL procedures. These can be viewed as extra credit but aren’t part of the answer.
6. What happens if the constraint name is left out of a constraint clause?
Level: Low
Expected answer: The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
7. What happens if a tablespace clause is left off of a primary key constraint clause?
Level: Low
Expected answer: This results in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
8. What is the proper method for disabling and re-enabling a primary key constraint?
Level: Intermediate
Expected answer: You use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys.
9. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
Level: Intermediate
Expected answer: The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
10. (On UNIX) When should more than one DB writer process be used? How many should be used?
Level: High
Expected answer: If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter.
11. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?
Level: High
Expected answer: You can’t use hot backup without being in archivelog mode. So no, you couldn’t recover.
12. What causes the "snapshot too old" error? How can this be prevented or mitigated?
Level: Intermediate
Expected answer: This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.
13. How can you tell if a database object is invalid?
Level: Low
Expected answer: By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.
14. A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?
Level: Low
Expected answer: You need to check that the user has specified the full name of the object (select empid from scott.emp; instead of select empid from emp;) or has a synonym that points to the object (create synonym emp for scott.emp;)
15. A developer is trying to create a view and the database won’t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem?
Level: Intermediate
Expected answer: You need to verify the developer has direct grants on all tables used in the view. You can’t create a stored object with grants given through views.
16. If you have an example table, what is the best way to get sizing data for the production table implementation?
Level: Intermediate
Expected answer: The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.
17. How can you find out how many users are currently logged into the database? How can you find their operating system id?
Level: high
Expected answer: There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l’ command, but this only works against a single instance installation.
18. A user selects from a sequence and gets back two values, his select is:
SELECT pk_seq.nextval FROM dual;
What is the problem?
Level: Intermediate
Expected answer: Somehow two values have been inserted into the dual table. This table is a single row, single column table that should only have one value in it.
19. How can you determine if an index needs to be dropped and rebuilt?
Level: Intermediate
Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn’t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio
BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
Start-----------------------------------
SQL/ SQLPlus
1. How can variables be passed to a SQL routine?
Level: Low
Expected answer: By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
2. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
Level: Intermediate to high
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn’t always portable is to use the return/linefeed as a part of a quoted string.
3. How can you call a PL/SQL procedure from SQL?
Level: Intermediate
Expected answer: By use of the EXECUTE (short form EXEC) command.
4. How do you execute a host operating system command from within SQL?
Level: Low
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an example
Level: Intermediate to high
Expected answer: This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ‘drop user ‘||username||’ cascade;’ from dba_users
where username not in ("SYS’,’SYSTEM’);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ‘||’ the values selected from the database.
6. What SQLPlus command is used to format output from a select?
Level: low
Expected answer: This is best done with the COLUMN command.
7. You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
Level: Intermediate
Expected answer: The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
Level: Intermediate to high
Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?
Level: High
Expected answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
10. What is a Cartesian product?
Level: Low
Expected answer: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
Level: High
Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
12. What is the default ordering of an ORDER BY clause in a SELECT statement?
Level: Low
Expected answer: Ascending
13. What is tkprof and how is it used?
Level: Intermediate to high
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
14. What is explain plan and how is it used?
Level: Intermediate to high
Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
15. How do you set the number of lines on a page of output? The width?
Level: Low
Expected answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
16. How do you prevent output from coming to the screen?
Level: Low
Expected answer: The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
17. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
Level: Low
Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF.
18. How do you generate file output from SQL?
Level: Low
Expected answer: By use of the SPOOL command
End ------------------------------------------------
Tuning Questions:
1. A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
Level: Intermediate
Expected answer: Multiple extents in and of themselves aren’t bad. However if you also have chained rows this can hurt performance.
2. How do you set up tablespaces during an Oracle installation?
Level: Low
Expected answer: You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.
3. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Level: Low
Expected answer: Ensure that users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Level: Intermediate
Expected answer: Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.
5. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Level: High
Expected answer: Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.
6. What is the fastest query method for a table?
Level: Intermediate
Expected answer: Fetch by rowid
7. Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
Level: High
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
Level: Intermediate
Expected answer: If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.
9. When should you increase copy latches? What parameters control copy latches?
Level: high
Expected answer: When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
10. Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
Level: Low
Expected answer: You can look in the init
11. Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
Level: Intermediate
Expected answer: The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
Level: high
Expected answer: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
13. When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it?
Level: high
Expected answer: Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.
14. If you see contention for library caches how can you fix it?
Level: Intermediate
Expected answer: Increase the size of the shared pool.
15. If you see statistics that deal with "undo" what are they really talking about?
Level: Intermediate
Expected answer: Rollback segments and associated structures.
16. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?
Level: High
Expected answer: The SMON process won’t automatically coalesce its free space fragments.
17. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
Level: High
Expected answer: In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#';’ command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ‘alter tablespace
18. How can you tell if a tablespace has excessive fragmentation?
Level: Intermediate
If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.
19. You see the following on a status report:
redo log space requests 23
redo log space wait time 0
Is this something to worry about? What if redo log space wait time is high? How can you fix this?
Level: Intermediate
Expected answer: Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.
20. What can cause a high value for recursive calls? How can this be fixed?
Level: High
Expected answer: A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
21. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
Level: Intermediate
Expected answer: This indicate that the shared pool may be too small. Increase the shared pool size.
22. If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Level: Intermediate
Expected answer: Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.
23. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?
Level: High
Expected answer: A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.
24. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
Level: High
Expected answer: A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
25. In a system with an average of 40 concurrent users you get the following from a query on rollback extents:
ROLLBACK CUR EXTENTS
--------------------- --------------------------
R01 11
R02 8
R03 12
R04 9
SYSTEM 4
You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action?
Level: Intermediate
Expected answer: No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.
26. You see multiple extents in the temporary tablespace. Is this a problem?
Level: Intermediate
Expected answer: As long as they are all the same size this isn’t a problem. In fact, it can even improve performance since Oracle won’t have to create a new extent when a user needs one.
Installation/Configuration
1. Define OFA.
Level: Low
Expected answer: OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.
2. How do you set up your tablespace on installation?
Level: Low
Expected answer: The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.
3. What should be done prior to installing Oracle (for the OS and the disks)?
Level: Low
Expected Answer: adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.
4. You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem?
Level: Intermediate to high
Expected Answer: Check to make sure that the archiver isn’t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.
5. When configuring SQLNET on the server what files must be set up?
Level: Intermediate
Expected answer: INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
6. When configuring SQLNET on the client what files need to be set up?
Level: Intermediate
Expected answer: SQLNET.ORA, TNSNAMES.ORA
7. What must be installed with ODBC on the client in order for it to work with Oracle?
Level: Intermediate
Expected answer: SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.
8. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for?
Level: Intermediate
Expected answer: The first thing to check with a large SGA is that it isn’t being swapped out.
9. What OS user should be used for the first part of an Oracle installation (on UNIX)?
Level: low
Expected answer: You must use root first.
10. When should the default values for Oracle initialization parameters be used as is?
Level: Low
Expected answer: Never
11. How many control files should you have? Where should they be located?
Level: Low
Expected answer: At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.
12. How many redo logs should you have and how should they be configured for maximum recoverability?
Level: Intermediate
Expected answer: You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
13. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces?
Expected answer: At least 7, see disk configuration answer above.
---------------------------------------------
Data Modeler:
1. Describe third normal form?
Level: Low
Expected answer: Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
2. Is the following statement true or false:
"All relational databases must be in third normal form"
Why or why not?
Level: Intermediate
Expected answer: False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
3. What is an ERD?
Level: Low
Expected answer: An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.
4. Why are recursive relationships bad? How do you resolve them?
Level: Intermediate
A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn’t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.
5. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
Level: Low to intermediate
Expected answer: This means the two entities should probably be made into one entity.
6. How should a many-to-many relationship be handled?
Level: Intermediate
Expected answer: By adding an intersection entity table
7. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used?
Level: Intermediate
Expected answer: A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
8. When should you consider denormalization?
Level: Intermediate
Expected answer: Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
UNIX:
1. How can you determine the space left in a file system?
Level: Low
Expected answer: There are several commands to do this: du, df, or bdf
2. How can you determine the number of SQLNET users logged in to the UNIX system?
Level: Intermediate
Expected answer: SQLNET users will show up with a process unique name that begins with oracle
3. What command is used to type files to the screen?
Level: Low
Expected answer: cat, more, pg
4. What command is used to remove a file?
Level: Low
Expected answer: rm
5. Can you remove an open file under UNIX?
Level: Low
Expected answer: yes
6. How do you create a decision tree in a shell script?
Level: intermediate
Expected answer: depending on shell, usually a case-esac or an if-endif or fi structure
7. What is the purpose of the grep command?
Level: Low
Expected answer: grep is a string search command that parses the specified string from the specified file or files
8. The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program?
Level: intermediate
Expected answer: ps -ef|grep *nocomp*|wc -l
9. What is an inode?
Level: Intermediate
Expected answer: an inode is a file status indicator. It is stored in both disk and memory and tracts file status. There is one inode for each file on the system.
10. The system administrator tells you that the system hasn’t been rebooted in 6 months, should he be proud of this?
Level: High
Expected answer: Maybe. Some UNIX systems don’t clean up well after themselves. Inode problems and dead user processes can accumulate causing possible performance and corruption problems. Most UNIX systems should have a scheduled periodic reboot so file systems can be checked and cleaned and dead or zombie processes cleared out.
11. What is redirection and how is it used?
Level: Intermediate
Expected answer: redirection is the process by which input or output to or from a process is redirected to another process. This can be done using the pipe symbol "|", the greater than symbol ">" or the "tee" command. This is one of the strengths of UNIX allowing the output from one command to be redirected directly into the input of another command.
12. How can you find dead processes?
Level: Intermediate
Expected answer: ps -ef|grep zombie -- or -- who -d depending on the system.
13. How can you find all the processes on your system?
Level: Low
Expected answer: Use the ps command
14. How can you find your id on a system?
Level: Low
Expected answer: Use the "who am i" command.
15. What is the finger command?
Level: Low
Expected answer: The finger command uses data in the passwd file to give information on system users.
16. What is the easiest method to create a file on UNIX?
Level: Low
Expected answer: Use the touch command
17. What does >> do?
Level: Intermediate
Expected answer: The ">>" redirection symbol appends the output from the command specified into the file specified. The file must already have been created.
18. If you aren’t sure what command does a particular UNIX function what is the best way to determine the command?
Expected answer: The UNIX man -k
-----------------------------------------------------
Oracle Troubleshooting:
1. How can you determine if an Oracle instance is up from the operating system level?
Level: Low
Expected answer: There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX a ps -ef|grep dbwr will show what instances are up.
2. Users from the PC clients are getting messages indicating :
Level: Low
ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)
What could the problem be?
Expected answer: The instance name is probably incorrect in their connection string.
3. Users from the PC clients are getting the following error stack:
Level: Low
ERROR: ORA-01034: ORACLE not available
ORA-07318: smsget: open error when opening sgadef.dbf file.
HP-UX Error: 2: No such file or directory
What is the probable cause?
Expected answer: The Oracle instance is shutdown that they are trying to access, restart the instance.
4. How can you determine if the SQLNET process is running for SQLNET V1? How about V2?
Level: Low
Expected answer: For SQLNET V1 check for the existence of the orasrv process. You can use the command "tcpctl status" to get a full status of the V1 TCPIP server, other protocols have similar command formats. For SQLNET V2 check for the presence of the LISTENER process(s) or you can issue the command "lsnrctl status".
5. What file will give you Oracle instance status information? Where is it located?
Level: Low
Expected answer: The alert
6. Users aren’t being allowed on the system. The following message is received:
Level: Intermediate
ORA-00257 archiver is stuck. Connect internal only, until freed
What is the problem?
Expected answer: The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.
7. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?
Level: Intermediate
Expected answer: There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert
8. You attempt to add a datafile and get:
Level: Intermediate
ORA-01118: cannot add anymore datafiles: limit of 40 exceeded
What is the problem and how can you fix it?
Expected answer: When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
9. You look at your fragmentation report and see that smon hasn’t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem?
Level: High
Expected answer: Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
10. Your users get the following error:
Level: Intermediate
ORA-00055 maximum number of DML locks exceeded
What is the problem and how do you fix it?
Expected answer: The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
11. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?
Level: High
Expected answer: As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following:
CONNECT INTERNAL
STARTUP MOUNT
(Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE .... OFFLINE;)
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
(bring read-only tablespaces back online)
Shutdown and backup the system, then restart
If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well.
If no backup of the control file is available then the following will be required:
CONNECT INTERNAL
STARTUP NOMOUNT
CREATE CONTROL FILE .....;
However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.
No comments:
Post a Comment