Frequently Asked Questions

Q.  What are the components of physical database structure of Oracle database?

One or more datafiles,
Two are more redo log files,
One or more control files.

Q. What are the components of logical database structure of Oracle database?

There are tablespaces and database's schema objects.

Q. What is a tablespace?

A database is divided into Logical Storage Units called tablespaces. A tablespace is used to grouped related logical structures together.

Q. What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Q. Explain the relationship among database, tablespace and data file.

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

Q. What is schema?

A schema is collection of database objects of a user.

Q. What are Schema Objects?

Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

Q. Can objects of the same schema reside in different tablespaces?

Yes.

Q. Can a tablespace hold objects from different schemes?

Yes.

Q. What is database link?

A database link is a named object that describes a "path" from one database to another.

Q. What are the types of database links?

Private database link, public database link & network database link.

Q. What is private database link?

Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

Q. What is public database link?

Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

Q. What is network database link?

Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

Q. What is row chaining?

In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

Q. What is an extent?

An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

Q. What is a segment?

A segment is a set of extents allocated for a certain logical structure.

Q. What are the different types of segments?

Data segment, index segment, rollback segment and temporary segment.

Q. What is a data segment?

Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

Q. What is an index segment?

Each index has an index segment that stores all of its data.

Q. What is rollback segment?

A database contains one or more rollback segments to temporarily store "undo" information.

Q. What are the uses of rollback segment?

To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

Q. What is a temporary segment?

Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

Q. What is a datafile?

Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

Q. What are the characteristics of data files?

A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

Q. What is a redo log?

The set of redo log files for a database is collectively known as the database redo log.

Q. What is the function of redo log?

The primary function of the redo log is to record all changes made to data.

 Q. What is the use of redo log information?

The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

Q. What does a control file contains?

- Database name
- Names and locations of a database's files and redolog files.
- Time stamp of database creation.

Q. What is the use of control file?

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Q. What is a database instance?

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users. The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

Q. What is Parallel Server?

Multiple instances accessing the same database (only in multi-CPU environments)

Q. What is an index? How it is implemented in Oracle database?

An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command

Q. What are the basic element of base configuration of an Oracle database?

It consists of
          one or more data files.
          one or more control files.
           two or more redo log files.
The Database contains
          multiple users/schemas
          one or more rollback segments
          one or more tablespaces
          Data dictionary tables
          User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers,Shared SQL pool)
          SMON (System MONitor)
          PMON (Process MONitor)
          LGWR (LoG  Write)
          DBWR (Data Base Write)
          ARCH (ARCHiver)
          CKPT  (Check Point)
          RECO
          Dispatcher
          User Process with associated PGS

Q. What is SGA?

The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

Q. What is a shared pool?

The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.

Q. What is mean by Program Global Area (PGA)?

It is area in memory that is used by a single Oracle user process.

Q. What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient shared pool size.
Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

Q. What is Database Buffers?

Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

Q. What is dictionary cache?

Dictionary cache is information about the database objects stored in a data dictionary table.

Q. What is meant by recursive hints?

Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of data dictionary cache.

Q. What is redo log buffer?

Changes made to the records are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into redo log files frequently. LOG_BUFFER parameter will decide the size.

Q. How will you swap objects into a different table space for an existing database?

- Export the user
- Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql.
- Drop necessary objects.
- Run the script newfile.sql after altering the tablespaces.
- Import from the backup for the necessary objects.

Q. List the Optional Flexible Architecture (OFA) of Oracle database?  How can we organize the tablespaces in Oracle database to have maximum performance?

SYSTEM -           Data dictionary tables.
DATA  -               Standard operational tables.
DATA2-               Static tables used for standard operations
INDEXES -          Indexes for Standard operational tables.
INDEXES1 -        Indexes of static tables used for standard operations.
TOOLS -             Tools table.
TOOLS1 -           Indexes for tools table.
RBS -                  Standard Operations Rollback Segments,
RBS1,RBS2 -      Additional/Special Rollback segments.
TEMP -                Temporary purpose tablespace
TEMP_USER -    Temporary tablespace for users.
USERS -             User tablespace.

Q. How will you force database to use particular rollback segment?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.

Q. What is meant by free extent?

A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.

Q. Which parameter in Storage clause will reduce number of rows per block?

PCTFREE parameter
Row size also reduces no of rows per block.

Q. What is the significance of having storage clause?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updating, etc.,

Q. How does Space allocation table place within a block?

Each block contains entries as follows
Fixed block header
Variable block header
Row Header, row date (multiple rows may exists)
PCTEREE (% of free space for row updating in future)

Q. What is the role of PCTFREE parameter is storage clause?

This is used to reserve certain amount of space in a block for expansion of rows.

