UTIL_FILE example

-öncelik ile yükleme yapacağımız tabloyu yaratalım.

create table kisiler(adi varchar2(30),
soyadi varchar2(30),
yasi number);

--Küçük bir not ekleyelim hemen araya. burada directory adını verirken buyuk harf ile vermeyi

--unutmayalım. dosyadan okuduğu için buyuk hart vermeniz gerekecektir.
--paketimizi yaratalım.

CREATE OR REPLACE PROCEDURE dosyadan_yukleme
AS
v_line VARCHAR2 (2000); -- dosyadan okunacak her bir satır
v_file UTL_FILE.file_type; -- Verilerin olduğu dosya
v_dir VARCHAR2 (250); -- dosyanın bulunduğu dizin
v_filename VARCHAR2 (50); -- dosyamızın adı
v_1st_virgul NUMBER; --ilk virgulun olduğu yer
v_2nd_virgul NUMBER; --ikinci virgülün olduğu yer
v_adi VARCHAR2(30); --alanlarımız için değişkenler ADI
v_soyadi VARCHAR2 (30); --alanlarımız için değişkenler SOYADI
v_yasi NUMBER; --alanlarımız için değişkenler YASI
BEGIN
v_dir := 'dosya_yolum';
v_filename := 'kisiler.txt';
v_file := UTL_FILE.fopen (v_dir, 'kisiler.txt', 'r',1000);

-- --------------------------------------------------------
--DOSYA SONUNA GELENE KADAR OKU
-- --------------------------------------------------------
LOOP
BEGIN
UTL_FILE.get_line (v_file, v_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;

v_1st_virgul := INSTR (v_line, ',', 1, 1);
v_2nd_virgul := INSTR (v_line, ',', 1, 2);
v_adi := SUBSTR (v_line, 1, v_1st_virgul - 1);
v_soyadi :=
SUBSTR (v_line, v_1st_virgul + 1, v_2nd_virgul - v_1st_virgul - 1);
v_yasi := SUBSTR (v_line, v_2nd_virgul + 1);
DBMS_OUTPUT.put_line (v_adi|| ' - ' || v_soyadi || ' - ' || v_yasi);

-- ------------------------------------------
-- Kayıtları oluşturduğumuz tabloya eklemeyi yapıyoruz.
-- ------------------------------------------
INSERT INTO kisiler
VALUES (upper(v_adi), UPPER (v_soyadi), v_yasi);
END LOOP;

UTL_FILE.fclose (v_file);
COMMIT;
END;
/

--dosyamda aşağıdaki gibi kayıtlar var.
--ad,a,12
--al,at25
--nur,ak,24
--ak,alçın,30



BEGIN
DOSYADAN_YUKLEME;
COMMIT;
END;
-----------------

ORACLE parameter

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

Oracle Mimarisi
System Admin Advanced Level
Data Guard for Oracle
Disaster for Oracle
Performans Tuning for Oracle
Backup for Oracle
Oracle on SAP
------------------------

first_rows
show parameter optimizer
buffer busy waits - buffer cache küçükken
db file scattered read - full table ve index taramada artıyor.
db file sequential read - index üzerinden erişildiğinde
timed_statistic=true olmalı wait eventleri görmek için
session ile system event sistem restart da sıfırlanır.
v$session view - row_wait_obj. hangi obje beklemeye neden oluyor.
v$active_session_history
temproray table space kullanmış pga kullanmasını sağlamalıyız.
lock-latch buffer daki lock

db file sequential read - düşürme yolları; increasing the number of freelist on the indexes
show parameter job_que
keep buffer pool büyüttü samples table nı keep
show parameter db_keep
db_cache_size; keep pool(buffer cache %10) recycle pool default pool
show parameter db_keep
alter system set db_keep_cahce_size=10M scope=spfile;
desc user_tables;
select id, name, current_size from v$buffer_ pool;
alter table hr.department storage v$buffer_ pool_statistic
show parameter query_rewrite_enabled
shoq parameter replication
select index_name from user_indexes;



select name, value from v$parameter

where name='sga_target'

statistics_level'

SELECT * FROM v$pq_sesstat;

select
statistic,
value
from
v$pq_sysstat
where
statistic = 'Servers Busy';

show parameter optimizer_features_enable
------------------------
--parametre dosyalarının konumunu öğrenmek için aşağıdaki sorgu işinize yarayabilir.
--ben burada dizin ile başlayanlara göre bir kırılım yaptım.
SELECT name,value,display_value
FROM sys.v_$parameter where value like '/%'
-----------------------------
--BLOCK CHANGE TRACKING (BCT)database'deki değişkliklerin yapıldığı block'ları tutan dosyadır. rman buraya bakıyor.
SELECT filename, status, BYTES
FROM v$block_change_tracking;
-------


ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
--dosyayı ben belirliyorum.
USING FILE 'c:\oracle\product\flash_recovery_area\ORABASE\bctf01.log';
disable yapmak için ise
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
disable yaptığınızda dosyanız silinecektir.
dosyanın adını ve konumunu değiştirmek için MOUNT moda geçtikten sonra aşağıdaki işlemi yapmak gerekiyor.Aksi halde aşağıdaki hataları alırsınız.

ORA-01511: günlük/veri dosyalarını adlandırmada hata
ORA-19771: veritabanı açıkken değişiklik izleme dosyası yeniden adlandırılamaz.

ALTER DATABASE RENAME FILE
'c:\oracle\product\flash_recovery_area\ORABASE\bctf01.log'
TO 'c:\oracle\product\flash_recovery_area\ORABASE\new_name.log';

--------------
select name,open_mode,log_mode,platform_name,CURRENT_SCN from v$database
-----------------
select * from V$DATABASE_BLOCK_CORRUPTION

bozuk bir block'ta olan tabloya erişmeye çalışdığımızda aşağıdaki hatayı alırız.
ORA-01578: ORACLE data block corrupted (file # 6, block # 256)
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/orcl/bozuk_dosya.dbf’
aklımıza ilk gelen durum burada RMAN ile bağlanarak aşağıdaki sorguyu çalıştırmaktır.
BLOCKRECOVER DATAFILE 6 BLOCK 256
----------------

v$ tables perf, create user , pfile

todays date SELECT SYSDATE FROM DUAL
Oracle provides CPU statistics in V$SQL
check V$SESS_TIME_MODEL for database CPU usage
buffer gets at the database level; check V$SESSTAT and V$SQL
wait events listed in V$SESSION_WAIT
V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity

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

SELECT
/*+
first_ro
*/
s.module
FROM
v$sessio
WHERE
s.sql_ad
'HOST' )
ORDER BY
t.piece#


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

alter user hr acount unlock; /unlock etme
alter user hr identified by hr; /şifre koyma

sqlplus hr/hr@fer

select table_name from tabs;
desc departments;
select * from departments;
create table test (id int, name varchar2(20));
inser into test values(1, 'test1');
exp system/oracle@orcl file=c:\exp_hr.dmp owner=hr
possible varsa türkçe karakterler gidiyor.
hlm\software\oracle\key_OraDb10g_home1\ NLS_LANG sunucudaki ile aynı olacak.
nvarchar2 varchar2 den daha fazla dil destekler


select user from dual; /dual table

shutdwon immediate / db kapatıldı
startup /db aç

sqlplus-
ORA-12514 - listener.ora
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)


