1] SGA (System or Shared Global Area):

SGA is used to store database

information that is shared by database processes. It
contains data and control information for the Oracle
Server and is allocated in the virtual memory if the
computer where Oracle resides.

SGA consists of several memory structures:-

1. Redo Buffer: The redo buffer is where data that

needs to be written to the online redo logs will be
cached temporarily, before it is written to disk.
Since a memory-to-memory transfer is much
faster than a memory-to-disk transfer, use of the
redo log buffer can speed up database operation.
The data will not reside in the redo buffer for very
long. In fact, LGWR initiates a flush of this area in
one of the following scenarios:
• Every three seconds
• Whenever someone commits
• When LGWR is asked to switch log files
• When the redo buffer gets one-third full or
contains 1MB of cached redo log data

2. Buffer Cache: The block buffer cache is where

Oracle stores database blocks before writing
them to disk and after reading them in from disk.
There are three places to store cached blocks
from individual segments in the SGA:

3. • Default pool (hot cache): The location where all

segment blocks are normally cached.

4. • Keep pool (warm cache): An alternate buffer

pool where by convention you assign segments

1
 that are accessed fairly frequently, but still get
aged out of the default buffer pool due to other
segments needing space.

5. • Recycle pool (do not care to cache): An

alternate buffer pool where by convention you
assign large segments that you access very
randomly, and which would therefore cause
excessive buffer flushing of many blocks from
many segments. There’s no benefit to caching
such segments because by the time you wanted
the block again, it would have been aged out of
the cache. You would separate these segments
out from the segments in the default and keep
pools so they would not cause those blocks to age
out of the cache.

6. Shared Pool: The shared pool is where Oracle

caches many bits of “program” data. When we
parse a query, the parsed representation is
cached there. Before we go through the job of
parsing an entire query, Oracle searches the
shared pool to see if the work has already been
done. PL/SQL code that you run is cached in the
shared pool, so the next time you run it, Oracle
doesn’t have to read it in from disk again. PL/SQL
code is not only cached here, it is shared here as
well. If you have 1,000 sessions all executing the
same code, only one copy of the code is loaded
and shared among all sessions. Oracle stores the
system parameters in the shared pool. The data
dictionary cache (cached information about
database objects) is stored here. Shared Pool
manages memory on an LRU basis, similar to
buffer cache, which is perfect for caching and

2
 reusing data.

7. Large Pool: The large pool is not so named

because it is a “large” structure (although it may
very well be large in size). It is so named because
it is used for allocations of large pieces of
memory that are bigger than the shared pool is
designed to handle. Large memory allocations
tend to get a chunk of memory, use it, and then
be done with it. There was no need to cache this
memory as in buffer cache and Shared Pool,
hence a new pool was allocated. So basically
Shared pool is more like Keep Pool whereas Large
Pool is similar to the Recycle Pool. Large pool is
used specifically by:
• Shared server connections, to allocate the UGA
region in the SGA.
• Parallel execution of statements, to allow for
the allocation of interprocess message buffers,
which are used to coordinate the parallel query
servers.
• Backup for RMAN disk I/O buffers in some
cases.

8. Java Pool: The Java pool is used in different ways,

depending on the mode in which the Oracle
server is running. In dedicated server mode the
total memory required for the Java pool is quite
modest and can be determined based on the
number of Java classes you’ll be using. In shared
server connection the java pool includes shared
part of each java class and Some of the UGA used
for per-session state of each session, which is
allocated from the JAVA_POOL within the SGA.

9. Streams Pool: The Streams pool (or up to 10

3
 percent of the shared pool if no Streams pool is
configured) is used to buffer queue messages
used by the Streams process as it moves or copies
data from one database to another.

2] Oracle Initialization Parameters :

Oracle initialization parameters are configurable settings

that control the behavior and operation of an Oracle
database instance. These parameters are essential for
defining various aspects of the database's environment,
including memory allocation, file locations, process limits,
and more. They determine how the database starts up,
manages resources, and interacts with users or
applications.. The type of file used to start the instance
determines if dynamic initialization parameter changes
persist across database shutdown and startup.

The parameter file types are:

1. Server parameter file(SPF): which is the

preferred form of the initialization parameter file,
and is a binary file that can be written to and read
by the database. It must not be edited manually.

2. Text initialization parameter file(PF): which is a

text file that can be read by the Oracle instance,
but it is not written to by the instance. You can
change a text initialization parameter file with a
text editor, but changes do not take effect until
you restart the Oracle instance.