Q. What is the OPTIMAL parameter?

It is used to set the optimal length of a rollback segment.

Q. What is the functionality of SYSTEM table space?

To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.

Q. How will you create multiple rollback segments in a database?

- Create a database, which implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.
- Create a second rollback segment name R0 in the SYSTEM tablespace.
- Make new rollback segment available (after shutdown, modify init.ora file and start database)
- Create other tablespaces (RBS) for rollback segments.
- Deactivate rollback segment R0 and activate the newly created rollback segments.

Q. How the space utilization takes place within rollback segments?

It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent (number of extents is based on the optimal size)

Q. Why query fails sometimes?

Rollback segment dynamically extent to handle larger transactions entry loads.
A single transaction may wipeout all available free space in the rollback segment tablespace. This prevents other user using rollback segments.

Q. How will you monitor the space allocation?
By querying DBA_SEGMENTS table/view

Q. How will you monitor rollback segment status?

Querying the DBA_ROLLBACK_SEGS view
IN USE                                   - Rollback Segment is on-line.
AVAILABLE                            - Rollback Segment available but not on-line.
OFF-LINE                     - Rollback Segment off-line
INVALID                        - Rollback Segment Dropped.
NEEDS RECOVERY            - Contains data but need recovery or corrupted.
PARTLY AVAILABLE           - Contains data from an unresolved transaction involving a distributed database.

Q. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend.
Transaction Begins.
An entry is made in the RES header for new transactions entry
Transaction acquires blocks in an extent of RBS
The entry attempts to wrap into second extent.
None is available, so that the RBS must extent.
The RBS checks to see if it is part of its OPTIMAL size.
RBS chooses its oldest inactive segment.
Oldest inactive segment is eliminated.
RBS extents
The data dictionary tables for space management are updated.
Transaction Completes.

Q. How can we plan storage for very large tables?

--Limit the number of extents in the table
--Separate table from its indexes.
--Allocate sufficient temporary storage

Q. How will you estimate the space required by a non-clustered tables?

Calculate the total header size
Calculate the available data space per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.

Q. It is possible to use raw devices as data files and what are the advantages over file system files?

Yes.
The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing onto disk. Disk corruption will be very less.

Q. What is a Control file?

Database's overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.

Q. How to implement the multiple control files for an existing database?

Shutdown the database
Copy one of the existing controlfile to new location
Edit Config ora(init.ora) file by adding new control filename
Restart the database.

Q. What is redo log file mirroring?  How can be achieved?

Process of having a copy of redo log files is called mirroring.
This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

Q. What is advantage of having disk shadowing / mirroring?

Shadow set of disks save as a backup in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

Q. What is use of rollback segments in Oracle database?

They allow the database to maintain read consistency between multiple transactions.

Q. What is a rollback segment entry?

It is the set of before image data blocks that contain rows that are modified by a transaction. Each rollback segment entry must be completed within one rollback segment. A single rollback segment can have multiple rollback segment entries.

Q. What is hit ratio?
It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.

Q. When will be a segment released?

When Segment is dropped.
When Shrink (RBS only)
When truncated (TRUNCATE used with drop storage option)

Q. What are disadvantages of having raw devices?

We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.

Q. List the factors that can affect the accuracy of the estimations?
- The space used transaction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
- Trailing nulls and length bytes are not stored.
- Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.

Q. What is user Account in Oracle database?

A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

Q. How will you enforce security using stored procedures?

Don't grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

Q. What are the dictionary tables used to monitor a database space?

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.

Q. If a view on a single base table is manipulated will the changes be reflected on the base table?

Yes, If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

Q. Give one method for transferring a table from one schema to another:

There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.

 Q. Which table contains the names of all the data dictionary views?

DICTIONARY
The DICTIONARY data dictionary view provides an overview for all the data dictionary views. DB_VIEWS is not a dictionary table. The DBA_VIEWS view contains the text of all views in the database. DBA_VIEW_DATA is not a dictionary table or view.

Q. You have a rollback segment in a version 7.2 database that has expanded beyond  optimal, how can it be restored to optimal?

Use the ALTER TABLESPACE ..... SHRINK command.

Q. Which of the following could be determined by the DBA_FREE_SPACE view?

The size of an extent in Oracle Blocks
DBA_FREE_SPACE displays the tablespace name, relative file number, file ID, block ID, and size of the extent in Oracle blocks. The DBA_FREE_SPACE view does not contain any table information or extent creation dates.

Q. If the DEFAULT and TEMPORARY tablespace clauses are left out of a
CREATE USER command what happens? Is this bad or good? Why?
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).

Q. Your database has a log group with three members. Two of the three members have become corrupted. What would happen when the database tries to write to this log group?