lsnrctl status
lsnrctl stop
lsnrctl start

emctl status dbconsole
emca -config dbcontrol db -repos create
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos recreate



sqlplus sys/sys@oracle as sysdba
tnsping fer

select a+nvl(b,0) from table

set pagesize 10000
select ...

select count(*) from table
where rownum<=1 -kayıt var mı?


select sysdate from dual -bir tane donuyor

--restricted
SELECT LOGINS FROM V$INSTANCE
SELECT b.grantee, a.grantee || '(Role)' AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = 'RESTRICTED SESSION'
AND a.grantee = b.granted_role
UNION
SELECT b.username, 'User (Direct)'
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = 'RESTRICTED SESSION'
AND a.grantee = b.username;

GRANT RESTRICTED SESSION TO DB_USER;
REVOKE RESTRICTED SESSION FROM DB_USER;
ALTER SYSTEM ENABLE RESTRICTED SESSION;

SELECT 'ALTER SYSTEM KILL SESSION''' || sid || ', ' || serial#|| ''' IMMEDIATE; '
FROM V$SESSION WHERE TYPE!= 'BACKGROUND' AND USERNAME NOT IN
( SELECT b.grantee FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = 'RESTRICTED SESSION' AND a.grantee = b.granted_role
UNION
SELECT b.username FROM dba_sys_privs a, dba_users b
WHERE a.privilege = 'RESTRICTED SESSION' AND a.grantee = b.username );
ALTER SYSTEM DISABLE RESTRICTED SESSION;