Types of Initialization Parameters in Oracle: There are

three types of parameters, they are as follows:

i. Basic Parameters in Oracle:

4
 Most databases should only need to have the
database’s basic initialization parameters set to
run properly and efficiently. We have hundreds of
parameters in the database but there are a few
basic parameters that we use and follow during
our daily tasks. Basic parameters are around 30
parameters. Most of the DBA do not even use all
of these 30 parameters during their tasks. We
have operating system-dependent parameters:
These parameters are also both basic and
advanced parameters.
Example: DB_BLOCK_SIZE

ii. Advanced Parameters in Oracle:

Even the expert DBA will not know about the
advanced parameters. No need to understand
each and every parameter at the beginning.
Sometimes we need to change the parameter to
enhance the performance. Some of these
parameters require expert DBAs. Even the oracle
itself suggests we should not touch the advanced
parameters.
Example: DB_CACHE_SIZE

iii. Derived Parameters in Oracle:

The Derived parameters are the parameters that
are calculated from the values of other
parameters. The Derived parameters are neither
Basic parameters, nor advanced parameters.
Example: sessions derived from processes.
SESSION= (1.5 * PROCESSES) + 22

Example: DB_BLOCK_SIZE, SGA_TARGET,

CONTROL_FILES

5
 parameters:

1. **DB_NAME**: Specifies the name of the database.

This is a mandatory parameter and uniquely identifies the
database instance.

2. **CONTROL_FILES**: Indicates the paths of control files

used by the database. These files contain metadata critical
for database startup and recovery.

3. **SGA_TARGET**: Sets the total size of the System

Global Area (SGA), which is a shared memory region
storing data and control information for the database.

4. **PGA_AGGREGATE_TARGET**: Defines the total

memory allocated to the Program Global Area (PGA), used
for processes like sorting and hashing.

5. **UNDO_TABLESPACE**: Identifies the tablespace for

undo data, which is used to roll back transactions and
maintain read consistency.

6. **PROCESSES**: Determines the maximum number of

operating system processes that can connect to the
database, including both user and background processes.

7. **LOG_BUFFER**: Sets the amount of memory

allocated for the redo log buffer, which temporarily stores
redo entries before they are written to the redo log files.

8. **OPEN_CURSORS**: Specifies the maximum number

of cursors (SQL statements) that a single session can open
simultaneously.

9. **MEMORY_TARGET**: Automatically manages

memory allocation between the SGA and PGA, simplifying

6
memory management.

10. **COMPATIBLE**: Specifies the Oracle database

compatibility level. This parameter ensures that the
database behaves in a manner compatible with a specified
Oracle version.

These parameters are fundamental for configuring and

managing an Oracle database efficiently.

3] Create a trigger to convert new value of insert/update

statements into capital letters.

SQL trigger that converts the new values of an INSERT or

UPDATE statement into capital letters for a specific
column in a table. Let's assume the table is named
employees and the column to be converted is name.

CREATE TRIGGER trg_capitalize_name

BEFORE INSERT OR UPDATE ON employees

FOR EACH ROW

BEGIN

SET [Link] = UPPER([Link]);

END;

Explanation:

Trigger Name: trg_capitalize_name

Timing: BEFORE INSERT OR UPDATE - This ensures the

trigger fires before the data is inserted or updated.

Table: employees - The table on which the trigger is

7
applied.

Action:

FOR EACH ROW - This means the trigger will execute for
each row that is inserted or updated.

SET [Link] = UPPER([Link]); - This converts the

new value of the name column to uppercase.

This trigger ensures that any new or updated value in the

name column of the employees table will be automatically
converted to uppercase before being stored in the
database.

3] Oracle Background process:

Oracle Database background processes are essential for

the smooth functioning of the database. These processes
perform various maintenance tasks and ensure the
database operates efficiently. Here are some key
background processes in Oracle:

Process Monitor (PMON): Monitors other background

processes and performs process recovery when a server or
dispatcher process terminates abnormally. It also cleans
up resources used by failed processes.

System Monitor (SMON): Performs system-level cleanup

duties, such as instance recovery, temporary space
reclamation, and undo tablespace management.

Database Writer (DBWn): Writes modified blocks from the

database buffer cache to the datafiles. It ensures that

8
changes made to the database are saved to disk.

