Oracle Interview Questions – Part 34
Q.Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
Q.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.
Q.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.
Q.You have just compiled a PL/SQL package but got errors, how would you view the errors?
Q.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:
Q.What is a Checkpoint?
A checkpoint occurs when the DBWR (database writer) process writes all modified buffers in the SGA buffer cache to the database data files.
Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks. Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters.
Set parameter LOG_CHECKPOINTS_TO_ALERT=TRUE to observe checkpoint start and end times in the database alert log.
Checkpoints can be forced with the ALTER SYSTEM CHECKPOINT; command.
SCN can refer to:
System Change Number – A number, internal to Oracle that is incremented over time as change vectors are generated, applied, and written to the Redo log.
System Commit Number – A number, internal to Oracle that is incremented with each database COMMIT.
Note: System Commit Numbers and System Change Numbers share the same internal sequence generator.
Q.What is PGA_AGGREGATE_TARGET parameter?
PGA_AGGREGATE_TARGET: specifies the target aggregate PGA memory available to all server processes attached to the instance.
Q.Large Pool is used for what?
The large pool is an optional memory area and provides large memory allocations for:
•Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
•I/O server processes, buffer area
•Oracle backup and restore operations (RMAN)
•User Global Area (UGA) for shared servers
Q.What is a Locally Managed Tablespace?
Locally Managed Tablespace is a tablespace that record extent allocation in the tablespace header.
Each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.
Advantages of Locally Managed Tablespaces:
•Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
•Reduce contention on data dictionary tables (single ST enqueue)
•Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
•Changes to the extent bitmaps do not generate rollback information
Q.Can you audit SELECT statements?
YES. But beware, you will need a storage mechanism to hold your SQL SELECT audits, a high data volume that can exceed the size of your whole database, everyday.
SQL SELECT auditing can be accomplished in several ways:
•Oracle audit table command: audit SELECT table by FRED by access;
•Oracle Fined-grained Auditing
In a busy database, the volume of the SELECT audit trail could easily exceed the size of the database every data.
Plus, all data in the audit trail must also be audited to see who has selected data from the audit trail.
Q.Can you see Execution Plan of a statement?
YES. In many ways, for example from GUI based tools like TOAD, Oracle SQL Developer.
Configuring AUTOTRACE, a SQL*Plus facility
AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we’ve executed, and the resources they used.
Once the PLAN_TABLE has been installed in the database, You can control the report by setting the AUTOTRACE system variable.
•SET AUTOTRACE OFF – No AUTOTRACE report is generated. This is the default.
•SET AUTOTRACE ON EXPLAIN – The AUTOTRACE report shows only the optimizer execution path.
•SET AUTOTRACE ON STATISTICS – The AUTOTRACE report shows only the SQL statement execution statistics.
•SET AUTOTRACE ON – The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
•SET AUTOTRACE TRACEONLY – Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any.
Q.A table has been created with below settings. What will be size of 4th extent?
storage (initial 200k
What will be size of 4th extent?
“NEXT” Specify in bytes the size of the next extent to be allocated to the object.
Percent Increase allows your segment to grow at an increasing rate.
The first two extents will be of a size determined by the Initial and Next parameter (200k)
The third extent will be 1 + PCTINCREASE/100 times the second extent (1,4*200=280k).
AND The fourth extent will be 1 + PCTINCREASE/100 times the third extent (1,4*280=392k!!!), and so on…
Q.What is difference between Multithreaded/Shared Server and Dedicated Server?
Oracle Database creates server processes to handle the requests of user processes connected to an instance.
A server process can be either of the following:
-A dedicated server process, which services only one user process
-A shared server process, which can service multiple user processes
Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters.
Q.Can you import objects from Oracle ver. 7.3 to 9i?
Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility.
Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility).
For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.
Q.How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
Q.Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
Q.Which Process reads data from Datafiles?
Server Process – There is no background process which reads data from datafile or database buffer.
Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.
Oracle can be configured to vary the number of user processes for each server process.
In a dedicated server configuration, a server process handles requests for a single user process.
A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.
Q.How many maximum Redo Logfiles one can have in a Database?Maximum number of logfiles is limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement. Control file can be resized to allow more entries; ultimately an operating system limit. Maximum number of logfiles per group – Unlimited
Consider the parameters that can limit the number of redo log files before setting up or altering the configuration of an instance redolog.
The following parameters limit the number of redo log files that you can add to a database: MAXLOGFILES & MAXLOGMEMBERS.
The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of redo log files for each database. Group values can range from 1 to MAXLOGFILES.
When the compatibility level is set earlier than 10.2.0, the only way to override this upper limit is to re-create the database or its control file. Therefore, it is important to consider this limit before creating a database.
When compatibility is set to 10.2.0 or later, you can exceed the MAXLOGFILES limit, and the control files expand as needed.
If MAXLOGFILES is not specified for the CREATE DATABASE statement, then the database uses an operating system specific default value.
The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members for each group. As with MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file. Therefore, it is important to consider this limit before creating a database.
If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, then the database uses an operating system default value.
Q.What does DBMS_FGA package do?
The DBMS_FGA package provides fine-grained security functions. DBMS_FGA is a PL/SQL package used to define Fine Grain Auditing on objects.
DBMS_FGA Package Subprograms:
•ADD_POLICY Procedure – Creates an audit policy using the supplied predicate as the audit condition
•DISABLE_POLICY Procedure – Disables an audit policy
•DROP_POLICY Procedure – Drops an audit policy
•ENABLE_POLICY Procedure – Enables an audit policy
Q.What is Cost Based Optimization?
The Oracle Cost Based Optimizer (CBO) is a SQL Query optimizer that uses data statistics to identify the query plan with lowest cost before execution. The cost is based on the number of rows in a table, index efficiency, etc.
All applications should be converted to use the Cost Based Optimizer as the Rule Based Optimizer is not be supported in Oracle 10g and above releases.
Q.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;
Q.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.
Q.How do you delete duplicate rows in a table?
There is a few ways to achieve that:
•Using subquery to delete duplicate rows:
DELETE FROM table_name WHERE rowid NOT IN (SELECT max(rowid) FROM table_name GROUP BY id);
•Use RANK to find and remove duplicate table rows
•Use self-join to remove duplicate rows
•Use analytics to detect and remove duplicate rows
•Delete duplicate table rows that contain NULL values
Q.What is Automatic Management of Segment Space setting?
Oracle9i New Feature Series: Automatic Segment Space Management
Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps.
It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.
ASSM can be specified only with the locally managed tablespaces (LMT).
Oracle uses bitmaps to manage the free space. Bitmaps allow Oracle to manage free space more automatically.
Here is an example:
CREATE TABLESPACE example
DATAFILE ‘/oradata/ORA_SID/example01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;
The storage parameters PCTUSED, FREELISTS and FREELIST GROUPS specified while creating a table are ignored by Oracle on a LMT ASSM tablespace. Oracle does not produce an error.
One huge benefit of having ASSM is to reduce the “Buffer Busy Waits” you see on segments.
Using ASSM can hinder database DML performance, and most Oracle experts will use manual freelists and freelist groups.
Q.How do you move tables from one tablespace to another tablespace?
There are several methods to do this;
1) export the table, drop the table, create the table definition in the new
tablespace, and then import the data (imp ignore=y).
2) Create a new table in the new tablespace with the CREATE TABLE statement AS SELECT all from source table
CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM source_table;
Then drop the original table and rename the temporary table as the original:
DROP TABLE real_table;
RENAME temp_name TO real_table;
Note: don’t forget to rebuild any indexes.
Q.Can you change SHARED_POOL_SIZE online?
YES. That’s possible.
SQL>alter system set shared_pool_size=500M scope=both;
It’s a lot quicker to bounce the instance when changing this.
Q.Can you Redefine a table Online?
Yes you can. In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:
•Improve the performance of queries or DML
•Accommodate application changes
Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table.
The mechanism is called online table redefinition.
When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process.
The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.
Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.
You can perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.
Q.A Column is having many repeated values which type of index you should create on this column, if you have to?
For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. Each column contains less than 100 distinct values by themselves, and a b-tree index would be fairly useless in a database of 20 million vehicles.
Q.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.