LGWR will write to the available member.
If at least one member of the current group is accessible, the Oracle Server ignores the corrupt members. Because the database can write to the available log member, the database will not hang. The instance will not crash, because the can take place with the one log member. Oracle will not reformat corrupted log members.

Q. What are some of the Oracle provided packages that DBAs should be aware of?
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.

Q. Which script is used to create the data dictionary views?

catalog.sql
The catalog.sql script creates the data dictionary views to decode and summarize the data in the base tables. The catproc.sql script runs required scripts for PL/SQL use in the database. The dbmsutil.sql script creates the DBMS_TRANSACTION, DBMS_SESSION, DBMS_DDL, DBMS_UTILITY, DBMS_APPLICATION_INFO, DBMS_SYSTEM, DBMS_SPACE, and DBMS_ROWID packages. The sql.bsq script creates the base data dictionary tables.

 Q. What happens if the constraint name is left out of a constraint clause?

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.

Q. What is the maximum number of control files that you can specify in the CONTROL FILES parameter?

8 (Eight)

Q. What happens if a tablespace clause is left off of a primary key constraint clause?

 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.

Q. Which parameter would you use to control the space reserved in each data block for updates of existing rows?

PCTFREE
PCTFREE determines the percentage of space in each block that is reserved for updates.
INITRANS specifies the initial number of transaction entries allocated within each data block allocated to the table.
MAXTRANS is the limit of concurrent transactions allowed in a data block.
PCTUSED is the the amount of space Oracle maintains in each data block for inserts.

Q. (On UNIX) When should more than one DB writer process be used? How many should be used?
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.

 Q. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?

You can’t use hot backup without being in archivelog mode. So no, you couldn’t recover.         

Q. Why would you use direct-load inserts when loading data into a table?
-Other rows in the same table can be concurrently modified by other users.
-Data can be quickly copied from one table into another within the same database, because it bypasses the buffer cache.  
During a direct-load insert, users can be concurrently modifying existing data in the table. Redo information is not generated during a direct-load insert. Also, the buffer cache is bypassed to speed up the insert. Direct-load insert does not create any indexes. Data is loaded above the high watermark.

Q. What causes the "snapshot too old" error? How can this be prevented or mitigated?

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.                                 

Q.  How can we identify a full table scan?
When a ‘db file scattered read’ wait event is identified in the V$SYSTEM_EVENT view or in a STATSPACK/ Trace file report.

SQL> desc v$system_event;

 Name                                     Null?    Type
 ------------------------------- --------         ----
 EVENT                                            VARCHAR2(64)
 TOTAL_WAITS                             NUMBER
 TOTAL_TIMEOUTS                      NUMBER
 TIME_WAITED                              NUMBER
 AVERAGE_WAIT                           NUMBER

Q.  Explain Direct and Conventional path in SQL*Loader
In DIRECT path the SQL Processing and database buffer cache is bypassed during data load as against the Conventional path where every stage of SQL processing is done.

Q.  Explain freelist mechanism in brief
Freelist is a list of data blocks that are eligible for INSERTS. Typically, when the usage of a data block drops below PCTUSED, its block address is reflected in the freelist.

Q.  What is Oracle Managed Files(OMF)?
Normally, datafiles and redologs are created as per the options and commands indicated by the DBA as he has control of all the files. In OMF, Oracle manages the datafiles on its own. Maintenance of these files are completely in the hands of Oracle as well as the location, which is specified only once in the INIT.ORA.

Q.  Define the Oracle Data Dictionary and who owns it?
The dictionary is the repository of information about all the objects in the database and is also called as metadata. It is owned by SYS.

Q.  What is the difference between Locally Managed Tablespace(LMT) and Dictionary Managed Tablespace(DMT)?

In LMT, the extents are managed as bitmaps in the datafile whereas in DMT, the extent management is done using the data dictionary tables and the user can specify various space management options/commands during a DDL operation.

Q.  What is the difference between Cost Based Optimizer and Rule Based Optimizer?

CBO calculates the cost of executing a query based on various information gathered and chooses the least expensive path. In RBO, the optimiser blindly follows strict rules according to a defined hierarchy and does not calculate costs like CBO.

Q.  How to change the block size in an Oracle Database?

The only way is to do an export and import of all the tables.

Q.  Explain what is EXPLAIN PLAN?
Explain plan is the detailed step-by-step execution path that a query will take.

Q.  Which view will display the name of the database?

V$DATABASE

Q.  Can you take SYSTEM tablespace offline during the database maintenance?
No

Q.  During the database hot backup, the changes are recorded in:

Datafiles and Redologs

Q.  Table partition option was introduced in which version of Oracle database Server
Oracle 8

Q.  Expand MTS?

