Dynamically SGA increase ORACLE 10G Database

Step 1:
Login in
>export ORACLE_SID=test

>sqlplus /nolog

> conn / as sysdba or conn sys/password as sysdba


>ALTER SYSTEM SET sga_max_size = 1711276032 COMMENT='internally adjusted' SCOPE=SPFILE;

>ALTER SYSTEM SET sga_target = 1711276032 SCOPE=BOTH;



Step 2:

>shutdown immediate

>startup

>show sga


Finally you can see your expected SGA size

Identified Every day Database Growth Size

Step 1: Create table

CREATE TABLE DB_GROWTH

(

DAY DATE,

DATABASE_SIZE_MB NUMBER,

DAILY_GROWTH_MB NUMBER

);

Step 2:

Create Store procedure , which generated automatic database growth size

CREATE OR REPLACE PROCEDURE database_growth

AS

today_size NUMBER;

yesterday_size NUMBER;

growth_size NUMBER;

cnt NUMBER;

BEGIN

SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED where tablespace_name <> 'UNDOTBS2';

SELECT COUNT(1) INTO cnt FROM db_growth ;

IF cnt > 0

THEN

SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(day,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');

ELSE

yesterday_size:=today_size;

END IF;

growth_size := today_size - yesterday_size;

INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);

EXCEPTION

WHEN no_data_found THEN

INSERT INTO db_growth VALUES(sysdate,today_size,0);

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Step 3:

Create DBMS database job, which one execute every day

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X

,what => 'database_growth ;'

,next_date => to_date('26/04/2010 21:32:06','dd/mm/yyyy hh24:mi:ss')

,interval => 'SYSDATE+30/24'

,no_parse => TRUE

);

SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

END;

/

commit;

Note: if you needed are change job execution date and time manually

Step 4: Now see your CREATE TABLE DB_GROWTH, this (DAILY_GROWTH_MB) column are provide daily growth in database.

Resolve the problem "ORA-00031: session marked for kill"

If get "ORA-00031: session marked for kill" from oracle database then Follow the

Simulation Steps 1:

SQL> ALTER SYSTEM KILL SESSION '205,32449' IMMEDIATE;
ALTER SYSTEM KILL SESSION '205,32449' IMMEDIATE
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> SELECT s.sid,
s.serial#,
s.osuser,
s.program
FROM v$session s;

Select from query your desire sid and serial# no from query

Steps 2:

Find out the your desire thread id (needed to be os level kill session)

SQL>select
spid,
osuser,
s.program
from v$process p,
v$session s
where p.addr=s.paddr



For Windows Operating System

C:> orakill ORACLE_SID spid

note: ORACLE_SID means oracle unique database name

like>

C:> orakill test 1236

Finally your database session and OS child level session has been killed.

For UNIX Operating System

Check the SPID from UNIX Process

Test# % ps -ef | grep ora

Then Run following:

Test# % kill -9 spid