buffer cache and Shared Pool

Posted on by Osama Mustafa in Database
Buffer Cache :


What is the Buffer Cache?

The buffer cache is part of the SGA. It holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk.

Purpose 
The purpose of the buffer cache is to minimize physical io. When a block is read by Oracle, it places this block into the buffer cache, because there is a chance that this block is needed again. Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk. 
 
Different Pool within Cache :
 
Keep pool
The keep pool’s purpose is to take small objects that should always be cached, for example Look Up Tables. 
 Recycle pool

The recycle pool is for larger objects.
Default pool
The default pool is for everything else. 
 
 Cold Area/Hot Area in Buffer Cache :
Each pool’s LRU is divided into a hot area and a cold area. Accordingly, buffers with in the hot area are hot buffers (and buffers in the cold are are called cold buffers).
By default, 50% of the buffers belong to the cold area and the other 50% belong to the hot area. This behaviour can be changed with _db_percent_hot_default (for the default pool) _db_percent_hot_recycle (for the recycle pool) and _db_percent_hot_keep (for the keep pool).
A newly read db block will be inserted between the cold and the hot area such that it belongs to the hot area. This is called midpoint insertion. However, this is only true for single block reads, multi block reads will be placed at the LRU end.

 

how to Flush Buffer Cache :
In 10g you can Flush Buffer using :
 alter system flush buffer_cache.
Optimal Size:

Some common wisdom says that the larger the buffer cache is, the better the performance of the database becomes. However, this claim is not always true.
To begin with, the cache needs to be managed. The bigger the cache, the larger the LRU and dirty list becomes. That results in longer search times for a free buffer (buffer busy waits.
Also, the bigger the cache, the greater the burden on the DBWn process.

 Shared Pool

 What is Shared Pool 
 
the shared pool is the part of the SGA where (among others) the following things are stored:

  • Optimized query plans
  • Security checks
  • Parsed SQL statements
  • Packages
  • Object informatio.

Shared Pool Latch :
used when memory is allocated or freed in the shared pool.

Library Cache latch :
this latch protects operations within the library cache.

Flush Shared Pool 

alter system flush shared_pool

 Allocation in memory

Memory in the shared pool is managed and freed in a LRU fashion, that is, Memory that isn’t used for long gets aged out first. This is in contrast to the large pool, where memory is managed in a heap (using allocate and free).

Check Shared Pool Size : 

select    name, bytes/1024/1024 "MB" from v$sgastat where pool = 'shared pool'
 order by bytes desc;

Refence Link :
 1-Buffer Cache
 2-Shared Pool

Use RMAN to Manage Oracle Files / DataFiles

Posted on by Osama Mustafa in Database
RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    750      SYSTEM               ***     /u01/app/oracle/oradata/ORCL/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/ORCL/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/ORCL/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/ORCL/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/ORCL/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1    370      TEMP                 32767       /u01/app/oracle/oradata/ORCL/temp01.dbf

RMAN>

Copy the file(s) to the new location.

RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/ORCL/soe.dbf';

Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.

RMAN> SQL 'ALTER TABLESPACE osama OFFLINE';

Switch to the new datafile copy(s) and recover the tablespace.

RMAN> SWITCH DATAFILE 8 TO COPY;
RMAN> RECOVER TABLESPACE osama;

Remove the old datafile(s).

Done .
Osama Mustafa

Manage Oracle Files / DataFiles Part 3

Posted on by Osama Mustafa in Database
This will be the last part for manage Oracle Database files we will mention another way using RMAN

SQL> SELECT name FROM v$datafile;

NAME
———————————————————
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS01.DBF

4 rows selected.

SQL>

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

You can rename Datafiles , Move it now .

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE ‘C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS01.DBF’ –
>  TO ‘C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_USERS01.DBF’;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

Done

Enjoy
Osama mustafa

ORA-12557: TNS:protocol adapter not loadable

Posted on by Osama Mustafa in Database
this problem On windows Platforms .

and it must be related to Windows Environment or Oracle Home PATH because sqlplus command works smoothly when I execute it inside ORACLE_HOME\bin.

RUN: SYSDM.CPL to open Windows System Properties
Click on Advanced Tab > Environment Variables…

Click the Path variable under System Variable, then click  Edit…

change the order between Oracle Client Home and Oracle DB Home:

From: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;

To: D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\client_1\bin;

in other words, put the Oracle DB Home in front of the other path.

Or i Don’t love this way , Since its not actual solution but its solve problem sometimes :
1-Remove ORACLE_HOME From environment Variable .


2- Restart PC 
Done

Osama mustafa 

ORA-39152: Table exists

Posted on by Osama Mustafa in Database
Error :

ORA-39152: Table exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append.

Cause :

Using APPEND to import the existing tables, as to not overrite them gives the following error:

Solution :

Truncating the table preserves the structure of the table for future use, so you are seeing this error message because there is a constraint or index in place.

To get around this you can use the following DataPump import parameters:

CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND EXCLUDE=INDEX,CONSTRAINT

Thank you
Osama mustafa

Cursor in EXECUTE IMMEDIATE

Posted on by Osama Mustafa in Database
Sometimes you want to use cursor without Open Cursor,you can use ref cursor .

syntax :

declare
   SQL_Text varchar2(32760) := 'qurey'; --your query goes here
   cur sys_refcursor;
begin
   open cur for SQL_Text;
end;
 

example :
V_query := ‘Cursor statement’ ;

declare
  rc sys_refcursor;
begin
  open rc for ;
  loop
    fetch rc into variable;
    exit when rc%notfound;
    <do your process.>
  end loop;
  close rc;
end;

Link Useful :
1-Blog
2-Cursor Loop Example 

Enjoy

Osama mustafa

Tablespace growth

Posted on by Osama Mustafa in Scripts
Sometimes you need to know how much your tablespace grow this month find below some scripts to do this :

Script-1 :

SELECT TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’) days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN (‘SYSAUX’,’SYSTEM’)
GROUP BY TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’), ts.tsname
ORDER BY ts.tsname, days;