Multi Threaded Server

Q. Expand TSPITR and MTTR?

Tablespace Point in Time Recovery & Mean Time to Recover.

Q.  What are the mandatory background processes for Oracle Instance?
SMON, PMON, DBWR, LGWR and CKPT

Q.  Expand Oracle RAC?
Real Application Clusters

Q.  Where do you use “optimal” clause?
In Rollback Segments creation or modification

Q. How would you drop a redo log member from an active group when archiving is enabled?

 Issue the ALTER SYSTEM SWITCH LOGFILE command, then issue the ALTER DATABASE DROP LOGFILE MEMBER command.
To drop a redo log member from an active group, cause a log switch first by using the ALTER SYSTEM SWITCH LOGFILE command and then use the ALTER DATABASE DROP LOGFILE MEMBER command after the archiving of the logfile is complete. You must cause a log switch so the log group is not active. The log group must be archived before you use the ALTER DATABASE DROP LOGFILE MEMBER command. If the database is in ARCHIVELOG mode, you cannot drop an online member until it has been archived. If the group is active, you must force a log switch before dropping one of its members.

Q. When using client-side caching with Oracle Names, what parameter determines the cache refresh rate?

TTL, TTL stands for time to live, which is the parameter
setting that determines how long connection address information should be maintained before it is refreshed. All of the other parameters listed are for the init.ora file on the server and are not used for Oracle Names.

Q. Which is the correct sequence of steps to move a datafile to a new location?

The correct sequence of steps to move a datafile is to
1.   take the tablespace offline,
2.   move the files using OS commands,
3.   issue the ALTER TABLESPACE RENAME DATAFILE command and then bring the tablespace back online. 

Q. Where does Oracle store the database creation timestamp?

Control file
The control file records the structure of the database,including the time that the database was created. Oracle has no database file headers. Tablespaces exist that have datafiles headers, which have information about the datafile and the last checkpoint. Oracle does not write back to parameter files. Redo log files contain information for recovery. Rollback segments keep before images of all changed data that is not committed.

Q. When you create a user, which clause would you use to control the amount of space the user can use in a tablespace?

QUOTA
The QUOTA clause defines the amount of space a user can use to create objects in a specified tablespace. The DEFAULT TABLESPACE clause specifies the tablespace to be used if a tablespace is not provided in a CREATE/ALTER statement. Profiles can be used to manage resources like CPU, session idle time, or logical reads per session, but not space allocation in tablespaces. The TEMPORARY TABLESPACE clause specifies the
tablespace to be used for temporary segments.

Q. Which privilege do you need to execute the ALTER SYSTEM ENABLE RESTRICTED SESSION command?

ALTER SYSTEM
You need the ALTER SYSTEM privilege to use the ALTER SYSTEM command. The RESTRICTED SESSION privilege allows users to connect to a database that has been started or altered to be in restricted mode. The ALTER DATABASE privilege allows you to add datafiles to the database, but it does not give you the privilege to put a database into restricted mode. The ALTER RESOURCE COST privilege allows you to set costs for resources used in a session but does not give you the ability to put a database into restricted mode. ENABLE RESTRICTED SESSION is a clause of the ALTER SYSTEM
command and not a privilege.

 Q. If you want to deallocate some but not all of the unused space in a table, which option of the ALTER TABLE command would you use?

KEEP
To deallocate unused space while keeping a specified number of bytes above the high watermark, use the KEEP option as follows: ALTER TABLE table_name DEALLOCATE UNUSED KEEP integer K/M; The CACHE option is used to control how rows from the table are placed on the LRU list in the buffer cache during a full-table scan.DEALLOCATE UNUSED deallocates all unused space above the high watermark unless the KEEP option is used. MODIFY is used to change the definition of existingcolumns.

Q. Which view would you query to find out the names of the tables and the names of the columns included in a cluster?
DBA_CLU_COLUMNS

DBA_CLU_COLUMNS displays the names of the columns included in the cluster and their corresponding table column. The DBA_CLUSTERS view contains a description of the storage information on all clusters in the database. The DBA_OBJECTS view lists all objects in the database. The DBA_CATALOG view lists all database tables, views, synonyms, and sequences.

Q. How can you show the current locations of the control files using Server Manager in line mode? 

Query V$CONTROLFILE
Query V$PARAMETER
Use the SHOW PARAMETER command
You can query the V$CONTROLFILE or V$PARAMETER view, or execute the SHOW PARAMETER command. The SHOW SGA command displays information about the current instance's system global area. You cannot use the DESCRIBE command on a control file. The V$SYSTEM_PARAMETER view contains information on system parameters, but doesnot include the control file location.

Q. If you set the LOG_CHECKPOINT_INTERVAL initialization parameter to 25, how frequently will a log switch occur?

