COLUMN SID NEW_VALUE abc
COLUMN SERIAL# NEW_VALUE efg
select SID ,SERIAL# from v$session where SID=1703;
declare
begin
dbms_output.put_line('SID = '||:abc||' SERIAL#= ' || :efg);
end;
/ 
variable v_version varchar2(17);
declare
BEGIN
select version
into :v_version
from v$instance;
:v_version := substr(:v_version,1,9);
if :v_version < '8.1.6.0.0'
and :v_version > '4.0' then
dbms_output.put_line(chr(10));
dbms_output.put_line('RDBMS Version = '||:v_version);
dbms_output.put_line('ERROR - Versiyon çok düşük');
dbms_output.put_line(chr(10));
end if;
exception
when others then
dbms_output.put_line(chr(10));
DBMS_OUTPUT.PUT_LINE('ERROR - RDBMS Versiyon hatası '|| sqlerrm);
END;
/ declare
BEGIN
select MAX(version)
into :v_version
from v$instance;
:v_version := substr(:v_version,1,9);
if :v_version < '8.1.6.0.0'
and :v_version > '4.0' then
dbms_output.put_line(chr(10));
dbms_output.put_line('RDBMS Version = '||:v_version);
dbms_output.put_line('ERROR - Versiyon çok düşük');
dbms_output.put_line(chr(10));
end if;
exception
when others then
dbms_output.put_line(chr(10));
DBMS_OUTPUT.PUT_LINE('ERROR - RDBMS Versiyon hatası '|| sqlerrm);
END;
/
declare
begin
dbms_output.put_line('RDBMS Version = '||:v_version);
end;
/ select sys_context('alan_adı','parametre',uzunluğu) from dual;select sys_context('USERENV','SID') from dual;
SET ECHO ON
-- ECHO = ON sorguyu gösterir ve spool dosyalarına yazılır.
-- ECHO = OFF ile sorgu gösterilir ama spool dosyalarına yazılmaz..
SET TRIMOUT ON
-- TRIMOUT = ON çıktıdan boşlukları çıkartır.
SET TRIMSPOOL ON
-- TRIMSPOOL = ON spool dosyasındaki boşlukları çıkartır.
SET HEADING OFF
-- HEADING = OFF kolon adlarını çıktıdan çıkartır.
SET FEEDBACK OFF
-- FEEDBACK = ON ile çıktı sayısı hesaplanır.
SET PAUSE OFF
-- PAUSE = ON ile komutlar işlendikten sonra "return" tuşuna basılması gerekir.
SET PAGESIZE 0
-- PAGESIZE = 0 ile bütün başlıklar ve sayfa aralıkları bastırılır.
SET LINESIZE 80
-- LINESIZE = sayfa genişiği genelde 80 dir.
SET VERIFY OFF
-- VERIFY = ON verify ile değişkenlerin değeri sorgu öncesi ve sonrasında gösterilir.
--Spool etmek demek sql sorgularının çıktılarının bir dosyaya yazılması demektir.
--abc.log çıktılarımızı yazacağımız dosyadır.
--Bütün sorgularımız bittikten sonra da "spool off" ile dosyamızı kapatırız.
SPOOL abc.log
SELECT * FROM dual;
SPOOL OFF sqlplus -s apps/apps <= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.language='TR'
AND v.last_update_date>sysdate-30
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
PROMPT "Bitti"
exit;
EOF USER_PROFI
----------
ICX:Oturum
MO: Faaliy
POS: Müker
Uygulama Y
XXIS: GRC
İş Yönetim USER_PROFILE_OPTION_NAME
----------------------------------------------------------------------------------------------------
ICX:Oturum Kesildi
MO: Faaliyet Birimi
POS: Mükerrer Vergi Mükellefi No'suna Sahip Tedarikçilere İzin Ver
Uygulama Yazılımları SSO Otomatik Bağlantı Kullanıcısı
XXIS: GRC Custom PLL
İş Yönetim Sistemi SSO - OID Kimlik Ekleme Olayını Etkinleştir column profile_option_value format a10;
column user_profile_option_name format a10; USER_PROFI PROFILE_OP CREATION_ LAST_UPDA Created By
---------- ---------- --------- --------- ----------------------------------------------------------
ICX:Oturum 30 30-OCT-04 11-MAR-14 AUTOINSTALL
MO: Faaliy 81 21-MAR-14 21-MAR-14 KURULUM
POS: Müker Y 26-FEB-14 26-FEB-14 KURULUM
Uygulama Y Y 19-MAR-14 19-MAR-14 SYSADMIN
XXIS: GRC 0 16-NOV-13 10-MAR-14 KURULUM
İş Yönetim Y 29-AUG-05 19-MAR-14 ORACLE12.0.0 CREATE USER berke IDENTIFIED BY password;
GRANT CREATE SESSION TO berke IDENTIFIED BY password; ALTER USER berke IDENTIFIED BY password; DROP PROFILE "DEFAULT" CASCADE;
CREATE PROFILE "DEFAULT" LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL; select username,profile,account_status,lock_date,authentication_type from dba_users; CREATE USER berke
IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA 100M ON users
TEMPORARY TABLESPACE temp
PROFILE default;
GRANT CREATE SESSION TO berke; DROP USER berke CASCADE;
ALTER SYSTEM KILL SESSION '127, 55234'; select sys_context('USERENV','SESSION_USERID') from dual;
select sys_context('USERENV','SID') from dual; SELECT * FROM DBA_PROFILES
ORDER BY PROFILE; ALTER USER berke
IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA 100M ON users
TEMPORARY TABLESPACE temp
PROFILE default; select * from dba_users; alter user kullanıcı_ismi account unlock; SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE 'BERKE';
select * from v$session where sid= (select
sid
from
v$mystat
where
rownum <=1); SELECT sess.SERIAL#,sess.SID,USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory'; 
| Kısaltma | Açıklama | Sorgulama |
| %a | Veritabanı Activation ID | |
| %d | Veritabanının adı | select name from v$database; |
| %D | Bugünün tarihini DD formatında gösterir. | select to_char(sysdate,'DD') from dual; |
| %f | Dosya Numarası | |
| %F | ID,Gün,Ay,Yıl,Sequence No: | IIIIIIIIII-YYYYMMDD-QQ |
| %I | DBID | select dbid from v$database |
| %N | Tablespace adı verilir. | |
| %n | Veritabanı Adı_XXX | Ör: Berkexxx |
Create tablespace deneme datafile ‘/home/oracle/Desktop/deneme%F.dbf’ size 100M; 
select user_name, user_id
from fnd_user
where user_name like '&username'
/
insert into fnd_user_responsibility(
USER_ID,
APPLICATION_ID,
RESPONSIBILITY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
START_DATE,
DESCRIPTION,
WINDOW_WIDTH,
WINDOW_HEIGHT,
WINDOW_XPOS,
WINDOW_YPOS,
NEW_WINDOW_FLAG)
values (
&apps_user_id, -- change this to your APPS user_id and you will have SYSADMIN Responsibility
1,
20420,
sysdate,
0,
sysdate,
1,
1060,
sysdate,
'System Admin',
5.896,
5.427,
-.042,
-.24,
'R'); prompt --> Determining information about this Product Group
select product_group_id, product_group_name, release_name,
product_group_type, argument1
from fnd_product_groups;
prompt --> Multi-Org installed?
select multi_org_flag from fnd_product_groups;
prompt --> Uygulamada birden çok dil yüklü mü?
select multi_lingual_flag from fnd_product_groups;
prompt --> Lisanslanmış Ürünler
select decode(a.APPLICATION_short_name,
'SQLAP','AP','SQLGL','GL','OFA','FA',
a.APPLICATION_short_name) apps,
o.ORACLE_username, fpi.status, fpi.install_group_num,
fpi.product_version, fpi.sizing_factor,
fpi.tablespace, fpi.index_tablespace, fpi.temporary_tablespace
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2
/
prompt --> Kayıtlı Uygulamalar
select application_id, application_short_name,
basepath
from fnd_application
order by application_id
/
prompt --> Kayıtlı Oracle Schema'larını Bulmak için
select oracle_id, oracle_username, install_group_num, read_only_flag
from fnd_oracle_userid
order by 1
/
prompt --> Kurulu olan dilleri bulmak için
select decode(installed_flag,'I','Installed','B','Base','Unknown')
installed_flag,
language_code, nls_language from fnd_languages
where installed_flag in ('I','B')
order by installed_flag
/ SELECT report_headings.report_date,
report_headings.sid_name,
fa.application_id appn_id,
decode(fa.application_short_name,
'SQLAP', 'AP', 'SQLGL', 'GL', fa.application_short_name ) appn_short_name,
substr(fat.application_name,1,50)||
decode(sign(length(fat.application_name) - 50), 1, '...') Application,
fa.basepath Base_path,
fl.meaning install_status,
nvl(fpi.product_version, 'Not Available') product_version,
nvl(fpi.patch_level, 'Not Available') patch_level,
to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI') last_update_date,
nvl(fu.user_name, '* Install *') Updated_by
FROM applsys.fnd_application fa,
applsys.fnd_application_tl fat,
applsys.fnd_user fu,
applsys.fnd_product_installations fpi,
apps.fnd_lookups fl,
( SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI') report_date,
vd.name sid_name
FROM v$database vd ) report_headings
WHERE fa.application_id = fat.application_id
and fat.language(+) = userenv('LANG')
and fa.application_id = fpi.application_id
and fpi.last_updated_by = fu.user_id(+)
and fpi.status = fl.lookup_code
and fl.lookup_type = 'FND_PRODUCT_STATUS'
UNION ALL
SELECT report_headings.report_date,
report_headings.sid_name,
fa.application_id,
fa.application_short_name,
substr(fat.application_name,1,50)||
decode(sign(length(fat.application_name) - 50), 1, '...'),
fa.basepath,
'Not Available',
'Not Available',
'Not Available',
to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI'),
nvl(fu.user_name, '* Install *')
FROM applsys.fnd_application fa,
applsys.fnd_application_tl fat,
applsys.fnd_user fu,
( SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI') report_date,
vd.name sid_name
FROM v$database vd ) report_headings
WHERE fa.application_id = fat.application_id
and fat.language(+) = userenv('LANG')
and fa.last_updated_by = fu.user_id(+)
and fa.application_id not in
( SELECT fpi.application_id
FROM applsys.fnd_product_installations fpi )
ORDER by 5 
SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'IDLE_TIME';select profile from dba_users where username='kullanıcının_ismi'; alter user kullanıcı_ismi profile profil_ismi DROP PROFILE "DEFAULT" CASCADE;
CREATE PROFILE "DEFAULT" LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;  |
| Session Timeout Parametresi |
Parameter
|
Parametre
|
Tavsiye Edilen Değer
|
ICX:Session Timeout
|
Null
|
30 (dakika)
|
ICX: Limit Time
|
4 (saat)
|
4 (saat)
|
ICX: Limit Connect
|
1000
|
2000
|
select * from v$sess_time_model where stat_name='DB time' and SID=2; select * from v$sys_time_model where stat_name='DB time'; select * from v$statname; 
select * from v$mystat a inner join v$statname b on a.statistic#=b.statistic#; select t.sid,username,name,value from
v$statname n,v$session s, v$sesstat t
where s.sid=t.sid
and n.statistic#=t.statistic#
and s.type='USER'
and s.username is not null
and n.name='session pga memory'
and t.value> 30000; 
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1; cd $OA_HTML/cabo/styles
cp -R cache cache_bck cd $OA_HTML/cabo/images/cache
cp -R cache cache_bck cd $OA_HTML/cabo/styles/cache
rm -rf c*
cd $OA_HTML/cabo/images/cache
rm -rf c* 
select * from wf_notifications where mail_status='MAIL' and end_date is not null and status='CLOSED';
update wf_notifications
set mail_status = 'SENT'
where end_date is not null
and status = 'CLOSED'
and MAIL_STATUS = 'MAIL';
commit; select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL'; DECLARE
ln_sayi number;
BEGIN
ln_sayi := 0;
for rec in
(select * from apps.wf_notifications where status = 'OPEN' and Mail_status='MAIL' and begin_date sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations
FROM TABLE(dbms_space.asa_recommendations())
where segment_owner = user DECLARE
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_file_id NUMBER;
v_block_id NUMBER;
v_last_block NUMBER;
v_used NUMBER;
v_owner VARCHAR2(12);
v_segment VARCHAR2(80);
v_type CHAR(1);
CURSOR index_c is
SELECT owner, index_name
FROM sys.dba_indexes
WHERE tablespace_name = upper('&ts_name');
CURSOR table_c is
SELECT owner, table_name
FROM sys.dba_tables
WHERE tablespace_name = upper('&ts_name');
BEGIN
DBMS_OUTPUT.ENABLE(100000);
v_type := '&type';
IF (v_type = 'i' or v_type = 'I') THEN
OPEN index_c;
FETCH index_c into v_owner, v_segment;
WHILE index_c%FOUND LOOP
--
DBMS_SPACE.UNUSED_SPACE(v_owner, v_segment, 'INDEX', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
--
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Index Name = '||v_segment);
DBMS_OUTPUT.PUT_LINE('Total Blocks = '||v_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Bytes = '||v_total_bytes);
DBMS_OUTPUT.PUT_LINE('Unused Blocks = '||v_unused_blocks);
DBMS_OUTPUT.PUT_LINE('Unused Bytes = '||v_unused_bytes);
v_used := v_total_blocks - v_unused_blocks;
DBMS_OUTPUT.PUT_LINE('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
DBMS_OUTPUT.PUT_LINE('Used Bytes = '||v_used);
DBMS_OUTPUT.PUT_LINE('Last used extents file id = '||v_file_id);
DBMS_OUTPUT.PUT_LINE('Last used extents block id = '||v_block_id);
DBMS_OUTPUT.PUT_LINE('Last used block = '||v_last_block);
fetch index_c into v_owner, v_segment;
END LOOP;
CLOSE index_c;
ELSIF (v_type = 't' or v_type = 'T') THEN
OPEN table_c;
FETCH table_c into v_owner, v_segment;
WHILE table_c%FOUND LOOP
--
DBMS_SPACE.UNUSED_SPACE(v_owner, v_segment, 'TABLE', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
--
v_used := v_total_bytes - v_unused_bytes;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Table Name = '||v_segment);
DBMS_OUTPUT.PUT_LINE('Total Blocks = '||v_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Bytes = '||v_total_bytes);
DBMS_OUTPUT.PUT_LINE('Unused Blocks = '||v_unused_blocks);
DBMS_OUTPUT.PUT_LINE('Unused Bytes = '||v_unused_bytes);
v_used := v_total_blocks - v_unused_blocks;
DBMS_OUTPUT.PUT_LINE('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
DBMS_OUTPUT.PUT_LINE('Used Bytes = '||v_used);
DBMS_OUTPUT.PUT_LINE('. KBytes = '||v_used/1024);
DBMS_OUTPUT.PUT_LINE('. MBytes = '||(v_used/1024)/1024);
DBMS_OUTPUT.PUT_LINE('Last used extents file id = '||v_file_id);
DBMS_OUTPUT.PUT_LINE('Last used extents block id = '||v_block_id);
DBMS_OUTPUT.PUT_LINE('Last used block = '||v_last_block);
fetch table_c into v_owner, v_segment;
END LOOP;
CLOSE table_c;
END IF;
END;
/ create or replace procedure segment_advisor_calistirmak
authid current_user
as
obj_id number;
begin
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_name => 'segment_advisor' );
dbms_advisor.create_object (
task_name => 'segment_advisor',
object_type => 'TABLE',
attr1 => berke, --Kullanıcı ismi
attr2 => 'employees', --Tablo ismi
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => 'segment_advisor',
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task('segment_advisor');
end;
/ exec segment_advisor_calistirmak; "alter table tablo_adı enable row movement;" "alter table tablo_adı shrink space compact;" "alter table tablo_adı shrink space;" select blocks from dba_tables where table_name='TABLO_ADI'; analyze table tablo_adı compute statistics;
analyze table tablo_adı estimate statistics sample 10 percent; select blocks from dba_tables where table_name='TABLO_ADI'; select bytes/1024/1024 as 'MB' from dba_segments where segment_name='tablo_adı';