Script-2: 

column “Percent of Total Disk Usage” justify right format 999.99
column “Space Used (MB)” justify right format 9,999,999.99
column “Total Object Size (MB)” justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
set line 5000
column “SEGMENT_NAME” justify left format A30
column “TABLESPACE_NAME” justify left format A30
select * from (select c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, ‘MM/DD/YY’) mydate, sum(space_used_delta) / 1024 / 1024 “Space used (MB)”, avg(c.bytes) / 1024 / 1024 “Total Object Size (MB)”,
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) “Percent of Total Disk Usage”
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) – 10
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = ‘S_PARTY’
group by c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, ‘MM/DD/YY’)
order by c.TABLESPACE_NAME,c.segment_name,to_date(mydate, ‘MM/DD/YY’));

Script-3:

set pages 80
set feedback off
column “OBJECT_NAME” justify left format A30
column “SUBOBJECT_NAME” justify left format A30
column “OBJECT_TYPE” justify left format A30
column “Tablespace Name” justify left format A30
set line 5000
SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
t.NAME “Tablespace Name”, s.growth/(1024*1024) “Growth in MB”,
(SELECT sum(bytes)/(1024*1024)
FROM dba_segments
WHERE segment_name=o.object_name) “Total Size(MB)”
FROM DBA_OBJECTS o,
( SELECT TS#,OBJ#,
SUM(SPACE_USED_DELTA) growth
FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0
ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND o.OWNER=’SIEBEL’
ORDER BY 6 DESC
/

Script-4:

set feedback on
select * from (select c.TABLESPACE_NAME,c.segment_name “Object Name”,b.object_type,
sum(space_used_delta) / 1024 / 1024 “Growth (MB)”
from dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) – &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner =’SIEBEL’
group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
order by 3 asc;

Thank you 

UTL_MAIL Or send email from Oracle Database

Posted on by Osama Mustafa in Database
<!– @page { margin: 0.79in } P { margin-bottom: 0.08in }
–>

Steps to enable Mailing from Database:
1. sqlplus ‘/ as sysdba’
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb
  SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
 
4. Set smtp_server information in init.ora or spfile.ora like the following you have to change with right configuration for yourself :

alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;

Note : 25 = Default SMTP Port
If instance had been started with spfile

eg: alter system set smtp_out_server = ’172.25.90.165:25′ scope=both;

Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘omustafa@savvytek.com’, recipients => ‘omustafa@savvytek.com’, subject => ‘database alert’, message => ‘database is corrputed’);
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to omustafa;
Enjoy 
osama mustafa

Table Locks

Posted on by Osama Mustafa in Scripts
query to get the locked tables in oracle :

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id  ;

Or you can do the below :

 select * From v$locked_object;
select * From v$session where SID = ”;
select * from dba_objects where object_id = ”;

 Locked objects :

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

KILL SESSION COMMAND

ALTER SYSTEM KILL SESSION ‘SID,Serial#’ IMMEDIATE; 

Osama Mustafa

drop all tables in a tablespace

Posted on by Osama Mustafa in Scripts
set echo off
prompt
prompt this script is used to generate SQL file for deleting all tables in a tablespace.
prompt
prompt specify tablespace name 1:
define tsname=&1
prompt Add purge clause, y for yes, n for no 2:
define prg=&2
prompt enter output sql file name 3:
define filename=&3
set heading off
set verify off
set feedback off
start del_ts_tb.sql
set verify on
set heading on
set echo on
set feedback on
spool &filename
select 'drop table ' || owner || '.' || table_name || decode(upper('&prg'),'Y',' purge','') || ';' DropState
from dba_tables
where tablespace_name = upper('&tsname');
spool off 




Osama mustafa