After LGWR writes 25 operating system blocks
LOG_CHECKPOINT_INTERVAL specifies the number of OS blocks written by LGWR that will trigger a log switch. A&B incorrect, because LOG_CHECKPOINT_ TIMEOUT specifies the number of seconds between checkpoints and the time period for checkpoints. LOG_CHECKPOINT_INTERVAL uses OS blocks, not Oracle blocks, for its value.

Q. Which of the following methods can be used to enable automatic extension for an existing datafile?

Use  ALTER DATABASE datafile ‘<path of the datafile>’ autoextend on/off;
The AUTOEXTEND clause can be used with the ALTER DATABASE command to enable auto extension of an existing datafile. Datafiles can only be dropped if the tablespace is dropped. You cannot extend Oracle datafiles by using OS commands. When a datafile is being moved, the file in the new location must be an exact copy of the original file; it cannot be made larger.

Q. Which parameter would you use to control the space reserved in each data block for updates of existing rows?

PCTFREE
PCTFREE determines the percentage of space in each block that is reserved for updates. INITRANS specifies the initial number of transaction entries allocated within each data block allocated to the table.
MAXTRANS is the limit of concurrent transactions allowed in a data block. PCTUSED is the the amount of space Oracle maintains in each data block for inserts.

Q. What will happen to a user's tables if the quota for the tablespace containing the tables is altered to 0?

No new extents can be allocated to the table.
The objects created by the user remain, but no new extents can be allocated to the objects. Altering a user's quota does not truncate tables. Altering a user's quota has no effect on table accessibility. Altering a user's quota does not drop tables.

Q. Which view would you query to determine if any database user is close to exceeding their space limit on a tablespace?

DBA_TS_QUOTAS
DBA_TS_QUOTAS displays the names of tablespaces that any database user has a QUOTA on, the number of bytes and blocks used, and the users quota in bytes and blocks. The USER_TS_QUOTAS view lists information about tablespace quotas for the user doing the query. The USER_TABLESPACES view lists descriptions of accessible table-spaces. The DBA_TABLESPACES view lists descriptions of all tablespaces.The DBA_USERS view lists information about all users of the database, but does not contain quota information.

Q. In the database buffer cache, which buffers are on the dirty list?
Buffers waiting to be written to disk

Buffers waiting to be written to disk are on the dirty list and cannot be overwritten. Empty buffers are ready for use and do not need to be written to disk. The concept of a null buffer doesn't exist. Buffers currently being accessed are on the LRU list and are not dirty.

Q. Which view shows the SORT EXTENT POOL status?

V$SORT_SEGMENT
V$SORT_SEGMENT displays the status of the sort extent pool for the current instance. V$SORT_USAGE view describes sort usage, but not the SORT EXTENT POOL status. The V$SESSION view lists session  information for each current session. The V$INSTANCE view displays the state of the current instance.

Q. What would happen if you tried to start an Oracle instance and open a database with different database names in the parameter file and the control file?
An instance would start, but the database would not be mounted. The instance would start, but when the control file is read to open the database, the mismatch in database names would stop the database from mounting. The database could not open or mount because of the name mismatch.

Q. Which characteristic describes a role?
It can be disabled for each authorized user.
A role can be enabled or disabled for an individual user granted the role. Roles can have a password, but are not required to. Database triggers are used to enforce business rules and have nothing to do with roles. Roles are not owned by any schema.

Q. You have set the REMOTE_LOGIN_PASSWORD_FILE initialization parameter to EXCLUSIVE. Which view would you query to determine which users have been granted the SYSOPER privileges?

V$PWFILE_USERS
When password file authentication is enabled, V$PWFILE_USERS displays the names of users with the SYSDBA or SYSOPER privileges.
The V$DATABASE view contains database information from the control file.
The V$INSTANCE view displays the state of the current instance.
The V$PARAMETER view lists information about initialization parameters.

Q. Which view would you used to determine if a constraint is enabled?

DBA_CONS_COLUMNS
DBA_CONSTRAINTS displays the names and types of all the constraints in the database and their current status.
The DBA_TABLES view lists descriptions of all tables in the database.
The DBA_CATALOG view lists all database tables, views, synonyms, and sequences.
The PUBLIC_DEPENDENCY view lists dependencies to and from objects, by object number.
The DBA_CONS_COLUMNS view lists information about accessible columns in constraint definitions

Q. Which CREATE INDEX clause ensures that redo generation will not affect performance when the index is created?

