Ask Kg

TBD Istanbul Bilişim Kongresi Oracle Yazılım Geliştirme Araçları

2009-06-02T09:11:00.004+03:00

http://askthekg.googlepages.com/TBDOracleTools.ppt

Wap development

2009-01-19T04:01:00.002+02:00

http://askthekg.googlepages.com/wap.rar

javastoredprocedure

2009-01-19T03:59:00.002+02:00

http://askthekg.googlepages.com/javastoredprocedure.rar

fusion-ebusiness-suite-white-paper

2009-01-19T03:59:00.001+02:00

http://askthekg.googlepages.com/fusion-ebusiness-suite-white-paper.pdf

E-BusinessWebTransactions.pdf

2009-01-19T03:53:00.000+02:00

http://askthekg.googlepages.com/E-BusinessWebTransactions.pdf

Oracle Application Server 10g Release 2 (10.1.2) Installation On RedHat Advanced Server 3.0 (Update 3)

2009-01-18T03:42:00.001+02:00

Oracle Application Server 10g Release 2 (10.1.2) Installation On RedHat Advanced Server 3.0 (Update 3)
In this article I'll describe the installation of Oracle Application Server 10g Release 2 (10.1.2), Oracle's J2EE Application Server, on RedHat Advanced Server 3.0 (update 3). The article assumes you've performed the standard advanced server installation including the development tools.

Download Software
Unpack Files
Hosts File
Set Kernel Parameters
Setup
Installation
Post Installation
Note. I've experienced issues with installations on systems that are updated beyond Update 3, which is the version certified by Oracle.

Download Software
Download the following software:

Oracle Application Server 10g (9.0.4) Software
Unpack Files
Unpack the contents of the file:

cpio -idmv < as_linux_x86_core_101200.cpio
You should now have a directory (Disk1) containing installation files.

Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 256 32000 100 142
fs.file-max = 131072
net.ipv4.ip_local_port_range = 10000 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65535
Run the following command to change the current kernel parameters:

/sbin/sysctl -p
Add the following lines to the /etc/security/limits.conf file:

* soft nproc 2047
* hard nproc 16384
* soft nofile 2048
* hard nofile 65536
Add the following line to the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so
Setup
Install the following packages:

# From RedHat AS3 Disk 2
cd /mnt/cdrom/RedHat/RPMS
rpm -Uvh setarch-1.3-1.i386.rpm
rpm -Uvh sysstat-4.0.7-4.EL3.3.i386.rpm

# From RedHat AS3 Disk 3
cd /mnt/cdrom/RedHat/RPMS
rpm -Uvh openmotif21-2.1.30-8.i386.rpm
rpm -Uvh ORBit-0.5.17-10.4.i386.rpm
rpm -Uvh libpng10-1.0.13-15.i386.rpm
rpm -Uvh gnome-libs-1.4.1.2.90-34.1.i386.rpm
rpm -Uvh compat-glibc-7.x-2.2.4.32.6.i386.rpm \
compat-gcc-7.3-2.96.128.i386.rpm \
compat-gcc-c++-7.3-2.96.128.i386.rpm \
compat-libstdc++-7.3-2.96.128.i386.rpm \
compat-libstdc++-devel-7.3-2.96.128.i386.rpm
Create the new groups and users:

groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba -s /bin/ksh oracle
passwd oracle
Create the directories in which the Oracle software will be installed:

mkdir -p /u01/app/oracle/product/j2ee_10_1_2
chown -R oracle.oinstall /u01
Login as root and issue the following command:

xhost +
Login as the oracle user and add the following lines at the end of the .profile file:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/j2ee_10_1_2; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$ORACLE_HOME/dcm/bin:$ORACLE_HOME/opmn/bin; export PATH
PATH=$PATH:$ORACLE_HOME/Apache/Apache/bin; export PATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 16384
else
ulimit -u 16384 -n 16384
fi
fi

PS1="`hostname`> "
set -o emacs
set filec
Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:

DISPLAY=:0.0; export DISPLAY
Start the Oracle Universal Installer (OUI) by issuing the following command in the Disk1 directory:

./runInstaller
During the installation enter the appropriate ORACLE_HOME and name then continue with the installation.

Post Installation
With the installation complete you can perform any administration tasks using Enterprise Manager:

