loading objects into shared pool

Pin A Cursor In Memory:

VARIABLE x REFCURSOR
BEGIN OPEN :x for
SELECT * FROM all_tables;
END;
/
--Determine address and hash value of the SQL statement

SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';

-- substitute your query results for mine, below.

exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');

Pin A Trigger In Memory:
dbms_shared_pool.keep(‘TESTTRG’,’R’)

Pin A Package, Procedure Or Function In Memory (this is the default):
dbms_shared_pool.keep(‘PKG_TEST’,’P’)

Pin A Sequence In Memory:
dbms_shared_pool.keep(’SEQ_TEST’, ‘Q’);



DBMS_SHARED_POOL.UNKEEP (name VARCHAR2, flag CHAR DEFAULT ‘P’);
exec dbms_shared_pool.unkeep('UWCLASS.TESTPROC', 'P');

sga components used,free

select round(sum(bytes)/1024/1024,2)' MB' total_sga,
round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))' MB' used,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)' MB' free
from v$sgastat


free memory in shared pool:

select * from v$sgastatwhere name='free memory'and pool='shared pool'

Library cache hit ratio:

select sum(pinhits)/sum(pins) from v$librarycache

Dictionary cache hit ratio:

select (sum(gets-getmisses-fixed))/sum(gets)"row cache" from v$rowcache

log sequence number

Log sequence number:

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

If the database is opened with reset logs, the log sequence number is reset to 1.

statspack

SQL> @C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\spcreate

or

SQL> CONNECT / AS SYSDBA
SQL> define default_tablespace='ESASBRANCH'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='SYS'
SQL> @C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\spcreate
**********************


SQL> CONNECT perfstat/SYS
SQL> EXECUTE statspack.snap;

or

variable snap number;
begin :snap := statspack.snap;
end;
/

SQL> print snap
SNAP
----------
12


select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;


SAat basi statspack almasi icin:
@C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\spauto

desc user_jobs;

SELECT job
FROM user_jobs;

exec dbms_job.remove(1);

yarim saatde bir almasi icin:
EXECUTE DBMS_JOB.INTERVAL(job_number,'SYSDATE+(1/48)');


To remove a specified job:
EXECUTE DBMS_JOB.REMOVE(job_number);
---------------

report:

connect perfstat/sys

@C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\spreport

OR
connect perfstat/my_perfstat_password
define begin_snap=1
define end_snap=2
define report_name=batch_run
@C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\spreport


Truncating All Statspack Data
@C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\sptrunc

or

purge(SPPURGE.SQL deletes all snapshots ranging between the lower and upper bound snapshot IDs )
@C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\sppurge

or
drop

@C:\esas\oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\spdrop

************************************

Generate Execution Plan for given SQL statement

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/sprepsql.sql
Enter the Hash Value, in this example: 1279400914

sql_trace

exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
exec dbms_system.set_sql_trace_in_session(38,3,true);
exec dbms_system.set_sql_trace_in_session(38,3,false);




tkprof C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_224_tuncay_sql_trace.trc C:\sql_trace.txt explain=esastest/esastest sys=yes waits= yes

tkprof C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_224_tunca_sql_trace.trc C:\toutput.txt insert=C:\tkprof.sql record=C:\allsql.sql


alter session set sql_trace =true

alter session set tracefile_identifier = 'tuncay_sql_trace';





----------------
select b.username, c.value || '\' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE",
a.pid PID, b.serial# session_number
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and c.name = 'user_dump_dest'
and d.name = 'db_name'
and b.username is not null;