NOLOGGING
The NOLOGGING clause does not generate redo and increases the speed of the CREATE INDEX command. The NOSORT clause indicates to Oracle that the rows are stored in the database in ascending order;  therefore, Oracle does not have to sort the rows when creating the index. Redo information is still created. UNIQUE specifies that the value of the column, or combination of columns, in the table being indexed must be unique. NOSTORAGE is an invalid SQL parameter. LOGGING specifies that redo information will be generated. MINTRANS is not a CREATE INDEX option.

Q.  Which SQL*Loader file specifies the database tables to be loaded?

Control file
The control file used by SQL*Loader specifies the tables to be loaded. The log file holds information about the load, such as the number of records loaded/rejected, errors, and so forth. The discard file contains records that did not meet the specified selection criteria. The bad file holds records that are rejected because of incorrect data.
Q. When you created the temporary tablespace, the PCTINCREASE was set to zero. How will this affect the size of new extents in the temporary tablespace?
Because PCTINCREASE is always zero, the second and all other extents will always be the size of NEXT. The INITIAL parameter will be used only for the initial extent. SQL*Plus has no CREATE TEMPORARY SEGMENT command. A PCTINCREASE of zero will not stop extents from being allocated.

Q. Which Oracle utility could you use to re-create Oracle tables and their associated objects under a new schema name and move the associated data from the previous schema to the new tables?
Export/import utilities
Export/import utilities are used to move tables, their associated objects, and their data from one user to another user. SQL*Loader is used to load data from external files into Oracle tables. Direct-load insert writes data directly into Oracle datafiles, without using the buffer cache. Schema Manager is an OEM utility used to create, edit, and examine schema objects. It cannot move data from one schema to another.

Q. Which view would you use to find out the sequence number of the current redo log group?
V$THREAD
V$THREAD displays the current redo log group, the number of online redo log groups, and the current sequence number. The V$DATABASE view lists database information, including the log mode, but not the current log group or the sequence number. The V$INSTANCE view lists the state of the current instance and does not contain any log group information. The V$LOGFILE view lists information about redo log files, but does not include the sequence number.

 Q. What is the order in which objects are imported into a database from an export file?
Table definitions, table data, indexes on the table, integrity constraints, triggers, bitmap indexes

Q. Which best describes the incomplete recovery ?
- time-based , cancel-based , change-based
 
Q. LGWR process will hang if the online redo logs are filled faster than the archiving process?
- true only if database is in ARCHIVELOG mode
 
Q. What tablespaces could be recovered while the database is open?
- data tablespace
- index tablespace
 
Q. What's completed recovery? 
- tablespace recovery

Q. After which command you should backup your control file?
- alter tablespace add datafile ... ; 
- alter database drop logfile ... ;

Q. What's the disadvantage of running database in NOARCHIVELOG mode?
- lost transactions must be re-entered
 
Q. In which database state you can re-create a control file?
- nomount
 
Q. If you import a full database export back to a database, will this apply all archived log files need it for complete recovery? 
- false

Q. The characteristics of direct path export are?  
- the buffer size has no impact
- it takes less time to complete
 
Q. What will the rolling forward do?
- apply all commited entries in the archived logs

Q. Will alter tablespace ... begin backup; backup all of the data files in that tablespace? 
- false
 Note: It sets to  backup mode only

Q. What will be your backup strategy, if your company's data is highly volatile?
- always do full db export

Q. Will the ARCH process start automatically if you execute alter database archivelog; in SQL*Plus?
- true
 
Q. You have a corrupted data file. To minimize the downtime you run
startup mount;
alter database datafile ... offline;
What you should do next?
- alter database open

Q. Where is the location to all control files stored?
- init.ora file
 
Q. After you activate a standby database what should you do next?
 - switch the former primary database to standby mode

Q. Why Oracle instance recovery is faster?
- uncommited transactions will not be roll back immediately
  
Q. To do a completed recovery, the database must be in which mode?
- ARCHIVELOG
 
Q. In case of incompeted recovery, which files has to be restored?
- all data files

Q. After incomplete recovery, how you have to open the database?
- RESETLOGS
 
Q. To change a database to archive log, the current database mode must be?
- mount
 
Q. What privilege you must have to change database to archive log mode?
- alter database
 

Q. Which type of recovery will recover the database to a time in the past?  
- time-based
- cancel-based
- change-based
 
Q. What's the result of alter database backup controlfile to trace; command?
- generate a editable script file
 
Q. If a backgroud process has an error, where the trace file will be?
- BACKGROUND_DUMP_DEST
 
Q. Import the full exported file and apply all the archive logs will recover the database to the point of failure? 
- false

Q. When should you perform a change based recovery?
- in a distributed environment

Q. How can you find out how many users are currently logged into the database? How can you find their operating system id?
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.

Q. A user selects from a sequence and gets back two values, his select is:
SELECT pk_seq.nextval FROM dual; What is the problem?
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.

