This article is available in english.
# apt-get update
# apt-get -f -y install unzip gcc make binutils lesstif2 libc6 libc6-dev rpm libmotif3 libaio libstdc++5 gawk alien libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5
# addgroup oinstall
# addgroup dba
# addgroup nobody
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# usermod -g nobody nobody
# mkdir /home/oracle
# chown oracle.dba /home/oracle
# passwd oracle
# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename
# mkdir /etc/rc.d
# ln -s /etc/rc0.d /etc/rc.d/rc0.d
# ln -s /etc/rc2.d /etc/rc.d/rc2.d
# ln -s /etc/rc3.d /etc/rc.d/rc3.d
# ln -s /etc/rc4.d /etc/rc.d/rc4.d
# ln -s /etc/rc5.d /etc/rc.d/rc5.d
# ln -s /etc/rc6.d /etc/rc.d/rc6.d
# ln -s /etc/init.d /etc/rc.d/init.d
# echo "Red Hat Linux release 4" > /etc/redhat-release
# echo -e "fs.file-max = 65536\nkernel.shmall = 2097152\nkernel.shmmax = 2147483648\nkernel.shmmni = 4096\nkernel.sem = 250 32000 100 128\nnet.ipv4.ip_local_port_range = 1024 65000\nnet.core.rmem_default = 4194308\nnet.core.rmem_max = 4194308\nnet.core.wmem_default = 262144\nnet.core.wmem_max = 262144\n" >> /etc/sysctl.conf
# sysctl -p
# echo -e "oracle soft nproc 2047\noracle hard nproc 16383\noracle soft nofile 1023\noracle hard nofile 65535\n" >> /etc/security/limits.conf
Egy alkalmas könyvtárba (ahol van elég hely - nálam /oracle) letöltjük az alábbi zipet: linux11gR1database.zip (1 844 533 232 byte)
# unzip linux_11gR1_database.zip
# chown -R oracle:oinstall database
Grafikus felületen:
# xhost +
# su - oracle
$ export DISPLAY=:1
$ cd /oracle/databases
$ ./runInstaller
# apt-get update
# apt-get -f -y install unzip gcc make binutils lesstif2 libc6 libc6-dev rpm libmotif3 libaio libstdc++5 gawk alien libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5
# addgroup oinstall
# addgroup dba
# addgroup nobody
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# usermod -g nobody nobody
# mkdir /home/oracle
# chown oracle.dba /home/oracle
# passwd oracle
# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename
# mkdir /etc/rc.d
# ln -s /etc/rc0.d /etc/rc.d/rc0.d
# ln -s /etc/rc2.d /etc/rc.d/rc2.d
# ln -s /etc/rc3.d /etc/rc.d/rc3.d
# ln -s /etc/rc4.d /etc/rc.d/rc4.d
# ln -s /etc/rc5.d /etc/rc.d/rc5.d
# ln -s /etc/rc6.d /etc/rc.d/rc6.d
# ln -s /etc/init.d /etc/rc.d/init.d
# echo "Red Hat Linux release 4" > /etc/redhat-release
# echo -e "fs.file-max = 65536\nkernel.shmall = 2097152\nkernel.shmmax = 2147483648\nkernel.shmmni = 4096\nkernel.sem = 250 32000 100 128\nnet.ipv4.ip_local_port_range = 1024 65000\nnet.core.rmem_default = 4194308\nnet.core.rmem_max = 4194308\nnet.core.wmem_default = 262144\nnet.core.wmem_max = 262144\n" >> /etc/sysctl.conf
# sysctl -p
# echo -e "oracle soft nproc 2047\noracle hard nproc 16383\noracle soft nofile 1023\noracle hard nofile 65535\n" >> /etc/security/limits.conf
Download the installer in a directory which has enough space (my place is /oracle): linux11gR1database.zip (1 844 533 232 byte)
# unzip linux_11gR1_database.zip
# chown -R oracle:oinstall database
On the X11:
# xhost +
# su - oracle
$ export DISPLAY=:1
$ cd /oracle/databases
$ ./runInstaller
select count(*) "Futásszám",
lpad(a.concurrent_program_id,8) "ProgID",
lpad(a.program_application_id,6) "ApplID",
rpad(b.user_concurrent_program_name,75) "Program név",
rpad(c.concurrent_program_name,25) "Rövid név"
from applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
apps.fnd_concurrent_programs_vl c
where a.program_application_id=b.application_id
and a.program_application_id=c.application_id
and a.concurrent_program_id = b.concurrent_program_id
and a.concurrent_program_id = c.concurrent_program_id
and b.language='HU'
group by a.concurrent_program_id,
a.program_application_id,
b.user_concurrent_program_name,
c.concurrent_program_name
order by count(*) desc;
select ap.patch_name
,substr(pr.patch_top, instr(pr.patch_top, ap.patch_name), 20) "patch"
,to_char(pr.start_date, 'HH24:MI:SS') "start"
,to_char(pr.end_date, 'HH24:MI:SS') "end"
,to_char(trunc(pr.end_date) + (pr.end_date - pr.start_date), 'HH24:MI:SS') "elapsed"
from applsys.ad_applied_patches ap
inner join applsys.ad_patch_drivers pd on ap.applied_patch_id=pd.applied_patch_id
inner join applsys.ad_patch_runs pr on pd.patch_driver_id=pr.patch_driver_id
order by 2
select rpad(u.description,16) "Indító",
to_char(fr.request_id,'99999999') "Feld. ID",
rpad(fp.user_concurrent_program_name,50) "Program név",
sysdate "Lekérdezés ideje",
fr.actual_start_date "Feld. indulás",
to_char(trunc(sysdate) + (sysdate - fr.actual_start_date), 'HH24:MI:SS') "Fut. idő"
from apps.fnd_concurrent_requests fr, apps.fnd_concurrent_programs_tl fp, apps.fnd_user u
where ((sysdate - fr.actual_start_date) * 24) > 1
and phase_code = 'R'
and status_code = 'R'
and fr.program_application_id = fp.application_id
and fr.concurrent_program_id = fp.concurrent_program_id
and u.user_id = fr.requested_by
and language = 'HU';
select application_short_name, patch_level
from fnd_product_installations i, fnd_application a
where a.application_id = i.application_id
select u.user_id, u.user_name, u.description, r.responsibility_name, g.start_date, g.end_date
from apps.fnd_responsibility_vl r
,apps.fnd_user_resp_groups g
,apps.fnd_user u
where u.user_id = g.user_id
and r.responsibility_id = g.responsibility_id
and u.user_id >= 1110
order by u.user_id;
select f.tablespace_name,
to_char(sum(f.bytes)/1024/1024, '999G999G999D99')||'M' "FREE",
to_char(t.bytes/1024/1024, '999G999G999D99')||'M' "TOTAL",
to_char(sum(f.bytes)/t.bytes*100, '990.99')||'%' "FREE %"
from dba_free_space f, dba_data_files t
where f.tablespace_name=t.tablespace_name
group by f.tablespace_name, t.bytes
order by 3 desc
select segment_name
,to_char(sum(bytes)/1024/1024, '999G999G999G999') as MB
from dba_segments
where owner='SYS'
group by segment_name
order by 2 desc;
select to_char((1-(phy.value - lob.value - dir.value)/ses.value)*100, '900') "buffer cache hit ratio"
from v$sysstat ses,
v$sysstat lob,
v$sysstat dir,
v$sysstat phy
where ses.name ='session logical reads'
and dir.name ='physical reads direct'
and lob.name ='physical reads direct (lob)'
and phy.name ='physical reads'
select to_char(((sum(gets-getmisses))/sum(gets))*100, '900') "dictionary cache hit ratio" from v$rowcache
select to_char(sum(pins-reloads)/sum(pins)*100, '900') "library cache hit ratio" from v$librarycache
explain plan for
select * from dual;
select * from table(dbms_xplan.display());
set lines 156
set pages 50
set colsep |
set long 2000000
set timing on
set time on
set serveroutput on
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
col db_name format a14
col host format a14
col terminal format a14
col sessionid format a14
col db_domain format a14
col ip_address format a14
select
sys_context('userenv', 'db_name') db_name,
sys_context('userenv', 'host') host,
sysdate,
sys_context('userenv', 'terminal') terminal,
sys_context('userenv', 'sessionid') sessionid,
sys_context('userenv', 'db_domain') db_domain,
sys_context('userenv', 'ip_address') ip_address
from dual;
Ezek után az sqlplus-t a következőképpen érdemes indítani:
$ sqlplus "/ as sysdba" @sqlplusrc
select substr(s.sql_text, 1, instr(upper(s.sql_text), upper('from'))+4) as sel, o.name, u.name
-- select 'select '''||o.name||''', count(*) from "'||u.name||'"."'||o.name||'" union all'
from v$sql s, x$kqlfxpl p, obj$ o, user$ u
where p.kqlfxpl_oopt = 'FULL'
and p.kqlfxpl_oper != 'FIXED TABLE'
and p.kqlfxpl_phad = s.address
and o.obj# = p.kqlfxpl_objn
and u.user# = o.owner#
and (o.name not like '%$%'
and o.name != 'DUAL'
and o.name != 'PLAN_TABLE'
and o.name not like '%SQLPLUS%'
and o.name not like '%LOGMNR%'
)
group by o.name, u.name, substr(s.sql_text, 1, instr(upper(s.sql_text),upper('from'))+4);
select p.pid,
p.spid,
p.username,
p.program,
s.sid,
s.user#,
s.status,
s.schemaname,
s.osuser,
s.machine,
s.terminal,
s.module,
s.action,
s.logon_time,
s.event,
q.sql_text,
q.sql_id,
q.parsing_schema_name,
q.object_status,
q.program_id,
q.last_active_time
from v$process p
right join v$session s on p.addr = s.paddr
right join v$sql q on q.address = s.sql_address
where p.spid = <OSPID>