Log Writer (LGWR): Writes redo log entries from the redo
log buffer to the online redo log files. This process ensures
that all changes made to the database are recorded for
recovery purposes.

Checkpoint (CKPT): Signals the DBWn process to write all

modified buffers to disk and updates the control file and
datafile headers with the latest checkpoint information.

Archiver (ARCn): Copies redo log files to archive storage

when they are full or when a log switch occurs. This
process is essential for database recovery.

Recoverer (RECO): Resolves distributed transactions that

are pending due to a network or system failure.

4]Procedure:

A procedure in PL/SQL (Procedural Language/Structured

Query Language) is a subprogram that performs a specific
task. Unlike functions, procedures do not return values
directly but can modify data through their parameters.
They are used to encapsulate and reuse code, making your
programs more modular and maintainable.

Creating a Procedure:

Here is a basic structure to define a procedure:

sql

CREATE OR REPLACE PROCEDURE procedure_name (

param1 IN data_type, -- IN parameter

9
 param2 OUT data_type -- OUT parameter

) AS

BEGIN

-- PL/SQL code here

NULL; -- Placeholder statement

END procedure_name;

Example: Procedure with OUT Parameter

In this example, we'll create a procedure that calculates

the square of a number and returns the result using an
OUT parameter.

5] explain loading schema in which file resides in control

file in sql

Loading a schema can involve utilizing a control file when

dealing with SQLLoader in Oracle Database. The control
file in SQLLoader defines the data file to be loaded,
describes the format of that data, and specifies how
SQL*Loader should handle the data as it's loaded into the
target database tables. Here's a step-by-step explanation:

1. Create the Control File

The control file contains instructions for SQL*Loader about

how to load data. Here’s a sample control file:

plain

LOAD DATA

10
INFILE 'data_file.csv'

INTO TABLE employees

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

employee_id INTEGER EXTERNAL,

first_name CHAR,

last_name CHAR,

hire_date DATE "YYYY-MM-DD",

salary DECIMAL EXTERNAL

6] Direct path and Conventional path loaading:

Direct path: loading involves providing the compiler with

explicit paths to the body files of units, bypassing the need

for separate specification

files. This approach can be useful in certain scenarios

where you want to compile

units without needing their specifications available. Direct

path loading can also be

used to enforce a particular build order or to compile units

from multiple directories.

To use direct path loading in SQL Loader, you need to

specify the DIRECT=TRUE parameter in your control file.

11
For example:

LOAD DATA

INFILE '[Link]'

INTO TABLE employees

APPEND

FIELDS TERMINATED BY ','

(Department, EmployeeID, FirstName, LastName)

DIRECT=TRUE

Conventional path: In conventional path loading, the Ada

compiler relies

on the specification (*.ads) files of units to locate their

corresponding body (*.adb)

files. When compiling a unit that depends on another unit,

the compiler searches for

the necessary body files in directories specified by the with

and pragma

Source_File_Name clauses in the referencing unit. The

compiler then loads these bodies

according to the information provided in the

specifications.

To use conventional path loading in SQL Loader, simply

omit the DIRECT=TRUE parameter in your control file. For
example:

12
LOAD DATA

INFILE '[Link]'

INTO TABLE employees

APPEND

FIELDS TERMINATED BY ','

(Department, EmployeeID, FirstName, LastName)

7] Export/Import command parameters:

The EXP command is an SQL*Plus utility for Oracle

databases that allows you to export data from an Oracle
database into a dump file. Here are the details about the
EXP command and its various parameters

Syntax:

exp username/password@database [parameters]

Common Parameters:

FILE: Specifies the name of the export dump file.

OWNER: Specifies the owner of the objects to be

exported.

TABLES: Lists the tables to be exported.

ROWS: Determines whether to export rows (Y - Include

rows; N - Exclude rows).

GRANTS: Determines whether to export grants (Y - Include

grants; N - Exclude grants).

13
INDEXES: Determines whether to export indexes (Y -
Include indexes; N - Exclude indexes).

CONSTRAINTS: Determines whether to export constraints

(Y - Include constraints; N - Exclude constraints).

LOG: Specifies the name of the log file to write the export
log to.

Example:

sql

exp hr/hr@orcl FILE=[Link] OWNER=hr

TABLES=(employees, departments) ROWS=Y GRANTS=Y
INDEXES=Y CONSTRAINTS=Y LOG=[Link]

This command exports the employees and departments