Q. How can you determine if an index needs to be dropped and rebuilt?
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.

Q. How can variables be passed to a SQL routine?
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.

Q. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
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.

Q. 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
The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.

Q. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
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.

Q. 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?
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.

Q. What is a Cartesian product?
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.

Q. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
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.

Q. What is tkprof and how is it used?
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.

Q. What is explain plan and how is it used?
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.

Q. A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
 Multiple extents in and of themselves aren’t bad. However if you also have chained rows this can hurt performance.

Q. How do you set up tablespaces during an Oracle installation?
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.

 Q. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Ensure that users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

Q. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
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.

Q. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.

Q. Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
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.

Q. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
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.

Q. When should you increase copy latches? What parameters control copy latches?
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.

Q. 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?
You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

Q. 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?
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.

Q. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
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.

Q. 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?
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.

Q. If you see contention for library caches how can you fix it?
 Increase the size of the shared pool.

Q. If you see statistics that deal with "undo" what are they really talking about?
 Rollback segments and associated structures.

Q. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?
 The SMON process won’t automatically coalesce its free space fragments.

Q. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
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 coalesce;’ is best. If the free space isn’t contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.



Q. How can you tell if a tablespace has excessive fragmentation?
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.

Q. 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?
Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.

Q. What can cause a high value for recursive calls? How can this be fixed?
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.

Q. 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?
This indicate that the shared pool may be too small. Increase the shared pool size.

Q. If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.

Q. 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?
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.

Q. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
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.



Q. 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?
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.

Q. You see multiple extents in the temporary tablespace. Is this a problem?
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.

Q. Define OFA.
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.

Q. How do you set up your tablespace on installation?
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.

Q. What should be done prior to installing Oracle (for the OS and the disks)?
adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.

 Q. 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?
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.

Q. When configuring SQLNET on the server what files must be set up?
INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file

Q. When configuring SQLNET on the client what files need to be set up?
SQLNET.ORA, TNSNAMES.ORA

Q. What must be installed with ODBC on the client in order for it to work with Oracle?
SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

Q. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for?
The first thing to check with a large SGA is that it isn’t being swapped out.

Q. What OS user should be used for the first part of an Oracle installation (on UNIX)?
You must use root first.

Q. When should the default values for Oracle initialization parameters be used as is?
Never

Q. How many control files should you have? Where should they be located?
At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.

Q. How many redo logs should you have and how should they be configured for maximum recoverability?
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.

Q. 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?
At least 7, see disk configuration answer above.

Q. How can you determine the number of SQLNET users logged in to the UNIX system?
SQLNET users will show up with a process unique name that begins with oracle, if you do a ps -ef|grep oracle|wc -l you can get a count of the number of users.

Q. What command is used to type files to the screen?
cat, more, pg

Q. What command is used to remove a file?
rm

Q. Can you remove an open file under UNIX?
yes

Q. How do you create a decision tree in a shell script?
depending on shell, usually a case-esac or an if-endif or fi structure

Q. What is the purpose of the grep command?
grep is a string search command that parses the specified string from the specified file or files

Q. 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?
ps -ef|grep *nocomp*|wc -l

Q. What is an inode?
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.

Q. The system administrator tells you that the system hasn’t been rebooted in 6 months, should he be proud of this?
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.

Q. What is redirection and how is it used?
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.

Q. How can you find dead processes?
ps -ef|grep zombie -- or -- who -d depending on the system.

Q. How can you find all the processes on your system?
Use the ps command

Q. How can you find your id on a system?
Use the "who am i" command.

Q. What is the finger command?
The finger command uses data in the passwd file to give information on system users.

Q. What is the easiest method to create a file on UNIX?
Use the touch command

Q. What does >> do?
The ">>" redirection symbol appends the output from the command specified into the file specified. The file must already have been created.

Q. If you aren’t sure what command does a particular UNIX function what is the best way to determine the command?
The UNIX man -k command will search the man pages for the value specified. Review the results from the command to find the command of interest.

Q. How can you determine if an Oracle instance is up from the operating system level?
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.

Q. Users from the PC clients are getting messages indicating :
ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)
What could the problem be?
The instance name is probably incorrect in their connection string.

Q. Users from the PC clients are getting the following error stack:
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?
The Oracle instance is shutdown that they are trying to access, restart the instance.

Q. How can you determine if the SQLNET process is running for SQLNET V1? How about V2?
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".

Q. What file will give you Oracle instance status information? Where is it located?
The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.

Q. Users aren’t being allowed on the system. The following message is received:
ORA-00257 archiver is stuck. Connect internal only, until freed
What is the problem?
The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.

Q. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?
There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.

