Author: Syed Saad Ali
How to Find Database Object and it’s Corresponding Datafiles
SELECT DISTINCT t.ts#,
t.name AS Tablespace_name,
d.name AS DATAFILE,
s.owner,
s.segment_name,
s.segment_type,
s.bytes / 1024 / 1024 AS “MB”
FROM v$tablespace t,
v$datafile d,
dba_segments s
WHERE t.ts# = d.ts#
AND t.name = s.tablespace_name
AND s.owner = ‘APPS’;
How to Find CPU Usage for a Session
SET linesize 145
SET pagesize 9999
COLUMN sid format 9999 heading ‘SID’
COLUMN serial_id format 999999 heading ‘Serial#’
COLUMN session_status format a9 heading ‘Status’ justify right
Oracle Real Application Cluster Intro
RAC Defination
Oracle RAC stands for Oracle Real Application cluster. It is a clustering solution that ensures high availability of databases by providing instance failover and Load Balancing features.
Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.
Oracle RAC instances use two processes
1- GES(Global Enqueue Service)
- The Global Enqueue Service (GES) manages or tracks the status of all the Oracle enqueuing mechanism.
- This involves all non Cache fusion intra-instance operations.
- GES performs concurrency control on dictionary cache locks, library cache locks, and the transactions.
- GES does this operation for resources that are accessed by more than one instance.
2- GCS(Global Cache Service)
- The main function of the global cache service (GCS) is to track the status and location of the data blocks.
- Status is the resource role and the resource mode.
- The GCS is the main mechanism by which cache coherency among the multiple caches is maintained.
- GCS maintains the modes for blocks in the global role and is responsible for block transfers between the instances.
- The LMS processes handle the GCS messages and carry out the bulk of the GCS processing.
Oracle RAC instances are composed of following Background Processes
ACMS—Atomic Controlfile to Memory Service (ACMS)
In an Oracle RAC environment, the atomic controlfile to memory service (ACMS) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure.
GTX0-j—Global Transaction Process
he GTX0-j process provides transparent support for XA global transactions in a RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions.
LMON—Global Enqueue Service Monitor
The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.
LMD—Global Enqueue Service Daemon
The LMD process manages incoming remote resource requests within each instance.
LMS—Global Cache Service Process
The LMS process maintains records of the datafile statuses and each cached block by recording information in a Global Resource Directory (GRD). The LMS process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances. This processing is part of the Cache Fusion feature.
LCK0—Instance Enqueue Process
The LCK0 process manages non-Cache Fusion resource requests such as library and row cache requests.
RMSn—Oracle RAC Management Processes (RMSn)
The RMSn processes perform manageability tasks for Oracle RAC. Tasks accomplished by an RMSn process include creation of resources related Oracle RAC when new instances are added to the clusters.
RSMN—Remote Slave Monitor
Remote Slave Monitor manages background slave process creation and communication on remote instances. These background slave processes perform tasks on behalf of a coordinating process running in another instance.
SELECT gls.name,
idfs.id_flex_num chart_of_accounts_id,
idfs.segment_num,
idfs.flex_value_set_id,
fvs.flex_value_set_name,
idfs.application_id,
idfs.id_flex_code,
idfs.application_column_name,
idfs.segment_name,
fvs.security_enabled_flag,
( CASE
WHEN fvs.validation_type = ‘F’ THEN ‘Table’
WHEN fvs.validation_type = ‘I’ THEN ‘Independent’
WHEN fvs.validation_type = ‘D’ THEN ‘Dependent’
WHEN fvs.validation_type = ‘N’ THEN ‘None’
WHEN fvs.validation_type = ‘P’ THEN ‘Pair’
WHEN fvs.validation_type = ‘U’ THEN ‘Special’
ELSE ‘Unknown Type’
END ) validation_type,
( CASE
WHEN fvs.validation_type = ‘F’ THEN fvt.application_table_name
ELSE ‘Not Applicable’
END ) validation_table_name
FROM gl_ledgers gls,
fnd_id_flex_segments idfs,
fnd_flex_value_sets fvs,
fnd_flex_validation_tables fvt
WHERE gls.chart_of_accounts_id (+) = idfs.id_flex_num
AND fvs.flex_value_set_id = idfs.flex_value_set_id
AND gls.ledger_id = &ledgerid
AND idfs.application_id = 101
AND idfs.id_flex_code IN ( ‘GL#’, ‘GLLE’ )
AND fvs.flex_value_set_id = fvt.flex_value_set_id (+)
ORDER BY idfs.id_flex_code,
idfs.id_flex_num,
idfs.segment_num ASC;