SHUTDOWN IMMEDIATE
SHUTDOWN ABORT hard crash gibi

ALTER SYSTEM CHECKPOINT; - ALTER SYSTEM SWITCH LOGFILE;
SHUTDOWN ABORT;
STARTUP RESTRICT;
SHUTDOWN IMMEDAITE;

STARTUP NOMOUNT
STARTUP MOUNT
STARTUP
STARTUP RESTRICT
STARTUP MIGRATE
STARTUP FORCE

COLUMN PLATFORM_NAME FORMAT A32
SELECT * FROM V$TRANSPORTABLE_PLATFORM;
SQL> COLUMN tablespace_name FORMAT A20
SQL> COLUMN name FORMAT A50
SQL> select tablespace_name, name from v$datafile_header;

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(' USERS, DATA_TS', TRUE); -self contained
SELECT * FROM TRANSPORT_SET_VIOLATIONS; --self contained
no rows selected olmalı

show parameter job_queue_process;
desc v$parameter;
create pfile from spfile;
1. SPFILE dosyasından, metin formatında bir PFILE oluşturuyoruz:
SQL> create pfile='/tmp/deneme.text' from spfile;
2. PFILE dosyasını açıp, log_archive_dest parametresinde ilgili değişiklikleri yapıyoruz.
SQL> ! vi /tmp/deneme.text
...
3. Çalışmakta olan bir veritabanının spfile'ini ezemeyeceğimiz için veritabanını kapatıyoruz.
SQL> shutdown immediate;
4. Değişiklik yaptığımız pfile'i kullanarak yeni bir spfile oluşturuyoruz.
SQL> create spfile from pfile='/tmp/deneme.text';
5. Veritabanını açıyoruz.:
SQL> alter database open;

create spfile='/data2/spfile_yedek.ora'
from pfile='/tmp/deneme.text';
1. spfile.ora
2. spfile.ora
3. init.ora
4. init.ora
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';

STARTUP PFILE='/tmp/deneme.text';

SPFILE=/klasor/adresi/spfile_adi.ora
STARTUP PFILE='/tmp/calistir.text';

select ad||' '||soyad from table
where department.id is null -- boş mu







CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"

CREATE SMALLFILE TABLESPACE "HR_TS" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HR_TS' SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

ALTER USER "HR" DEFAULT TABLESPACE "HR_TS"
GRANT CREATE TABLE TO "HR"