tables owned by the hr user from the orcl database into
the [Link] file, including rows, grants, indexes, and
constraints, and writing the export log to [Link].

The IMP command is an SQL*Plus utility for Oracle

databases that allows you to import data from an export
dump file. Here's a brief overview of the IMP command:

Syntax:

imp username/password@database [parameters]

Common Parameters:

FILE: Specifies the name of the export dump file.

FROMUSER: Specifies the user whose objects are to be

imported.

TOUSER: Specifies the user into whose account the objects

14
are to be imported.

TABLES: Lists the tables to be imported.

IGNORE: Determines how errors in the import process

should be handled.

Example:

sql

imp hr/hr@orcl FILE=[Link] FROMUSER=hr

TOUSER=hr TABLES=(employees, departments) IGNORE=y

This command imports the employees and departments

tables from the hr user in the [Link] file into the
same hr user account in the orcl database, while ignoring
errors.

8] ROI :

Return on Investment (ROI) is a financial metric used to

evaluate the efficiency or profitability of an investment. In
SQL, you can calculate ROI by using a formula that
typically involves the gain from the investment and the
cost of the investment. Here's a simple example to
illustrate how you might calculate ROI using SQL:

Let's say you have a table named investments with the

following columns:

investment_id: Unique identifier for each investment

initial_investment: The amount of money initially invested

final_value: The value of the investment at the end of the

period

15
ROI Formula:

The formula for ROI =(Net Profit/Cost of Investment)*100

SQL Query:

Here is how you can write an SQL query to calculate the

ROI for each investment:

SELECT

investment_id,

initial_investment,

final_value,

((final_value - initial_investment) / initial_investment) *

100 AS roi_percentage

FROM

investments;𝑅o𝐼=(Net Profit/Cost of Investment)*100

9] explain database fragumentation:

1. Logical Fragmentation: This occurs when data is logically

divided across

different tables or database objects. For example, if

related data is stored in

separate tables rather than being consolidated, it can lead

to logical

fragmentation.

2. Physical Fragmentation: Physical fragmentation

16
happens when data is

scattered across different physical locations on the storage

medium. This can

occur due to various factors such as disk fragmentation,

inefficient storage

allocation, or data storage optimizations.

3. Performance Impact: Fragmentation can lead to

decreased performance due

to increased disk access times, additional I/O operations,

and reduced cache

efficiency. Queries may take longer to execute because the

required data is

scattered across different locations.

4. Storage Efficiency: Fragmentation can also impact

storage efficiency. When

data is fragmented, there may be unused space within

data blocks or pages,

leading to wasted storage capacity. Compact storage

arrangements can

improve efficiency and reduce storage costs.

5. Maintenance Overhead: Fragmentation increases the

complexity of database

maintenance tasks such as backup, restore, and data

reorganization. It

17
requires additional effort to manage and optimize
fragmented databases to

ensure optimal performance and storage efficiency.

6. Fragmentation Avoidance and Management: Database

administrators and

developers employ various techniques to avoid and

manage fragmentation.

These techniques include proper database design,

indexing, defragmentation

routines, and storage optimizations. Tools and utilities are

also available to

analyze and address fragmentation issues

10] what is backup ? explain online/offline backup?

Online Backup: An online backup is taken while the

database remains

operational and continues to process transactions. It

allows backup

operations to occur without disrupting database

availability.

• Procedure:

• Database Configuration: Configure the DBMS to support

online backups, which typically involves enabling features

like

18
online redo logs, archive logging, or backup mode.

• Backup Command: Use DBMS-specific backup commands

or

APIs to initiate an online backup process. These commands

typically trigger the DBMS to create a consistent snapshot

of the

database while transactions continue to occur.

• Backup Destination: Specify the destination for the

backup

data, which can be a file system directory, tape device,

network

location, or cloud storage.

• Backup Completion: Once the online backup is

completed,

ensure that all necessary files and data have been

successfully

copied to the backup destination.

• Backup Verification: Optionally, verify the integrity and

completeness of the backup data using DBMS utilities or

validation tools.

• Backup Completion: After verification, the backup

process is

19
complete, and normal database operations can continue.

3. Ada Implementation:

• To perform offline or online backups in Ada, you can

utilize database

specific APIs or utilities provided by the DBMS.

• If the DBMS supports online backup operations through

API calls, you

can incorporate these calls into your Ada application to

trigger backup

processes.

• For offline backups, you can use Ada to execute system

commands or

utilities that interact with the DBMS to shut down the

database, copy

files, and restart the database.

Offline Backup: An offline backup is taken when the

database is not actively processing transactions. It involves
shutting down the database or

placing it in a state where no changes can occur during the

backup

process.

• Procedure:

• Database Shutdown: Gracefully shut down the database

20
to

ensure all transactions are completed and data is in a

consistent

state.

• File Copy: Copy database files (data files, control files,

redo logs,

etc.) to a backup destination using file system utilities or

backup

software.

• Restart Database: After backup completion, restart the

database to resume normal operations.

16] performance tune:

1. Performance Optimization: Tuning Ada programs can

lead to significant

performance improvements, such as faster execution

times, reduced memory

usage, and improved responsiveness. By optimizing critical

code paths, data

structures, and algorithms, you can enhance the overall

performance of Ada

applications, making them more efficient and responsive

to user interactions.

2. Resource Efficiency: Ada programs often run on

21
resource-constrained

systems, such as embedded devices or real-time systems,

where resource

utilization is critical. Tuning Ada programs helps minimize

resource

consumption, such as CPU usage, memory footprint, or

energy consumption,

to ensure optimal performance within the constraints of

the target

environment.

3. Scalability: Tuning Ada applications is essential for

ensuring scalability,

especially in multi-user or distributed systems where the

workload may vary

dynamically. By optimizing concurrency control

mechanisms, data access

patterns, and communication protocols, you can scale Ada

applications to

handle increasing workloads and accommodate growing

user demands

effectively.

4. Real-Time Requirements: In real-time systems, where

timely and predictable

responses are crucial, tuning Ada programs is essential to

22
meet stringent

timing constraints and deadlines. By optimizing task

scheduling, interrupt

handling, and event processing, you can ensure that Ada

real-time

applications respond predictably and reliably to external

stimuli.

5. Reduced Costs: Tuning Ada programs can lead to cost

savings by optimizing

resource usage, reducing hardware requirements, and

improving system

performance. By minimizing resource contention,

bottlenecks, and

inefficiencies, you can achieve better utilization of existing

infrastructure and

avoid unnecessary investments in additional hardware or

resources.

6. Quality of Service: Tuning Ada applications improves the

overall quality of

service by enhancing reliability, availability, and

maintainability. By optimizing

error handling, fault tolerance, and recovery mechanisms,

you can ensure that

Ada programs operate reliably under normal conditions

and gracefully recover

23
from failures or errors.

7. Competitive Advantage: Optimized Ada applications

have a competitive

advantage in the marketplace by offering superior

performance, reliability,

and scalability compared to competing solutions. By

continuously tuning and

improving Ada programs, organizations can differentiate

themselves, attract

customers, and maintain a competitive edge in their

industry.

12] Implementing locks with latches :

Define a Latch Type: Create a type definition for the latch,

specifying the necessary

components such as a Boolean flag indicating whether the

latch is locked or unlocked, and a

condition variable for tasks to wait on.

Implement Locking and Unlocking Procedures: Define

procedures within the protected

type to acquire and release the latch. Use Ada's built-in

synchronization mechanisms such as

entries and condition variables to implement waiting and

signaling.

Use the Latch: In your Ada tasks or procedures that need

24
to access the shared resource

protected by the latch, call the Lock procedure to acquire

the latch before accessing the

resource. Release the latch by calling the Unlock

procedure when done.

Error Handling: Implement error handling mechanisms to

handle exceptional conditions

that may occur during locking or unlocking operations,

such as timeouts or resource

unavailability. Consider using Ada's exception handling

features to gracefully handle such

scenarios and ensure the robustness of your latch

implementation.

13]Administering database objects:

• Tables. tables are database objects that contain all the

data in a database. A table

definition is a collection of columns. In tables, data is

organized in a row-and

column format similar to a spreadsheet. Each row

represents a unique record,

and each column represents a field within the record.

• Views. Each view is essentially a saved SQL statement

that performs a query that

allows the system to generate a report or reports.

25
• Stored Procedures. Each procedure is set of SQL
statements that can perform

both queries and actions that allow the system to

generate a report or reports.

Stored procedures can accept parameters at run time,

whereas views cannot.

• Indexes. A table index allows the database program to

find data in a table

without scanning the entire table. There can be multiple

indexes per table or no

index at all.

• Triggers. A trigger is a special type of stored procedure

that executes when data

in a specified table is modified. A trigger can query other

