Row chaining row migration
PCT used PCT free
##########################################################
PCTFREE - The percentage of space reserved for future update of existing data.
PCTUSED - The percentage of minimum space used for insertion of new row data.
This value determines when the block gets back into the FREELISTS structure.
FREELIST - Structure where Oracle maintains a list of all free available
blocks.
##########################################################
MMON - The Oracle 10g background process to collect statistics for the Automatic
Workload Repository (AWR).
MMNL - This process performs frequent and lightweight manageability-related tasks,
such as session history capture and metrics computation.
MMAN - is used for internal database tasks that manage the automatic shared
memory(ASMM). MMAN serves as the SGA Memory Broker and coordinates the sizing of
the memory components.
##########################################################
PGA Memory
The Program Global Area (PGA) is a memory region that contains data and control
information for a single process (server or background). The PGA is made up of the
following:
Stack Space
A PGA always contains a stack space, which is memory allocated to hold
a session's variables, arrays, and other information.
Session Information - (UGA)
A PGA in an instance running without the multi-threaded server (named
Shared Server in Oracle9i) requires additional memory for the user's session, such
as private SQL areas and other information. If the instance is running the multi-
threaded server, this extra memory is not in the PGA, but is instead allocated in
the SGA (the Shared Pool).
Shared SQL Areas
Shared SQL areas are always in shared memory areas of the SGA (not the
PGA), with or without the multi-threaded server.
Non-shared and Writable
The PGA is a non-shared memory area to which a process can write. One
PGA is allocated for each server process; the PGA is exclusive to a server process
and is read and written only by Oracle code acting on behalf of that process.
UGA Memory
The UGA, or User Global Area, is allocated in the PGA for each session
connected to Oracle in a dedicated server environment. The PGA is memory allocated
at the client to hold a stack which contains all of the session's variables, etc.
In a Shared Server environment, Oracle allocates this memory in the Shapred Pool
(the shared pool is contained in the SGA), for all sessions. This helps to reduce
the PGA (client) memory footprint of Oracle, but will increase the SGA (shared
pool) requirements.
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User'
JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User'
JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program'
TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine'
JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/
SID Oracle User O/S User Session Program Machine PGA Memory PGA Memory
Max UGA Memory UGA Memory MAX
---- ------------ --------- ------------------ -------- --------------
-------------- -------------- --------------
3 oracle oracle@alex (LGWR) alex 5,526,516
5,526,516 77,956 77,956
9 oracle oracle@alex (ARC0) alex 4,500,080
4,500,080 77,956 77,956
10 oracle oracle@alex (ARC1) alex 4,500,080
4,500,080 77,956 77,956
11 SYSTEM oracle sqlplus@alex (TNS alex 3,403,988
4,780,244 77,956 1,400,476
2 oracle oracle@alex (DBW0) alex 2,488,624
2,488,624 77,956 77,956
4 oracle oracle@alex (CKPT) alex 1,359,484
1,359,484 77,956 77,956
17 SCOTT oracle sqlplus@alex (TNS alex 577,512
708,584 339,812 339,812
5 oracle oracle@alex (SMON) alex 499,704
499,704 77,956 77,956
8 oracle oracle@alex (QMN0) alex 475,596
541,132 274,348 274,348
19 SCOTT oracle sqlplus@alex (TNS alex 454,964
454,964 143,420 143,420
6 oracle oracle@alex (RECO) alex 237,024
237,024 77,956 77,956
1 oracle oracle@alex (PMON) alex 228,512
228,512 77,956 77,956
7 oracle oracle@alex (CJQ0) alex 228,512
228,512 77,956 77,956
13 rows selected.
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
##########################################################
20.DIFFERENTIATE BETWEEN Latches vs Enqueues
Enqueues are another type of locking mechanism used in Oracle. An enqueue is a more
sophisticated mechanism which permits several concurrent processes to have varying
degree of sharing of "known" resources. Any object which can be concurrently used,
can be protected with enqueues. A good example is of locks on tables. We allow
varying levels of sharing on tables e.g. two processes can lock a table in share
mode or in share update mode etc.
One difference is that the enqueue is obtained using an OS specific locking
mechanism. An enqueue allows the user to store a value in the lock, i.e the mode in
which we are requesting it. The OS lock manager keeps track of the resources
locked. If a process cannot be granted the lock because it is incompatible with the
mode requested and the lock is requested
with wait, the OS puts the requesting process on a wait queue which is serviced in
FIFO.
Another difference between latches and enqueues is that in latches there is no
ordered
queue of waiters like in enqueues.
Latch waiters may either use timers to wakeup and retry or spin (only in
multiprocessors). Since all waiters are concurrently retrying (depending on the
scheduler), anyone might get the latch and conceivably the first one to try might
be the last one to get.
###########################################################
What Is Syntactic and Semantic Checking?
Rules of syntax specify how language elements are sequenced to form valid
statements. Thus, syntactic checking verifies that keywords, object names,
operators, delimiters, and so on are placed correctly in your SQL statement. For
example, the following embedded SQL statements contain syntax errors:
-- misspelled keyword WHERE
EXEC SQL DELETE FROM EMP WERE DEPTNO = 20;
-- missing parentheses around column names COMM and SAL
EXEC SQL INSERT INTO EMP COMM, SAL VALUES (NULL, 1500);
Rules of semantics specify how valid external references are made. Thus, semantic
checking verifies that references to database objects and host variables are valid
and that host-variable datatypes are correct. For example, the following embedded
SQL statements contain semantic errors:
-- nonexistent table, EMPP
EXEC SQL DELETE FROM EMPP WHERE DEPTNO = 20;
-- undeclared host variable, emp_name
EXEC SQL SELECT * FROM EMP WHERE ENAME = :emp_name;
############################################################
GRANULES
In order to help manage the ability to dynamically change SGA parameters, Oracle
introduced the concept of a granule. A granule is � a unit of contiguous virtual
memory�. SGA components in Oracle 9i are now based on the granule size. If your
total SGA is less than 128 MB in size, the granule size is 4MB per granule.
Otherwise, the granule size is 16MB in size.
In 10G Granule size is determined by total SGA size. On most platforms, the size of
a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB
for larger SGAs. Some platform dependencies arise. For example, on 32-bit Windows,
the granule size is 8 M for SGAs larger than 1 GB.
select component, granule_size/1024/1024 "GRANULE_SIZE(Mb)" from
v$sga_dynamic_components;
############################################################
Oracle DBA - What are PCT Free and PCT Used? What is PCT increase parameter in
segment? - March 08, 2008 at 19:10 pm by Rajmeet Ghai
What are PCT Free and PCT Used? What is PCT increase parameter in segment?
PCTFREE is a parameter used to find how much space should be left in a database
block for future updates. This means that if the PCTFREE = 20, new rows will be
added in the block until it is 80% full.
PCTUSED is a parameter helps Oracle to find when it should consider a database
block to be empty enough to be added to the freelist. This means that if the
PCTFREE = 50, new rows will be not be added in the block until sufficient rows are
deleted from the block so that it falls below 40% empty.
PCTINCREASE parameter is used to find how much will the each subsequent segment
will grow. This value is in %
############################################################
How are the indexes updated?
Whenever a table partition is modified using a DDL command, the associated index
partitions are placed in the default tablespace or in the same tablespace as the
data segments. This has been an issue with the previous releases of the Oracle
databases.
With the Oracle 10g release, it has become possible to modify the partitioned
table, using the UPDATE INDEXES clause. It can rebuild the associated index
segments automatically. It helps to reduce the level of management needed and also
enhances the availability of the data in the table.
You can create and Alter the table and update indexes to reflect the changes
##############################################################
Latches vs Enqueues
Enqueues permit several concurrent processes to have varying degree of sharing of
resources. Any object which can be concurrently used, can be protected with
enqueues. The enqueue is obtained using an OS specific locking mechanism. An
enqueue allows the user to store a value in the lock.
In latches there is no ordered queue of waiters like in enqueues. Latch waiters may
use wakeup timers and retry or spin (only in multiprocessors). Since the processes
keep trying concurrently, any process can get the latch. The first process to try
might be the last one to get
#############################################################
DIFFERENTIATE BETWEEN Latches vs Enqueues
Enqueues are another type of locking mechanism used in Oracle. An enqueue is amore
sophisticated mechanism which permits several concurrent processes to havevarying
degree of sharing of "known" resources. Any object which can beconcurrently used,
can be protected with enqueues. A good example is of locks ontables. We allow
varying levels of sharing on tables e.g. two processes can lock atable in share
mode or in share update modeetc.One difference is that the enqueue is obtained
using an OS specific lockingmechanism. An enqueue allows the user to store a value
in the lock, i.e the mode inwhich we are requesting it. The OS lock manager keeps
track of the resourceslocked. If a process cannot be granted the lock because it is
incompatible with themode requested and the lock is requestedwith wait, the OS puts
the requesting process on a wait queue which is serviced inFIFO.Another difference
between latches and enqueues is that in latches there is noordered queue of waiters
like in enqueues.Latch waiters may either use timers to wakeup and retry or spin
(only inmultiprocessors). Since all waiters are concurrently retrying (depending on
thescheduler), anyone might get the latch and conceivably the first one to try
might bethe last one to get.
##############################################################
What is a latch?Latches are low level serialization mechanisms used to protect
shared datastructures in the SGA. The implementation of latches is operating
systemdependent, particularly in regard to whether a process will wait for a latch
and forhow long.A latch is a type of a lock that can be very quickly acquired and
freed. Latches aretypically used to prevent more than one process from executing
the same piece ofcode at a given time. Associated with each latch is a cleanup
procedure that will becalled if a process dieswhile holding the latch. Latches have
an associated level that is used to preventdeadlocks. Once a process acquires a
latch at a certain level it cannot subsequentlyacquire a latch at a level that is
equal to or less than that level (unless it acquires itnowait)
##############################################################
9.Where we use bitmap index?
a. Bitmap indexes are most appropriate for columns having low distinct values.
b. We can use bitmap index where cardinality is very low like gender columnor color
column.
##############################################################
What is the frequency of log Updated..?
1.COMMIT or ROLLABCK
2.time out occurs (3 secs)
3 1/3 of log is full
4.1 mb of redo5. Checkpoint occurs
##############################################################
Describe the Oracle Wait Interface, how it works, and what it provides. What are
somelimitations? What do the db_file_sequential_read and db_file_scattered_read
eventsindicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait
events. Selecting from tables such as v$system_event and v$session_event give you
event totals through the life of the database (or session). The former are totals
for the whole system, and latter on a per session basis. The event
db_file_sequential_read refers to single block reads, and table accesses by rowid.
db_file_scattered_read conversely refers to full table scans. It is so named
because the blocks are read, and scattered into the buffer cache.
##############################################################
How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.Use the explain plan set statement_id =
'tst1' into plan_table for a SQL statementLook at the explain plan with utlxplp.sql
or utlxpls.sql
##############################################################
How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the
v$db_cache_advice table.
If a change was necessary then I would use the alter system set db_cache_size
command.
##############################################################
How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or Use ALTER SESSION SET SQL_TRACE = TRUE
##############################################################
Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corrupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving
adistributed database.
###############################################################
What are the dictionary tables used to monitor a database space?
DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES.
###############################################################
Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.PL/SQL
construct to identify each and every work are used, is called as Cursor.For SQL
queries returning a single row, PL/SQL declares all implicit cursors.For queries
that returning more than one row, the cursor needs to be explicitly declared.
9. Explicit Cursor attributes
There are four cursor attributes used in Oraclecursor_name%Found, cursor_name
%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQLSQL%Found, SQL%NOTFOUND, SQL
%ROWCOUNT, SQL%ISOPENTips : 1. Here SQL%ISOPEN is false, because oracle
automatically closed the implicit cursor after executing SQL statements.
###############################################################
A long time ago, the only optimizer in the Oracle database was the Rule-Based
Optimizer (RBO). Basically, the RBO used a set of rules to determine how to execute
a query. If an index was available on a table, the RBO rules said to always use the
index. There are some cases where the use of an index slowed down a query. For
example, assume someone put an index on the GENDER column, which holds one of two
values, MALE and FEMALE. Then someone issues the following query:
SELECT * FROM emp WHERE gender='FEMALE';
If the above query returned approximately 50% of the rows, then using an index
would actually slow things down. It would be faster to read the entire table and
throw away all rows that have MALE values. Experts in Oracle query optimization
have come to a rule of thumb that says if the number of rows returned is more than
5-10% of the total table volume, using an index would slow things down. The RBO
would always use an index if present because its rules said to.
It became obvious that the RBO, armed with its set of discrete rules, did not
always make great decisions. The biggest problem with the RBO was that it did not
take the data distribution into account. So the Cost-Based Optimizer (CBO) was
born. The CBO uses statistics about the table, its indexes and the data
distribution to make better informed decisions. Using our previous example, assume
that the company has employees that are 95% female and 5% male. If you query for
females, then you do not want to use the index. If you query for males, then you
would like to use the index. The CBO has information at hand to help make these
kind of determinations that were not available in the old RBO.
###############################################################
How do u clear a unarchived redo log member if it is corrupted.
alter database clear unarchived logfile �logfilename';
How to register archive logfile in standy.
SELECT name FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence#
BETWEEN 24 and 28;
ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s24.dbf';
###############################################################
How do u recover a corrupted block what are it views.
V$DATABASE_BLOCK_CORRUPTION
rman target /
blockrecover datafile 1 block 507;
BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5, 98, 99;
###############################################################
What are different modes in dataguard, and in which protection mode do u use
standby redolog files and what is default mode and how can u switch between modes.
alter database set standby database to maximize [protection|availability|
performance]
################################################################
How to convert Dictionary-managed tablespace to local-managed tablespace
dbms_space_admin.tablespace_migrate_to_local()
dbms_space_admin.tablespace_migrate_from_local()
################################################################
Renaming database using NID?
nid TARGET=system/manager@orcl DBNAME=newdb SETNAME=Y
################################################################
RMAN has two packages to controle entire backup and restore activities . It is
avaialble under $ORACLE_HOME/rdbms/admin directory
recover.bsq
Package names are:
1. dbms_backup_restore
2. dbms_rcvman
##################################################################
To find out status of the RMAN jobs:
select sid,serial#,opname,sofar,totalwork,round(sofar/totalwork*100,2) complete
from v$session_longops where opname like '%RMAN%' and totalwork <>0;
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2)
"%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE
'%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
