What is a database?
·         Database offer a single point of mechanism for storing and retrieving information with the help of tables.
·     Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.
·      It is a structure that stores information about the attributes of the entities and relationships among them.
·         It also stores data types for attributes and indexes.
·    Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.
What are the different types of storage systems available and which one is used by Oracle?
Two types of storage systems are available
·   Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)
·         Most databases use RDBMS model, Oracle also uses RDBMS model.
·         Hierarchical Storage Management System (HSM)
·         Information Management System (IMS) from IBM.
·         Integrated Database Management System (IDMS) from CA.
Explain some examples of join methods
Join methods are of mainly 3 types.
Merge Join
·         Sorting both the tables using join key and then merge the rows which are sorted.
Nested loop join
·         It gets a result set after applying filter conditions based on the outer table.
·         Then it joins the inner table with the respective result set.
Hash join -
·         It uses hash algorithm first on smaller table and then on the other table to produce joined columns. After that matching rows are returned.
What are the components of logical data model and list some differences between logical and physical data model?
Components of logical data model are
Entity - Entity refers to an object that we use to store information. It has its own table.
Attribute - It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.
Record - It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.
Domain - It is the set of all the possible values for a particular attribute.
Relation - Represents a relation between two entities.
Difference between Logical and Physical data model.
Logical data model represents database in terms of logical objects, such as entities and relationships.
Physical data model represents database in terms of physical objects, such as tables and constraints.
Explain Two Easy Sql Optimizations?
EXISTS can be better than IN under various conditions.
UNION ALL is faster than UNION (not sorting).
Name Three Sql Operations That Perform a Sort?
·         CREATE INDEX.
·         DISTINCT.
·         GROUP BY.
·         ORDER BY.
·         INTERSECT.
·         MINUS.
·         UNION.
Explain the Difference Between a Function, Procedure and Package?
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
Explain the Use of Table Functions?
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Name Three Advisory Statistics You Can Collect?
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.
Where in The Oracle Directory Tree Structure Are Audit Traces Placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer.
Explain Materialized Views and How They Are Used?
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
What Background Process Refreshes Materialized Views?
The Job Queue Processes.
Describe What Redo Logs Are?
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
How Would You Force a Log Switch?
What Does Coalescing a Tablespace Do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
What Is the Difference Between a Temporary Tablespace and A Permanent Tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
How Do You Add a Data File to A Tablespace?
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>.
How Do You Resize a Data File?
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>.
What View Would You Use to Look at The Size of a Data File?
What View Would You Use to Determine Free Space In A Tablespace?
How Would You Determine Who Has Added a Row to A Table?
Turn on fine grain auditing for the table.
How Can You Rebuild an Index?
Explain What Partitioning Is and What Its Benefit Is?
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
How Can You Gather Statistics on A Table?
The ANALYZE command.
How Can You Enable a Trace for A Session?
What Is the Difference Between the Sql*loader And Import Utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
What Is the Job of Smon And Pmon Processes?
SMON:System Monitor Process - Performs recovery after instance failure, monitors temporary segments and extents; cleans temp segments, coalesces free space (mandatory process for DB and starts by default).
PMON:Process Monitor - Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed dispatcher or server proceses (mandatory process for DB and starts by default).
[oracle@hostname ~]$ ps -ef |grep -e pmon -e smon |grep -v grep
oracle 6755 1 0 12:59 ? 00:00:05  ora_pmon_DB1_SID
oracle 6779 1 0 12:59 ? 00:00:06  ora_smon_DB1_SID.
How Do You Control Number Of Datafiles One Can Have In An Oracle Database?
The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different - "hard limit" parameter. When issuing a "create database" command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.
What Is a Tablespace?
A tablespace is a logical storage unit within an Oracle database. Tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.
A datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace.The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.
There are three types of tablespaces in Oracle:
·         Permanent tablespaces.
·         Undo tablespaces.
·         Temporary tablespaces.
What Is the Purpose Of Redo Log Files?
Before Oracle changes data in a datafile it writes these changes to the redo log. If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replied, which brings the datafile to the state it had before it became unavailable.
What Is a Shared Pool?
The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.
The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.
When Should You Rebuilt Indexes?
In 90% cases - NEVER. When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based. Also index blevel is one of the key indicators of performance of sql queries doing Index range scans.
Can You Built Indexes Online?
YES. You can create and rebuild indexes online.
This enables you to update base tables at the same time you are building or rebuilding indexes on that table.
You can perform DML operations while the index build is taking place, but DDL operations are not allowed.
Parallel execution is not supported when creating or rebuilding an index online.
The following statements illustrate online index build operations:
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE.
What Is Db Buffer Cache Advisor?
The Buffer Cache Advisor provides advice on how to size the Database Buffer Cache to obtain optimal cache hit ratios. Member of Performance Advisors --> Memory Advisor pack.
What Is Statspack Tool?
STATSPACK: is a performance diagnosis tool provided by Oracle starting from Oracle 8i and above.STATSPACK is a diagnosis tool for instance-wide performance problems it also supports application tuning activities by providing data which identifies high-load SQL statements.
Although AWR and ADDM (introduced in Oracle 10g) provide better statistics than STATSPACK,
users that are not licensed to use the Enterprise Manager Diagnostic Pack should continue to use statspack.
What Is Schema?
A schema is collection of database objects of a user.
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.
What Is Oracle Table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
What Is an Oracle View?
view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses).
What Is Partial Backup?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
What Is Full Backup?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.
What 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.
What Is A Redo Log?
The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
What Are Clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
What Is an Integrity Constrains?
An integrity constraint is a declarative way to define a business rule for a column of a table.
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.
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.
What Is a View?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses).
What Is Table?
A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
What Is a Synonym?
A synonym is an alias for a table, view, sequence or program unit.
What Is a Private Synonym?
Only its owner can access a private synonym.
What Is a Public Synonym?
Any database user can access a public synonym.
What Is Index Cluster?

A Cluster with an index on the Cluster Key.
Request to Download PDF

Post A Comment: