Explore topic-wise InterviewSolutions in .

We can follow either a systematic APPROACH or a reactive approach for PERFORMANCE tuning.

We can follow either a systematic approach or a reactive approach for performance tuning.

LIKE many other features of Oracle like exp/imp,backu recovery this field can't be automated. This is one area that REQUIRES a lot of detective work on the part of APPLICATION programmers and DBA's to see w some process is running slower than expected, why can't we scale applications to a larger number of users without problems like performance degradation etc.This is a area where our technical knowledge must be USED ALONG with constant experimentation and observation.

Like many other features of Oracle like exp/imp,backu recovery this field can't be automated. This is one area that requires a lot of detective work on the part of application programmers and DBA's to see w some process is running slower than expected, why can't we scale applications to a larger number of users without problems like performance degradation etc.This is a area where our technical knowledge must be used along with constant experimentation and observation.

ORACLE provides SEVERAL options to aid performance tuning, such as partitioning lair tables, using materialized views, storing plan outlines, using tools like Automatic Optimizer STATISTICS collection feature, cages like DBMS_STATS, SQL Tuning Advisor to TUNE SQL statements,ETC.

Oracle provides several options to aid performance tuning, such as partitioning lair tables, using materialized views, storing plan outlines, using tools like Automatic Optimizer statistics collection feature, cages like DBMS_STATS, SQL Tuning Advisor to tune SQL statements,etc.

Performance tuning focuses PRIMARILY on WRITING efficient SQL, allocating APPROPRIATE computing resources, and ANALYZING wait events and contention in a SYSTEM.

Performance tuning focuses primarily on writing efficient SQL, allocating appropriate computing resources, and analyzing wait events and contention in a system.

An APPLICATION context allows us to define a set of application attributes, usually a set of session environmental variables, that we can use to control an application's ACCESS to the DATABASE. Using application attributes we can SUPPLY relevant predicate values for fine-grained access control POLICY.

An application context allows us to define a set of application attributes, usually a set of session environmental variables, that we can use to control an application's access to the database. Using application attributes we can supply relevant predicate values for fine-grained access control policy.

VPD policy can be APPLIED to SELECT, INSERT, UPDATE, INDEX and DELETE STATEMENTS.

VPD policy can be applied to SELECT, INSERT, UPDATE, INDEX and DELETE statements.

To create a VPD, we have to create what is known as an application context and then implement fine grained access control to enforce the row-level SECURITY for database tables and views. The application context helps US create security policies that draw upon certain aspects of a user's session information. For example when an user LOGS into the database, the user ID identifies the user, and BASED on that piece of information, the application's security POLICY sets limits on what the user can do within the database. VPD is simple an implementation of the application context with fine-grained access control.

To create a VPD, we have to create what is known as an application context and then implement fine grained access control to enforce the row-level security for database tables and views. The application context helps us create security policies that draw upon certain aspects of a user's session information. For example when an user logs into the database, the user ID identifies the user, and based on that piece of information, the application's security policy sets limits on what the user can do within the database. VPD is simple an implementation of the application context with fine-grained access control.

VPD uses a type of query REWRITE to restrict users to CERTAIN ROWS of TABLES and views. a security policy is attached to the table or tables to which we want to control access,and stored procedures are written to modify any relevant SQL statements made against the tables in the question. For example, when a user issues an UPDATE statement against the table with such a security policy, Oracle will dynamically append a predicate(a WHERE clause)to the user's statement to modify it and limit the user's access to that table. This will prevent users belonging to say SALES department from accessing and modifying FINANCE department records.

UPDATE emp set salary=salary*10 will be modified as UPDATE emp set salary=salary*10 where dept='SALES';.

VPD uses a type of query rewrite to restrict users to certain rows of tables and views. a security policy is attached to the table or tables to which we want to control access,and stored procedures are written to modify any relevant SQL statements made against the tables in the question. For example, when a user issues an UPDATE statement against the table with such a security policy, Oracle will dynamically append a predicate(a WHERE clause)to the user's statement to modify it and limit the user's access to that table. This will prevent users belonging to say SALES department from accessing and modifying FINANCE department records.

UPDATE emp set salary=salary*10 will be modified as UPDATE emp set salary=salary*10 where dept='SALES';.

We can use the fine-grained access control for the following purposes:

1. Enforce row-level access control through SELECT, INSERT, UPDATE and DELETE STATEMENTS.
2. Create a security policy that controls access based on a certain value of a column.
3. Create Policies that are APPLIED the same way always as well as policies that DYNAMICALLY change during the execution of the query.
4. Create SETS of scurity policies,called policy groups.