tables and can include

complex SQL statements.

14]The database administration tools:

• Oracle Universal Installer (OUI)

The Oracle Universal Installer installs your Oracle software

and options. It can

automatically launch the Database Configuration Assistant

to install a

database.

26
• Database Configuration Assistant (DBCA)

The Database Configuration Assistant creates a database

from templates that

are supplied by Oracle, or you can create your own. It

enables you to copy a

preconfigured seed database, thus saving the time and

effort of generating

and customizing a database from scratch.

• Database Upgrade Assistant

This Database Upgrade Assistant guides you through the

upgrade of your

existing database to a new Oracle release.

• Oracle Net Manager

Net Manager is an alternate tool for configuring and

managing Oracle

Database networks.

• Oracle Enterprise Manager

The primary tool for managing your database is Oracle

Enterprise Manager, a

web-based interface. After you have installed the Oracle

software, created or

upgraded a database, and configured the network, you

can use Oracle

27
Enterprise Manager for managing your database. In
addition, Oracle

Enterprise Manager also provides an interface for

performance advisors and

for Oracle utilities such as SQL*Loader and Recovery

Manager.

15]Using the sql plus copy command:

The SQL*Plus COPY command can copy data between two

databases via

SQL*Net. The preferred method of doing this is to use

SQL*Plus on the host

where the database resides. If performing the copy

command from a client

SQL*Net connection, the data is transferred through the

client machine.

The copy command copies data from one Oracle instance

to [Link] data is

simply copied directly from a source to a target. The

format of the copy command

is:

COPY FROM database TO database action -

destination_table (column_name, column_name...) USING

query

The action can include:

28
create - If the destination table already exists, copy will
report an error, otherwise

the table is created and the data is copied.

replace - If the destination table exists, copy will drop and

recreate the table with

the newly copied data. Otherwise, it will create the table

and populate it with the

data.

insert - If the destination table exists, copy inserts the new

rows into the table.

Otherwise, copy reports an error and aborts.

append - Inserts the data into the table if it exists,

otherwise it will create the table

and then insert the data.

SQL> copy from scott/tiger@ORCL92 to

scott/tiger@ORCL92-

create new_emp using select * from emp;

16] Goals of performance tuning.

1. Accessibility: Ensuring that individuals with disabilities

have equal access to

public spaces, services, and opportunities. This may

involve making physical

spaces, digital platforms, and communication channels

29
more accessible and

inclusive.

2. Non-Discrimination: Preventing discrimination against

individuals with

disabilities in various aspects of life, including

employment, education,

housing, transportation, and public services. Tuning goals

may involve

strengthening enforcement mechanisms to address

instances of

discrimination and promoting awareness of rights and

responsibilities under

the ADA.

3. Reasonable Accommodation: Promoting the provision

of reasonable

accommodations to individuals with disabilities to ensure

their full

participation in various activities and settings. Tuning goals

may focus on

clarifying and expanding the types of accommodations

required under the

ADA and addressing barriers to implementation.

4. Integration and Inclusion: Encouraging the integration

and inclusion of

30
individuals with disabilities into all aspects of society,
including workplaces,

schools, recreational facilities, and community activities.

Tuning goals may

involve promoting inclusive practices, removing barriers to

participation, and

fostering a culture of acceptance and respect for diversity.

5. Accessibility Standards: Establishing and updating

accessibility standards

and guidelines to ensure that buildings, transportation

systems, technology,

and communication methods are accessible to individuals

with disabilities.

Tuning goals may involve aligning standards with advances

in technology and

best practices in accessibility design.

6. Advocacy and Awareness: Increasing awareness of

disability rights and

advocating for the rights and needs of individuals with

disabilities. Tuning

goals may involve conducting outreach and education

campaigns, engaging

with stakeholders, and mobilizing support for disability

rights initiatives.

7. Enforcement and Compliance: Strengthening

31
enforcement mechanisms to

ensure compliance with the ADA and address violations of

disability rights.

Tuning goals may involve enhancing enforcement

agencies' resources,

streamlining complaint processes, and promoting

voluntary compliance

through education and incentives.

17] Index.

An index is a database structure that can be used to

improve the performance of

SQL queries. An PL/SQL index is a schema object that has

the role to provide direct

andfast access without reading the entire table.

Syntax -CREATE INDEX index_name

ON table_name (column1, column2, ...);

B-tree indexes – By default, Oracle Database creates B-