Connect to the Enterprise Manager Website (http://:1810) using the username "ias_admin" and the password you assigned during the installation. If EM is not available start it with the "emctl start iasconsole" command.
Stop enterprise manager by issuing the "emctl stop iasconsole" command.
For more information see:

Oracle Application Server Installation Guide 10g Release 2 (10.1.2) for hp HP-UX PA-RISC (64-bit), and Linux x86
Hope this helps. Regards Tim...

prc_table_to_csv

2009-01-18T03:22:00.001+02:00

PROCEDURE prc_table_to_csv (p_table_name VARCHAR2,
p_sep_char VARCHAR2 DEFAULT '","',
p_filename VARCHAR2 DEFAULT 'Output.txt',
p_file_location VARCHAR2
)
--
IS

CURSOR cur_table_data
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_table_name;

v_line VARCHAR2(20000);
v_sep VARCHAR2(30);

file_handle UTL_FILE.FILE_TYPE;

TYPE TabCurTyp IS REF CURSOR;
v_TabCurTyp TabCurTyp;

BEGIN

v_sep := '||''' || p_sep_char|| '''||';
dbms_output.put_line(v_sep);

FOR cur_table_data_rec IN cur_table_data
LOOP
v_line := v_line || cur_table_data_rec.column_name || v_sep;
END LOOP;

v_line := SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) );

IF p_sep_char = '","'
THEN
v_line := ''''||'"'||'''||' || v_line || '||''' || '"' || '''';
dbms_output.put_line(v_line);
END IF;

v_line := 'SELECT ' || v_line || ' FROM ' || p_table_name;

/*
v_line := 'SELECT '||
SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) )||
' FROM ' || p_table_name ;
*/

dbms_output.put_line(v_line);

OPEN v_TabCurTyp FOR v_line;

file_handle := UTL_FILE.FOPEN(p_file_location, p_filename, 'W');

LOOP

FETCH v_TabCurTyp INTO v_line;
EXIT WHEN v_TabCurTyp%NOTFOUND;

UTL_FILE.PUT_LINE(file_handle,v_line);
dbms_output.put_line(v_line);

END LOOP;

CLOSE v_TabCurTyp;
UTL_FILE.FCLOSE(file_handle);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
dbms_output.put_line('file location or name was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_MODE
THEN
dbms_output.put_line('the open_mode string was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN
dbms_output.put_line('not a valid file handle');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_OPERATION
THEN
dbms_output.put_line('File could not be opened as requested');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.READ_ERROR
THEN
dbms_output.put_line('OS error occurred during read');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.WRITE_ERROR
THEN
dbms_output.put_line('OS error occured during write operation');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INTERNAL_ERROR
THEN
dbms_output.put_line('Internal Error');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN OTHERS
THEN
UTL_FILE.FCLOSE(file_handle);
RAISE;

END prc_table_to_csv;

row count procedure

2009-01-18T03:19:00.001+02:00

declare

sql_stmt varchar2(254);
row_count number;

cursor get_tab is
select table_name, num_rows
from dba_tables
where owner=upper('&&1');

begin

dbms_output.put_line('Checking Record Counts for schema &&1 ');
dbms_output.put_line('Log file to numrows_&&1.lst ....');
dbms_output.put_line('....');

FOR get_tab_rec IN get_tab LOOP

BEGIN

sql_stmt := 'select count(*) col1 from &&1.'||get_tab_rec.table_name;

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,30)
||' '||TO_CHAR(row_count)||' rows (Analyze '||get_tab_rec.num_rows||' rows).');

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error counting rows for table '
||get_tab_rec.table_name);

END;

END LOOP;

END;

oracle data to xml format

2009-01-18T03:18:00.000+02:00

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

process search

2009-01-18T01:46:00.000+02:00

[root@merkezora root]# ps -fe|grep LOCAL|wc -l

17

[root@merkezora root]# ps -efw|grep ora_

oracle 21891 1 0 08:14 ? 00:00:00 ora_pmon_orcl

oracle 21893 1 0 08:14 ? 00:00:00 ora_dbw0_orcl

oracle 21895 1 0 08:14 ? 00:00:00 ora_lgwr_orcl

oracle 21897 1 0 08:14 ? 00:00:00 ora_ckpt_orcl

oracle 21899 1 0 08:14 ? 00:00:00 ora_smon_orcl

oracle 21901 1 0 08:14 ? 00:00:00 ora_reco_orcl

oracle 21903 1 0 08:14 ? 00:00:00 ora_cjq0_orcl

oracle 21905 1 0 08:14 ? 00:00:00 ora_qmn0_orcl

oracle 21907 1 0 08:14 ? 00:00:00 ora_s000_orcl

oracle 21909 1 0 08:14 ? 00:00:00 ora_d000_orcl

root 22058 21957 0 08:19 pts/0 00:00:00 grep ora_

[root@merkezora root]#

dbms random value from a table

2008-12-01T00:43:00.000+02:00

SELECT * FROM employees

WHERE employee_id IN

(SELECT employee_id FROM

(SELECT employee_id, SYS.DBMS_RANDOM.RANDOM FROM employees ORDER BY 2)

WHERE rownum <= 1 );

Oracle JHeadstart for ADF (10.1.3)

2008-11-11T11:03:00.002+02:00

http://www.oracle.com/technology/products/jdev/tips/muench/jhstutorial/index.html

My DBA commands history

2008-10-10T17:09:00.000+03:00

http://pages.kivancg.com/DBAcommandshistory.txt

TheFutureofJava

2008-10-08T10:22:00.000+03:00

http://pages.kivancg.com/TheFutureofJava.pdf

Director read write file operations

2008-09-24T10:24:00.000+03:00

declare

l_file UTL_FILE.file_type;

fileHandler UTL_FILE.FILE_TYPE;

l_location VARCHAR2(100) := '';

l_text VARCHAR2(32767);

mesaj VARCHAR2(32767);

l_filename VARCHAR2(100) := '';

l_filename_output VARCHAR2(100) := '';

asd VARCHAR2(32767);

begin

mesaj:=' ';

l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);

LOOP

UTL_FILE.get_line(l_file, l_text, 32767);

--DBMS_OUTPUT.PUT_LINE(l_text);

--mesaj:=mesaj||l_text||', '', '||chr(13) || chr(10)||' ';

END LOOP;

UTL_FILE.fclose(l_file);

fileHandler := UTL_FILE.FOPEN(l_location,l_filename_output, 'w');

UTL_FILE.PUTF(fileHandler, mesaj);

UTL_FILE.FCLOSE(fileHandler);

EXCEPTION

WHEN OTHERS THEN

NULL;

end;

Oracle Plsql Injection

2008-09-23T17:10:00.001+03:00

http://pages.kivancg.com/OraclePLSQLInjection.ppt

Türkçe Oracle white paper

2008-09-19T16:03:00.002+03:00

http://pages.kivancg.com/Oracle_VTYS_Giris.pdf

Cursor in Cursor

2008-09-18T17:27:00.000+03:00

DECLARE

CURSOR cur_log IS SELECT PK_VALUES, COLUMNS_HEADER, COLUMNS_OLD_VALUE,COLUMNS_NEW_VALUE FROM XXTEB_AUDIT_LOG WHERE PK_VALUES=202;

BEGIN

FOR cur_log_rec IN cur_log LOOP

DECLARE CURSOR cur_result IS SELECT h.COLUMNVAL as Header, nv.COLUMNVAL as NewValue, ov.COLUMNVAL as OldValue

FROM TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_HEADER,'|') AS XXTEB_TABLE)) h

JOIN TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_NEW_VALUE,'|') AS XXTEB_TABLE)) nv ON nv.ROWNO=h.ROWNO

JOIN TABLE(CAST (XXTEB_STR2TBL (cur_log_rec.COLUMNS_OLD_VALUE,'|') AS XXTEB_TABLE)) ov ON ov.ROWNO=nv.ROWNO

WHERE 1=1;

BEGIN

FOR cur_result_rec IN cur_result LOOP

dbms_output.put_line (cur_result_rec.Header||';'||cur_result_rec.OldValue||';'||cur_result_rec.NewValue);

END LOOP;

END;

END LOOP;

END;

Redo Undo difference

2008-09-15T17:50:00.001+03:00

http://askthekg.googlepages.com/Haisley.pdf

Document Library view document problem

2008-09-12T09:32:00.001+03:00

Problem solved:
It is not mentioned in Document Library Readme.

Here is what I need to do:
edit following function in flow_ schema

wwv_flow_epg_include_mod_local

and
comment out this line
-- return false;

and

Modify following line and change your schema_name and then compile function.

if upper(procedure_name) in (
'GET_DOC','SCHEMA_NAME.GET_DOC

') then
return TRUE;

send email from database utl smtp

2008-09-06T12:45:00.001+03:00

alter system set smtp_out_server = ':25' scope=both;

exec utl_mail.send(sender => 'sender@sender.com', recipients => 'kivancg@gmail.com', subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);

Oracle Business Accelerators for Oracle E-Business Suite (Release 12 Training)

2008-08-30T19:30:00.000+03:00

https://accelerators.oracle.com/COMMON_DOCS/r12training/index.html

JVM System out println Apache logs R11&R12

2008-08-29T15:05:00.002+03:00

/ora/iAS/Apache/Jserv/logs/jvm
tail -f OACoreGroup.0.stdout

/oracle/VIS12/apps/inst/apps/VIS12_linuxblade/logs/ora/10.1.3/opmn/oacore_default_group_1

Oracle Application Server 10g Release 2 (10.1.2) Installation On RedHat Advanced Server 3.0 (Update 3)

2008-08-26T18:24:00.003+03:00

http://askthekg.googlepages.com/appserver10g.txt

Ajax and google examples

2008-08-26T18:22:00.001+03:00

http://askthekg.googlepages.com/dwr.rar

Java applet chat

2008-08-25T16:04:00.000+03:00

http://askthekg.googlepages.com/Chat.rar

Java script Takvim

2008-08-25T16:02:00.000+03:00

http://askthekg.googlepages.com/date_javaScript.rar

2003-07-25 Moore - Oracle Utilities

2008-08-25T15:33:00.001+03:00

http://askthekg.googlepages.com/2003-07-25Moore-OracleUtilities.ppt

Having the right backup and recovery process

2008-08-22T17:53:00.000+03:00

http://askthekg.googlepages.com/Havingtherightbackupandrecoveryproce.doc

manual upgrade from 9i to 10G patch

2008-08-22T17:17:00.001+03:00

http://askthekg.googlepages.com/upgrade_10g.doc

Oracle TimesTen install Guide

2008-08-22T17:15:00.001+03:00

http://askthekg.googlepages.com/TimesTen.doc

oracle 10G solaris install_Guide

2008-08-22T17:07:00.001+03:00

http://askthekg.googlepages.com/oracle10Gsolarisinstall_Guide.pdf

What’s E-Business or ERP?

2008-08-22T16:29:00.001+03:00

http://askthekg.googlepages.com/whatisaebusinesssuite.pdf

RENAME OR MOVE DATAFILE(S) WITH THE DATABASE

2008-08-22T16:26:00.001+03:00

http://askthekg.googlepages.com/alterdatafile.doc

Certification Success Guide java Sun Micro. System

2008-08-22T16:17:00.000+03:00

http://askthekg.googlepages.com/CertificationSuccessGuidejava.pdf

oracle10g se rac backup with screens

2008-08-22T16:12:00.001+03:00

http://askthekg.googlepages.com/oracle10g_se_rac_backup_screens.pdf

Oracle FAQ Oracle Backup and Recovery

2008-08-22T16:07:00.003+03:00

http://askthekg.googlepages.com/WhyandwhenshouldIbackupmydatabase.doc

replace char in all database by another char

2008-08-21T16:59:00.001+03:00

CREATE OR REPLACE PROCEDURE REPLACE_CHAR(p_tablename in varchar2 default null,
--table name
p_search in varchar2 default null,--search valuep_replace in varchar2 default null--replace value) AS
--this plsql procedure will replace the spqcific char with another specific char in specific table of the schema and in all columns ('VARCHAR2','CHAR','VARCHAR')l_columname user_tab_cols.COLUMN_NAME%type;l_data varchar2(32767);l_rowid rowid;v_sql varchar2(1000);temp varchar2(1000);
type t_data is REF CURSOR;c_data t_data;
cursor c_user_tab_columnsisselect column_namefrom user_tab_colswhere table_name = p_tablenameand data_type in ('VARCHAR2','CHAR','VARCHAR');--cursor declaration
BEGINtemp:='';v_sql:='';
OPEN c_user_tab_columns;loop
--DBMS_output.put_line( 'loop');fetch c_user_tab_columns into l_columname;EXIT WHEN c_user_tab_columns%NOTFOUND;
--DBMS_output.put_line( 'columnsname ' l_columname );
open c_data for 'select rowid,'l_columname' from 'p_tablename;--ref cursor dynamic sql within plsql
loop
fetch c_data into l_rowid,l_data;
EXIT WHEN c_data%NOTFOUND;
temp:=temp'replace(''';temp:=templ_data;temp:=temp''',''';temp:=tempp_search;temp:=temp''',''';temp:=tempp_replace;temp:=temp''')';v_sql:=v_sql'update 'p_tablename' set'l_columname'='temp' whererowid=chartorowid('''(l_rowid)''')';
execute immediate v_sql;
commit;
temp:='';v_sql:='';
--EXECUTE IMMEDIATE 'select 'l_columname' from 'p_tablename into l_data;/*dbms_output.put_line(p_tablename);dbms_output.put_line(l_columname);dbms_output.put_line(l_data);dbms_output.put_line(l_rowid);dbms_output.put_line(p_search);dbms_output.put_line(p_replace);*/
end loop;close c_data;
end loop;close c_user_tab_columns;
EXCEPTION WHEN OTHERS THEN DBMS_output.put_line(' error ' SQLERRM);
END;/

Oracle EBS and SOA

2008-08-21T16:55:00.000+03:00

http://askthekg.googlepages.com/Oracle_EBS_and_SOA.pdf

dynamic row count per table

2008-08-21T16:50:00.001+03:00

declare
sql_stmt varchar2(254);row_count number;
cursor get_tab is select table_name, num_rows from dba_tables where owner=upper('&&1');
begin
dbms_output.put_line('Checking Record Counts for schema &&1 ');dbms_output.put_line('Log file to numrows_&&1.lst ....');dbms_output.put_line('....');
FOR get_tab_rec IN get_tab LOOP
BEGIN
sql_stmt := 'select count(*) col1 from &&1.'get_tab_rec.table_name; EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table 'rpad(get_tab_rec.table_name,30) ' 'TO_CHAR(row_count)' rows (Analyze 'get_tab_rec.num_rows' rows).');
EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error counting rows for table ' get_tab_rec.table_name);
END;
END LOOP;
END;

sqlserver connection with ias

2008-08-21T16:47:00.000+03:00

jdbc:weblogic:mssqlserver4:asistan@193.193.193.9:1433
weblogic.jdbc.mssqlserver4.Driver
weblogic.codeset
windows-1254

Installing Oracle Database 10g on Linux x86

2008-08-21T16:41:00.000+03:00

http://askthekg.googlepages.com/InstallingOracleDatabase10gonLinuxx8.doc

dbf file size with sql

2008-08-21T15:15:00.001+03:00

select value, name, null "size"
from v$parameter
where name like 'log_archive_dest%'
union all
select file_name,tablespace_name,bytes/1024/1024 Mb
from dba_data_files
union all
select file_name,tablespace_name,bytes/1024/1024 Mb
from dba_temp_files
union all
select member file_name,'log group 'a.group# tablespace_name
,b.bytes/1024/1024 Mb
from v$logfile a,v$log b
where a.group#=b.group#
union all
select name file_name,'control file' tablespace_name,0 Mb
from v$controlfile
order by 1,2

apex sending email from apex mail engine

2008-08-21T14:12:00.002+03:00

begin
apex_mail.send(p_to => 'first@somedomain.com;second@somedomain.com',p_from => 'apex@someaddress.com',p_body => 'test message');
apex_mail.push_queue;
end;

RealTime Data Warehousing

2008-08-20T16:27:00.001+03:00

http://askthekg.googlepages.com/GGateWhitePaper_RealTimeDW07.pdf

IBM SOA nice presentation

2008-08-15T10:34:00.001+03:00

http://www.blogger.com/www.selab.isti.cnr.it/IW-SOSWE07/presentations/Pr_Ind.ppt

http://ocw.kfupm.edu.sa/user062%5CICS51101%5CPresentations/Service%20Oriented%20Architecture.pdf

dbverify which object corrupted

2008-08-12T12:06:00.000+03:00

http://askthekg.googlepages.com/dbverify.doc

Partitioning

2008-08-07T13:46:00.000+03:00

http://askthekg.googlepages.com/2007_02_28_BIWA_Partitioning1.pdf

Introduction to Simple Oracle Auditing

2008-08-07T11:29:00.001+03:00

http://askthekg.googlepages.com/IntroductiontoSimpleOracleAuditing.doc

Data Dictionary nedir?

2008-08-07T10:39:00.005+03:00

“Sistem katoloğu(system catalogue)” olarakta bilinen “Data Dictionary” ,veritabanında tutulan her türlü objenin metadata’sının (veri hakkında verinin) tutuldugu yerdir.Aynı zamanda DBMS hakkında bilgilerde burada tutulmaktadır.Peki bu metadata(veri hakkında veri) ile kastedilen nedir? Bir tablomuz oldugunu düşünerek özetlemek gerekirse bir tablonun “data dictionary” de tutulan metadata’sı :

adı
ne zaman oluşturulduğu
en son ne zaman ulaşıldıgı
sahibi, izin bilgileri
datanın tutuldugu yer hakkında fiziksel bilgiler
………….. gibi bilgilerdir.

Data Dictionary Yapısı : Dictionary –> User tables –> All tables –> DBA tables (izin gerektirir) –> V$ tables (izin gerektirir)“dictionary” iki seviyeden oluşur.Bir tanesi aslında “SYS” nin altında bulunan bir view olan “dictionary” dir ve iki kolondan olusur :“TABLE_NAME” ve “COMMENTS”
Bu view’ı sorguladığımız zaman aşağıdaki gibi veriler gelecektir :

table_name comments

USER_RESOURCE_LIMITS Display resource limit of the user
USER_PASSWORD_LIMITS Display password limits of the user
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
USER_CLUSTERS Descriptions of user’s own clusters
ALL_CLUSTERS Description of clusters accessible to the user
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
USER_DB_LINKS Database links owned by the user
ALL_DB_LINKS Database links accessible to the user
USER_INDEXES Description of the user’s own indexes
V$SESSTAT Synonym for V_$SESSTAT
V$MYSTAT Synonym for V_$MYSTAT
V$STATNAME Synonym for V_$STATNAME
V$VERSION Synonym for V_$VERSION
………………………… ……………………………
İkinci seviye ise 4 ayrı tablo grubundan olusur :
USER tables : Kullanıcının sahip olduğu objeler hakkında bilgi bulunur.(User’s schema objects)
ALL tables : Ulaşılabilen tüm kullanıcıların objeleri hakkında bilgi bulunur.
DBA tables : DBA lerin ulaşması gereken bilgiler bulunur.
V$ tables : Daha çok DBA lerin işine yarayan ve DBMS hakkında o anki bilgilere ulaşılabilen tablolardır.

Başka bir deyişle “base tables” ve “user-accessible views” olmak üzere 2 farklı yapıdan da bahsedilebilir.Her ikisinin sahibi de SYS’dir.(Tüm table ve view lar “SYSTEM” tablespace’inde tutulmaktadir.)Baska hiçbir kullanıcı hiçbir şekilde data dictionary’nin yapısına müdahele etmemeli ,edememelidir.Çünkü burada veritabanı işlemlerinin temelini oluşturan objelerin her türlü yapısal bilgileri ile DBMS in kendi bilgileri tutulmaktadır.Bunlardaki bir değişiklik geri dönelemez hataların oluşmasına neden olabilir.
Data Dictionary Kullanımı :Oracle ve kullanıcı başlıca 3 amaç için kullanır :1) Oracle; kullanıcılar, şema ve objeler hakkında bilgi almak için kullanır.2) Oracle; gerçekleşen her DDL(Data Definition Language) işlemi sonunda Data Dictionary i düzenler.3) Kullanıcılar sadece bilgi amaçlı olarak kullanırlar.
Bunu bir örnek ile açıklamak gerekirse bir tablo oluşturduğumuzu düşünelim.Bu tablonun hangi kolonlardan oluştuğu, kolonların tipleri, tablonun segment, extents ve kullanıcı hakları gibi bilgileri Oracle tarafından bilinmeliki oluşturma aşamasından sonra gerçekleşecek tabloyu ilgilendiren operasyonlar sağlıklı biçimde gerçekleştirilsin.Bu amaçla ornekte geçen tüm bilgiler ve fazlası Data Dictionary e yazılır ve oluşturma yapıldıktan hemen sonra dictionary sorgulandığında bu bilgiler görülebilir.Bu bilgiler sadece Oracle a değil kullanıcılara da lazım(dır) olabilir.Bu amaçla genel olarak data dictionary bilgileri kullanıcılara açıktır bazıları ise özel kullanıcı hakkı ister.
Bu kadar sık kullanılan bir yapının memory de tutulması mantıklı olcaktır, nitekim Oracle bu bilgileri SGA(System Global Area)’da “dictionary cache” denilen yerde tutar.(Sadece dictionary nin “comment” kolonu bilgileri cache de tutumaz.Ama çok sık kullanım olursa zaten cache e atılır.)Data Dictionary bilgilerine klasik SQL sorgularıyla ulaşılır.Yukarıda da bahsedildiği üzere bazısına tüm kullanıcılar erişebilirken bazısı için özel kullanım hakları tanımlanmalıdır.USER prefix i ile başlayanlar ALL prefix i ile başlayanların alt kümesi denilebilir.Örnek :
select * from user_objects
select * from all_objects
select * from SYS.DBA_OBJECTS;
DBA prefix i ile başlayan data dictionary bilgilerine ulaşmak için bunların başına “SYS” getirilmelidir.Ama genel olarak DBA ile başlayan dicrionary bilgilerine ulaşmayı kısıtlamak gereklidir.Bunun için ANY kullanım hakkı verilmiş kullanıcılarla , SYS şeması ve SYSDBA ile bağlanan herhangi diğer kullanıcıların data dictionary’e erişimi “dictionary protection” kullanıma geçirilerek engelenebilir / engellenebilmelidir.

Dynamic Performans Tables and Views:Oracle’ın işlemler gerçekleştirirken gerekli bilgileri ve o anki veritabanı aktivitelerini tutmak için kullandığı tablolardır.”Fixed Views” da denir, DBA tarafından bile değiştirilemez ya da silinemezler.SYS altında bulunurlar ve isimleri “V_$” ile başlar.Bunlara verilen synonym isimleri ise “V$” ile başlar.Örneğin V$DATAFILE veritabanının datafile’ları hakkında bilgileri içerir ve örneğin V$FIXED_TABLE veritabanının kullandığı “Dynamic Performans” tabloları ve View’ları hakkında bilgi verir.NOT : DBMS_METADATA paketi veritabanında bulunan objelerin tanımları hakkında detay bilgilere ulaşmak için gerekli metodları içerir.
Sık kullanılan bazı data dictionary objeleri ve acıklamaları :

USER_TABLES ; All tables with their name, number of columns, storage information, statistical information etc. (TABS)
USER_CATALOG ; tables, views, and synonyms (CAT)
USER_COL COMMENTS ; comments on columns
USER_CONSTRAINTS ; constraint definitions for tables
USER_INDEXES ; All information about indexes created for tables (IND)
USER_OBJECTS ; All database objects owned by the USER_(OBJ)
USER_TAB COLUMNS ; columns of the tables and views owned by the user(COLS)
USER_TAB COMMENTS ; comments on tables and views
USER_TRIGGERS ; triggers defined by the user
USER_USERS ; information about the current user
USER_VIEWS ; views defined by the user
ALL_CATALOG ; owner, name and type of ALL_accessible tables, views, and synonyms
ALL_TABLES ; owner and name of ALL_accessible tables
ALL_OBJECTS ; owner, type, and name of accessible database objects
DBA_TABLES ; tables of ALL_users in the database
DBA_CATALOG ; tables, views, and synonyms defined in the database
DBA_OBJECTS ; object of ALL_users
DBA_DATA_FILES ; information about data files
DBA_USERS ; information about ALL_users known in the database

Dataguard goldengate

2008-08-06T18:03:00.000+03:00

http://askthekg.googlepages.com/GGZeroDT_Oracle8i9ito10g_2007.pdf

golden gate ile dataguard nasıl yapılır?

REST second generation web service

2008-08-06T11:00:00.000+03:00

http://askthekg.googlepages.com/REST.ppt

http://tssblog.techtarget.com/index.php/interoperability/mini-guide-rest-representational-state-transfer/
http://tomayko.com/writings/rest-to-my-wife

REST, second generation web service olarak anılıyor.

Vgo_FormsConversionSummary_WP.pdf

2008-07-31T12:13:00.002+03:00

http://askthekg.googlepages.com/Vgo_FormsConversionSummary_WP.pdf

costbasedopt.pdf

2008-07-31T12:13:00.001+03:00

http://askthekg.googlepages.com/costbasedopt.pdf

oracle_application_express_now_and_i.pdf

2008-07-31T12:12:00.001+03:00

http://askthekg.googlepages.com/oracle_application_express_now_and_i.pdf

APEX with e-business suite

2008-07-31T12:11:00.003+03:00

http://askthekg.googlepages.com/APEXwithebuss.pdf

apex ooug ppt

2008-07-31T12:10:00.000+03:00

http://askthekg.googlepages.com/APEX_OOUG_2007.ppt

sweetdev useful links

2008-07-31T10:52:00.001+03:00

http://espace.sig.echonet/sweetdev/index.htm (French)
http://espace.sig.echonet/sweetdev/sweetbook/2_2_2/Sweetbook-2_2_2.htm (English)
http://wikinet.group.echonet/display/ITPSInetSweetdev/Home (Wiki ıin english)
http://wikinet.group.echonet/display/ITPSInetSweetdev/SweetDEV+-+Components+-+Table (customizatıon of the table tag)
http://www.ideotechnologies.com/documentation/sweetdev-en/help-doc.htm
http://www.ideotechnologies.com/documentation/javadoc/
http://espace.sig.echonet/downloads/

Oracle Flexfields

2008-07-23T14:38:00.000+03:00

http://oracle.anilpassi.com/descriptive-flexfield-basics-in-oracle-apps.html

CrossTab

2008-07-23T00:33:00.000+03:00

Hi,

I'm glad you asked this question. I have a recipe
for a similar query in my upcoming book and
I was juggling with a few solutions.
Answering it here will give me a chance to get some
real feedback on my proposed solution :)

As always, there's actually a few ways to do this,
here's one approach:

SQL> select * from food;

WHO APPLES PEACHES ORANGES PIZZA
---- ---------- ---------- ---------- ----------
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6

/* the key is transforming the columns into rows, you can
do that with a cartesian product then keep only
the rows you need
*/

SQL> select decode(iter.pos,
2 1,'apples',
3 2,'peaches',
4 3,'oranges',
5 4,'pizza' ) fruit,
6 decode(iter.pos,
7 1,food.apples,
8 2,food.peaches,
9 3,food.oranges,
10 4,food.pizza) cnt,
11 food.who,
12 iter.pos
13 from food,
14 (select rownum pos from all_objects) iter
15 where iter.pos <= 4
16 order by who;

FRUIT CNT WHO POS
------- ---------- ---- ----------
apples 2 jill 1
peaches 1 jill 2
oranges 6 jill 3
pizza 6 jill 4
apples 5 john 1
peaches 1 john 2
pizza 15 john 4
oranges 2 john 3
apples 1 mary 1
oranges 4 mary 3
pizza 12 mary 4
peaches 3 mary 2

/* in the query above, I named the inline view that select from
all_objects, ITER, and rownum, POS, to indicate you are
sort of "iterating" until a certain "position". In this
case, there are 4 food columns, so 4 "iterations" (rows) need
to be generated. POS is also used to provide a "ranking"
for WHO and FRUIT - you'll need this later when grouping
*/

/* the next step is to use DECODE to start organizing the data
into groups - in the book I think I use CASE cuz it's universal
- either work fine here
*/

SQL> select who,
2 fruit,
3 decode(who,'john',cnt) john,
4 decode(who,'mary',cnt) mary,
5 decode(who,'jill',cnt) jill,
6 pos
7 from (
8 select decode(iter.pos,
9 1,'apples',
10 2,'peaches',
11 3,'oranges',
12 4,'pizza' ) fruit,
13 decode(iter.pos,
14 1,food.apples,
15 2,food.peaches,
16 3,food.oranges,
17 4,food.pizza) cnt,
18 food.who,
19 iter.pos
20 from food,
21 (select rownum pos from all_objects) iter
22 where iter.pos <= 4
23 )
24 order by who;

WHO FRUIT JOHN MARY JILL POS
---- ------- ---------- ---------- ---------- ----------
jill apples 2 1
jill peaches 1 2
jill oranges 6 3
jill pizza 6 4
john apples 5 1
john peaches 1 2
john pizza 15 4
john oranges 2 3
mary apples 1 1
mary oranges 4 3
mary pizza 12 4
mary peaches 3 2

/* at this point, the last step is to bump the rows for
JOHN and MARY up, to do that you need to get rid of the
NULLS. This is done by grouping by FRUIT and POS while
taking the max for JOHN, MARY, and JILL
*/

SQL> select fruit,
2 max(decode(who,'john',cnt)) john,
3 max(decode(who,'mary',cnt)) mary,
4 max(decode(who,'jill',cnt)) jill
5 from (
6 select decode(iter.pos,
7 1,'apples',
8 2,'peaches',
9 3,'oranges',
10 4,'pizza' ) fruit,
11 decode(iter.pos,
12 1,food.apples,
13 2,food.peaches,
14 3,food.oranges,
15 4,food.pizza) cnt,
16 food.who,
17 iter.pos
18 from food,
19 (select rownum pos from all_objects) iter
20 where iter.pos <= 4
21 )
22 group by fruit,pos
23 order by decode(fruit,'oranges','yoranges','pizza','zizza',fruit);

FRUIT JOHN MARY JILL
------- ---------- ---------- ----------
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6

/* the funky ORDER BY at the end is to ensure the results are
displayed how you had them in your post - there's other
ways to do that as well
*/

Let me know if this has been helpful. If so, this will be the approach
I use for
the Oracle recipe that deals with creating cross tab results when n
columns
are involved ( I will be covering 5 dbms's, not just Oracle ).

Thanks and regards,
Anthony

-----Original Message-----
From: RM via oracle-db-l [mailto:oracle-db-l@Groups.ITtoolbox.com]
Sent: Sunday, February 13, 2005 1:02 AM
To: Anthony Molinaro
Subject: [oracle-db-l] Cross Tab

# Oracle Database 10g: A Revolution in Database Technology
# Read Paper: http://oracle.ITtoolbox.com/r/hdr.asp?r=41933

# View Group Archive: http://oracle.ITtoolbox.com/hrd.asp?i=838

Hi, I have the following table...

apples peaches oranges pizza
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6

How is it possible to create a cross-tab (table/view) in sql or plsql
(but I prefer regular sql) so that the data now looks like this...

john mary jill
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6

internet seminar

2008-07-23T00:30:00.000+03:00

http://www.oracle.com/pls/ebn/ebn_dpage.ondemand_archive?p_theme=&p_keyword=Turkish

plsql send mail

2008-07-23T00:29:00.001+03:00

declare

CRLF CONSTANT varchar2(10) := utl_tcp.CRLF;

BOUNDARY CONSTANT varchar2(256) := '-----7D81B75CCC90D2974F7A1CBD';

FIRST_BOUNDARY CONSTANT varchar2(256) := '--'||BOUNDARY||CRLF;

LAST_BOUNDARY CONSTANT varchar2(256) := '--'||BOUNDARY||'--'||CRLF;

MULTIPART_MIME_TYPE CONSTANT varchar2(256) := 'multipart/mixed; boundary="'||BOUNDARY||'"';

mail_conn utl_smtp.connection;

sender VARCHAR2(200);

mhost VARCHAR2(30);

inRecipient VARCHAR2(100);

inMessage VARCHAR2(100);

mime_type varchar2(255) := 'text/html';

attachment_file_name varchar2(255) := 'abc.txt';

--attachment_file_name varchar2(255) := 'abc.txt';

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS

BEGIN

utl_smtp.write_data(mail_conn, name || ': ' || header || utl_tcp.CRLF);

END;

procedure send_receive_header(name in varchar2, header in varchar2) is

begin

utl_smtp.write_data(mail_conn, name||': '||header||CRLF);

end;

BEGIN

inMessage :='DENEME1';

inRecipient:='ctahtaci@yfas.com.tr';

mhost:='193.193.193.96';

mail_conn := utl_smtp.open_connection(mhost, 25);

sender :='ct_deneme_attach@yfas.com.tr';

utl_smtp.helo(mail_conn, mhost);

utl_smtp.mail(mail_conn, sender);

utl_smtp.rcpt(mail_conn, inRecipient);

utl_smtp.open_data(mail_conn);

send_header('From', 'xxxxx');

send_header('To', '"Recipient" ');

send_header('Subject', 'Hello1');

send_header('Content-Type',MULTIPART_MIME_TYPE);

--utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!');

-- utl_smtp.data(mail_conn, inMessage);

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

-- Send the main message text

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

-- mime header

utl_smtp.write_data(mail_conn, FIRST_BOUNDARY);

send_receive_header('Content-Type',mime_type);

utl_smtp.write_data(mail_conn, CRLF);

utl_smtp.write_data(mail_conn, 'see attachment');

utl_smtp.write_data(mail_conn, CRLF);

-- add the attachment

utl_smtp.write_data(mail_conn, FIRST_BOUNDARY);

send_receive_header('Content-Type',mime_type);

send_receive_header('Content-Disposition','attachment; filename= '||attachment_file_name);

utl_smtp.write_data(mail_conn, CRLF);

utl_smtp.write_data(mail_conn, 'This is the attachment and this text is written in add attachement section');

utl_smtp.write_data(mail_conn, CRLF);

-- Close the message

utl_smtp.write_data(mail_conn, LAST_BOUNDARY);

utl_smtp.close_data(mail_conn);

utl_smtp.quit(mail_conn);

EXCEPTION

WHEN OTHERS THEN

null;

END;

used temp tbs per each session

2008-07-22T08:04:00.002+03:00

SET termout ON
> > SET heading ON
> > SET PAGESIZE 60
> > SET LINESIZE 126
> >
> > COLUMN tablespace FORMAT a20 HEADING 'Tablespace|Name'
> > COLUMN tablespace_name FORMAT a20 HEADING 'Tablespace|Name'
> > COLUMN username FORMAT a12 HEADING 'Oracle|Username'
> > COLUMN Client_User FORMAT a20 HEADING 'Client|Username'
> > COLUMN osuser FORMAT a12 HEADING 'O/S|Username'
> >
> > TTITLE CENTER 'Who/What is Using Which SORT Tablespace' -
> > skip Center '~~~~~~~~~~~~~~~~~~~~~~~~~~~' -
> > skip 2
> > repfooter off
> > btitle off
> >
> > SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid,
>a.serial#,
> > a.username, a.osuser, a.status
> > FROM v$session a,v$sort_usage b
> > WHERE a.saddr = b.session_addr
> > ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
> > ;
> >
> > select tablespace_name, used_blocks, free_blocks, total_blocks
> > from v$sort_segment;
> >

ref cursor kullanmak

2008-07-22T08:03:00.001+03:00

Hello,

RefCursor example:

declare
type TRefCursor is ref cursor;
vc TRefCursor;
vr dual%rowtype;
begin

open vc for 'select * from dual';
loop
fetch vc into vr;
exit when vc%notfound;
dbms_output.put_line( 'dummy='||vr.dummy );
end loop;
if( vc%isopen )then close vc; end if;
end;
The main difference it could be dinamic.

find invalid object dependencies

2008-07-22T08:02:00.004+03:00

SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
WHERE object_id IN (
SELECT object_id
FROM public_dependency
CONNECT BY PRIOR object_id = referenced_object_id
START WITH referenced_object_id =
(SELECT object_id
FROM SYS.dba_objects
WHERE owner = :owner
AND object_name = :NAME
AND object_type = :TYPE))

oracle code tips

2008-07-22T08:01:00.000+03:00

http://www.oracle.com/technology/oramag/code/tips2005/index.html

ftp script

2008-07-22T08:00:00.001+03:00

#!/bin/sh

HOST='10.206.145.42'

USER='oracle'

PASSWD='AnGntk06'

FILE='/GANTEKT/mini2.dmp'

ftp -n $HOST <

quote USER $USER

quote PASS $PASSWD

bin

put $FILE /ata031/bscs5/tempo/mini3.dmp

quit

END_SCRIPT

exit 0

disk mount etmek

2008-07-22T07:59:00.000+03:00

FW: Conversation with GOKCE BAYRAM disk mount etmek

root@sdpool # mount -F nfs 10.200.165.11:/patches /A

unix df-k yazma hızı

2008-07-22T07:58:00.001+03:00

df -k /ata* | grep -v File | awk 'BEGIN{s=0}{s+=$3}END{print s}' ; date

bekleyen session

2008-07-22T07:57:00.000+03:00

select /*+ ordered */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_requested

from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1

where

(((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and

(((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr

rollback segment

2008-07-22T07:56:00.003+03:00

SELECT owner,segment_name,partition_name, sum(bytes)

FROM dba_extents

WHERE tablespace_name='RBS'

GROUP BY owner,segment_name,partition_name

ORDER BY 4 DESC

alter rollback segment rbs13 shrink

--TABLESPACE SPACE CHECK (SPACE MANAGEMENT)

SELECT d.tablespace_name "Name", d.status "Status",

to_char((a.bytes / 1024 / 1024),'99,999,990.900') "Size (M)",

to_char(((a.bytes - decode(f.bytes, null, 0, f.bytes)) / 1024 / 1024),'99,999,990.900') "Used (M)",

to_char((a.bytes - decode(f.bytes, null, 0, f.bytes))*100/a.bytes,'99,99,990') "USED %"

FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f

WHERE d.tablespace_name = a.tablespace_name

AND f.tablespace_name (+) = d.tablespace_name

ORDER by 5 desc

SELECT segs.SEGMENT_NAME "RB NAME ",sta.RSSIZE, p.spid "SYSTEM PID ",s.sid,s.serial#,

NVL (p.username, 'NO TRANSACTION') "OS USER", s.username , action, p.terminal, tablespace_name

FROM v$rollstat sta , dba_rollback_Segs segs, v$process p , v$session s ,v$lock l

WHERE l.sid = s.sid(+)

AND s.paddr = p.addr

AND TRUNC (l.id1(+)/65536) = sta.usn

AND l.type(+) = 'TX'

AND l.lmode(+) = 6

AND segs.segment_id=usn

ORDER BY sta.rssize desc

shrink

SELECT 'ALTER rollback segment '||segment_name||' shrink;'

FROM dba_segments WHERE tablespace_name!='SYSTEM' AND SEGMENT_TYPE LIKE 'R%';

Tru64 Xterm login

2008-07-22T07:56:00.001+03:00

Arkadaslar,

Tru64 Unix server’lara (toros,alaca, anadolu, trakya....) Xterm (GUI) “root” login olmak istediginizde “Root login refused on this terminal"

Hatasi alacaksiniz muhtemelen . Bunu kaldirmak icin.

Makinaya telnet olup,

# vi /etc/securettys

Satirin sonuna Laptopunuzun ıpsini yazip sonuna :0 yazmaniz lazim.

Ornek : 10.204.101.121:0 gibi...

utl mail

2008-07-22T07:53:00.001+03:00

{ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb

Required Object Privilege :

GRANT execute ON utl_mail TO

Initialization Parameter:

SMTP_OUT_SERVER

Göndermek için de;

--the SMTP_OUT_SERVER parameter must be set
SQL> ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com'
SCOPE=SPFILE;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP

CREATE OR REPLACE PROCEDURE eblast IS

CURSOR mcur IS
SELECT per_h_email
FROM psoug.person
WHERE per_ok2_email = 'Y'
AND per_h_email IS NOT NULL;

vSender VARCHAR2(30) := 'mailsys@psoug.org';
vSubj VARCHAR2(50) := 'April PSOUG News';
vMesg VARCHAR2(4000);
vMType VARCHAR2(30) := 'text/plain; charset=us-ascii';

BEGIN
SELECT msgcol
INTO vMesg
FROM msg;

FOR mrec IN mcur
LOOP
utl_mail.send(vSender, mrec.per_h_email, NULL, NULL, vSubj,
vMesg, vMType, NULL);
END LOOP;
END eblast;
/

unix banner yaratmak

2008-07-22T07:50:00.001+03:00

banner $ORACLE_SID

export KMENU_DAT=/usr/users/bscsadm/newpih/ana.kmn

# bill_menu settings

export WD=/bgh/BGH_WORK

export COBRUSER=postbgh

export COBRPW=sysadm

export PBGHUSER=postbgh

export PBGHPW=sysadm

export PATH=$PATH:/bgh/BGH_WORK/_BGH_BIN

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

export SKGFR_TRACE_ENABLED=MEMCMP

#################################################

export RIH_NO_REASON_CODE_CHECK=1

#BATUHAN Settings

alias "d=ls -l|grep ^d"

alias "ll=ls -la"

alias "psg=ps -ef|grep -v grep |grep"

alias "lt=ls -ltr"

export RIH_NO_REASON_CODE_CHECK=1

alias "FTAM7=cd $DATA/DIH70/FTAM_IN/"

ratingk1@aksu:/usr/users/ratingk1>banner deneme

##### ###### # # ###### # # ######

# # # ## # # ## ## #

# # ##### # # # ##### # ## # #####

# # # # # # # # # #

# # # # ## # # # #

##### ###### # # ###### # # ######

ratingk1@aksu:/usr/users/ratingk1>banner deneme

index unusable

2008-07-22T07:45:00.000+03:00

select 'ALTER INDEX '||p.index_owner||'.'||p.index_name||' REBUILD SUBPARTITION '||p.subpartition_name||' PARALLEL 8;'
from dba_ind_subpartitions p where status <> 'USABLE'

unix kill sql session

2008-07-22T07:43:00.001+03:00

Aşağıadki sql ile bir tane unix procesi kill ettim.

Hiç killed session kalmadı v$session da .

SELECT DISTINCT 'kill -9 ' || p.spid FROM v$session s, v$process p, sys.v_$sess_io si,v$access b

WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid AND s.sid=b.sid AND S.SID=389 AN

tar kullanmak unix

2008-07-22T07:42:00.001+03:00

tar -cvf 10103patch.tar 10103patch

tar -xsvf 10103patch.tar

install uninstall java tru64

2008-07-22T07:37:00.002+03:00

aasdo01:/users/TRU64/kit/JAVA/ makinasında java142-3.tar dosyası var. Ordan alabilirsiniz.

Setld –i komutu ile yüklü olan paketleri görüyorsunuz.

Setld –d – paketleri uninstall etmek için

Tar dosyasını açıp aynı dizinde setld –d . ile javayı kuruyorsunuz...(Ekran görüntüleri aşağıda)

aasdo01:/users/TRU64/kit/JAVA/ 79967296 19208978 60390624 25% /mnt

anadolu:/usr/sbin> ls /mnt

Motif2.1.tar java142-3.tar

anadolu:/usr/sbin>

anadolu:/usr/sbin> setld -i|grep JAVA

JAVA142 installed Java 1.4.2-3 Environment (General Applications)

JAVADEV142 installed Java 1.4.2-3 Development Environment(Software Development)

JAVADOC142 installed Java 1.4.2-3 Online Documentation (Documentation Kit)

OSFEVMJAVADEV540 installed Event Management Development Environment(Software Development)

OSFJAVA540 not installed Java 1.3.1-2 Environment (General Applications)

OSFJAVADEV540 not installed Java 1.3.1-2 Development Environment(Software Development)

OSFJAVADOC540 not installed Java 1.3.1-2 Online Documentation (Supplemental Documentation)

setld: Exiting

anadolu:/usr/sbin> setld -d JAVA142 JAVADEV142 JAVADOC142

Deleting "Java 1.4.2-3 Development Environment" (JAVADEV142).

Deleting "Java 1.4.2-3 Online Documentation" (JAVADOC142).

Deleting "Java 1.4.2-3 Environment" (JAVA142).

anadolu:/usr/sbin> cd /java

anadolu:/java> ls

anadolu:/java> setld -l .

*** Enter subset selections ***

The following subsets are mandatory and will be installed automatically

unless you choose to exit without installing any subsets:

* Java 1.4.2-3 Environment

The subsets listed below are optional:

There may be more optional subsets than can be presented on a single

screen. If this is the case, you can choose subsets screen by screen

or all at once on the last screen. All of the choices you make will

be collected for your confirmation before any subsets are installed.

- Documentation Kit :

1) Java 1.4.2-3 Online Documentation

- Software Development :

2) Java 1.4.2-3 Development Environment

Or you may choose one of the following options:

3) ALL mandatory and all optional subsets

4) MANDATORY subsets only

5) CANCEL selections and redisplay menus

6) EXIT without installing any subsets

Estimated free diskspace(MB) in root:2957.1 usr:1699.2 var:8557.5

Enter your choices or press RETURN to redisplay menus.

Choices (for example, 1 2 4-6): 1 2 3

You are installing the following mandatory subsets:

Java 1.4.2-3 Environment

You are installing the following optional subsets:

- Documentation Kit :

Java 1.4.2-3 Online Documentation

- Software Development :

Java 1.4.2-3 Development Environment

Estimated free diskspace(MB) in root:2957.1 usr:1607.5 var:8557.5

Is this correct? (y/n): y

Checking file system space required to install selected subsets:

File system space checked OK.

3 subsets will be installed.

Loading subset 1 of 3 ...

Java 1.4.2-3 Environment

Copying from . (disk)

Verifying

Loading subset 2 of 3 ...

Java 1.4.2-3 Online Documentation

Copying from . (disk)

Verifying

Loading subset 3 of 3 ...

Java 1.4.2-3 Development Environment

Copying from . (disk)

Verifying

3 of 3 subsets installed successfully.

Java 1.4.2 is now the default for the system.

Configuring "Java 1.4.2-3 Environment" (JAVA142) on member0

Configuring "Java 1.4.2-3 Online Documentation" (JAVADOC142) on member0

Configuring "Java 1.4.2-3 Development Environment" (JAVADEV142) on member0

Configuring "Java 1.4.2-3 Environment" (JAVA142) on member1

Configuring "Java 1.4.2-3 Online Documentation" (JAVADOC142) on member1

Configuring "Java 1.4.2-3 Development Environment" (JAVADEV142) on member1

Configuring "Java 1.4.2-3 Environment" (JAVA142) on member2

Configuring "Java 1.4.2-3 Online Documentation" (JAVADOC142) on member2

Configuring "Java 1.4.2-3 Development Environment" (JAVADEV142) on member2

anadolu:/java>

anadolu:/usr/sbin> java -version

java version "1.4.2"

Java(TM) 2 Runtime Environment, Standard Edition

Fast VM (build 1.4.2-3, native threads, mixed mode, 03/23/2004-19:27)

anadolu:/usr/sbin>

free space calculation sql

2008-07-22T07:20:00.001+03:00

SELECT SUBSTR (df.file_id, 1, 3) "ID#",

df.tablespace_name,

df.BYTES "Total Bytes",

df.blocks "SQL Blocks",

df.BYTES / 512 "VMS Blocks",

nvl(SUM (fs.BYTES),0) "Bytes Free",

nvl((100 * ((SUM (fs.BYTES)) / df.BYTES)),0) "% Free",

nvl(df.BYTES - SUM (fs.BYTES),df.BYTES) "Bytes Used",

nvl((100 * ((df.BYTES - SUM (fs.BYTES)) /

df.BYTES)),100) "% Used"

FROM SYS.dba_data_files df,

SYS.dba_free_space fs

WHERE df.file_id = fs.file_id (+)

GROUP BY df.file_id,

df.tablespace_name,

df.BYTES,

df.blocks

ORDER BY df.tablespace_name;

onceden tablonun ne kadar yer tutacağını hesaplamak

2008-07-22T07:19:00.000+03:00

--
-- calcspac.sql
--
-- This sql proc extimates the initial amount of space required by a
-- non-clustered table in an Oracle database. When using these estimates,
-- keep in mind the following sources of inaccuracy:
-- 1. The space used by updates and deletes does not free up immediately.
-- 2. Trailing null columns and length bytes are not stored.
-- 3. Inserts, updates, and deletes cause fragmentation and chained row
-- pieces.
--
-- This procedure assumes that there is a table with a sample set of data
-- already created. It allows you to specify the number of rows the actual
-- table would grow to.
--
-- Inputs:
-- table name
-- table owner
-- number of rows
--

set serveroutput on
set verify off
set feedback off

prompt Estimate Space Used by a table.
prompt
prompt Method based on instructions in chapter 8 of Oracle7 Server Administrators Guide
prompt
prompt Add 10% to any results to give room for error.
prompt
accept owner prompt "Enter Table Owner (default user) : "
accept table prompt "Enter Table Name : "
accept numrows number prompt "Estimate of total number of rows (0 for existing count) : "

declare

-- Step 1. Calculate Total Block Header Size
--
-- block_header_part_a = fixed_header + variable_trans_header

block_header_part_a number := 0;

-- fixed_header = 57 bytes

fixed_header constant number := 57;

-- variable_trans_header = 23 * I
-- where I is the INITRANS for the table.

variable_trans_header number := 0;
variable_trans_header_overhead constant number := 23;

-- block_header_part_b = table_directory + row_directory

block_header_part_b number := 0;

-- table_directory = 4 bytes

table_directory constant number := 4;

-- row_directory = 2 * number_of_rows_per_block

row_directory number := 0;
row_directory_overhead constant number := 2;

-- total_block_header = block_header_part_a + block_header_part_b

total_block_header number := 0;

-- Step 2: Calculate available data space per data block.
--
-- available_data_space = (block_size - total_block_header) -
-- free_space_overhead
--
-- free_space_overhead = (block_size - block_header_part_a)*(pctfree/100)

available_data_space number := 0;
free_space_overhead number := 0;
pctfree_ number := 0;
block_size number := 0;

-- Step 3: Calculate combined data space.

average_row_data number := 0;

-- Step 4: Calculate total average row size.
--
-- avg_row_size = row_header + F + V + average_row_data
-- F = number of columns which store 250 bytes or less * 1
-- V = number of columns which store more than 250 byes * 3

avg_row_size number := 0;
row_header constant number := 3;
F number := 0;
F_overhead constant number := 1;
V number := 0;
V_overhead constant number := 3;
min_avg_row_size constant number := 9;

-- Step 5: Calculate average rows per block
--
-- number_of_rows_per_block = available_data_space / avg_row_size
--
-- but since the number of rows per block is used in available_data_space,
-- we must solve the above equation for number_of rows per block.
--
-- number_of_rows_per_block =
-- [(block_size - total_block_header) -
-- free_space_overhead] / avg_row_size
--
-- number_of_rows_per_block =
-- [(block_size - (block_header_part_a +
-- block_header_part_b)) -
-- free_space_overhead] / avg_row_size
--
-- number_of_rows_per_block =
-- [(block_size - (block_header_part_a +
-- table_directory + row_directory)) -
-- free_space_overhead] / avg_row_size
--
-- number_of_rows_per_block =
-- [(block_size - (block_header_part_a +
-- table_directory +
-- (row_directory_overhead *
-- number_of_rows_per_block))) -
-- free_space_overhead] / avg_row_size
--
-- avg_row_size * number_of_rows_per_block =
-- (block_size - (block_header_part_a +
-- table_directory +
-- (row_directory_overhead *
-- number_of_rows_per_block))) -
-- free_space_overhead
--
-- avg_row_size * number_of_rows_per_block =
-- block_size -
-- block_header_part_a -
-- table_directory -
-- (row_directory_overhead *
-- number_of_rows_per_block) -
-- free_space_overhead
--
-- (avg_row_size * number_of_rows_per_block) +
-- (row_directory_overhead * number_of_rows_per_block) =
-- block_size -
-- block_header_part_a -
-- table_directory -
-- free_space_overhead
--
-- (avg_row_size + row_directory_overhead) * number_of_rows_per_block =
-- block_size -
-- block_header_part_a -
-- table_directory -
-- free_space_overhead
--
-- number_of_rows_per_block =
--
-- block_size - block_header_part_a - table_directory - free_space_overhead
-- ------------------------------------------------------------------------
-- (avg_row_size + row_directory_overhead)
--
number_of_rows_per_block number := 0;

-- Step 6: Calculate Number of Blocks and Bytes
--
-- number_of_blocks = number_of_rows / number_of_rows_per_block;

number_of_blocks number := 0;
number_of_rows number := 0;

-- number_of_bytes = number_of_blocks * block_size

number_of_bytes number := 0;

--
begin

dbms_output.put_line ('.');
dbms_output.put_line ('Analyzing '|| upper(nvl ('&&owner',user)) || '.' ||
upper('&&table') || '.');
dbms_output.put_line ('.');

-- Analyze the table to get the most current information

dbms_ddl.analyze_object (type => 'TABLE',
schema => upper (nvl ('&&owner',user)),
name => upper ('&&table'),
method => 'ESTIMATE');

-- Get information about the table from the data dictionary.

select ini_trans * variable_trans_header_overhead,
num_rows,
pct_free,
avg_row_len
into variable_trans_header,
number_of_rows,
pctfree_,
average_row_data
from all_tables
where table_name = upper ('&&table')
and owner = upper (nvl ('&&owner',user));

select to_number(value)
into block_size
from v$parameter
where name = 'db_block_size';

if nvl (&&numrows, 0) <> 0 then
number_of_rows := &&numrows;
end if;

-- Step 1. Calculate Total Block Header Size

block_header_part_a := fixed_header + variable_trans_header;
dbms_output.put_line (' Block header part A = ' ||
to_char(block_header_part_a));

-- Step 2: Calculate available data space per data block.
--
free_space_overhead := ceil ((block_size - block_header_part_a)
*(pctfree_/100));
dbms_output.put_line (' Free space overhead = ' ||
to_char(free_space_overhead));

-- Step 3: Calculate combined data space. (obtained from analyze command.)

-- Step 4: Calculate total average row size.
--
-- F = number of columns which store 250 bytes or less * 1

select count(*) * F_overhead
into F
from all_tab_columns
where owner = upper (nvl ('&&owner', user))
and table_name = upper ('&&table')
and ((data_type in ('NUMBER','DATE'))
or (data_type in ('CHAR','VARCHAR2','RAW') and
data_length <= 250));

dbms_output.put_line (' No. columns <= 250 (F) = ' ||
to_char(F));

-- V = number of columns which store more than 250 byes * 3

select count(*) * V_overhead
into V
from all_tab_columns
where owner = upper (nvl ('&&owner',user))
and table_name = upper ('&&table')
and ((data_type in ('LONG','LONG RAW'))
or (data_type in ('CHAR','VARCHAR2','RAW') and
data_length > 250));

dbms_output.put_line (' No. columns > 250 (V) = ' ||
to_char(V));

avg_row_size := row_header + F + V + average_row_data;

dbms_output.put_line (' Average row size = ' ||
to_char(avg_row_size));

-- Step 5: Calculate average rows per block

-- block_size - block_header_part_a - table_directory - free_space_overhead
-- ------------------------------------------------------------------------
-- (avg_row_size + row_directory_overhead)
--

number_of_rows_per_block := floor (
(block_size - block_header_part_a - table_directory - free_space_overhead)/
(avg_row_size + row_directory_overhead));

dbms_output.put_line (' No. rows per block = ' ||
to_char(number_of_rows_per_block));

-- Step 6: Calculate Number of Blocks and Bytes

dbms_output.put_line ('.');
number_of_blocks := ceil (number_of_rows / number_of_rows_per_block);

dbms_output.put_line (' No. of blocks = ' ||
to_char(number_of_blocks));

number_of_bytes := number_of_blocks * block_size;

dbms_output.put_line (' No. of bytes = ' ||
to_char(number_of_bytes));

end;
/
set verify on
set feedback on

indexler gelmemişse

2008-07-22T07:18:00.000+03:00

I have used this one in the past and it's very helpful when working on particular schema changes. Hope it's helpful to Oracle DBA in need.

Note: The script will NOT capture the indexes created by the user 'SYS'. The script will capture indexes for clusters, but the create statement for these indexes will fail because it DOES NOT include the keyword CLUSTER. If the create index of a cluster is executed, it will result in an error 'ORA-00942 table or view does not exist' because it will be looking for a table of that name NOT A CLUSTER.

The other way of getting the indexes DDL is export with INDEXES=Y and then import will INDEXFILE=some file.sql

If you have any comments for improving the script - let me know.

REM
REM SCRIPT FOR CREATING INDEXES
REM
REM This script must be run by a user with the DBA role.
REM
REM Running this script will in turn create a script to build all the
REM indexes in the database. This created script, create_index.sql,
REM can be run by any user with the DBA role or with the 'CREATE ANY INDEX'
REM system privilege.
REM
REM The script will NOT capture the indexes created by the user 'SYS'.
REM
REM The script will capture indexes for clusters, but the create statement
REM for these indexes will fail because it DOES NOT include the
REM keyword CLUSTER. If the create index of a cluster is executed, it
REM will result in an error 'ora-942 table or view does not exist' because
REM it will be looking for a table of that name NOT A CLUSTER.
REM
REM NOTE: Indexes automatically created by table CONSTRAINTS will also
REM be INCLUDED in the create_index.sql script. It may cause a problem to
REM create an index with a system assigned name such as SYS_C00333.
REM

set verify off;
set termout off;
set feedback off;
set echo off;
set pagesize 0;

set termout on
select 'Creating index build script...' from dual;
set termout off;

create table i_temp
(lineno NUMBER, id_owner VARCHAR2(30), id_name VARCHAR2(30),
text VARCHAR2(800))
/

DECLARE
CURSOR ind_cursor IS select owner,
index_name,
table_owner,
table_name,
uniqueness,
tablespace_name,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
pct_free
from dba_indexes
where owner != 'SYS'
order by index_name;
CURSOR col_cursor (i_own VARCHAR2, c_ind VARCHAR2, c_tab VARCHAR2) IS
select column_name
from dba_ind_columns
where index_owner = i_own
and index_name = c_ind
and table_name = c_tab
order by column_position;

lv_index_owner dba_indexes.owner%TYPE;
lv_index_name dba_indexes.index_name%TYPE;
lv_table_owner dba_indexes.table_owner%TYPE;
lv_table_name dba_indexes.table_name%TYPE;
lv_uniqueness dba_indexes.uniqueness%TYPE;
lv_tablespace_name dba_indexes.tablespace_name%TYPE;
lv_ini_trans dba_indexes.ini_trans%TYPE;
lv_max_trans dba_indexes.max_trans%TYPE;
lv_initial_extent dba_indexes.initial_extent%TYPE;
lv_next_extent dba_indexes.next_extent%TYPE;
lv_min_extents dba_indexes.min_extents%TYPE;
lv_max_extents dba_indexes.max_extents%TYPE;
lv_pct_increase dba_indexes.pct_increase%TYPE;
lv_pct_free dba_indexes.pct_free%TYPE;
lv_column_name dba_ind_columns.column_name%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(800);
lv_lineno number := 0;

procedure write_out(p_line INTEGER, p_owner varchar2, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into i_temp (lineno,id_owner, id_name,text)
values (p_line,p_owner,p_name,p_string);
end;

BEGIN
OPEN ind_cursor;
LOOP
FETCH ind_cursor INTO lv_index_owner,
lv_index_name,
lv_table_owner,
lv_table_name,
lv_uniqueness,
lv_tablespace_name,
lv_ini_trans,
lv_max_trans,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_pct_free;
EXIT WHEN ind_cursor%NOTFOUND;
lv_lineno := 1;
lv_first_rec := TRUE;
if (lv_uniqueness = 'UNIQUE') then
lv_string:= 'CREATE UNIQUE INDEX ' || lower(lv_index_owner) || '.' ||
lower(lv_index_name);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
else
lv_string:= 'CREATE INDEX ' || lower(lv_index_owner) || '.' ||
lower(lv_index_name);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
end if;
OPEN col_cursor(lv_index_owner,lv_index_name,lv_table_name);
LOOP
FETCH col_cursor INTO lv_column_name;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_string := ' ON '|| lower(lv_table_owner) || '.' ||
lower(lv_table_name)||' (';
lv_first_rec := FALSE;
else
lv_string := lv_string || ', ';
end if;
lv_string := lv_string || lower(lv_column_name);
END LOOP;
CLOSE col_cursor;
lv_string := lv_string || ')';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := null;
lv_string := 'PCTFREE ' || to_char(lv_pct_free);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITRANS ' || to_char(lv_ini_trans) ||
' MAXTRANS ' || to_char(lv_max_trans);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TABLESPACE ' || lv_tablespace_name || ' STORAGE (';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITIAL ' || to_char(lv_initial_extent) ||
' NEXT ' || to_char(lv_next_extent);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'MINEXTENTS ' || to_char(lv_min_extents) ||
' MAXEXTENTS ' || to_char(lv_max_extents) ||
' PCTINCREASE ' || to_char(lv_pct_increase) || ')';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := '/';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
END LOOP;
CLOSE ind_cursor;
END;

Ajax web site

2008-07-22T07:04:00.001+03:00

http://java.sun.com/developer/technicalArticles/J2EE/AJAX/

get apps connection in jsps

2008-07-22T06:57:00.001+03:00

Bugüne kadar connection'u hep kendi yazdığımız classlarla alıyorduk. Onun yerine (tabi sadece fonksiyon olarak bağladığımız JSPlerde geçerli) 4 satır kodla eBusiness Suite'ın connection pool'undan almak mümkün.

Şöyle ki (importlara)

<%@ page import="oracle.apps.fnd.sso.Utils"%>
<%@ page import="oracle.apps.fnd.common.WebAppsContext"%>

satırlarını ekliyoruz.

Connection alırken:

WebAppsContext wctx = Utils.getAppsContext();
Connection conn = wctx.getJDBCConnection();

işimiz bitince;

conn.commit();
wctx.releaseJDBCConnection();

ftp upload java code

2008-07-22T06:56:00.001+03:00

package com.xt.db.oracle;

import sun.net.TelnetOutputStream;

import sun.net.ftp.FtpClient;

public class FTPUploader

{

public FTPUploader()

{

}

public void putFileOverFTP(byte dataBytes[], int startpos, int endpos, String saveFile, String tip)

throws Exception

{

String server = "";

if("1".equals(tip))

server = "165.198.192.238";

else if("2".equals(tip))

server = "165.198.192.246";

String user = "xttest";

String passwd = "XTtest3";

FtpClient ftpClient = new FtpClient();

ftpClient.openServer(server);

ftpClient.login(user, passwd);

ftpClient.cd("/");

ftpClient.cd("/usr/tmp");

ftpClient.ascii();

//ftpClient.binary();

TelnetOutputStream netOut = ftpClient.put(saveFile);

netOut.write(dataBytes, startpos, endpos);

netOut.close();

ftpClient.closeServer();

}

more infos about JIRA

2008-07-22T00:07:00.001+03:00

http://mustafatan.blogspot.com/2007/04/jira-nedir-i.html

applications license select

2008-07-22T00:05:00.001+03:00

Burada her application hangi lisansa ait cross bilgileri tutan bir de table oluşturmam gerekti onun için. Aşağıdaki sorguda o tabloyu çıkardım, her bölümde application sayılarını veren kısmı var:

SELECT pgt.NAME grade, apr.application_name, COUNT(*) sayi

FROM per_all_assignments_f a,

per_grades_tl pgt,

per_all_people_f p,

--xxfr_uygulama_lisans xul,

(SELECT DISTINCT fa.APPLICATION_NAME, fu.EMPLOYEE_ID, fa.APPLICATION_ID

FROM fnd_user_resp_groups frg, fnd_user fu, fnd_application_tl fa

WHERE ((NVL(frg.END_DATE,TO_DATE('31.12.4712','dd.mm.yyyy')) = TO_DATE('31.12.4712','dd.mm.yyyy')) OR

(NVL(frg.END_DATE,TO_DATE('31.12.4712','dd.mm.yyyy')) > SYSDATE))

AND frg.USER_ID = fu.USER_ID

AND fa.APPLICATION_ID = frg.RESPONSIBILITY_APPLICATION_ID

AND fa.LANGUAGE = 'US') apr

WHERE a.GRADE_ID = pgt.GRADE_ID

AND a.EFFECTIVE_END_DATE = TO_DATE('31.12.4712','dd.mm.yyyy')

AND pgt.LANGUAGE = 'US'

-- AND a.PERSON_ID = 787

AND p.PERSON_ID = a.PERSON_ID

AND p.EFFECTIVE_END_DATE = TO_DATE('31.12.4712','dd.mm.yyyy')

AND a.PERSON_ID = apr.employee_id

-- AND xul.APPLICATION_ID = apr.APPLICATION_ID

GROUP BY pgt.NAME, apr.application_name

ORDER BY pgt.NAME

applications audit table

2008-07-22T00:03:00.001+03:00

Asagidaki query ile Applications'taki Audit tutulan tablolari
listeyebilirsiniz.

select fag.GROUP_NAME
, ft.TABLE_NAME
, fc.COLUMN_SEQUENCE
, fc.COLUMN_NAME
from fnd_audit_tables fat
, fnd_audit_columns fac
, fnd_tables ft
, fnd_columns fc
, fnd_audit_groups fag
where fat.TABLE_ID = ft.TABLE_ID
and fat.TABLE_APP_ID = ft.APPLICATION_ID
and fat.TABLE_ID = fac.TABLE_ID
and fat.TABLE_APP_ID = fac.TABLE_APP_ID
and fac.COLUMN_ID = fc.COLUMN_ID
and fat.AUDIT_GROUP_ID = fag.AUDIT_GROUP_ID
and fag.STATE = 'E'
and fac.SCHEMA_ID = 900
order by 1, 2 , 3 , 4