SAP -sm37 tablo bilgileri

SELECT
*
FROM
TBTCP --as of timestamp to_timestamp('29/05/2010 07:00', 'DD/MM/Yyyy hh24:mi')
where jobname like 'Z100%GERC%'

SELECT
*
FROM
V_OP --as of timestamp to_timestamp('29/05/2010 07:00', 'DD/MM/Yyyy hh24:mi')
where jobname like 'Z100%GERC%'

FROM "TBTCO" T0001, "TBTCP" T0002
WHERE T0001."JOBNAME" = T0002."JOBNAME"
AND T0001."JOBCOUNT" = T0002."JOBCOUNT";


create table ZTBTCO
as select * from TBTCO

Oracle - redo log group eklem

SELECT * FROM V$LOG;
select GROUP#,MEMBER,TYPE from v$logfile order by 1 ;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle/sid/origlogA/log_g15m1.dbf','/oracle/sid/origlogA/log_g15m2.dbf') size 100M;

alter database drop logfile group 5;
--------------------veya;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle/sid/origlogA/log_g15m1.dbf') size 100M;

alter database add logfile member '/oracle/sid/origlogA/log_g15m2.dbf' to group 5 ;

alter database drop logfile member '/oracle/sid/origlogA/log_g15m2.dbf'
-----------------------
ALTER DATABASE CLEAR LOGFILE GROUP 5;

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/server.111/b28310/onlineredo003.htm

Oracle - iostat, vmstat , filestat ve çift kayıt silme

http://adminschoice.com/iostat-vmstat-netstat
http://vsbabu.org/oracle/sect02.html
http://www.dba-oracle.com/art_disk_iostat.htm
https://netfiles.uiuc.edu/jstrode/www/oraview/V$FILESTAT.html
http://download-uk.oracle.com/docs/cd/F49540_01/DOC/server.815/a67775/ch20_io.htm#2688
------------------------------
create table SAPSR3.zTPALOG as select * from SAPSR3.TPALOG

SAPSR3.CSMCLSMAP

WHERE ROWID IN (
SELECT t.ROWID AS targetrowid
FROM TEST t,
(SELECT tst_id, tst_name, MAX (ROWID) AS maxrowid
FROM SAPSR3.TPALOG
GROUP BY tst_id, tst_name
HAVING COUNT (*) > 1) m
WHERE t.ROWID != m.maxrowid AND t.tst_id = m.tst_id)

SELECT MAX (ROWID) AS maxrowid
FROM SAPSR3.TPALOG

SELECT TRTIME, TRKORR, TARSYSTEM,TRCLI,TRSTEP,ALLCLI,PROJECT,TRUSER,RETCODE,HOST,ADMIN,TPSTAT_KEY,LINESeQUENCE
FROM SAPSR3.TPALOG
GROUP BY TRTIME, TRKORR, TARSYSTEM,TRCLI,TRSTEP,ALLCLI,PROJECT,TRUSER,RETCODE,HOST,ADMIN,TPSTAT_KEY,LINESeQUENCE
HAVING count('x') > 1;


create table SAPSR3.TPALOG as

create table SAPSR3.TPALOG2 as select distinct * from SAPSR3.TPALOG
create table SAPSR3.TPALOG tablespace PSAPSR3 as select distinct * from SAPSR3.TPALOG2

drop table SAPSR3.TPALOG

rename SAPSR3.TPALOG2 to SAPSR3.TPALOG;

select * from sapsr3.CSMCLSMAP

Oracle -audit komutları

select * from SYS.AUD$
select * from DBA_AUDIT_TRAIL

DELETE FROM SYS.AUD$;

SELECT sql_text FROM dba_common_audit_trail;
-------------------------------
AUDIT ALL BY sapsr3

AUDIT SESSION BY sapsr3 by access;

AUDIT SELECT TABLE, UPDATE TABLE BY SAPSR3 BY ACCESS;

AUDIT select ANY TABLE;

AUDIT SELECT, INSERT, DELETE
ON sapsr3.vbak
BY ACCESS
WHENEVER SUCCESSFUL;
----------------------------
select * from sys.fga_log$


AUDIT SESSION
BY SAPSR3

NOAUDIT session;

NOAUDIT ALL;


SELECT * FROM DBA_PRIV_AUDIT_OPTS; -- aktif auditleri gosterir.

http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm

Oracle - session, process sayisi

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671284058977

ps -auxww | grep

set autotrace on statistics;
select username from v$session where username is not
null;

(1.1*processes)+5. That means process=415 will give me sessions=461

300 - 335

ps -eaf
-------------------------------------------
the number of dirty buffers that are waiting to be written;

select dirty, count(*) from v$bh group by dirty;
update big_table.big_table set id=id where rownum < 1000;
select dirty, count(*) from v$bh group by dirty;
alter system checkpoint;
select dirty, count(*) from v$bh group by dirty;
-------------------------------------------------
alter session set sql_trace=true;

select p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.audsid = userenv('sessionid');


---------------------------------
select username,count(*)
from v$session
group by username;
select *
from v$resource_limit
where resource_name in ('processes','sessions');

oracle ornek komutlar- unix için kurulum

select * from dba_registry_history
---------------------------------
select * from dba_audit_trail a
where
a.username='SYS' and
a.logoff_time> sysdate - 100
----------------------------
groupadd -g 102 dba
useradd -d /export/home/oruser -m -s /bin/bash -g dba -u 101 orauser
passwd orauser
usermod -u 101 orauser

ls -alF /export/home/orauser
chown -R orafmi /export/home/orauser
ORACLE_HOME ve ORACLE_SID set
mkdir oracle
ls -alF /oracle
chown -R orauser /oracle
vi /etc/system
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256

http://download.oracle.com/docs/cd/B19306_01/install.102/b15697/pre_install.htm#BABIBGFA

id -p
prctl -n project.max-shm-memory -i project 101

-prctl -n project.max-shm-memory -r -v 24gb -i project 100

projadd -p 101 -c 'ORA_DBA' -U orauser -G dba -K 'project.max-shm-memory=(privileged,6G,deny)' ORA

etc/user_attr
orasti::::project:ORA

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:454420165038
-------------------------------------
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnSolaris10.php
usermod -K project=OABS oracle
user_attr

Oracle conrole file yaratma

iki şekilde control file yaratılır.
trace ile yaratma üzerine duracağız;
alter database backup controlfile to trace; dememiz yeterli.
oluşturduğumuz control file scriptini oluşturabilememiz için datafileların mevcut olması gerekir. özellikle sistem datafile nin header kısmına bakacaktır...