We can use the fine-grained access control for the following purposes:

This term is used to refer to the implementation of fine-grained-access-control policies using APPLICATION CONTEXTS. This offers security at row-level rather than table level. Each user of the application can be limited to seeing only a part of the table's data by using VPD concept. This row-level security is enforced by attaching a security policy directly to a database object such as, table, VIEW or a synonym. It provides a much stronger security than application level security. No matter WHATEVER tool is used(SQL*PLUS, ADHOC query tool, report writer), the user can't elude this row-level security enforced by the database server.

This term is used to refer to the implementation of fine-grained-access-control policies using application contexts. This offers security at row-level rather than table level. Each user of the application can be limited to seeing only a part of the table's data by using VPD concept. This row-level security is enforced by attaching a security policy directly to a database object such as, table, view or a synonym. It provides a much stronger security than application level security. No matter whatever tool is used(SQL*PLUS, adhoc query tool, report writer), the user can't elude this row-level security enforced by the database server.

Oracle USES two RELATED mechanisms to enforce fine-grained SECURITY within the database.They are:

1. Application CONTEXT.
2. Fine-grained access control Policy.

Oracle uses two related mechanisms to enforce fine-grained security within the database.They are:

Traditional access CONTROL techniques like granting roles, privileges, views are broad-based that resulted in UNNECESSARILY restricting users. As a SOLUTION Oracle came up with fine-grained access control where control on DATA access is at low-level.

Traditional access control techniques like granting roles, privileges, views are broad-based that resulted in unnecessarily restricting users. As a solution Oracle came up with fine-grained access control where control on data access is at low-level.

It is a new security MECHANISM from ORACLE that PROVIDES low-level data security

It is a new security mechanism from Oracle that provides low-level data security

The following command is USED to ALLOCATE quota on a tablespace:
SQL>ALTER USER (USERNAME) QUOTA ON (tablespacename);

The following command is used to allocate quota on a tablespace:
SQL>ALTER USER (username) QUOTA ON (tablespacename);

No, a newly created user won't be ABLE to create database objects directly. Even if the user is assigned DEFAULT temporary and permanent tablespace at the creation time, it is mandatory to allocate QUOTA on a tablespace to USERS.

No, a newly created user won't be able to create database objects directly. Even if the user is assigned default temporary and permanent tablespace at the creation time, it is mandatory to allocate quota on a tablespace to users.

In order for a user to CONNECT and start communicating with the database ,he must be GRANTED CREATE SESSION system privilege.The following statement is USED:
SQL>GRANT CREATE SESSION to (username);
How to create a user with CONNECT system privilege?
SQL>GRANT CONNECT to (username) identified by (PASSWORD);

In order for a user to connect and start communicating with the database ,he must be granted CREATE SESSION system privilege.The following statement is used:
SQL>GRANT CREATE SESSION to (username);
How to create a user with CONNECT system privilege?
SQL>GRANT CONNECT to (username) identified by (password);

A NEW user can't CONNECT directly to database, because the user doesn't have any privileges to do so. When a user TRIES to connect he gets the following error at the SQL prompt:
ERROR:
Ora-01045: user lacks CREATE SESSION privilege; LOGON denied.

A new user can't connect directly to database, because the user doesn't have any privileges to do so. When a user tries to connect he gets the following error at the SQL prompt:
ERROR:
Ora-01045: user lacks CREATE SESSION privilege; logon denied.

The FOLLOWING query GIVES the default TEMPORARY and permanent tablespace for a user:
SQL>SELECT default_tablespace,temporary_tablespace from dba_users where username='(username)';

The following query gives the default temporary and permanent tablespace for a user:
SQL>SELECT default_tablespace,temporary_tablespace from dba_users where username='(username)';

SQL>CREATE USER (username) IDENTIFIED BY (password);
In case of ORACLE DATABASE 11g use the following statement:
SQL>CREATE USER (username) identified by "(password)";
The above query creates a user and password for the user. It is good practice to assign default temporary and permanent tablespace to the users. It is a good practice to ass them at the time of database creation.

SQL>CREATE USER (username) IDENTIFIED BY (password);
In case of Oracle database 11g use the following statement:
SQL>CREATE USER (username) identified by "(password)";
The above query creates a user and password for the user. It is good practice to assign default temporary and permanent tablespace to the users. It is a good practice to ass them at the time of database creation.