tree indexes(normal

indexes).

CREATE INDEX index_name

ON table_name(column_name);

Bitmap indexes -The Bitmap indexes store the rowids

32
column value in bits.

CREATE BITMAP INDEX bitmap_index_name

ON table_name(column_name)

TABLESPACE tbs_1

LOCAL(PARTITION ix_p1 TABLESPACE tbs_02,

PARTITION ix_p2,

PARTITION ix_p3 TABLESPACE tbs_03);

Partitioned indexes -are partitions that store an entry for

each value that appears in the indexed column of the
table.

CREATE INDEX index_name ON table_name

(column_name)

GLOBAL PARTITION BY RANGE (column_name)

(PARTITION p1 VALUES LESS THAN (100),

PARTITION p2 VALUES LESS THAN (200),

PARTITION p3 VALUES LESS THAN (MAXVALUE));

Function-based indexes -are based on expressions. You

can build queries that

evaluate the value returned by an expression.

CREATE INDEX index_name

ON table_name(UPPER(column_name));

33
18]Architecture of oracle enterprise manage.

1) ORACLE MANAGMENET AGENTS(OMA):

Agent or management agent is a piece of software that

runs on every host that you want to monitor.
Management agent work in conjunction with plug-ins to
monitor the target server. All hosts where you install the
agents are then termed as “Managed Hosts”.

Agents can be of two types:

CENTRAL AGENT : When you first install Oracle

Management Service (OMS), by default you receive a
Management Agent called the Central Agent. It is used for
monitoring the OMS host, the OMS, and the other targets
running on this OMS.

host.

STANDALONE TARGET AGENT : To monitor other hosts and

the targets running on those hosts, you must install a
separate Standalone Management Agent on each of those
hosts.

2) ORACLE MANAGEMENT SERVICE(OMS): OMS is the

actual brain of OEM. OMS is deployed over Weblogic 13c.
It is a web-based application and perform below broad
level activities:

Works with the Management Agents and the plug-ins to

discover targets.

Monitor and manage the agents

Store the collected information in a repository for future

34
reference and analysis

Renders the user interface for Enterprise Manager Cloud

Control.

3) ORACLE MANAGEMENT REPOSITORY(OMR): Oracle

Management Repository is simply a database/database
schema where all the information collected by the
Management Agent gets stored. The Management
Repository then organizes the data so that it can be
retrieved by the OMS and displayed in the Enterprise
Manager Cloud Control console.

4) PLUG-INS: Plug-ins as name suggest are pluggable

entities that offer special management capabilities
customized to suit specific target types. Example if your
target type is Oracle EBS, you will need specific plug-ins to
monitor the Oracle EBS. Plug-ins are deployed to the OMS
as well as the Management Agent in OEM 13c.

Good thing about Plug-ins is that they have independent

release cycles, so every time you have a new version of an
Oracle product released, you will have a new version of
the plug-in released to support monitoring of that new
product version in Enterprise Manager Cloud Control.

5) BI PUBLISHER: Oracle Business Intelligence (BI) Publisher

is Oracle’s primary reporting tool for authoring, managing,
and delivering all your highly formatted documents.
Starting with Oracle Enterprise Manager 13c, Oracle BI
Publisher is installed and configured by default on the
OMS.

6) CONSOLE: Console is the GUI Front end of the OEM

application. With the help of the console, you can monitor
and administer your entire computing environment from

35
one location. All the systems and services including
enterprise application systems, databases, hosts,
middleware application servers, listeners etc will be visible
through Console

7) EM CLI: The Enterprise Manager Command Line

Interface (EM CLI) is a command-line too that is accessible
through classic programming language constructs,
enabling tasks t be created and run either from the
command-line or programatically.

8) TARGETS: A target, or more specifically, a target

instance, can be defined as any entity that can be
monitored within an enterprise. Managed targets are the
entities that Enterprise Manager can monitor and manage.
Examples of targets include hosts, databases, application
servers, applications, and listeners. As your environment
changes, you can add and remove targets from Enterprise
Manager as required.

9) CONNECTORS: Connector is a very specialized piece of

software whose work is to be act like an intermediary
between OEM and third party application like BMC
Remedy Ticket generation system. Connectors make your
life easier in the sense that they give you ready-made
solution to connect your OEM system to other famous
third party applications.

10) JVMD ENGINE: Java Virtual Machine Diagnostics