ERR--CREATE TABLE "SYS"."USERS" ( "USER_ID" NUMBER(8), "USER_ROLE" VARCHAR2(10), "EMAIL" VARCHAR2(50), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "STREET_ADDRESS" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE_PROVINCE" VARCHAR2(25), "POSTAL_CODE" VARCHAR2(12), "COUNTRY_ID" CHAR(2), CONSTRAINT "USR_PK" PRIMARY KEY ("USER_ID") VALIDATE , CONSTRAINT "USR_UK" UNIQUE ("EMAIL") VALIDATE )

REVOKE CREATE TABLE FROM "HR"
GRANT CREATE TABLE TO "HR" WITH ADMIN OPTION

GRANT ALTER TABLESPACE TO "HR" WITH ADMIN OPTION

REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
GRANT UNLIMITED TABLESPACE TO "HR"

GRANT "RESOURCE" TO "HR"
ALTER USER "HR" DEFAULT ROLE ALL

CREATE TABLE "HR"."USERS" ( "USER_ID" NUMBER(8), "USER_ROLE" VARCHAR2(10), "EMAIL" VARCHAR2(50), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "STREET_ADDRESS" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE_PROVINCE" VARCHAR2(25), "POSTAL_CODE" VARCHAR2(12), "COUNTRY_ID" CHAR(2), CONSTRAINT "USR_PK" PRIMARY KEY ("USER_ID") VALIDATE , CONSTRAINT "USR_UK" UNIQUE ("EMAIL") VALIDATE )

CREATE TABLE "HR"."PRODUCTS" ( "PROD_ID" NUMBER(8), "NAME" VARCHAR2(50), "IMAGE" VARCHAR2(4000), "DESCRIPTION" VARCHAR2(4000), CONSTRAINT "PRD_PK" PRIMARY KEY ("PROD_ID") VALIDATE ) TABLESPACE "HR_TS"

CREATE TABLE "HR"."EXPERTISE_AREAS" ( "PROD_ID" NUMBER(8), "USER_ID" NUMBER(8), "EXPERTISE_LEVEL" VARCHAR2(30), "NOTES" VARCHAR2(4000), CONSTRAINT "EXA_PK" PRIMARY KEY ("PROD_ID", "USER_ID") VALIDATE , CONSTRAINT "EXA_PRD_FK" FOREIGN KEY ("PROD_ID") REFERENCES "HR"."PRODUCTS" ("PROD_ID") VALIDATE , CONSTRAINT "EXA_USR_FK" FOREIGN KEY ("USER_ID") REFERENCES "HR"."USERS" ("USER_ID") VALIDATE ) TABLESPACE "HR_TS"

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

JOB ile analiz

http://www.elliott-technologies.com/oracle.php

BEGIN
SYS.DBMS_JOB.REMOVE(28);
COMMIT;
END;
/

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin
dbms_stats.gather_schema_stats(ownname=> ''SAPSR3'' , cascade=> TRUE);
end;'
,next_date => to_date('07.11.2009 16:21:07','dd/mm/yyyy hh24:mi:ss')
,interval => 'null'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
------------------------------------
select * from user_jobs;

ps -auxww | grep snp._$ORACLE_SID

select * from v$version


SELECT RAWTOHEX(paddr) paddr_hex, name FROM v$bgprocess
WHERE RAWTOHEX(paddr) <> HEXTORAW(0)
AND name LIKE 'SNP%';

select * from dba_jobs_running;

select object_name,object_type from all_OBJECTS where object_name like '%DBA_JOBS_RUNNING%'

select object_name,object_type from dba_OBJECTS where object_name like '%DBA_JOBS_RUNNING%';

select text from all_views where view_name='DBA_JOBS_RUNNING';



select v.SID, v.id2 JOB, j.FAILURES,
LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
j.field1 INSTANCE
from sys.job$ j, v$lock v
where v.type = 'JQ' and j.job (+)= v.id2



---------------------
CREATE OR REPLACE PROCEDURE SAPSR3.test IS

BEGIN

dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'AAACC_OBJ_ERR', partname=> NULL);


END test;
/




----------
BEGIN
SYS.DBMS_JOB.REMOVE(21);
COMMIT;
END;
/

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

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'test;'
,next_date => to_date('30.10.2009 10:01:16','dd/mm/yyyy hh24:mi:ss')
,interval => 'NULL'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

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

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'BEGIN dbms_stats.gather_table_stats(ownname=> ''SAPSR3'', tabname=> ''AAACD2'', partname=> NULL); END;'
,next_date => to_date('30.10.2009 10:50:16','dd/mm/yyyy hh24:mi:ss')
,interval => 'NULL'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/



http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm

Buffer Cache Advisory - SQL

COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

Buffer Pool Hit Ratios;


SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache',
'physical reads cache');


Find the number of buffers in the instance: ;
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)
FROM V$BUFFER_POOL
GROUP BY NAME, BLOCK_SIZE
HAVING SUM(BUFFERS) > 0;


/* Formatted on 07.11.2009 14:42:34 (QP5 v5.115.810.9015) */
SELECT NAMESPACE,
PINS,
PINHITS,
RELOADS,
INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;select sum(pinhits) / sum(pins) "Library Cache Hit Ratio"
FROM V$LIBRARYCACHE

/* Formatted on 07.11.2009 14:42:49 (QP5 v5.115.810.9015) */
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool',

SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;


SELECT SUM (VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';


Sizing the Log Buffer;
MAX(0.5M, (128K * number of cpus))

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

* For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
* For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

SELECT * FROM V$PGASTAT;

SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
FROM V$PROCESS;


SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;


optimal memory requirement of at least 64 KB;
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM v$sql_workarea_histogram
WHERE low_optimal_size > 64*1024);

Querying V$SQL_WORKAREA_ACTIVE;
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;


The following query finds the top 10 work areas requiring most cache memory:

SELECT *
FROM
( SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size )
WHERE ROWNUM <= 10;


Using the hash value and address of a particular cursor, the following query displays the cursor execution plan, including information about the associated work areas.

col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name,
trunc(bytes/1024/1024) "input(MB)",
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem,


Querying V$PGA_TARGET_ADVICE
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w
WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)
AND p.address='88BB460C'
AND p.hash_value=3738161960;

RESOLVING CORRUPT BLOCK -2