If we dont assign a default tablespace System tablespace becomes the default tablespace. If a user creates a very LARGE object in the System table space, they MIGHT take up all the space in it and make it IMPOSSIBLE for the SYS superuser to create any new OBJECTS in it, causing the database to come to a grinding halt. This is the main reason why we should always create a default tablespace for every user.

If we dont assign a default tablespace System tablespace becomes the default tablespace. If a user creates a very large object in the System table space, they might take up all the space in it and make it impossible for the SYS superuser to create any new objects in it, causing the database to come to a grinding halt. This is the main reason why we should always create a default tablespace for every user.

If we don't assign a default table space, the SYSTEM TABLESPACE BECOMES the default tablespace.

If we don't assign a default table space, the System tablespace becomes the default tablespace.

In Oracle database 10g at the TIME of database creation default temporary tablespace and default PERMANENT tablespace for all users will be CREATED during database-creation process. Once these TWO tablespaces are created, we don't have to specify them again during the database creation process.

In Oracle database 10g at the time of database creation default temporary tablespace and default permanent tablespace for all users will be created during database-creation process. Once these two tablespaces are created, we don't have to specify them again during the database creation process.

USERS need to have a default tablespace, where their objects will be created if they don't EXPLICITLY ASSIGN a different tablespace during OBJECT CREATION.

Users need to have a default tablespace, where their objects will be created if they don't explicitly assign a different tablespace during object creation.

All users NEED a TEMPORARY TABLESPACE where they can perform operations such as sorting DATA during SQL execution.

All users need a temporary tablespace where they can perform operations such as sorting data during SQL execution.

1. Controlling ACCESS to data(authorization.
2. Restricting access to legitimate USERS (authentication).
3. ENSURING accountability on part of the users(AUDITING).
4. SAFEGUARDING key data in the database(encryption).
5. Managing the security of the entire organizational information structure (enterprise security).

The GOAL of database SECURITY is to prevent UNAUTHORIZED use of database or its components.Database security DEPENDS on system and NETWORK security.

The goal of database security is to prevent unauthorized use of database or its components.Database security depends on system and network security.

ASM and database instances share access to disks in a disk GROUP.

1. There can be one ASM INSTANCE and one database instance serving single database in a node.
2. There can be one ASM instance,MULTIPLE database instances serving many database in a node. If the ASM instance fails,all the databases fails.The advantage is that this doesn't demand a Server reboot(Operating system restart).
3. There can be clustered ASM instances that are clustered using ORACLE Clusterware.(i.e)One ASM instance on each node that are INTEGRATED using Oracle clusterware.

ASM and database instances share access to disks in a disk group.

An ASM metadata includes the FOLLOWING:
1) The disks that BELONG to a disk group
2) Amount of space AVAILABLE within a disk group
3) The filenames of the files within a disk group
4) The location of disk group datafile data extents
5) A redo log that records information about automatically changing data blocks

An ASM metadata includes the following:
1) The disks that belong to a disk group
2) Amount of space available within a disk group
3) The filenames of the files within a disk group
4) The location of disk group datafile data extents
5) A redo log that records information about automatically changing data blocks

ASM METADATA is the INFORMATION that ASM USES to control the disk group. It is present WITHIN a disk group.

ASM metadata is the information that ASM uses to control the disk group. It is present within a disk group.

No. An ASM instance can mount a DISK group that can be USED by a DATABASE instance.

No. An ASM instance can mount a disk group that can be used by a database instance.

No. The SGA size of an ASM is smaller than that of an Oracle DATABASE instance, as an ASM performs FEWER TASKS than a database.

No. The SGA size of an ASM is smaller than that of an Oracle database instance, as an ASM performs fewer tasks than a database.

An ASM instance is the instance that manages ASM disk groups. It composes of SYSTEM Global Area(SGA) and BACKGROUND processes. ASM instance mounts a disk group that is MADE AVAILABLE to the database instance. An ASM instance manages the METADATA of a disk group and provides file layout information to the database instances.

An ASM instance is the instance that manages ASM disk groups. It composes of System Global Area(SGA) and background processes. ASM instance mounts a disk group that is made available to the database instance. An ASM instance manages the metadata of a disk group and provides file layout information to the database instances.

YES, ASM can co-exist with non-ASM third-party file systems and thus can be INTEGRATED into pre-existing environments. OEM has an INTERFACE for MIGRATING non-ASM FILES to ASM.

Yes, ASM can co-exist with non-ASM third-party file systems and thus can be integrated into pre-existing environments. OEM has an interface for migrating non-ASM files to ASM.

Oracle Enterprise Manager(OEM), SQL*PLUS, ASMCMD command-line interface are some interfaces that can be used with ASM.

