!! oracle core database interview questions :!!
68. What is Cursor ?
-- A Cursor is a handle ( a name or pointer) for the memory associated with a
-- specific statement
21. What is an Index ?
-- An Index is an optional structure associated with a table to have direct access
-- to rows,which can be created to increase the performance of data retrieval. Index
-- can be created on one or more columns of a table.
22. How are Indexes Update ?
/*
Indexes are automatically maintained and used by ORACLE. Changes to table data are
automatically incorporated into all relevant indexes.
*/
23. What are Clusters ?
/*
Clusters are groups of one or more tables physically stores together to share common
columns and are often used together.
*/
24. What is cluster Key ?
/*
The related columns of the tables in a cluster is called the Cluster Key.
*/
25. What is Index Cluster ?
/*
A Cluster with an index on the Cluster Key.
*/
26. What is Hash Cluster ?
/*
A row is stored in a hash cluster based on the result of applying a hash function
to the rows cluster key value. All rows with the same hash key value are stores
together on disk.
*/
27. When can Hash Cluster used ?
/*
Hash clusters are better choice when a table is often queried with equality queries.
For such queries the specified cluster key value is hashed. The resulting hash key
value points directly to the area on disk that stores the specified rows.
*/
111. What is a Procedure ?
/*
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together
as a unit to solve a specific problem or perform a set of related tasks.
*/
112. What is difference between Procedures and Functions ?
/*
A Function returns a value to the caller where as a Procedure does not.
*/
113. What is a Package ?
/*
A Package is a collection of related procedures, functions, variables and other
package constructs together as a unit in the database.
*/
114. What are the advantages of having a Package ?
/*
Increased functionality (for example,global package variables can be declared
and used by any proecdure in the package) and performance (for example all objects
of the package are parsed compiled, and loaded into memory once)
*/
115. What is Database Trigger ?
/*
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is
automatically executed as a result of an insert in,update to, or delete from a
table.
*/
116. What are the uses of Database Trigger ?
/*
Database triggers can be used to automatic data generation, audit data modifications,
enforce complex Integrity constraints, and customize complex security authorizations.
*/
117. What are the differences between Database Trigger and Integrity constraints ?
/* A declarative integrity constraint is a statement about the database that is always true.
A constraint applies to existing data in the table and any statement that manipulates the table.
A trigger does not apply to data loaded before the definition of the trigger,
therefore, it does not guarantee all data in a table conforms to the rules
established by an associated trigger.
A trigger can be used to enforce transitional constraints where as a declarative
integrity constraint cannot be used.
*/
28. What is Database Link ?
-- A database link is a named object that describes a "path" from one database to
-- another.
29. What are the types of Database Links ?
-- Private Database Link, Public Database Link & Network Database Link.
30. 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 owners views or procedures.
31. 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.
32. 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.
33. What is Data Block ?
-- ORACLE databases data is stored in data blocks. One data block corresponds to a
-- specific number of bytes of physical database space on disk.
34. How to define Data Block size ?
-- A data block size is specified for each ORACLE database when the database is
-- created. A database users and allocated free database space in ORACLE datablocks.
-- Block size is specified in INIT.ORA file and cannt be changed latter.
35. 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.
36. What is an Extent ?
-- An Extent is a specific number of contiguous data blocks, obtained in a single
-- allocation, used to store a specific type of information.
37. What is a Segment ?
-- A segment is a set of extents allocated for a certain logical structure.
38. What are the different type of Segments ?
-- Data Segment, Index Segment, Rollback Segment and Temporary Segment.
39. What is a Data Segment ?
-- Each Non-clustered table has a data segment. All of the tables 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 clusters data segment.
40. What is an Index Segment ?
-- Each Index has an Index segment that stores all of its data.
41. What is Rollback Segment ?
-- A Database contains one or more Rollback Segments to temporarily store "undo"
-- information.
42. What are the uses of Rollback Segment ?
-- Rollback Segments are used :To generate read-consistent database information
-- during database recovery to rollback uncommitted transactions for users.
1) How do you switch from an init.ora file to a spfile?
-> Issue the create spfile from pfile command.
2) Explain the difference between a data block, an extent and a segment.
-> A data block is the smallest unit of logical storage for a database object.
As objects grow they take chunks of additional storage that are composed of
contiguous data blocks. These groupings of contiguous data blocks are called extents.
All the extents that an object takes when grouped together are considered the segment
of the database object.
3) Where would you look for errors from the database engine?
-> In the alert log.
4) Compare and contrast TRUNCATE and DELETE for a table.
-> Both the truncate and delete command have the desired outcome of getting rid of all the
rows in a table. The difference between the two is that the truncate command is a DDL operation
and just moves the high water mark and produces a now rollback. The delete command, on the other
hand, is a DML operation, which will produce a rollback and thus take longer to complete.
5) Give the reasoning behind using an index. ?
-> Faster access to data blocks in a table.
6) What type of index should you use on a fact table?
-> A Bitmap index.
7) Give two examples of referential integrity constraints.
-> A primary key and a foreign key.
8) Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode.?
-> ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions
that have occurred in the database so that you can recover to any point in time.
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage
of not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an
archive log and thus increases the performance of the database slightly.
9) What command would you use to create a backup control file?
-> Alter database backup control file to trace.
10) Give the stages of instance startup to a usable state where normal users may access it. ?
->
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened
11) Explain the difference between $ORACLE_HOME and $ORACLE_BASE.?
-> ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath
ORACLE_BASE where the oracle products reside.
12) Explain the difference between Pfile and Spfile ?
-> Pfile is Text file, where as SPfile is binary file which is created from pfile
before database is startup.
13) Explain logical and physical part of Oracle database in short
-> logical is tablespace and physical is datafile.
14) What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
-> v$session and v$process
15) What is an Integrity Constrains ?
-> An integrity constraint is a declarative way to define a business rule for a column of a table.
16) What is SYSTEM tablespace and when is it Created? Subscribe
-> 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.
17) How are Indexes Update ?
-> Indexes are automatically maintained and used by ORACLE. Changes to table
data are automatically incorporated into all relevant indexes.
18) What is Data Block ? Subscribe ?
-> ORACLE databases data is stored in data blocks. One data block corresponds to a specific
number of bytes of physical database space on disk
19)RE: what is the back ground process in oracle architec.?
-> the mandatory background process for 9i is 5.
they are SMON,PMON,CKPT,DBWR,LGWR.
The Background process related to Oracle Architecture are as follows:
1.DBWR(DATABASE WRITER)
2.LGWR(LOG WRITER)
3.SMON(SYSTEM MONITOR)
4.PMON(PROCESS MONITOR)
5.CKPT(CHECK POINT)
6.SERVER
7.CJQ(CO-ORDINATOR JOB QUEUE PROCESSES)
8.RECOVER
9.DISPATCHER
10.ARCHIVER
Including some more from Oracle 11G
1) VKTM (Virtual Keeper of TiMe keeps the "SGA time" variable current)
2) GEN 0 (General Task Execution Process)
3)
*. What is Oltp database?
OLTP means Online Transaction Processing. OLAP means Online Analytical Processing. OLTP deals with processing of data from transactional systems. For example, an application that loads the reservation data of a hotel is an OLTP system. An OLTP system is designed mainly keeping in the mind the performance of the end application. It comprises of the application, database & the reporting system that directly works on this database. The database in an OLTP system would be designed in a manner as to facilitate the improvement in the application efficiency thereby reducing the processing time of the application.
*. What is Olap database?
OLAP systems were mainly developed using data in a warehouse. Having said that a need was felt to isolate older data, it was necessary to store them in a format that would be useful in easing out the reporting bottlenecks. A need was felt to isolate the data & redesign the application data to such a format & structure that this data repository would be the prime source of business decisions. Coming back to OLAP systems, these systems were mainly developed on the isolated data.
*. What is the function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
*. What is Execution Plan?
The combination of the steps the optimizer chooses to execute a statement is called an execution plan.
*. What are the different approaches used by Optimizer in choosing an execution plan?
Rule-based and Cost-based.
*. What is dictionary cache?
The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.
*. What is a Data Dictionary?
The Oracle data dictionary is one of the most important components of the Oracle DBMS.It contains all information about the structures and objects of the database such as tables,columns, users, data files etc. The data stored in the data dictionary are also often called metadata.
*. 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
*. What is the functionality of SYSTEM table space?
System tablespace is a main part of oracle database. All the database information is in it.it is created when database is created.It is managed by oracle server . dba cannot change its contents. it contains the data dictionary objects .
*. What is the function of checkpoint (CKPT)?
Checkpoint is a background process which ensures dbwn process has written data to datafiles and upadates control file and datafile header to establish data consistency.The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.
*. When ckpt occurs?
1. For every 3 seconds
2. When 1/3rd of DB buffer fills
3. When log swtich occurs
4. When database shuts down
*. When does LGWR write to the database?
Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File.LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.
*. What is Shared SQL Area ?
A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oraclecan deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area.'
*. What Does DBWR do?
Database writer writes modified blocks from the database buffer cache to the data files.
*. What is server processes?
A server process is one that handles user requests. When you type in a SQL statement, the server process handles the parsing and running of that SQL statement,
*. Which background process performs Crash recovery?
SMON (system monitor)
*. What is On-line Redo Log?
The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.
*. What are the steps involved in Instance Recovery?
Rolling forward to recover data that has not been recorded in data files yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any Pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
*. What does COMMIT do?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
*. What are the steps involved in Database Startup?
Start an instance, Mount the Database and Open the Database.
*. What are the steps involved in Database Shutdown?
Close the Database; Dismount the Database and Shutdown the Instance.
*. What is Restricted Mode of Instance Startup?
An instance can be started in restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
*. What mode the instance should be to create the database?
No mount
*. While creating database can you specify the size of control files?
No
*. Which parameter determines the size of SHARED POOL?
SHARED_POOL_SIZE.
*. What is a trace file and how is it created?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
*. What is a Tablespace?
A database is divided into Logical Storage Unit called tablespaces.A tablespace is used to grouped related logical structures together
*. What are the Characteristics of Data Files?
A data file can be associated with only one Tablespaces. One or more data files can be use as data of database storage called a tablespace.
*. How do you drop a tablespace, if it has database objects?
Drop tablespace tablespacename including contents
*. How to rename a datafile?
1. Alter tablespace <TBSNAME> offline;
2. Alter tablepsace <TBSNAME> rename datafile 'oldname' to 'newname';
3. Alter tablespace <TBSNAME> online;
*. What is the procedure for Transportable tablespace migration?
Transportable Tablespaces (TTS) allows you to copy a set of datafiles for a tablespace on one database and plug that tablespace into a different database.As we noted, you cannot transport a single partition of a table without transporting the entire table. Therefore, we will need to exchange the partition with a stand-alone table temporarily, so that the partition becomes its own table.
*. How to rename the tablespace?
Sys>alter tablespace <tablesapce name> rename to <tablespace name>
*. Tell me about ORA 1555 and how do you address if you get this error?
It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.There are three situations that can cause the ORA-01555 error:
A. An active database with an insufficient number of small-sized rollback segments
B.A rollback segment corruption that prevents a consistent read requested by the query
C.A fetch across commits while your cursor is open
*. What is the view to see UNDO for how much data flushed out and how much data it having now….how many active blocks, expired blocks?
V$undostat
*. What are the init parameters you have to set to make use of undo management?
undo_tablespace= undotbs1
undo_management= auto
undo_retention= time in minutes
comment rollback_segment
*. What is flashback query and flash back recovery?
Flashback query, a new feature of Oracle 9i. Flashback query enables us to query our data as it existed in a previous state. In other words, we can query our data from a point in time before we or any other users made permanent changes to it. Flashback recovery can bring the complete database to the previous state based on SCN number, on timestamp, on restore point.
* . How to flush recycle bin?
We use “Purge” command to Flush Recycle bin. It will automatically remove old data from recycle bin if tablespace needs some more space. If you want to purge just one single table then you type "Purge table <tableName>"
*. What background process refreshes materialized views?
Cjqo (co-ordinate job queue)
*. What is netstat?
And it’s Usage? It is a utility to know the port numbers availability.
Usage: netstat -na grep port number
*. Which operations you can perform using database links?
Options :-> DDL or DML
DML
*. Which operations are not allowed on Materialized views?
DML
*. Can we manually refresh any materialized view? Using which package
Yes, DBMS_MVIEW
*. Where the Materialized view stored?
In database (client side db).this is not a view.it’s read only table.
*. What is snapshot?
Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals.
*. What are the various types of snapshots?There are three types of materialized views:
Read only materialized view, Updateable materialized view, Writeable materialized view
*. What are the options available to refresh snapshots?
COMPLETE -: Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
FAST -: If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
FORCE -: Default value. If possible it performs a FAST refresh; otherwise it will perform a complete refresh.
*. What is function of RECO?
Recover (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals,the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
*. What is Archived Redo Log?
Archived Redo Log consists of Redo Log files that have archived before being reused.
*. What is Mirrored on-line Redo Log?
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks; changes made to one member of the group are made to all members.
*. What does a Control file Contain?
A Control file records the physical structure of the database. It contains the following information. Database Name and locations of a database's files and redolog files. Time stamp of database creation'.
*. What happens when archive log destination becomes 100% full when the database is running in ARCHIVELOG mode?
How do you recover? The database gets shutdown. We should move old archives to different location and startup the database.
*. How many maximum control files you can create in Oracle database?
What is the error number you get if u tries to create more than 8?
8, ora 208
*. If you want to maintain one more archive destination which parameter you have to set?
It’s a dynamic parameter you have to set log_archive_dest_1=
*. How to create a trace file?
"Alter database backup controlfile to trace;"
*. Where do you get all hidden parameters?
In the table x$ksppi
Sys>select ksppinm,ksppdesc from x$ksppi;
*. How do you know whether the parameter is dynamic or static?
Check the value from the column ISINSTANCE_MODIFIABLE from V$PARAMETER.
*. Which role you grant to rman user while configuring rman user
Recovery_catalog_owner role.
*. Temporarily recovery catalog database (for RMAN) is down. Can you still run a backup? How?
Yes, using no catalog mode
*. You have run a backup of database using RMAN nocatalog. How do you sync the recovery catalog with the metadata about the backup that was taken?
Resync catalog
*. With our catalog can we connect 2 target databases at a time?
Yes …we can…
*. What is difference between OBSOLETE and EXPIRED?
Obsolete backups mean inconsistency backups for recovery purpose. Expired backups means those backups physical files are not available on disk.
*. How speed up backup jobs in Rman?
By increase number of channels.
*.
USER MANAGENMENTS :
*. What is the difference between a user and schema?
A user will not have any of his own objects and will always access others objects. Schema is a collection of objects
*. After creating user, what are the privileges you would assign?
We will give CONNECT role in 10g (CREATE SESSION if 9i) and any other roles or privileges specified by the application support
*. What is the disadvantage of resource role?
It will override the quota granted for a user and makes it unlimited
*. How you will identify the privileges and roles assigned to a user?
Using following views
dba_sys_privs
dba_tab_privs
dba_role_privs
role_sys_privs
role_tab_privs
role_role_privs
*. How to create user and grant the permission in a single command?
SQL> grant create session to user_a identified by <pwd>;
*. What happens to the objects if we change the default tablespace for the user?
Nothing. They will continue to work as normal
*. You got a requirement to run a script as user “MARK” and you don’t have the password of MARK. How you will take next step?
We will ask application support. In case they cannot be reached, we can take ASCII password stored in dba_users and change the password. Once work is done we can change the password back using ASCII that we stored earlier
Also we can set current_user option in SQLPLUS
*. What is the view to check default permanent and temp tablespace values?
database_properties
*. Have you implemented password policies?
a. Yes. Using profile management
*. What are SNIPPED sessions? If there any impact on database to have them?
Those are sessions terminated from database because of crossing IDLE_TIME limit. But disadvantage is even though oracle level sessions are cleared, at OS level processes will still occupy resources which is a burden to the server
*. I am using IDLE_TIME as 15 min, but observed that even after 20min idle time, session is not getting disconnected, what is the reason?
RESOURCE_LIMIT parameter is not set to TRUE
*. As a DBA will you recommend to implement auditing in the database?
Depends on the type of database, but it would be wise to implement atleast on some critical tables
*. What factors you will choose before you enable auditing?
We need to check about database performance and what level of auditing is required
*. If auditing is not in place, how you will get to know if a user performed update or not yesterday?
We can use logminer to read the archives which are generated yesterday
*. Are you maintaining any SOX compliance database?
No. We don’t have any SOX database as of now
*. How to check which users are granted with sysdba role?
We can check from v$syspw_file view
*. If we want to restrict users to connect to the database, but want to allow some users to connect, how can we do that?
We need to grant restrcited session privilege for the users whom we want to connect
68. What is Cursor ?
-- A Cursor is a handle ( a name or pointer) for the memory associated with a
-- specific statement
21. What is an Index ?
-- An Index is an optional structure associated with a table to have direct access
-- to rows,which can be created to increase the performance of data retrieval. Index
-- can be created on one or more columns of a table.
22. How are Indexes Update ?
/*
Indexes are automatically maintained and used by ORACLE. Changes to table data are
automatically incorporated into all relevant indexes.
*/
23. What are Clusters ?
/*
Clusters are groups of one or more tables physically stores together to share common
columns and are often used together.
*/
24. What is cluster Key ?
/*
The related columns of the tables in a cluster is called the Cluster Key.
*/
25. What is Index Cluster ?
/*
A Cluster with an index on the Cluster Key.
*/
26. What is Hash Cluster ?
/*
A row is stored in a hash cluster based on the result of applying a hash function
to the rows cluster key value. All rows with the same hash key value are stores
together on disk.
*/
27. When can Hash Cluster used ?
/*
Hash clusters are better choice when a table is often queried with equality queries.
For such queries the specified cluster key value is hashed. The resulting hash key
value points directly to the area on disk that stores the specified rows.
*/
111. What is a Procedure ?
/*
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together
as a unit to solve a specific problem or perform a set of related tasks.
*/
112. What is difference between Procedures and Functions ?
/*
A Function returns a value to the caller where as a Procedure does not.
*/
113. What is a Package ?
/*
A Package is a collection of related procedures, functions, variables and other
package constructs together as a unit in the database.
*/
114. What are the advantages of having a Package ?
/*
Increased functionality (for example,global package variables can be declared
and used by any proecdure in the package) and performance (for example all objects
of the package are parsed compiled, and loaded into memory once)
*/
115. What is Database Trigger ?
/*
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is
automatically executed as a result of an insert in,update to, or delete from a
table.
*/
116. What are the uses of Database Trigger ?
/*
Database triggers can be used to automatic data generation, audit data modifications,
enforce complex Integrity constraints, and customize complex security authorizations.
*/
117. What are the differences between Database Trigger and Integrity constraints ?
/* A declarative integrity constraint is a statement about the database that is always true.
A constraint applies to existing data in the table and any statement that manipulates the table.
A trigger does not apply to data loaded before the definition of the trigger,
therefore, it does not guarantee all data in a table conforms to the rules
established by an associated trigger.
A trigger can be used to enforce transitional constraints where as a declarative
integrity constraint cannot be used.
*/
28. What is Database Link ?
-- A database link is a named object that describes a "path" from one database to
-- another.
29. What are the types of Database Links ?
-- Private Database Link, Public Database Link & Network Database Link.
30. 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 owners views or procedures.
31. 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.
32. 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.
33. What is Data Block ?
-- ORACLE databases data is stored in data blocks. One data block corresponds to a
-- specific number of bytes of physical database space on disk.
34. How to define Data Block size ?
-- A data block size is specified for each ORACLE database when the database is
-- created. A database users and allocated free database space in ORACLE datablocks.
-- Block size is specified in INIT.ORA file and cannt be changed latter.
35. 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.
36. What is an Extent ?
-- An Extent is a specific number of contiguous data blocks, obtained in a single
-- allocation, used to store a specific type of information.
37. What is a Segment ?
-- A segment is a set of extents allocated for a certain logical structure.
38. What are the different type of Segments ?
-- Data Segment, Index Segment, Rollback Segment and Temporary Segment.
39. What is a Data Segment ?
-- Each Non-clustered table has a data segment. All of the tables 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 clusters data segment.
40. What is an Index Segment ?
-- Each Index has an Index segment that stores all of its data.
41. What is Rollback Segment ?
-- A Database contains one or more Rollback Segments to temporarily store "undo"
-- information.
42. What are the uses of Rollback Segment ?
-- Rollback Segments are used :To generate read-consistent database information
-- during database recovery to rollback uncommitted transactions for users.
1) How do you switch from an init.ora file to a spfile?
-> Issue the create spfile from pfile command.
2) Explain the difference between a data block, an extent and a segment.
-> A data block is the smallest unit of logical storage for a database object.
As objects grow they take chunks of additional storage that are composed of
contiguous data blocks. These groupings of contiguous data blocks are called extents.
All the extents that an object takes when grouped together are considered the segment
of the database object.
3) Where would you look for errors from the database engine?
-> In the alert log.
4) Compare and contrast TRUNCATE and DELETE for a table.
-> Both the truncate and delete command have the desired outcome of getting rid of all the
rows in a table. The difference between the two is that the truncate command is a DDL operation
and just moves the high water mark and produces a now rollback. The delete command, on the other
hand, is a DML operation, which will produce a rollback and thus take longer to complete.
5) Give the reasoning behind using an index. ?
-> Faster access to data blocks in a table.
6) What type of index should you use on a fact table?
-> A Bitmap index.
7) Give two examples of referential integrity constraints.
-> A primary key and a foreign key.
8) Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode.?
-> ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions
that have occurred in the database so that you can recover to any point in time.
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage
of not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an
archive log and thus increases the performance of the database slightly.
9) What command would you use to create a backup control file?
-> Alter database backup control file to trace.
10) Give the stages of instance startup to a usable state where normal users may access it. ?
->
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened
11) Explain the difference between $ORACLE_HOME and $ORACLE_BASE.?
-> ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath
ORACLE_BASE where the oracle products reside.
12) Explain the difference between Pfile and Spfile ?
-> Pfile is Text file, where as SPfile is binary file which is created from pfile
before database is startup.
13) Explain logical and physical part of Oracle database in short
-> logical is tablespace and physical is datafile.
14) What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
-> v$session and v$process
15) What is an Integrity Constrains ?
-> An integrity constraint is a declarative way to define a business rule for a column of a table.
16) What is SYSTEM tablespace and when is it Created? Subscribe
-> 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.
17) How are Indexes Update ?
-> Indexes are automatically maintained and used by ORACLE. Changes to table
data are automatically incorporated into all relevant indexes.
18) What is Data Block ? Subscribe ?
-> ORACLE databases data is stored in data blocks. One data block corresponds to a specific
number of bytes of physical database space on disk
19)RE: what is the back ground process in oracle architec.?
-> the mandatory background process for 9i is 5.
they are SMON,PMON,CKPT,DBWR,LGWR.
The Background process related to Oracle Architecture are as follows:
1.DBWR(DATABASE WRITER)
2.LGWR(LOG WRITER)
3.SMON(SYSTEM MONITOR)
4.PMON(PROCESS MONITOR)
5.CKPT(CHECK POINT)
6.SERVER
7.CJQ(CO-ORDINATOR JOB QUEUE PROCESSES)
8.RECOVER
9.DISPATCHER
10.ARCHIVER
Including some more from Oracle 11G
1) VKTM (Virtual Keeper of TiMe keeps the "SGA time" variable current)
2) GEN 0 (General Task Execution Process)
3)
*. What is Oltp database?
OLTP means Online Transaction Processing. OLAP means Online Analytical Processing. OLTP deals with processing of data from transactional systems. For example, an application that loads the reservation data of a hotel is an OLTP system. An OLTP system is designed mainly keeping in the mind the performance of the end application. It comprises of the application, database & the reporting system that directly works on this database. The database in an OLTP system would be designed in a manner as to facilitate the improvement in the application efficiency thereby reducing the processing time of the application.
*. What is Olap database?
OLAP systems were mainly developed using data in a warehouse. Having said that a need was felt to isolate older data, it was necessary to store them in a format that would be useful in easing out the reporting bottlenecks. A need was felt to isolate the data & redesign the application data to such a format & structure that this data repository would be the prime source of business decisions. Coming back to OLAP systems, these systems were mainly developed on the isolated data.
*. What is the function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
*. What is Execution Plan?
The combination of the steps the optimizer chooses to execute a statement is called an execution plan.
*. What are the different approaches used by Optimizer in choosing an execution plan?
Rule-based and Cost-based.
*. What is dictionary cache?
The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.
*. What is a Data Dictionary?
The Oracle data dictionary is one of the most important components of the Oracle DBMS.It contains all information about the structures and objects of the database such as tables,columns, users, data files etc. The data stored in the data dictionary are also often called metadata.
*. 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
*. What is the functionality of SYSTEM table space?
System tablespace is a main part of oracle database. All the database information is in it.it is created when database is created.It is managed by oracle server . dba cannot change its contents. it contains the data dictionary objects .
*. What is the function of checkpoint (CKPT)?
Checkpoint is a background process which ensures dbwn process has written data to datafiles and upadates control file and datafile header to establish data consistency.The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.
*. When ckpt occurs?
1. For every 3 seconds
2. When 1/3rd of DB buffer fills
3. When log swtich occurs
4. When database shuts down
*. When does LGWR write to the database?
Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File.LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.
*. What is Shared SQL Area ?
A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oraclecan deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area.'
*. What Does DBWR do?
Database writer writes modified blocks from the database buffer cache to the data files.
*. What is server processes?
A server process is one that handles user requests. When you type in a SQL statement, the server process handles the parsing and running of that SQL statement,
*. Which background process performs Crash recovery?
SMON (system monitor)
*. What is On-line Redo Log?
The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.
*. What are the steps involved in Instance Recovery?
Rolling forward to recover data that has not been recorded in data files yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any Pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
*. What does COMMIT do?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
*. What are the steps involved in Database Startup?
Start an instance, Mount the Database and Open the Database.
*. What are the steps involved in Database Shutdown?
Close the Database; Dismount the Database and Shutdown the Instance.
*. What is Restricted Mode of Instance Startup?
An instance can be started in restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
*. What mode the instance should be to create the database?
No mount
*. While creating database can you specify the size of control files?
No
*. Which parameter determines the size of SHARED POOL?
SHARED_POOL_SIZE.
*. What is a trace file and how is it created?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
*. What is a Tablespace?
A database is divided into Logical Storage Unit called tablespaces.A tablespace is used to grouped related logical structures together
*. What are the Characteristics of Data Files?
A data file can be associated with only one Tablespaces. One or more data files can be use as data of database storage called a tablespace.
*. How do you drop a tablespace, if it has database objects?
Drop tablespace tablespacename including contents
*. How to rename a datafile?
1. Alter tablespace <TBSNAME> offline;
2. Alter tablepsace <TBSNAME> rename datafile 'oldname' to 'newname';
3. Alter tablespace <TBSNAME> online;
*. What is the procedure for Transportable tablespace migration?
Transportable Tablespaces (TTS) allows you to copy a set of datafiles for a tablespace on one database and plug that tablespace into a different database.As we noted, you cannot transport a single partition of a table without transporting the entire table. Therefore, we will need to exchange the partition with a stand-alone table temporarily, so that the partition becomes its own table.
*. How to rename the tablespace?
Sys>alter tablespace <tablesapce name> rename to <tablespace name>
*. Tell me about ORA 1555 and how do you address if you get this error?
It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.There are three situations that can cause the ORA-01555 error:
A. An active database with an insufficient number of small-sized rollback segments
B.A rollback segment corruption that prevents a consistent read requested by the query
C.A fetch across commits while your cursor is open
*. What is the view to see UNDO for how much data flushed out and how much data it having now….how many active blocks, expired blocks?
V$undostat
*. What are the init parameters you have to set to make use of undo management?
undo_tablespace= undotbs1
undo_management= auto
undo_retention= time in minutes
comment rollback_segment
*. What is flashback query and flash back recovery?
Flashback query, a new feature of Oracle 9i. Flashback query enables us to query our data as it existed in a previous state. In other words, we can query our data from a point in time before we or any other users made permanent changes to it. Flashback recovery can bring the complete database to the previous state based on SCN number, on timestamp, on restore point.
* . How to flush recycle bin?
We use “Purge” command to Flush Recycle bin. It will automatically remove old data from recycle bin if tablespace needs some more space. If you want to purge just one single table then you type "Purge table <tableName>"
*. What background process refreshes materialized views?
Cjqo (co-ordinate job queue)
*. What is netstat?
And it’s Usage? It is a utility to know the port numbers availability.
Usage: netstat -na grep port number
*. Which operations you can perform using database links?
Options :-> DDL or DML
DML
*. Which operations are not allowed on Materialized views?
DML
*. Can we manually refresh any materialized view? Using which package
Yes, DBMS_MVIEW
*. Where the Materialized view stored?
In database (client side db).this is not a view.it’s read only table.
*. What is snapshot?
Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals.
*. What are the various types of snapshots?There are three types of materialized views:
Read only materialized view, Updateable materialized view, Writeable materialized view
*. What are the options available to refresh snapshots?
COMPLETE -: Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
FAST -: If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
FORCE -: Default value. If possible it performs a FAST refresh; otherwise it will perform a complete refresh.
*. What is function of RECO?
Recover (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals,the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
*. What is Archived Redo Log?
Archived Redo Log consists of Redo Log files that have archived before being reused.
*. What is Mirrored on-line Redo Log?
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks; changes made to one member of the group are made to all members.
*. What does a Control file Contain?
A Control file records the physical structure of the database. It contains the following information. Database Name and locations of a database's files and redolog files. Time stamp of database creation'.
*. What happens when archive log destination becomes 100% full when the database is running in ARCHIVELOG mode?
How do you recover? The database gets shutdown. We should move old archives to different location and startup the database.
*. How many maximum control files you can create in Oracle database?
What is the error number you get if u tries to create more than 8?
8, ora 208
*. If you want to maintain one more archive destination which parameter you have to set?
It’s a dynamic parameter you have to set log_archive_dest_1=
*. How to create a trace file?
"Alter database backup controlfile to trace;"
*. Where do you get all hidden parameters?
In the table x$ksppi
Sys>select ksppinm,ksppdesc from x$ksppi;
*. How do you know whether the parameter is dynamic or static?
Check the value from the column ISINSTANCE_MODIFIABLE from V$PARAMETER.
*. Which role you grant to rman user while configuring rman user
Recovery_catalog_owner role.
*. Temporarily recovery catalog database (for RMAN) is down. Can you still run a backup? How?
Yes, using no catalog mode
*. You have run a backup of database using RMAN nocatalog. How do you sync the recovery catalog with the metadata about the backup that was taken?
Resync catalog
*. With our catalog can we connect 2 target databases at a time?
Yes …we can…
*. What is difference between OBSOLETE and EXPIRED?
Obsolete backups mean inconsistency backups for recovery purpose. Expired backups means those backups physical files are not available on disk.
*. How speed up backup jobs in Rman?
By increase number of channels.
*.
USER MANAGENMENTS :
*. What is the difference between a user and schema?
A user will not have any of his own objects and will always access others objects. Schema is a collection of objects
*. After creating user, what are the privileges you would assign?
We will give CONNECT role in 10g (CREATE SESSION if 9i) and any other roles or privileges specified by the application support
*. What is the disadvantage of resource role?
It will override the quota granted for a user and makes it unlimited
*. How you will identify the privileges and roles assigned to a user?
Using following views
dba_sys_privs
dba_tab_privs
dba_role_privs
role_sys_privs
role_tab_privs
role_role_privs
*. How to create user and grant the permission in a single command?
SQL> grant create session to user_a identified by <pwd>;
*. What happens to the objects if we change the default tablespace for the user?
Nothing. They will continue to work as normal
*. You got a requirement to run a script as user “MARK” and you don’t have the password of MARK. How you will take next step?
We will ask application support. In case they cannot be reached, we can take ASCII password stored in dba_users and change the password. Once work is done we can change the password back using ASCII that we stored earlier
Also we can set current_user option in SQLPLUS
*. What is the view to check default permanent and temp tablespace values?
database_properties
*. Have you implemented password policies?
a. Yes. Using profile management
*. What are SNIPPED sessions? If there any impact on database to have them?
Those are sessions terminated from database because of crossing IDLE_TIME limit. But disadvantage is even though oracle level sessions are cleared, at OS level processes will still occupy resources which is a burden to the server
*. I am using IDLE_TIME as 15 min, but observed that even after 20min idle time, session is not getting disconnected, what is the reason?
RESOURCE_LIMIT parameter is not set to TRUE
*. As a DBA will you recommend to implement auditing in the database?
Depends on the type of database, but it would be wise to implement atleast on some critical tables
*. What factors you will choose before you enable auditing?
We need to check about database performance and what level of auditing is required
*. If auditing is not in place, how you will get to know if a user performed update or not yesterday?
We can use logminer to read the archives which are generated yesterday
*. Are you maintaining any SOX compliance database?
No. We don’t have any SOX database as of now
*. How to check which users are granted with sysdba role?
We can check from v$syspw_file view
*. If we want to restrict users to connect to the database, but want to allow some users to connect, how can we do that?
We need to grant restrcited session privilege for the users whom we want to connect