/* Formatted on 2008/06/05 14:49 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE start_trace (
v_sid IN NUMBER,
v_serial# IN NUMBER,
seconds IN NUMBER
)
---------------------------------------------

-- 2003 - Oracle Utilities

-- D. Moore

--

-- This procedure serves as a wrapper to

-- session tracing. It accepts

-- a sid and serial#, along with the amount of

-- time in seconds that the trace should last.

-- The trace will be stopped when that time

-- period expires. After tracing is turned

-- off, the name of the trace file will be

-- displayed.

---------------------------------------------
IS
v_user VARCHAR2 (32);
stop_trace_cmd VARCHAR2 (200);
DURATION NUMBER;
v_spid NUMBER;
dump_dest VARCHAR2 (200);
db_name VARCHAR2 (32);
v_version VARCHAR2 (32);
v_compatible VARCHAR2 (32);
file_name VARCHAR2 (32);
no_session_found EXCEPTION;
BEGIN
BEGIN
SELECT a.username, b.spid
INTO v_user, v_spid
FROM v$session a, v$process b
WHERE a.SID = v_sid AND a.serial# = v_serial# AND a.paddr = b.addr;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE no_session_found;
END;

DBMS_SYSTEM.set_sql_trace_in_session (v_sid, v_serial#, TRUE);
DBMS_OUTPUT.put_line ('Tracing Started for User: ' || v_user);
DBMS_OUTPUT.put_line ( 'Tracing Start Time: '
|| TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')
);

---------------------------------------------------

-- Sleep for the amount of seconds specified as

-- seconds input parameter. When complete, stop

-- the tracing and display the resulting trace file

-- name

---------------------------------------------------
IF seconds IS NULL
THEN
DURATION := 60;
ELSE
DURATION := seconds;
END IF;

DBMS_LOCK.sleep (DURATION);
-- the time alotted has now expired. Disable

-- tracing and output the trace file information
DBMS_SYSTEM.set_sql_trace_in_session (v_sid, v_serial#, FALSE);
DBMS_OUTPUT.put_line ( 'Tracing Stop Time: '
|| TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')
);

-- get all of the data needed to format the trace file name
SELECT VALUE
INTO dump_dest
FROM v$parameter
WHERE NAME = 'user_dump_dest';

SELECT VALUE
INTO db_name
FROM v$parameter
WHERE NAME = 'db_name';

-- we need the version of the database in order to determine

-- the naming scheme for the trace file
DBMS_UTILITY.db_version (v_version, v_compatible);

IF SUBSTR (v_version, 1, 1) = '9'
THEN
file_name := db_name || '_ora_' || v_spid || '.trc';
ELSIF SUBSTR (v_version, 1, 3) = '8.1'
THEN
file_name := 'ora_' || v_spid || '_' || db_name || '.trc';
ELSIF SUBSTR (v_version, 1, 3) = '8.0'
THEN
file_name := 'ora_' || v_spid || '.trc';
END IF;

DBMS_OUTPUT.put_line ('Trace Directory: ' || dump_dest);
DBMS_OUTPUT.put_line ('Trace Filename: ' || file_name);
EXCEPTION
WHEN no_session_found
THEN
DBMS_OUTPUT.put_line
('No session found for sid and serial#

specified'
);
END start_trace;


---------------------
exec start_trace(17, 6157, 30);

schedules

BEGIN
DBMS_SCHEDULER.create_schedule ('S1',repeat_interval => 'FREQ=WEEKLY;BYHOUR=15;BYDAY=SUN',start_date => SYSTIMESTAMP);
DBMS_SCHEDULER.create_schedule ('S2',repeat_interval => 'FREQ=HOURLY;INTERVAL=2;BYDAY=MON,TUE,WED,THU,FRI,SAT;BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19,20,21,22',start_date => SYSTIMESTAMP );
DBMS_SCHEDULER.create_schedule ('S3', repeat_interval =>'S1,S2',start_date => SYSTIMESTAMP);
END;
/


begin
dbms_scheduler.create_job
(job_name => 'TK',
job_type => 'PLSQL_BLOCK',
job_action=> 'begin select sysdate from dual; end;',
schedule_name=>'S3',
enabled=>true,
auto_drop=>false,
comments=>'Refresh the gift_advice table from ADVANCE.WORLD');
end;


BEGIN
dbms_scheduler.drop_schedule(
schedule_name => 'S1');
END;
/




CREATE OR REPLACE procedure SYS.print_schedule_dates
( schedule in varchar2,
start_date in timestamp with time zone default dbms_scheduler.stime(),
number_of_dates in pls_integer default 10 )
is date_after timestamp with time zone := start_date - interval '1' second;
next_date timestamp with time zone;
begin for i in 1 .. number_of_dates
loop
dbms_scheduler.evaluate_calendar_string
(schedule, start_date, date_after, next_date);
dbms_output.put_line(to_char(next_date,'DY DD-MON-YYYY (DDD-IW) HH24:MI:SS TZH:TZM TZR'));
date_after := next_date;
end loop;
end;
/


exec print_schedule_dates('S3');

job scripts

BEGIN sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp,
comments => 'schedule for the backup job',
schedule_name => 'my_backup_job_schedule');
END;
/


BEGIN sys.dbms_scheduler.create_job(
job_name => 'daily_backup_job',
job_type => 'EXECUTABLE',
job_action => 'C:\esas\oracle\hotbackup.bat',
schedule_name => 'my_backup_job_schedule',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Job which backup the db',
auto_drop => FALSE,
enabled => TRUE);
END;
/

EXIT




BEGIN
DBMS_SCHEDULER.DROP_JOB (
job_name =>'DAILY_BACKUP_JOB');
END;
/


BEGIN
dbms_scheduler.drop_schedule(
schedule_name => 'my_backup_job_schedule');
END;
/


select * from dba_scheduler_jobs;

select next_run_date from dba_scheduler_jobs where job_name='DAILY_BACKUP_JOB';


select ADDITIONAL_INFO,ERROR# from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='DAILY_BACKUP_JOB';