(JVMD) Engine enables you to diagnose performance
problems in Java applications. Starting with Oracle
Enterprise Manager 13c , as part of the Oracle Fusion
Middleware Plug-in deployment, one JVMD Engine is
installed and configured by default on the

OMS. You will also need JVMD Agents to be manually

36
deployed on the targeted JVMs.

19] Cursor: A PL/SQL cursor is a pointer to a result set, or

the data that results from a query.

Cursors let you fetch one or more rows from the database
into memory, process .them, and then either commit or
roll back those changes.

Types of cursors:

Implicit cursors are generated automatically by Oracle

database when an SQL statement occurs in the PL/SQL
executable part;

Explicit cursors are declared and defined by the user when

an query which appears

in a PL/SQL return multiple lines as a result.

20] v$lock:

1. Use an Oracle Database API: Utilize an Oracle Database

API to execute SQL

queries against the V$LOCK view and retrieve lock-related

information. Popular

APIs for interfacing with Oracle databases include Oracle

Call Interface (OCI),

Oracle's Pro*C/C++, and Oracle's JDBC (Java Database

Connectivity).

2. Execute SQL Queries: Write Ada code that utilizes the

Oracle API to execute

SQL queries against the V$LOCK view. These queries can

37
retrieve information

about locks held by different database sessions, such as

the type of lock, the

locked object, and the session ID of the holder.

3. Process and Analyze Data: Once you've retrieved lock-

related data from

V$LOCK, process and analyze it within your Ada

application as needed. You can

perform various analyses, such as identifying deadlock

situations, monitoring

lock contention, or tracking lock acquisition patterns.

4. Handle Error Conditions: Implement error handling

mechanisms in your Ada

code to handle potential errors or exceptions that may

occur during the

interaction with the Oracle database. This ensures the

robustness and

reliability of your application, especially when dealing with

critical database

operations.

21]Rollback segments:

1. Using Database APIs or Libraries: Ada developers can

utilize database

specific APIs or libraries to manage transactions and

38
rollback segments. These

APIs often provide functions or methods to explicitly start,

commit, and

rollback transactions.

2. Configuring Rollback Segments: If you have access to the

DBMS

configuration, you can configure rollback segments within

the database itself.

This involves setting parameters such as the size of

rollback segments, the

number of rollback segments, and their management

policies. However, this

configuration is typically performed using database

administration tools

specific to the chosen DBMS, rather than within Ada code

directly.

3. Error Handling and Rollback: Proper error handling is

essential when

managing transactions in Ada. If an error occurs during

transaction execution,

the transaction should be rolled back to maintain data

consistency. This is

typically done using exception handling constructs within

Ada code, as

39
demonstrated in the example above.

4. Concurrency Control: Rollback segments play a crucial

role in concurrency

control mechanisms such as multi-version concurrency

control (MVCC) or

locking protocols. Ada developers should consider

concurrency control

strategies when designing database interactions to

prevent data

inconsistencies and conflicts among concurrent

transactions.

22]Components of sql loader:

1. SQL Database: You need a SQL database such as Oracle,

PostgreSQL, MySQL,

etc., where you want to load data.

2. AdaDB Package: AdaDB provides packages that allow

Ada programs to

connect to SQL databases, execute SQL commands, and

handle data retrieval

and manipulation. The key package in AdaDB is

[Link], which

defines the interfaces for database connections, cursors,

transactions, etc.

3. AdaDB Drivers: AdaDB drivers are specific to each

40
database system and are

responsible for establishing connections, executing SQL

statements, and

fetching results. These drivers are implemented separately

for different

databases. For example, you might have an Oracle driver

([Link]) for Oracle

databases, a PostgreSQL driver ([Link]) for PostgreSQL

databases, and so on.

4. AdaDB Utility Programs: You might develop Ada utility

programs similar to

SQL Loader that read data from external sources (e.g., text
files, CSV files, etc.)

and use AdaDB to load that data into the SQL database.
These utility

programs would typically perform tasks such as parsing

input files, preparing

SQL statements for data insertion, and executing those

statements using

AdaDB.

5. Data Files: Just like SQL Loader, you would have data
files containing the data

to be loaded into the database. These files could be in

various formats such as

41
CSV, fixed-width, or custom delimited formats.

6. Ada Programming: You would write Ada programs that

utilize AdaDB to read

data from the data files and insert or update records in the
SQL database.

These programs would typically include logic for error

handling, data

validation, and database transactions.

42