Q. You attempt to add a datafile and get:
ORA-01118: cannot add anymore datafiles: limit of 40 exceeded
What is the problem and how can you fix it?
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.

Q. 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?
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.

Q. Your users get the following error:
ORA-00055 maximum number of DML locks exceeded
What is the problem and how do you fix it?
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.

Q. 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?
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.

NOTES:
Log Miner is ideally the choice when: -
·        There is an object loss, for example if an object gets dropped.
·        When an object is wrongly updated with incorrect values.
  Under such circumstances Log Miner is ideal to bring the object back to the old 
   Status.    

SELECT column_name FROM dict_columns 

WHERE table_name = 'ALL_TAB_COLUMNS';

Change passowrd:
ALTER USER SYS IDENTIFIED BY new_password
SELECT table_name, comments FROM dictionary
WHERE table_name LIKE '%TABLE%'
ORDER BY table_name

SELECT column_name FROM dict_columns 

WHERE table_name = 'ALL_TAB_COLUMNS';

select tname from tab where tname like 'AD_APPL%';

or
select table_name from dba_tables where table_name like ‘AD_APPL%’;

How to find out what DBMS Packages are available in the Database?

DBMS packages are been created  by executing the catproc.sql file
select substr(owner, 1,6), substr(object_name, 1, 20), object_type, status
from dba_objects
where object_name like '%DBMS%';

DBMS_LOCK

DBMS_PIPE
DBMS_SESSION
DBMS_STATS
DBMS_TRACE
DBMS_PIPE
DBMS_ALERT
SELECT count(object_name)
FROM dba_objects
WHERE owner = ‘SYS’
AND object_type = ‘PACKAGE’ ORDER BY object_name; 
SELECT table_name, grantee, privilege
FROM all_tab_privs
WHERE table_name LIKE ‘DBMS%’; 
GRANT EXECUTE ON <package name> TO <user name>;
conn sys/change_on_install  as sysdba
conn system/manager@pgnd

How to execute DBMS packages?

Oracle9i - Gathering statistics for objects in a schema:
-------------------------------------------------------
exec dbms_stats.gather_schema_stats( -
              ownname => NULL, -
          estimate_percent => 100, - (***)
          granularity => 'ALL', -
          method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
          degree => DBMS_STATS.DEFAULT_DEGREE, -
          options => 'GATHER AUTO', -
          cascade => TRUE -
          );

Oracle9i - Gathering statistics for objects in the database:

------------------------------------------------------------
exec dbms_stats.gather_database_stats( -
          estimate_percent => 100, - (***)
          granularity => 'ALL', -
          method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
          degree => DBMS_STATS.DEFAULT_DEGREE, -
          options => 'GATHER AUTO', -
          cascade => TRUE -
          );
(***) Note that the estimate_percent can be automatically determined in 9i
      by using:
          estimate_percent => dbms_stats.auto_sample_size,
      By using the constant DBMS_STATS.AUTO_SAMPLE_SIZE, Oracle automatically
      determines the best sample size for good statistics.

Q. How to set the notepad or vi editor for sql

Sqlplus> define_editor=vi or notepad (by default)

Q. How to view user

Sqlplus > show user

Q. How to change the SID of the Database?

-Stop all services
-sqlplus>Alter database backup control file to trace; (available in udump)
-sqlplus>shutdown normal
-cold backup the full Database
-create user ID’s and the mount points and copy the backup to the respective mount points
-change the file path by replacing OLDSID with newSIDand the ownership
-change the parameters in the env (SID.env, adovars.env) and script files
-edit the control file with the new SID replace the RESUE by SET
-edit the init.ora with the NEW DB name, RBS details, CONTROL FILE ,UDUMP, BDUMP, CDUMP locations
-sqlplus>@control file.sql (edited with the new SID name)
-$lsnrctl start NEW SID

Q. what is HIGH WATER MARK?

All Oracle segments have an upper boundary containing the data within the segment. This upper boundary is called the "high water mark" or HWM. The high water mark is an indicator that marks blocks that are allocated  to a segment, but are not used yet. This high water mark typically bumps  up at 5 data blocks at a time. It is reset to "zero" (position to the start of the segment) when a TRUNCATE command is issued.  So you can have empty  blocks below the high water mark, but that means that the block has been  used (and is probably empty caused by deletes). Oracle does not move the  HWM, nor does it *shrink* tables, as a result of deletes.  This is also 
true of Oracle8.  Full table scans typically read up to the high water mark.
Data files do not have a high water mark; only segments do have them.

Q. What are "runaway process" and what are the reasons for this?

Concurrent process or Form process which are hogging the memory utilization or cpu utilization are called as runaway process.

Q. How to change the global name?

alter database rename global_name TO <new global name>;