DB_FILE_MULTIBLOCK_READ_COUNT SAYISI DÜŞÜRÜLEREK ÇOKLU BLOCK OKUMASI ENGELLENEBILIR. VE BOYLECE PROBLEM ONLENIR.

ORA-7445: EXCEPTION ENCOUNTERED: CORE DUMP [] [] [] [] PATCH 4257187 GEÇILMELI .
Doc ID: 452951.1

ORA-01013: user requested cancel of current operation Doc ID: 365043.1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set


select /*+ ordered use_nl(lob pn ses) */
decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.kglnaobj object_name,
pn.kglpnmod lock_mode_held,
pn.kglpnreq lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
from v$session_wait vsw,
x$kglob lob,
x$kglpn pn,
v$session ses
where vsw.event = 'library cache lock'
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr

order by pn.kglpnmod desc, pn.kglpnreq desc
/


http://www.freelists.org/post/oracle-l/library-cache-lock,15


select * from v$sqlarea where parsing_schema_name='PSAPSR3'

desc v$sqlarea

select segment_name,tablespace_name from user_segments

where segment_name='S' ;

SELECT product_id
, store_id
, clearance_price
, effective_date
, out_of_stock_date
, reset_date
, flag
FROM mdo_pre_temp_retek_price;

select * FROM MDO.MDO_BSE_TEMP_RETEK_PRICE

Select * from gv$session_wait where event='library cache lock';


select ses.sid, ses.serial#,lck.* from x$kgllk lck , v$session ses
where kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0)
and lck.KGLLKUSE = ses.saddr
Order by lck.KGLNAOBJ

RESOLVING CORRUPT BLOCK

KASIM 2 08:16:10 2009 TARIHINDE FILE 47, BLOCK 425345 UNDAN READ EDILEMIYOR.
BLOKLAR CROSS CHECK EDILMESI;
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER
FROM DBA_EXTENTS
WHERE FILE_ID =
AND BETWEEN BLOCK_ID
AND BLOCK_ID + BLOCKS -1;
SELECT * FROM DBA_FREE_SPACE WHERE FILE_ID=
AND BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS -1;
USER KULLANICISIYLA GIRIP DUMP LAR ICIN TABLOYU YARATIYORUZ;

CREATE TABLE S (
N NUMBER,
C VARCHAR2(4000)
) NOLOGGING TABLESPACE ;

SELECT SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS
WHERE SEGMENT_NAME='S' ;

CREATE OR REPLACE TRIGGER CORRUPT_TRIGGER
AFTER INSERT ON USER.S
REFERENCING OLD AS P_OLD NEW AS NEW_P
FOR EACH ROW
DECLARE
CORRUPT EXCEPTION;
BEGIN
IF (DBMS_ROWID.ROWID_BLOCK_NUMBER(:NEW_P.ROWID)=&BLOCKNUMBER) THEN
RAISE CORRUPT;
END IF;
EXCEPTION
WHEN CORRUPT THEN
RAISE_APPLICATION_ERROR(-20000, 'CORRUPT BLOCK HAS BEEN FORMATTED');
END;
/

ALTER TABLE SCOTT.S
ALLOCATE EXTENT (DATAFILE 'E:\XXXX\TEST.ORA' SIZE 1M);
BLOCKLARI LISTELTIYORUZ;
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER
FROM DBA_EXTENTS
WHERE FILE_ID =
AND BETWEEN BLOCK_ID
AND BLOCK_ID + BLOCKS -1 ;

OLUŞTURDUĞUMUZ TABLOYA BLOCKLARI INSERT EDIYORUZ.FARKLI YONTEMLER VAR BIRI;
BEGIN
FOR I IN 1..1000000000 LOOP
FOR J IN 1..1000 LOOP
INSERT INTO SCOTT.S VALUES(I,'X');
END LOOP;
COMMIT;
END LOOP;
END;
TRIGER ILE INSERT EDERKEN HATALI BLOKU HATA VERECEK.
DAHA SONRA HANGI DATAFILE LAR DA PORBLEM VARSA VERIFY YAPILIR.
DATAFILE DA HATA YOKSA RAPOR DA GOZUKMEYECEKTIR.
metalik :Doc ID: 336133.1
https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28from=BOOKMARK&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocTitle=How%20to%20Format%20%3Cb%3ECorrupted%3C/b%3E%20%3Cb%3EBlock%3C/b%3E%20Not%20Part%20of%20Any%20Segment&viewingMode=1143&bmDocID=336133.1%29%29