Oracle Enterprise Manager(OEM), SQL*PLUS, ASMCMD command-line interface are some interfaces that can be used with ASM.

ASM uses the Oracle Managed Files(OMF) feature which simplifies file MANAGEMENT. Files are created at specified location, RENAMES files, deletes files when OPERATIONS at TABLESPACE level happens(SAY tablespace deletion).

ASM uses the Oracle Managed Files(OMF) feature which simplifies file management. Files are created at specified location, renames files, deletes files when operations at tablespace level happens(say tablespace deletion).

This is a flexible option provided by ASM. The normal and redundant options of ASM PROVIDES two and THREE way mirroring. We can use external option to ENABLE RAID(Redundant ARRAY Of Independent Disks).

This is a flexible option provided by ASM. The normal and redundant options of ASM provides two and three way mirroring. We can use external option to enable RAID(Redundant Array Of Independent Disks).

DISKS can be added and removed from the disk groups online(i.e)during the operation of the DATABASE WITHOUT any downtime. When disks are added or removed ASM automatically redistributes the CONTENTS with out any downtime.Thus ASM eliminates down time.

Disks can be added and removed from the disk groups online(i.e)during the operation of the database without any downtime. When disks are added or removed ASM automatically redistributes the contents with out any downtime.Thus ASM eliminates down time.

The files distributed across the set of disks in a DISK group are striped or distributed across the disks to ELIMINATE hot spots and improve performance.Thus they perform EQUALLY well as RAW disks.

The files distributed across the set of disks in a disk group are striped or distributed across the disks to eliminate hot spots and improve performance.Thus they perform equally well as raw disks.

An ASM DISK group is a group of disks that ASM manages as an unit.Within the diskgroups an file system interface maintains the Oracle DATABASE files. ASM SIMPLIFIES database STORAGE by consolidating disks into disk groups.This reduces I/O overhead.

An ASM disk group is a group of disks that ASM manages as an unit.Within the diskgroups an file system interface maintains the Oracle database files. ASM simplifies database storage by consolidating disks into disk groups.This reduces I/O overhead.

ASM uses DISK GROUPS to store DATA FILES.

ASM uses disk groups to store data files.

ASM is the recommended storage MANAGEMENT solution from oracle as it PROVIDES an alternative to conventional volume managers,FILE systems and RAW DEVICES.

ASM is the recommended storage management solution from oracle as it provides an alternative to conventional volume managers,file systems and raw devices.

There are three MAJOR types of SERVER POOLS namely Master Server, Utility Server, VIRTUAL Servers.

There are three major types of server pools namely Master Server, Utility Server, Virtual Servers.

DOMU REFERS to UNPRIVILEGED DOMAINS.

Domu refers to unprivileged domains.

DOM0 REFERS to the Domain0 which is the REFERNCE given to ORACLE VM Server.

Dom0 refers to the Domain0 which is the refernce given to Oracle VM Server.

VM agent is a component of ORACLE VM Server.It is the communication MEDIUM between the Oracle VM MANAGER and the Oracle VM Server.

VM agent is a component of Oracle VM Server.It is the communication medium between the Oracle VM manager and the Oracle VM Server.

Oracle VM is composed of Oracle VM Server ,the software COMPONENT that is USED for virtualization provisioning and Oracle VM Manager a GUI tool used to CONTROL VM Server.

Oracle VM is composed of Oracle VM Server ,the software component that is used for virtualization provisioning and Oracle VM Manager a GUI tool used to control VM Server.

ORACLE VM is the LATEST virtualization technology from oracle. It is built upon the OPEN SOURCE project XEN.

Oracle VM is the latest virtualization technology from oracle. It is built upon the open source project Xen.

From OMB PLUS console ENTER the FOLLOWING : OMBCONNECT. repos/password@host:PORT:service_name USE WORKSPACE wokspace_name.

From OMB Plus console enter the following : OMBCONNECT. repos/password@host:port:service_name USE WORKSPACE wokspace_name.

We can LAUNCH OMB plus at the UNIX PROMPT using OWB_HOME/owb/bin/unix/OMBPlus.sh.

We can launch OMB plus at the unix prompt using OWB_HOME/owb/bin/unix/OMBPlus.sh.

OWB scripts are installed at \ owb \RTP \sql and are USED for managing deployment jobs,execution jobs,CONTROL centers from SQL*PLUS.

OWB scripts are installed at \ owb \rtp \sql and are used for managing deployment jobs,execution jobs,control centers from SQL*PLUS.