Dibawah contoh sederhana menggunakan format tanggal dan waktu :
Menampilkan tanggal dalam format mm-dd-yy
#
ketik perintah berikut ini :
$ date +"%m-%d-%y"
#
Hasil:
09-29-08
#
Menampilkan 4 digit format tahun:
$ date +"%m-%d-%Y"
#
menampilkan format mm/dd/yy :
$ date +"%D"
#
#
Menampilkan time saja
#
ketik perintah berikut:
$ date +"%T"
#
Hasil:
#
19:55:04
#
Menampilkan Time dalam format 12-jam :
$ date +"%r"
#
Hasil
#
07:56:05 PM
#
Menampilkan time dengan format HH:MM :
$ date +"%H-%M"
#
#
Bgaimana memasukkan tanggal atau waktu ke dalam variable ?
Sedrhana saja cukup ketik perintah di bawah ini:
#
$ WAKTU_SEKARANG=$(date +"%m-%d-%Y")
#
Untuk menampilkan variable waktu tersebut gunakan perintah echo / printf :
$ echo $WAKTU_SEKARANG
#
Berikut ini contoh sederhana shell scripting menggunakan format tanggal atau waktu :
#
#!/bin/bash
WAKTU_SEKARANG=$(date +"%m-%d-%Y")
NAMA_FILE="BKP_DATA.$WAKTU_SEKARANG.tar.gz"
# Lanjutkan dengan script yang diinginkan
Author :Ida Bagus Enderajana
Reference : Shell Scripting Programming Documentations
Tuesday, February 28, 2012
Script untuk export data
Script ini saya buat untuk saya gunakan mengekspor schema-schema di database untuk keperluan testing , berikut ini scriptnya :
#!/bin/ksh
#
#
# author : Ida Bagus Enderajana
#
TODAY=$(date +%Y\%m\%d)
ORACLE_SID=DEVODS
export HOST=`hostname`
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/bin:/usr/sbin:/sbin:/usr/bin:/usr/ccs/bin:/usr/ucb:/usr/X11R6/lib
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
#
# Buat direktori untuk file dmp
EXPDIR=/u01/backup_data/${TODAY}
#
#cek apakah direktori sudah tersedia apa belom
if [ ! -d $EXPDIR ]
then
mkdir -p $EXPDIR
fi
#
# Membuat opsi atau pilihan untuk export
while getopts :s:u:r:f: opt;
do
case $opt in
s) ORACLE_SID=$OPTARG ;;
u) OWNERS=$OPTARG ;;
r) ROW=$OPTARG ;;
f) NFILE=$OPTARG ;;
\?) usage ;;
esac
done
#
FILE_NAME=$EXPDIR/bkp_"$NFILE"_$TODAY.dmp
#
#
exp userid=system/manager@$ORACLE_SID rows=$ROW owner=$OWNERS file=$EXPDIR/bkp_"$NFILE"_$TODAY.dmp log=$EXPDIR/bkp_"$NFILE"_$TODAY.log feedback=10000 consistent=y direct=y statistics=none
#
# Kompres file hasil export
gzip $FILE_NAME
# Contoh untuk menjalankan scriptnya
./expdb.sh -u Schema_A,Schema_B -s DEVODS -r Y
#!/bin/ksh
#
#
# author : Ida Bagus Enderajana
#
TODAY=$(date +%Y\%m\%d)
ORACLE_SID=DEVODS
export HOST=`hostname`
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/bin:/usr/sbin:/sbin:/usr/bin:/usr/ccs/bin:/usr/ucb:/usr/X11R6/lib
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
#
# Buat direktori untuk file dmp
EXPDIR=/u01/backup_data/${TODAY}
#
#cek apakah direktori sudah tersedia apa belom
if [ ! -d $EXPDIR ]
then
mkdir -p $EXPDIR
fi
#
# Membuat opsi atau pilihan untuk export
while getopts :s:u:r:f: opt;
do
case $opt in
s) ORACLE_SID=$OPTARG ;;
u) OWNERS=$OPTARG ;;
r) ROW=$OPTARG ;;
f) NFILE=$OPTARG ;;
\?) usage ;;
esac
done
#
FILE_NAME=$EXPDIR/bkp_"$NFILE"_$TODAY.dmp
#
#
exp userid=system/manager@$ORACLE_SID rows=$ROW owner=$OWNERS file=$EXPDIR/bkp_"$NFILE"_$TODAY.dmp log=$EXPDIR/bkp_"$NFILE"_$TODAY.log feedback=10000 consistent=y direct=y statistics=none
#
# Kompres file hasil export
gzip $FILE_NAME
# Contoh untuk menjalankan scriptnya
./expdb.sh -u Schema_A,Schema_B -s DEVODS -r Y
Wednesday, February 15, 2012
Script untuk Regenerate Table Partition
Berikut ini scipt dari Rampant untuk Re-Create table partition. Dimana ada sedikit modifikasi yang saya lakukan di script ini .
/* Copyright © 2004, 2008 by Rampant TechPress Inc.*/
--- Modify By Ida Bagus Enderajana DTP
REM
REM part_tab_rct.sql
REM
REM FUNCTION: SCRIPT FOR CREATING PARTITIONED TABLES
REM
REM This script can be run by any user .
REM
REM This script is intended to run with Oracle8i.
REM
REM Running this script will in turn create a script to
REM build all the partitioned tables owned by the user in the database.
REM This created script, create_part_table.sql, can be run by any user
REM with the 'CREATE TABLE' system privilege.
REM
REM NOTE: The script will NOT include constraints on tables. This
REM script will also NOT capture tables created by user 'SYS'.
REM
REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
REM
set verify off
set feedback off
set echo off;
set pagesize 0 lines 132 embedded on
set termout on
select 'Creating table build script...' from dual;
--- drop table t_temp;
create table t_temp
(lineno NUMBER, tb_owner VARCHAR2(30), tb_name VARCHAR2(30),
text VARCHAR2(2000))
/
drop sequence lineno_seq;
create sequence lineno_seq start with 1 increment by 1 nocache nocycle;
DECLARE
CURSOR tab_cursor(tab_name VARCHAR2) IS select table_name,
PARTITIONING_TYPE,
SUBPARTITIONING_TYPE,
PARTITION_COUNT,
DEF_SUBPARTITION_COUNT,
PARTITIONING_KEY_COUNT,
SUBPARTITIONING_KEY_COUNT,
DEF_TABLESPACE_NAME
from dba_part_tables
where table_name=upper(tab_name);
--
CURSOR col_cursor (c_tab VARCHAR2) IS select
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from dba_tab_columns
where table_name = c_tab
order by column_id;
--
CURSOR part_key (c_tab VARCHAR2) is select column_name,
column_position
from dba_part_key_columns
where trim(name)=c_tab
and trim(object_type)='TABLE'
order by column_position;
--
CURSOR part_cursor (c_tab VARCHAR2) IS select PARTITION_NAME,
SUBPARTITION_COUNT,
HIGH_VALUE,
HIGH_VALUE_LENGTH,
PARTITION_POSITION,
TABLESPACE_NAME
from dba_tab_partitions
where table_name=c_tab
order by partition_position;
--
CURSOR subpart_key (c_tab VARCHAR2) IS select column_name,
column_position
from dba_subpart_key_columns
where trim(name)=c_tab
and trim(object_type)='TABLE'
order by column_position;
--
CURSOR subpart_tbsp (c_tab VARCHAR2) IS select unique tablespace_name
from dba_tab_subpartitions
where table_name=c_tab;
--
CURSOR subpart_cursor (c_tab VARCHAR2, tab_part VARCHAR2) IS select
SUBPARTITION_NAME,
tablespace_name
from dba_tab_subpartitions
where table_name=c_tab
and partition_name=tab_part
order by subpartition_position;
--
CURSOR subpart_stor (c_tab varchar2, tab_part varchar2) IS select
initial_extent,
next_extent,
pct_increase
from dba_tab_subpartitions
where table_name=c_tab
and partition_name=tab_part
and subpartition_position=1;
--
lv_table_name dba_part_tables.table_name%TYPE;
lv_partitioning_type dba_part_tables.partitioning_type%TYPE;
lv_subpartitioning_type dba_part_tables.subpartitioning_type%TYPE;
lv_partition_count dba_part_tables.partition_count%TYPE;
lv_subpartition_count dba_part_tables.def_subpartition_count%TYPE;
lv_partition_key_count dba_part_tables.partitioning_key_count%TYPE;
lv_subpartition_key_count dba_part_tables.subpartitioning_key_count%TYPE;
lv_tablespace_name dba_part_tables.def_tablespace_name%TYPE;
lv_column_name dba_tab_columns.column_name%TYPE;
lv_data_type dba_tab_columns.data_type%TYPE;
lv_data_length dba_tab_columns.data_length%TYPE;
lv_data_precision dba_tab_columns.data_precision%TYPE;
lv_data_scale dba_tab_columns.data_scale%TYPE;
lv_nullable dba_tab_columns.nullable%TYPE;
lv_pkey_column_name dba_part_key_columns.column_name%TYPE;
lv_pkey_column_number dba_part_key_columns.column_position%TYPE;
lv_spkey_column_name dba_subpart_key_columns.column_name%TYPE;
lv_spkey_column_number dba_subpart_key_columns.column_position%TYPE;
lv_part_COMPOSITE dba_tab_partitions.composite%TYPE;
lv_PARTITION_NAME dba_tab_partitions.partition_name%TYPE;
lv_part_SUBPARTITION_COUNT dba_tab_partitions.subpartition_count%TYPE;
lv_HIGH_VALUE dba_tab_partitions.high_value%TYPE;
lv_HIGH_VALUE_LENGTH dba_tab_partitions.high_value_length%TYPE;
lv_PARTITION_POSITION dba_tab_partitions.partition_position%TYPE;
lv_part_TABLESPACE_NAME dba_tab_partitions.tablespace_name%TYPE;
lv_SUBPARTITION_NAME dba_tab_subpartitions.SUBPARTITION_NAME%TYPE;
lv_spart_TABLESPACE_NAME dba_tab_subpartitions.TABLESPACE_NAME%TYPE;
lv_subpartition_position dba_tab_subpartitions.subpartition_position%TYPE;
lv_subp_initial_extent dba_tab_subpartitions.initial_extent%TYPE;
lv_subp_next_extent dba_tab_subpartitions.next_extent%TYPE;
lv_subp_pct_increase dba_tab_subpartitions.pct_increase%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(2000);
tbsp_string VARCHAR2(2000);
tmp_string VARCHAR2(2000);
nul_cnt number;
sp_cnt number;
flg VARCHAR2(32);
fl VARCHAR2(32);
minex VARCHAR2(32);
maxex VARCHAR2(32);
--
procedure write_out(p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into t_temp (lineno, tb_name, text)
values (lineno_seq.nextval,p_name,rtrim(p_string,chr(32)));
end;
--
BEGIN
OPEN tab_cursor('&tab_name');
LOOP
FETCH tab_cursor INTO lv_table_name,
lv_partitioning_type,
lv_subpartitioning_type,
lv_partition_count,
lv_subpartition_count,
lv_partition_key_count,
lv_subpartition_key_count,
lv_tablespace_name;
EXIT WHEN tab_cursor%NOTFOUND;
lv_string := 'DROP TABLE '|| lower(lv_table_name)||';';
write_out(lv_table_name, lv_string);
lv_first_rec := TRUE;
lv_string := 'CREATE TABLE '|| lower(lv_table_name)||' (';
write_out(lv_table_name, lv_string);
lv_string := null;
OPEN col_cursor(lv_table_name);
nul_cnt:=0;
LOOP
FETCH col_cursor INTO lv_column_name,
lv_data_type,
lv_data_length,
lv_data_precision,
lv_data_scale,
lv_nullable;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
else
lv_string := ',';
end if;
if ((lv_data_type = 'NUMBER') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||','||
nvl(lv_data_scale,0)||')';
elsif ((lv_data_type = 'FLOAT') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||')';
else
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type;
end if;
if ((lv_data_type = 'CHAR') or (lv_data_type = 'VARCHAR2')) then
lv_string := lv_string || '(' || lv_data_length || ')';
end if;
if (lv_nullable = 'N') then
nul_cnt:=nul_cnt+1;
lv_string := lv_string || ' constraint ck_'||lv_table_name||'_'||nul_cnt||' NOT NULL';
end if;
write_out(lv_table_name, lv_string);
END LOOP;
CLOSE col_cursor;
lv_string := ')';
write_out(lv_table_name, lv_string);
lv_string := NULL;
nul_cnt:=0;
--
OPEN part_key(lv_table_name);
IF lv_partition_key_count>1 THEN
LOOP
FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
exit when part_key%NOTFOUND;
if nul_cnt=0 THEN
tmp_string:=lv_pkey_column_name;
nul_cnt:=nul_cnt+1;
else
tmp_string:=tmp_string||','||lv_pkey_column_name;
nul_cnt:=nul_cnt+1;
dbms_output.put_line(tmp_string);
end if;
END LOOP;
end if;
if lv_partition_key_count=1 THEN
FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
tmp_string:=lv_pkey_column_name;
end if;
CLOSE part_key;
--
lv_string:='PARTITION BY RANGE ('||tmp_string||')';
write_out(lv_table_name, lv_string);
lv_string := NULL;
tmp_string:=NULL;
--
if lv_subpartition_key_count>0 THEN
OPEN subpart_key(lv_table_name);
if lv_subpartition_key_count=1 THEN
FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
tmp_string:=lv_spkey_column_name;
else
nul_cnt:=0;
LOOP
FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
exit when subpart_key%NOTFOUND;
if nul_cnt=0 THEN
tmp_string:=lv_spkey_column_name;
nul_cnt:=nul_cnt+1;
dbms_output.put_line(tmp_string);
else
tmp_string:=tmp_string||','||lv_spkey_column_name;
nul_cnt:=nul_cnt+1;
end if;
END LOOP;
CLOSE subpart_key;
end if;
--
if lv_subpartition_count>1 then
OPEN subpart_tbsp(lv_table_name);
nul_cnt:=0;
LOOP
FETCH subpart_tbsp INTO lv_spart_tablespace_name;
exit when subpart_tbsp%NOTFOUND;
dbms_output.put_line(lv_spart_tablespace_name);
if nul_cnt=0 then
tbsp_string:=lv_spart_tablespace_name;
nul_cnt:=nul_cnt+1;
else
tbsp_string:=tbsp_string||','||lv_spart_tablespace_name;
nul_cnt:=nul_cnt+1;
end if;
END LOOP;
end if;
CLOSE subpart_tbsp;
--
lv_string := 'SUBPARTITION BY HASH ('||tmp_string||')';
write_out(lv_table_name, lv_string);
lv_string := 'SUBPARTITIONS '||to_char(lv_subpartition_count);
write_out(lv_table_name, lv_string);
lv_string := 'STORE IN ('||tbsp_string||')';
write_out(lv_table_name, lv_string);
lv_string := null;
end if;
--
OPEN part_cursor(lv_table_name);
if lv_partition_count=1 THEN
FETCH part_cursor INTO lv_PARTITION_NAME,
lv_part_SUBPARTITION_COUNT,
lv_HIGH_VALUE,
lv_HIGH_VALUE_LENGTH,
lv_PARTITION_POSITION,
lv_part_TABLESPACE_NAME;
if lv_string is not null then
write_out(lv_table_name, lv_string);
end if;
ELSE
nul_cnt:=0;
LOOP
FETCH part_cursor INTO lv_PARTITION_NAME,
lv_part_SUBPARTITION_COUNT,
lv_HIGH_VALUE,
lv_HIGH_VALUE_LENGTH,
lv_PARTITION_POSITION,
lv_part_TABLESPACE_NAME;
exit when part_cursor%NOTFOUND;
lv_string:='PARTITION '||lv_partition_name||' VALUES LESS THAN ('||lv_high_value||')';
if nul_cnt=0 then
lv_string:='('||lv_string;
else
lv_string:=','||lv_string;
end if;
write_out(lv_table_name, lv_string);
lv_string:=fl||flg||minex||maxex;
if lv_string is not null then
write_out(lv_table_name, lv_string);
end if;
nul_cnt:=nul_cnt+1;
--
if lv_part_subpartition_count>0 THEN
OPEN subpart_cursor(lv_table_name,lv_partition_name);
sp_cnt:=0;
LOOP
FETCH subpart_cursor INTO lv_SUBPARTITION_NAME,
lv_spart_TABLESPACE_NAME;
exit when subpart_cursor%NOTFOUND;
if sp_cnt=0 THEN
lv_string:='(SUBPARTITION '||lv_subpartition_name||
' TABLESPACE '||lv_spart_tablespace_name;
write_out(lv_table_name, lv_string);
else
lv_string:=',SUBPARTITION '||lv_subpartition_name||
' TABLESPACE '||lv_spart_tablespace_name;
write_out(lv_table_name, lv_string);
end if;
sp_cnt:=sp_cnt+1;
END LOOP;
lv_string:=')';
write_out(lv_table_name, lv_string);
lv_string := null;
close subpart_cursor;
end if;
END LOOP;
close part_cursor;
end if;
--
lv_string := ')'||chr(10)||'/';
write_out(lv_table_name, lv_string);
lv_string:=' ';
write_out(lv_table_name, lv_string);
END LOOP;
CLOSE tab_cursor;
END;
/
set heading off trimspool on
spool create_part_table.sql
select text
from T_temp
order by tb_name, lineno;
spool off
drop table t_temp;
set verify on
set feedback on
set termout on
set pagesize 22 lines 80
( Source : Internet )
/* Copyright © 2004, 2008 by Rampant TechPress Inc.*/
--- Modify By Ida Bagus Enderajana DTP
REM
REM part_tab_rct.sql
REM
REM FUNCTION: SCRIPT FOR CREATING PARTITIONED TABLES
REM
REM This script can be run by any user .
REM
REM This script is intended to run with Oracle8i.
REM
REM Running this script will in turn create a script to
REM build all the partitioned tables owned by the user in the database.
REM This created script, create_part_table.sql, can be run by any user
REM with the 'CREATE TABLE' system privilege.
REM
REM NOTE: The script will NOT include constraints on tables. This
REM script will also NOT capture tables created by user 'SYS'.
REM
REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
REM
set verify off
set feedback off
set echo off;
set pagesize 0 lines 132 embedded on
set termout on
select 'Creating table build script...' from dual;
--- drop table t_temp;
create table t_temp
(lineno NUMBER, tb_owner VARCHAR2(30), tb_name VARCHAR2(30),
text VARCHAR2(2000))
/
drop sequence lineno_seq;
create sequence lineno_seq start with 1 increment by 1 nocache nocycle;
DECLARE
CURSOR tab_cursor(tab_name VARCHAR2) IS select table_name,
PARTITIONING_TYPE,
SUBPARTITIONING_TYPE,
PARTITION_COUNT,
DEF_SUBPARTITION_COUNT,
PARTITIONING_KEY_COUNT,
SUBPARTITIONING_KEY_COUNT,
DEF_TABLESPACE_NAME
from dba_part_tables
where table_name=upper(tab_name);
--
CURSOR col_cursor (c_tab VARCHAR2) IS select
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from dba_tab_columns
where table_name = c_tab
order by column_id;
--
CURSOR part_key (c_tab VARCHAR2) is select column_name,
column_position
from dba_part_key_columns
where trim(name)=c_tab
and trim(object_type)='TABLE'
order by column_position;
--
CURSOR part_cursor (c_tab VARCHAR2) IS select PARTITION_NAME,
SUBPARTITION_COUNT,
HIGH_VALUE,
HIGH_VALUE_LENGTH,
PARTITION_POSITION,
TABLESPACE_NAME
from dba_tab_partitions
where table_name=c_tab
order by partition_position;
--
CURSOR subpart_key (c_tab VARCHAR2) IS select column_name,
column_position
from dba_subpart_key_columns
where trim(name)=c_tab
and trim(object_type)='TABLE'
order by column_position;
--
CURSOR subpart_tbsp (c_tab VARCHAR2) IS select unique tablespace_name
from dba_tab_subpartitions
where table_name=c_tab;
--
CURSOR subpart_cursor (c_tab VARCHAR2, tab_part VARCHAR2) IS select
SUBPARTITION_NAME,
tablespace_name
from dba_tab_subpartitions
where table_name=c_tab
and partition_name=tab_part
order by subpartition_position;
--
CURSOR subpart_stor (c_tab varchar2, tab_part varchar2) IS select
initial_extent,
next_extent,
pct_increase
from dba_tab_subpartitions
where table_name=c_tab
and partition_name=tab_part
and subpartition_position=1;
--
lv_table_name dba_part_tables.table_name%TYPE;
lv_partitioning_type dba_part_tables.partitioning_type%TYPE;
lv_subpartitioning_type dba_part_tables.subpartitioning_type%TYPE;
lv_partition_count dba_part_tables.partition_count%TYPE;
lv_subpartition_count dba_part_tables.def_subpartition_count%TYPE;
lv_partition_key_count dba_part_tables.partitioning_key_count%TYPE;
lv_subpartition_key_count dba_part_tables.subpartitioning_key_count%TYPE;
lv_tablespace_name dba_part_tables.def_tablespace_name%TYPE;
lv_column_name dba_tab_columns.column_name%TYPE;
lv_data_type dba_tab_columns.data_type%TYPE;
lv_data_length dba_tab_columns.data_length%TYPE;
lv_data_precision dba_tab_columns.data_precision%TYPE;
lv_data_scale dba_tab_columns.data_scale%TYPE;
lv_nullable dba_tab_columns.nullable%TYPE;
lv_pkey_column_name dba_part_key_columns.column_name%TYPE;
lv_pkey_column_number dba_part_key_columns.column_position%TYPE;
lv_spkey_column_name dba_subpart_key_columns.column_name%TYPE;
lv_spkey_column_number dba_subpart_key_columns.column_position%TYPE;
lv_part_COMPOSITE dba_tab_partitions.composite%TYPE;
lv_PARTITION_NAME dba_tab_partitions.partition_name%TYPE;
lv_part_SUBPARTITION_COUNT dba_tab_partitions.subpartition_count%TYPE;
lv_HIGH_VALUE dba_tab_partitions.high_value%TYPE;
lv_HIGH_VALUE_LENGTH dba_tab_partitions.high_value_length%TYPE;
lv_PARTITION_POSITION dba_tab_partitions.partition_position%TYPE;
lv_part_TABLESPACE_NAME dba_tab_partitions.tablespace_name%TYPE;
lv_SUBPARTITION_NAME dba_tab_subpartitions.SUBPARTITION_NAME%TYPE;
lv_spart_TABLESPACE_NAME dba_tab_subpartitions.TABLESPACE_NAME%TYPE;
lv_subpartition_position dba_tab_subpartitions.subpartition_position%TYPE;
lv_subp_initial_extent dba_tab_subpartitions.initial_extent%TYPE;
lv_subp_next_extent dba_tab_subpartitions.next_extent%TYPE;
lv_subp_pct_increase dba_tab_subpartitions.pct_increase%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(2000);
tbsp_string VARCHAR2(2000);
tmp_string VARCHAR2(2000);
nul_cnt number;
sp_cnt number;
flg VARCHAR2(32);
fl VARCHAR2(32);
minex VARCHAR2(32);
maxex VARCHAR2(32);
--
procedure write_out(p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into t_temp (lineno, tb_name, text)
values (lineno_seq.nextval,p_name,rtrim(p_string,chr(32)));
end;
--
BEGIN
OPEN tab_cursor('&tab_name');
LOOP
FETCH tab_cursor INTO lv_table_name,
lv_partitioning_type,
lv_subpartitioning_type,
lv_partition_count,
lv_subpartition_count,
lv_partition_key_count,
lv_subpartition_key_count,
lv_tablespace_name;
EXIT WHEN tab_cursor%NOTFOUND;
lv_string := 'DROP TABLE '|| lower(lv_table_name)||';';
write_out(lv_table_name, lv_string);
lv_first_rec := TRUE;
lv_string := 'CREATE TABLE '|| lower(lv_table_name)||' (';
write_out(lv_table_name, lv_string);
lv_string := null;
OPEN col_cursor(lv_table_name);
nul_cnt:=0;
LOOP
FETCH col_cursor INTO lv_column_name,
lv_data_type,
lv_data_length,
lv_data_precision,
lv_data_scale,
lv_nullable;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
else
lv_string := ',';
end if;
if ((lv_data_type = 'NUMBER') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||','||
nvl(lv_data_scale,0)||')';
elsif ((lv_data_type = 'FLOAT') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||')';
else
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type;
end if;
if ((lv_data_type = 'CHAR') or (lv_data_type = 'VARCHAR2')) then
lv_string := lv_string || '(' || lv_data_length || ')';
end if;
if (lv_nullable = 'N') then
nul_cnt:=nul_cnt+1;
lv_string := lv_string || ' constraint ck_'||lv_table_name||'_'||nul_cnt||' NOT NULL';
end if;
write_out(lv_table_name, lv_string);
END LOOP;
CLOSE col_cursor;
lv_string := ')';
write_out(lv_table_name, lv_string);
lv_string := NULL;
nul_cnt:=0;
--
OPEN part_key(lv_table_name);
IF lv_partition_key_count>1 THEN
LOOP
FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
exit when part_key%NOTFOUND;
if nul_cnt=0 THEN
tmp_string:=lv_pkey_column_name;
nul_cnt:=nul_cnt+1;
else
tmp_string:=tmp_string||','||lv_pkey_column_name;
nul_cnt:=nul_cnt+1;
dbms_output.put_line(tmp_string);
end if;
END LOOP;
end if;
if lv_partition_key_count=1 THEN
FETCH part_key INTO lv_pkey_column_name,lv_pkey_column_number;
tmp_string:=lv_pkey_column_name;
end if;
CLOSE part_key;
--
lv_string:='PARTITION BY RANGE ('||tmp_string||')';
write_out(lv_table_name, lv_string);
lv_string := NULL;
tmp_string:=NULL;
--
if lv_subpartition_key_count>0 THEN
OPEN subpart_key(lv_table_name);
if lv_subpartition_key_count=1 THEN
FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
tmp_string:=lv_spkey_column_name;
else
nul_cnt:=0;
LOOP
FETCH subpart_key INTO lv_spkey_column_name,lv_spkey_column_number;
exit when subpart_key%NOTFOUND;
if nul_cnt=0 THEN
tmp_string:=lv_spkey_column_name;
nul_cnt:=nul_cnt+1;
dbms_output.put_line(tmp_string);
else
tmp_string:=tmp_string||','||lv_spkey_column_name;
nul_cnt:=nul_cnt+1;
end if;
END LOOP;
CLOSE subpart_key;
end if;
--
if lv_subpartition_count>1 then
OPEN subpart_tbsp(lv_table_name);
nul_cnt:=0;
LOOP
FETCH subpart_tbsp INTO lv_spart_tablespace_name;
exit when subpart_tbsp%NOTFOUND;
dbms_output.put_line(lv_spart_tablespace_name);
if nul_cnt=0 then
tbsp_string:=lv_spart_tablespace_name;
nul_cnt:=nul_cnt+1;
else
tbsp_string:=tbsp_string||','||lv_spart_tablespace_name;
nul_cnt:=nul_cnt+1;
end if;
END LOOP;
end if;
CLOSE subpart_tbsp;
--
lv_string := 'SUBPARTITION BY HASH ('||tmp_string||')';
write_out(lv_table_name, lv_string);
lv_string := 'SUBPARTITIONS '||to_char(lv_subpartition_count);
write_out(lv_table_name, lv_string);
lv_string := 'STORE IN ('||tbsp_string||')';
write_out(lv_table_name, lv_string);
lv_string := null;
end if;
--
OPEN part_cursor(lv_table_name);
if lv_partition_count=1 THEN
FETCH part_cursor INTO lv_PARTITION_NAME,
lv_part_SUBPARTITION_COUNT,
lv_HIGH_VALUE,
lv_HIGH_VALUE_LENGTH,
lv_PARTITION_POSITION,
lv_part_TABLESPACE_NAME;
if lv_string is not null then
write_out(lv_table_name, lv_string);
end if;
ELSE
nul_cnt:=0;
LOOP
FETCH part_cursor INTO lv_PARTITION_NAME,
lv_part_SUBPARTITION_COUNT,
lv_HIGH_VALUE,
lv_HIGH_VALUE_LENGTH,
lv_PARTITION_POSITION,
lv_part_TABLESPACE_NAME;
exit when part_cursor%NOTFOUND;
lv_string:='PARTITION '||lv_partition_name||' VALUES LESS THAN ('||lv_high_value||')';
if nul_cnt=0 then
lv_string:='('||lv_string;
else
lv_string:=','||lv_string;
end if;
write_out(lv_table_name, lv_string);
lv_string:=fl||flg||minex||maxex;
if lv_string is not null then
write_out(lv_table_name, lv_string);
end if;
nul_cnt:=nul_cnt+1;
--
if lv_part_subpartition_count>0 THEN
OPEN subpart_cursor(lv_table_name,lv_partition_name);
sp_cnt:=0;
LOOP
FETCH subpart_cursor INTO lv_SUBPARTITION_NAME,
lv_spart_TABLESPACE_NAME;
exit when subpart_cursor%NOTFOUND;
if sp_cnt=0 THEN
lv_string:='(SUBPARTITION '||lv_subpartition_name||
' TABLESPACE '||lv_spart_tablespace_name;
write_out(lv_table_name, lv_string);
else
lv_string:=',SUBPARTITION '||lv_subpartition_name||
' TABLESPACE '||lv_spart_tablespace_name;
write_out(lv_table_name, lv_string);
end if;
sp_cnt:=sp_cnt+1;
END LOOP;
lv_string:=')';
write_out(lv_table_name, lv_string);
lv_string := null;
close subpart_cursor;
end if;
END LOOP;
close part_cursor;
end if;
--
lv_string := ')'||chr(10)||'/';
write_out(lv_table_name, lv_string);
lv_string:=' ';
write_out(lv_table_name, lv_string);
END LOOP;
CLOSE tab_cursor;
END;
/
set heading off trimspool on
spool create_part_table.sql
select text
from T_temp
order by tb_name, lineno;
spool off
drop table t_temp;
set verify on
set feedback on
set termout on
set pagesize 22 lines 80
( Source : Internet )
Monday, February 6, 2012
Membuat Function Terbilang
Fungsi ini berfungsi untuk mengkonversi number ke terbilang , biasanya banyak di pakai untuk mencetak billing statement .
Berikut ini contoh script sederhana yang saya buat :
# Author : Ida Bagus Enderajana
# Website : http://zmp165.blogspot.com/
Create or Replace Function Terbilang(Digit varchar2) Return Varchar2 is
Tampung99 varchar2(100);
kel_1 varchar2(30);
kel_2 varchar2(30);
kel_3 varchar2(30);
kel_4 varchar2(30);
kel_5 varchar2(30);
hasil varchar2(3000);
cekdigit number;
sisabagi number;
Begin
tampung99 :='';
kel_1:='';
kel_2:='';
kel_3:='';
kel_4:='';
kel_5:='';
cekdigit :=length(digit);
sisabagi :=mod(cekdigit,3);
if sisabagi=0 then
tampung99:=digit;
elsif sisabagi=1 then
tampung99:='00'||digit;
elsif sisabagi=2 then
tampung99:='0'||digit;
end if;
if length(tampung99)=3 then
kel_1:=substr(tampung99,1,3);
hasil:=basecurr_triple_number_words(kel_1);
elsif length(tampung99)=6 then
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
if kel_1='001' and kel_2='000' then
hasil:='seribu';
elsif kel_1=('001') and kel_2 <>('000') then
hasil:='seribu '||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>('001') and kel_2 <>('000') then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' ribu '||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>'001' and kel_2='000' then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' ribu ';
end if;
elsif length(tampung99)=9 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
if kel_1 <>'000' then
begin
if kel_2 <>'000' and kel_2 <>'001' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_2)||' ribu '||basecurr_triple_number_words(kel_3);
elsif kel_2 ='000' and kel_3='000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_3);
elsif kel_2='000' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_3);
elsif kel_2='001' and kel_3='000' then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' juta '||' seribu';
elsif kel_2='001' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' juta '||' seribu '||basecurr_triple_number_words(to_number(kel_3));
elsif kel_2 ='001' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||' seribu '||basecurr_triple_number_words(kel_3);
elsif kel_2 <>'000' and kel_2 <>'001' and kel_3 <>'000' or kel_3='000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_2)||' ribu '||basecurr_triple_number_words(kel_3);
end if;
end;
end if;
end;
elsif length(tampung99)=12 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
if kel_1 <>'000' and kel_2 <>'000' then
begin
if kel_3 <>'000' and kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_3)||' ribu '||basecurr_triple_number_words(kel_4);
elsif kel_3 ='000' and kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_4);
elsif kel_3 ='000' and kel_4='000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_4);
elsif kel_3='001' and kel_4='000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||' seribu ';
elsif kel_3='001' and kel_4 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||' seribu '||basecurr_triple_number_words(kel_4);
elsif kel_3 <>'000' and kel_3 ='001' and kel_4 <>'000' or kel_4='000' then
hasil :=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_3)||' ribu '||basecurr_triple_number_words(kel_4);
end if;
end;
elsif kel_1 <>'000' and kel_2='000' then
begin
if kel_3 ='000' and kel_4='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_4);
elsif kel_3 <>'001' or kel_3 <>'000' and kel_4 ='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_3)||' ribu '||basecurr_triple_number_words(kel_4);
end if;
end;
end if;
end;
elsif length(tampung99)=15 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
kel_5:=substr(tampung99,13,3);
if kel_1 <>'000' and kel_2 <>'000' and kel_3 <>'000' then
begin
if kel_4 <>'000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4='001' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||' seribu ';
elsif kel_4='001' and kel_5 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||' seribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 <>'000' and kel_4 ='001' and kel_5 <>'000' or kel_5='000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>'000' and kel_2 <>'000' and kel_3 ='000' then
begin
if kel_4 <>'000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_5);
elsif kel_4='001' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||' seribu ';
elsif kel_4='001' and kel_5 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||' seribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 <>'000' and kel_4 ='001' and kel_5 <>'000' or kel_5='000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>'000' and kel_2 ='000' and kel_3 <>'000' then
begin
if kel_4 <>'000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4='001' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||' seribu ';
elsif kel_4='001' and kel_5 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||' seribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 <>'000' and kel_4 ='001' and kel_5 <>'000' or kel_5='000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>'000' and kel_2='000' and kel_3='000' then
begin
if kel_3 ='000' and kel_4='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_5);
elsif kel_3 <>'001' or kel_3 <>'000' and kel_4 ='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
end if;
end;
end if;
return(hasil);
end;
/
Berikut ini contoh script sederhana yang saya buat :
# Author : Ida Bagus Enderajana
# Website : http://zmp165.blogspot.com/
Create or Replace Function Terbilang(Digit varchar2) Return Varchar2 is
Tampung99 varchar2(100);
kel_1 varchar2(30);
kel_2 varchar2(30);
kel_3 varchar2(30);
kel_4 varchar2(30);
kel_5 varchar2(30);
hasil varchar2(3000);
cekdigit number;
sisabagi number;
Begin
tampung99 :='';
kel_1:='';
kel_2:='';
kel_3:='';
kel_4:='';
kel_5:='';
cekdigit :=length(digit);
sisabagi :=mod(cekdigit,3);
if sisabagi=0 then
tampung99:=digit;
elsif sisabagi=1 then
tampung99:='00'||digit;
elsif sisabagi=2 then
tampung99:='0'||digit;
end if;
if length(tampung99)=3 then
kel_1:=substr(tampung99,1,3);
hasil:=basecurr_triple_number_words(kel_1);
elsif length(tampung99)=6 then
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
if kel_1='001' and kel_2='000' then
hasil:='seribu';
elsif kel_1=('001') and kel_2 <>('000') then
hasil:='seribu '||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>('001') and kel_2 <>('000') then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' ribu '||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>'001' and kel_2='000' then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' ribu ';
end if;
elsif length(tampung99)=9 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
if kel_1 <>'000' then
begin
if kel_2 <>'000' and kel_2 <>'001' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_2)||' ribu '||basecurr_triple_number_words(kel_3);
elsif kel_2 ='000' and kel_3='000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_3);
elsif kel_2='000' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_3);
elsif kel_2='001' and kel_3='000' then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' juta '||' seribu';
elsif kel_2='001' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(to_number(kel_1))||' juta '||' seribu '||basecurr_triple_number_words(to_number(kel_3));
elsif kel_2 ='001' and kel_3 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||' seribu '||basecurr_triple_number_words(kel_3);
elsif kel_2 <>'000' and kel_2 <>'001' and kel_3 <>'000' or kel_3='000' then
hasil:=basecurr_triple_number_words(kel_1)||' juta '||basecurr_triple_number_words(kel_2)||' ribu '||basecurr_triple_number_words(kel_3);
end if;
end;
end if;
end;
elsif length(tampung99)=12 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
if kel_1 <>'000' and kel_2 <>'000' then
begin
if kel_3 <>'000' and kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_3)||' ribu '||basecurr_triple_number_words(kel_4);
elsif kel_3 ='000' and kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_4);
elsif kel_3 ='000' and kel_4='000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_4);
elsif kel_3='001' and kel_4='000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||' seribu ';
elsif kel_3='001' and kel_4 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||' seribu '||basecurr_triple_number_words(kel_4);
elsif kel_3 <>'000' and kel_3 ='001' and kel_4 <>'000' or kel_4='000' then
hasil :=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_2)||' juta '||basecurr_triple_number_words(kel_3)||' ribu '||basecurr_triple_number_words(kel_4);
end if;
end;
elsif kel_1 <>'000' and kel_2='000' then
begin
if kel_3 ='000' and kel_4='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_4);
elsif kel_3 <>'001' or kel_3 <>'000' and kel_4 ='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' milyar '||basecurr_triple_number_words(kel_3)||' ribu '||basecurr_triple_number_words(kel_4);
end if;
end;
end if;
end;
elsif length(tampung99)=15 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
kel_5:=substr(tampung99,13,3);
if kel_1 <>'000' and kel_2 <>'000' and kel_3 <>'000' then
begin
if kel_4 <>'000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4='001' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||' seribu ';
elsif kel_4='001' and kel_5 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||' seribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 <>'000' and kel_4 ='001' and kel_5 <>'000' or kel_5='000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>'000' and kel_2 <>'000' and kel_3 ='000' then
begin
if kel_4 <>'000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_5);
elsif kel_4='001' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||' seribu ';
elsif kel_4='001' and kel_5 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||' seribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 <>'000' and kel_4 ='001' and kel_5 <>'000' or kel_5='000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_2)||' milyar '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>'000' and kel_2 ='000' and kel_3 <>'000' then
begin
if kel_4 <>'000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4 ='000' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_5);
elsif kel_4='001' and kel_5='000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||' seribu ';
elsif kel_4='001' and kel_5 <>'000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||' seribu '||basecurr_triple_number_words(kel_5);
elsif kel_4 <>'000' and kel_4 ='001' and kel_5 <>'000' or kel_5='000' then
hasil :=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_3)||' juta '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>'000' and kel_2='000' and kel_3='000' then
begin
if kel_3 ='000' and kel_4='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_5);
elsif kel_3 <>'001' or kel_3 <>'000' and kel_4 ='000' or kel_4 <>'000' then
hasil:=basecurr_triple_number_words(kel_1)||' trilyun '||basecurr_triple_number_words(kel_4)||' ribu '||basecurr_triple_number_words(kel_5);
end if;
end;
end if;
end;
end if;
return(hasil);
end;
/
Thursday, February 2, 2012
Menggunakan perintah Find
Berikut ini contoh menggunakan perintah find di kombinasi dengan ctime dan cmin :
Mencari file yang dibuat di atas 2 hari yang lalu
-----------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -ctime +2 -exec ls -l {} \;
-rw-r----- 1 root root 1 Feb 22 18:50 ./OIC.txt
-rw-r--r-- 1 root root 756637 Aug 16 2009 ./rep/CL01CHNMSAPP.txt
Mencari file yang dibuat di atas 2 menit yang lalu
-----------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -cmin +2 -exec ls -l {} \;
-rw-r----- 1 root root 1 Feb 22 18:50 ./OIC.txt
-rw-r--r-- 1 root root 756637 Aug 16 2009 ./rep/CL01CHNMSAPP.txt
-rw-r--r-- 1 root root 5906 Jun 18 2010 ./sanlun_lun_show-vp.old.txt
-rwxr-xr-x 1 root root 13 Apr 28 2009 ./Desktop/rpm/link rpm.txt
Mencari file yang dibuat dimulai dari sekarang sampai 2 hari yang lalu
--------------------------------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -ctime -2 -exec ls -l {} \;
Mencari file yang dibuat dimulai dari sekarang sampai 2 menit yang lalu
--------------------------------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -cmin -2 -exec ls -l {} \;
Referensi : Dokumentasi Linux
Mencari file yang dibuat di atas 2 hari yang lalu
-----------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -ctime +2 -exec ls -l {} \;
-rw-r----- 1 root root 1 Feb 22 18:50 ./OIC.txt
-rw-r--r-- 1 root root 756637 Aug 16 2009 ./rep/CL01CHNMSAPP.txt
Mencari file yang dibuat di atas 2 menit yang lalu
-----------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -cmin +2 -exec ls -l {} \;
-rw-r----- 1 root root 1 Feb 22 18:50 ./OIC.txt
-rw-r--r-- 1 root root 756637 Aug 16 2009 ./rep/CL01CHNMSAPP.txt
-rw-r--r-- 1 root root 5906 Jun 18 2010 ./sanlun_lun_show-vp.old.txt
-rwxr-xr-x 1 root root 13 Apr 28 2009 ./Desktop/rpm/link rpm.txt
Mencari file yang dibuat dimulai dari sekarang sampai 2 hari yang lalu
--------------------------------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -ctime -2 -exec ls -l {} \;
Mencari file yang dibuat dimulai dari sekarang sampai 2 menit yang lalu
--------------------------------------------------------------------------
[root@cl01nodsq ~]# find . -name '*.txt' -cmin -2 -exec ls -l {} \;
Referensi : Dokumentasi Linux
Backup File dengan Menggunakan Tar
Berikut ini beberapa contoh backup data menggunakan tar utility :
Backup Data
--------------
tar -pczf bkp_archive_20110722.tar.gz archive/
Restore Data
--------------
tar xvfz bkp_archive_20110722.tar.gz
Backup data (Menghindari error 'remove leading "/" from members' )
----------------------------------------------------------------------------
tar -czPf etc.tgz /etc
Restore Data (Menghindari error 'remove leading "/" from members' )
-----------------------------------------------------------------------------
tar -xzPf bebek.tar.gz => extract dan replace direktori backup (di contoh ini backup direktorinya adalah /bebek )
Melihat isi file dengan extention .tar
----------------------------------------------
$ tar -tvf file.tar
Melihat isi file dengan extention tar.gz
---------------------------------------------
$ tar -ztvf file.tar.gz
Referensi : Dokumentasi Linux
Backup Data
--------------
tar -pczf bkp_archive_20110722.tar.gz archive/
Restore Data
--------------
tar xvfz bkp_archive_20110722.tar.gz
Backup data (Menghindari error 'remove leading "/" from members' )
----------------------------------------------------------------------------
tar -czPf etc.tgz /etc
Restore Data (Menghindari error 'remove leading "/" from members' )
-----------------------------------------------------------------------------
tar -xzPf bebek.tar.gz => extract dan replace direktori backup (di contoh ini backup direktorinya adalah /bebek )
Melihat isi file dengan extention .tar
----------------------------------------------
$ tar -tvf file.tar
Melihat isi file dengan extention tar.gz
---------------------------------------------
$ tar -ztvf file.tar.gz
Referensi : Dokumentasi Linux
Mengubah Nilai Maxdatafiles di Oracle RAC
Terkadang saat kita install oracle database kita sering lupa membiarkan nilai MAXDATAFILES ke nilai default yang mana nilainya adalah 1024 , klo databasenya sizenya kecil mungkin tidak ada masalah , tapi bagaimana kalau databasenya berukuran sangat besar (yang jumlah datafilenya melebihi 1024) ? tentu kita akan mendapatkan error ketika ingin menmbahkan datafile di database di karenakan batas maximum jumlah datafiles yang boleh di buat sudah tercapai .
Nah untuk mengganti nilai MAXDATAFILES ini kita harus membuat ulang controlfilenya , berikut ini adalah step-stepnya untuk mengganti nilai default dari MAXDATAFILES. disini nilainya saya ganti menjadi 20000.
Step-step create RAC controlfile secara urut (jangan lupa ganti db_files=20000)
--------------------------------------------------------------------------------------------
1. create pfile => create pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
2. Edit init99.ora => ganti cluster_database=true menjadi cluster_database=false
3. shutdown database RAC => srvctl stop database -d nama_database
4. backup redolog dan controlfile => copy ke direktory lain atau di rename
5. start db dari node 1 dengan menggunakan pfile => startup nomount pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
6 execute controlfile_script => @controlfile_script
7 recover database => jalankan step2 (lihat bagian bawah )
8 add logfile untuk thread berikutnya => jalankan step 3 (lihat bagian bawah )
9 open db resetlogs dan enable thread => jalankan step 4 (lihat bagian bawah)
10 add temp tablespace => jalankan step 5 (lihat bagian bawah)
11 shutdown immediate database => shutdown immediate
12 edit init99.ora cluster_database=false menjadi cluster_database=true
13 startup mount pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
14 create spfile => create spfile='/oradata1/ORCLdb/spfileORCLdb.ora' from pfile='$ORACLE_HOME/dbs/init99.ora' ;
15 shutdown database => shutdwon immediate
16 startup RAC DB => srvctl start database -d nama_database
controlfile_script
-----------------------
---STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 320
MAXLOGMEMBERS 5
MAXDATAFILES 20000
MAXINSTANCES 32
MAXLOGHISTORY 1019
LOGFILE
GROUP 1 (
'/oraredo1/ORCL/redo01a.log',
'/oraredo2/ORCLdb/redo01b.log',
'/oraredo3/ORCLdb/redo01c.log'
) SIZE 4096M BLOCKSIZE 512,
GROUP 2 (
'/oraredo1/ORCLdb/redo02a.log',
'/oraredo2/ORCLdb/redo02b.log',
'/oraredo3/ORCLdb/redo02c.log'
) SIZE 4096M BLOCKSIZE 512,
GROUP 3 (
'/oraredo1/ORCLdb/redo03a.log',
'/oraredo2/ORCLdb/redo03b.log',
'/oraredo3/ORCLdb/redo03c.log'
) SIZE 4096M BLOCKSIZE 512
DATAFILE
'/oradata1/ORCLdb/system01.dbf',
'/oradata1/ORCLdb/sysaux01.dbf',
'/oradata1/ORCLdb/undotbs01.dbf',
'/oradata1/ORCLdb/users01.dbf',
'/oradata2/ORCLdb/undotbs02.dbf',
'/oradata3/ORCLdb/undotbs03.dbf',
'/oradata2/ORCLdb/ods_log_01.dbf',
'/oradata2/ORCLdb/ods_apph_01.dbf',
'/oradata2/ORCLdb/ods_dim_01.dbf',
'/oradata1/ORCLdb/undotbs01_01.dbf',
'/oradata2/ORCLdb/undotbs02_01.dbf',
'/oradata3/ORCLdb/undotbs03_01.dbf',
'/oradata1/ORCLdb/undotbs01_02.dbf',
'/oradata2/ORCLdb/undotbs02_02.dbf',
'/oradata3/ORCLdb/undotbs03_02.dbf',
........................................
'/oradata6/ORCLdb/data_147.dbf',
'/oradata6/odsrepdb/data_148.dbf'
CHARACTER SET WE8MSWIN1252
;
Step ke 2
---------------
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
CANCEL
Step ke 3
-----------------
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 (
'/oraredo1/ORCLdb/redo04a.log',
'/oraredo2/ORCLdb/redo04b.log',
'/oraredo3/ORCLdb/redo04c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 5 (
'/oraredo1/ORCLdb/redo05a.log',
'/oraredo2/ORCLdb/redo05b.log',
'/oraredo3/ORCLdb/redo05c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 6 (
'/oraredo1/ORCLdb/redo06a.log',
'/oraredo2/ORCLdb/redo06b.log',
'/oraredo3/ORCLdb/redo06c.log'
) SIZE 4096M BLOCKSIZE 512 ;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 7 (
'/oraredo1/ORCLdb/redo07a.log',
'/oraredo2/ORCLdb/redo07b.log',
'/oraredo3/ORCLdb/redo07c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 8 (
'/oraredo1/ORCLdb/redo08a.log',
'/oraredo2/ORCLdb/redo08b.log',
'/oraredo3/ORCLdb/redo08c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 9 (
'/oraredo1/ORCLdb/redo09a.log',
'/oraredo2/ORCLdb/redo09b.log',
'/oraredo3/ORCLdb/redo09c.log'
) SIZE 4096M BLOCKSIZE 512 ;
Step Ke 4
-----------------------------
ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE ENABLE PUBLIC THREAD 3;
Step Ke 5
-------------------
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/ORCLdb/temp01.dbf'
SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/ORCLdb/temp02.dbf'
SIZE 20480M REUSE AUTOEXTEND OFF;
Author : Ida Bagus Enderajana
Referensi : Dokumentasi Oracle
Nah untuk mengganti nilai MAXDATAFILES ini kita harus membuat ulang controlfilenya , berikut ini adalah step-stepnya untuk mengganti nilai default dari MAXDATAFILES. disini nilainya saya ganti menjadi 20000.
Step-step create RAC controlfile secara urut (jangan lupa ganti db_files=20000)
--------------------------------------------------------------------------------------------
1. create pfile => create pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
2. Edit init99.ora => ganti cluster_database=true menjadi cluster_database=false
3. shutdown database RAC => srvctl stop database -d nama_database
4. backup redolog dan controlfile => copy ke direktory lain atau di rename
5. start db dari node 1 dengan menggunakan pfile => startup nomount pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
6 execute controlfile_script => @controlfile_script
7 recover database => jalankan step2 (lihat bagian bawah )
8 add logfile untuk thread berikutnya => jalankan step 3 (lihat bagian bawah )
9 open db resetlogs dan enable thread => jalankan step 4 (lihat bagian bawah)
10 add temp tablespace => jalankan step 5 (lihat bagian bawah)
11 shutdown immediate database => shutdown immediate
12 edit init99.ora cluster_database=false menjadi cluster_database=true
13 startup mount pfile='$ORACLE_HOME/dbs/init99.ora' from spfile;
14 create spfile => create spfile='/oradata1/ORCLdb/spfileORCLdb.ora' from pfile='$ORACLE_HOME/dbs/init99.ora' ;
15 shutdown database => shutdwon immediate
16 startup RAC DB => srvctl start database -d nama_database
controlfile_script
-----------------------
---STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 320
MAXLOGMEMBERS 5
MAXDATAFILES 20000
MAXINSTANCES 32
MAXLOGHISTORY 1019
LOGFILE
GROUP 1 (
'/oraredo1/ORCL/redo01a.log',
'/oraredo2/ORCLdb/redo01b.log',
'/oraredo3/ORCLdb/redo01c.log'
) SIZE 4096M BLOCKSIZE 512,
GROUP 2 (
'/oraredo1/ORCLdb/redo02a.log',
'/oraredo2/ORCLdb/redo02b.log',
'/oraredo3/ORCLdb/redo02c.log'
) SIZE 4096M BLOCKSIZE 512,
GROUP 3 (
'/oraredo1/ORCLdb/redo03a.log',
'/oraredo2/ORCLdb/redo03b.log',
'/oraredo3/ORCLdb/redo03c.log'
) SIZE 4096M BLOCKSIZE 512
DATAFILE
'/oradata1/ORCLdb/system01.dbf',
'/oradata1/ORCLdb/sysaux01.dbf',
'/oradata1/ORCLdb/undotbs01.dbf',
'/oradata1/ORCLdb/users01.dbf',
'/oradata2/ORCLdb/undotbs02.dbf',
'/oradata3/ORCLdb/undotbs03.dbf',
'/oradata2/ORCLdb/ods_log_01.dbf',
'/oradata2/ORCLdb/ods_apph_01.dbf',
'/oradata2/ORCLdb/ods_dim_01.dbf',
'/oradata1/ORCLdb/undotbs01_01.dbf',
'/oradata2/ORCLdb/undotbs02_01.dbf',
'/oradata3/ORCLdb/undotbs03_01.dbf',
'/oradata1/ORCLdb/undotbs01_02.dbf',
'/oradata2/ORCLdb/undotbs02_02.dbf',
'/oradata3/ORCLdb/undotbs03_02.dbf',
........................................
'/oradata6/ORCLdb/data_147.dbf',
'/oradata6/odsrepdb/data_148.dbf'
CHARACTER SET WE8MSWIN1252
;
Step ke 2
---------------
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
CANCEL
Step ke 3
-----------------
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 (
'/oraredo1/ORCLdb/redo04a.log',
'/oraredo2/ORCLdb/redo04b.log',
'/oraredo3/ORCLdb/redo04c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 5 (
'/oraredo1/ORCLdb/redo05a.log',
'/oraredo2/ORCLdb/redo05b.log',
'/oraredo3/ORCLdb/redo05c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 6 (
'/oraredo1/ORCLdb/redo06a.log',
'/oraredo2/ORCLdb/redo06b.log',
'/oraredo3/ORCLdb/redo06c.log'
) SIZE 4096M BLOCKSIZE 512 ;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 7 (
'/oraredo1/ORCLdb/redo07a.log',
'/oraredo2/ORCLdb/redo07b.log',
'/oraredo3/ORCLdb/redo07c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 8 (
'/oraredo1/ORCLdb/redo08a.log',
'/oraredo2/ORCLdb/redo08b.log',
'/oraredo3/ORCLdb/redo08c.log'
) SIZE 4096M BLOCKSIZE 512 ,
GROUP 9 (
'/oraredo1/ORCLdb/redo09a.log',
'/oraredo2/ORCLdb/redo09b.log',
'/oraredo3/ORCLdb/redo09c.log'
) SIZE 4096M BLOCKSIZE 512 ;
Step Ke 4
-----------------------------
ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE ENABLE PUBLIC THREAD 3;
Step Ke 5
-------------------
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/ORCLdb/temp01.dbf'
SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/ORCLdb/temp02.dbf'
SIZE 20480M REUSE AUTOEXTEND OFF;
Author : Ida Bagus Enderajana
Referensi : Dokumentasi Oracle
Oradebug
Jika kita ingin menganalisa performance dan eksekusi plan dari SQL atau PL/SQL yang kita jalankan kita bisa menggunakan ORADEBUG utility untuk mendebug statement SQL yang kita jalankan .
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG UNLIMIT
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8
Statement processed.
SQL> SELECT ... /* jalankan SQL staement yang ingin dianalisa . . . tunggu beberapa menit */
SQL> ORADEBUG TRACEFILE_NAME /* Perintah ini untuk mengetahui nama tracfile yang di generate oleh ORADEBUG */
/u01/app/admin/ORCL/udmp/ORCL_ora_24953.trc
#Disable trace ORADEBUG
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
Statement processed.
#Setelah itu kita bisa format file trace dengan menggunakan utility tkprof
#Note : Untuk menggunakan oradebug harus login sebagai sys atau user yang punya role sysdba
contoh diatas hanyalah salah satu contoh fungsi dari utility oradebug
untuk melihat comand-comand apa saya yang bisa di pakai bisa di lihat di helpnya sbb :
SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
DUMP <dump_name> <lvl> [addr] Invoke named dump DUMPSGA [bytes]
Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variableSETVAR
<p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix (return outputSETINST
<instance# .. | all> Set instance list in double quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double
quotesDMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double
quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS <ifname> <ofname> Helps translate PCs to names
WATCH <address> <len> <self|exist|all|target> Watch a region of memoryDELETE
<local|global|target> watchpoint <id> Delete a watchpoint
SHOW <local|global|target> watchpoints Show watchpoints
CORE Dump core without crashing process
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
SQL>
Referensi : Oradebug Documentation
SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG UNLIMIT
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8
Statement processed.
SQL> SELECT ... /* jalankan SQL staement yang ingin dianalisa . . . tunggu beberapa menit */
SQL> ORADEBUG TRACEFILE_NAME /* Perintah ini untuk mengetahui nama tracfile yang di generate oleh ORADEBUG */
/u01/app/admin/ORCL/udmp/ORCL_ora_24953.trc
#Disable trace ORADEBUG
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
Statement processed.
#Setelah itu kita bisa format file trace dengan menggunakan utility tkprof
#Note : Untuk menggunakan oradebug harus login sebagai sys atau user yang punya role sysdba
contoh diatas hanyalah salah satu contoh fungsi dari utility oradebug
untuk melihat comand-comand apa saya yang bisa di pakai bisa di lihat di helpnya sbb :
SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
DUMP <dump_name> <lvl> [addr] Invoke named dump DUMPSGA [bytes]
Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variableSETVAR
<p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix (return outputSETINST
<instance# .. | all> Set instance list in double quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double
quotesDMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double
quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS <ifname> <ofname> Helps translate PCs to names
WATCH <address> <len> <self|exist|all|target> Watch a region of memoryDELETE
<local|global|target> watchpoint <id> Delete a watchpoint
SHOW <local|global|target> watchpoints Show watchpoints
CORE Dump core without crashing process
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
SQL>
Referensi : Oradebug Documentation
Recover Datafile
Bagaimana cara merecover datafile yang corrupt atau terhapus secara tidak sengaja ?
Disini saya simulasikan datafile milik tablespace COBA di delete, berikut ini step-stepnya :
# rm /export/home/oracle/tempfile/coba02.dbf
#su - oracle
# login ke database sebagai user sys
$ sqlplus "/as yssdba"
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
COBA ========> tablespace yang datafilenya di delete
7 rows selected.
# offline tablespace COBA =====> muncul error dibawah
SQL> alter tablespace coba offline;
alter tablespace coba offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/export/home/oracle/tempfile/coba02.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
#shutdown database normal ======> masih tetap error
SQL> shutdown immediate;
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/export/home/oracle/tempfile/coba02.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
# Shutdown Abort
SQL> shutdown abort;
ORACLE instance shut down.
# Setelah database shutdown ,database harus di startup on exclusive mode
SQL> startup exclusive mount;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
#Perintah dibawah ini membuat datafile baru untuk tablespace COBA
SQL> alter database create datafile '/export/home/oracle/tempfile/coba02.dbf' as '/export/home/oracle/tempfile/coba02.dbf';
Database altered.
#Setelah perintah berhasil, shutdown database
SQL> shutdown ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
#Startup mount database untuk proses recovery
SQL> startup mount;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
#Cek datafile mana saja yang memerlukan recovery
SQL> select file# from v$recover_file; (cek nomor file yang perlu di recover )
SQL> select name from v$datafile where file#=nomor_file ; (hasil dari cek nomor file )
#Datafile yang baru dibuat harus di recover karena dianggap sebagai file yang rusak dengan command dibawah ini
SQL> recover datafile '/export/home/oracle/tempfile/coba02.dbf';
Atau
SQL> recover datafile nomor_file;
Media recovery complete.
SQL> alter database open;
Database altered.
#Recovery Sukses
Referensi : Oracle Documentation
Disini saya simulasikan datafile milik tablespace COBA di delete, berikut ini step-stepnya :
# rm /export/home/oracle/tempfile/coba02.dbf
#su - oracle
# login ke database sebagai user sys
$ sqlplus "/as yssdba"
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
COBA ========> tablespace yang datafilenya di delete
7 rows selected.
# offline tablespace COBA =====> muncul error dibawah
SQL> alter tablespace coba offline;
alter tablespace coba offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/export/home/oracle/tempfile/coba02.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
#shutdown database normal ======> masih tetap error
SQL> shutdown immediate;
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/export/home/oracle/tempfile/coba02.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
# Shutdown Abort
SQL> shutdown abort;
ORACLE instance shut down.
# Setelah database shutdown ,database harus di startup on exclusive mode
SQL> startup exclusive mount;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
#Perintah dibawah ini membuat datafile baru untuk tablespace COBA
SQL> alter database create datafile '/export/home/oracle/tempfile/coba02.dbf' as '/export/home/oracle/tempfile/coba02.dbf';
Database altered.
#Setelah perintah berhasil, shutdown database
SQL> shutdown ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
#Startup mount database untuk proses recovery
SQL> startup mount;
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 1281936 bytes
Variable Size 176160880 bytes
Database Buffers 478150656 bytes
Redo Buffers 2912256 bytes
Database mounted.
#Cek datafile mana saja yang memerlukan recovery
SQL> select file# from v$recover_file; (cek nomor file yang perlu di recover )
SQL> select name from v$datafile where file#=nomor_file ; (hasil dari cek nomor file )
#Datafile yang baru dibuat harus di recover karena dianggap sebagai file yang rusak dengan command dibawah ini
SQL> recover datafile '/export/home/oracle/tempfile/coba02.dbf';
Atau
SQL> recover datafile nomor_file;
Media recovery complete.
SQL> alter database open;
Database altered.
#Recovery Sukses
Referensi : Oracle Documentation
Subscribe to:
Posts (Atom)