Month: June 2011

Insert into view with trigger


[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
SYSTEM@valeh> insert into emp_list values (1,'Valeh');
SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
SYSTEM@valeh> insert into emp_age values (1,1,26);
SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
instead of insert on emp_view
referencing new as new old as old
declare
v_id VARCHAR(20);
begin
if :new.id is not null then
insert into emp_list values (:new.id,:new.name);
insert into emp_age values (:new.age_id,:new.emp_id,:new.emp_age)
returning :new.id into v_id;
else
raise_application_error (-20999, 'Cannot create employee without name');
end if;
end;

SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

[/pcsh]

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
SYSTEM@valeh> insert into emp_list values (1,'Valeh');
SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
SYSTEM@valeh> insert into emp_age values (1,1,26);
SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
instead of insert on emp_view
referencing new as new old as old
declare
v_id VARCHAR(20);
begin
if :new.id is not null then
insert into emp_list values (:new.id,:new.name);
insert into emp_age values (:new.age_id,:new.emp_id,:new.emp_age)
returning :new.id into v_id;
else
raise_application_error (-20999, 'Cannot create employee without name');
end if;
end;

SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

[/pcsh]

2 cədvəlin birləşməsi olan VİEW-ya trigger vasitəsi ilə insert


SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
 SYSTEM@valeh> insert into emp_list values (1,'Valeh');
 SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
 SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
 instead of insert on emp_view
 referencing new as new old as old
 declare
 v_id VARCHAR(20);
 begin
 if :new.id is not null then
 insert into emp_list (id,name) values (:new.id,:new.name);
 insert into emp_age (age_id,emp_id,emp_age) values (:new.age_id,:new.emp_id,:new.emp_age)
 returning :new.id into v_id;
 else
 raise_application_error (-20999, 'Cannot create employee without name');
 end if;
 end;
 
SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

SQL*Loader-dən istifadə edərək məlumatlari fayldan cədvələ yukləmək


1)test üçün aşağıdakı formada valeh.dat faylını yaradırıq, bunlardan 3, 5,7,9 xətalı sətirlərdir

1;Valeh;10
2;Taleh;25
3xetali35
4;samir;4
5xetali8
6;Mammad;1
7xetali21
8;Elmir;45
9xetali37
10;Zaur;71


2)SYSTEM User-i ile daxil olub loader adlı cədvəl yaradırıq

create table loader (
id          number,
AD          varchar2(20),
DEYER       number);

3) /u01/ direktoriyasında loader.ctl  adlı bir fayl yaradırıq ve içinəaşağıdakı məlumatları daxil edirik.

load data
infile '/u01/valeh.dat'
discardfile 'load_3.dsc'
insert
into table system.loader
fields terminated by ';'
(
id,
AD,
DEYER
)


4)daha sonra aşağıdakı sətri icra edirik

[oracle@localhost ~]$ sqlldr system/valeh control=/u01/loader.ctl log=/u01/loader.log
Commit point reached – logical record count 10

5)select edirik ki görək məlumatlarımız insert olunub yoxsa yox

SYSTEM@valeh> select * from loader;

ID AD                        DEYER
---------- -------------------- ----------
1 Valeh                        10
2 Taleh                        25
4 samir                        4
6 Mammad                       1
8 Elmir                        45
10 Zaur                        71

6 rows selected.

DBCA-dan istifadə etmədən bazanın yaradılması (minimal üsul)


1)Yeni yaranacaq bazanınORACLE_SID-ni təyin edirik.

export ORACLE_SID=test
export ORACLE_HOME=/u01/oracle/product/10.2.0/db_1/

2) Aşağıdakı əmri icra edirik və  faylın içinə ashağıdakı məlumatları yazıb minimal init.ora faylınıı yaradırıq.

vi $ORACLE_HOME/dbs/inittest.ora

control_files = (/u01/testcontrol1.ctl,/u01/testcontrol2.ctl,/u01/testcontrol3.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = test
db_block_size = 8192
sga_max_size = 440401920
sga_target = 440401920

3.Password faylını yaradırıq

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwdtest.ora password=test entries=5

4. instance-ni NOMOUNT olaraq START edirik

sqlplus / as sysdba

startup nomount

5) Bazanı yaradırıq

create database test
logfile group 1 (‘/u01/testredo1.log’) size 50M,
group 2 (‘/u01/testredo2.log’) size 50M,
group 3 (‘/u01/testredo3.log’) size 50M
character set WE8ISO8859P1
national character set utf8
datafile ‘/u01/system.dbf’ size 500M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile ‘/u01/sysaux.dbf’ size 100M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile ‘/u01/undotbs1.dbf’ size 100M
default temporary tablespace temp tempfile ‘/u01/temp01.dbf’ size 100M;

6.catalog ve catproc  fayllarını işlədirik.

SYS@test>@?/rdbms/admin/catalog.sql
SYS@test>@?/rdbms/admin/catproc.sql

7.  Və sonda şifrələri dəyişirik

SYS@test>alter user sys identified by valeh;
SYS@test>alter user system identified by valeh;

 

8)Bazamızın adını və statusunu select edib çıxarıdırıq.

SYS@test> shutdown force;
SYS@test> select name from v$database;

NAME
———
TEST

SYS@test> select status from v$instance;

STATUS
————
OPEN

Saving Archive logs in different destinations and changing log_archive_format


[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYS@valeh> alter system set log_archive_dest_2 = 'LOCATION=/u01/';
SYS@valeh> alter system set log_archive_dest_2 = 'LOCATION=/tmp/';
SYS@valeh> SHOW PARAMETER LOG_ARCHIVE_FORMAT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf

LOG_ARCHIVE_FORMAT-a ashaqidaki parametrleri elave edirik

alter system set LOG_ARCHIVE_FORMAT='log%a_%d_%T_%S_%t_%s_%r.arc' scope=spfile;

SYS@valeh> startup force;

SYS@valeh> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      log%a_%d_%T_%S_%t_%s_%r.arc

SYS@valeh> alter system checkpoint;
SYS@valeh> alter system checkpoint;

SYS@valeh> SELECT NAME FROM V$ARCHIVED_LOG;
/u01/log97b822e4_97b801e4_0001_0000000038_1_38_751844965.arc
/tmp/log97b822e4_97b801e4_0001_0000000038_1_38_751844965.arc

[/pcsh]

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYS@valeh> alter system set log_archive_dest_2 = 'LOCATION=/u01/';
SYS@valeh> alter system set log_archive_dest_2 = 'LOCATION=/tmp/';
SYS@valeh> SHOW PARAMETER LOG_ARCHIVE_FORMAT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf

LOG_ARCHIVE_FORMAT-a ashaqidaki parametrleri elave edirik

alter system set LOG_ARCHIVE_FORMAT='log%a_%d_%T_%S_%t_%s_%r.arc' scope=spfile;

SYS@valeh> startup force;

SYS@valeh> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      log%a_%d_%T_%S_%t_%s_%r.arc

SYS@valeh> alter system checkpoint;
SYS@valeh> alter system checkpoint;

SYS@valeh> SELECT NAME FROM V$ARCHIVED_LOG;
/u01/log97b822e4_97b801e4_0001_0000000038_1_38_751844965.arc
/tmp/log97b822e4_97b801e4_0001_0000000038_1_38_751844965.arc

[/pcsh]

Arxiv logların muxtəlif yerlərdə saxlanması və log_archive_formatın dəyişdirilməsi.


SYS@valeh> alter system set log_archive_dest_2 = ‘LOCATION=/u01/’;
SYS@valeh> alter system set log_archive_dest_2 = ‘LOCATION=/tmp/’;SYS@valeh> SHOW PARAMETER LOG_ARCHIVE_FORMAT;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_format                   string      %t_%s_%r.dbf

LOG_ARCHIVE_FORMAT-a ashaqidaki parametrleri elave edirik

alter system set LOG_ARCHIVE_FORMAT=’log%a_%d_%T_%S_%t_%s_%r.arc’ scope=spfile;

SYS@valeh> startup force;

SYS@valeh> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_format                   string      log%a_%d_%T_%S_%t_%s_%r.arc

SYS@valeh> alter system checkpoint;
SYS@valeh> alter system checkpoint;

SYS@valeh> SELECT NAME FROM V$ARCHIVED_LOG;
/u01/log97b822e4_97b801e4_0001_0000000038_1_38_751844965.arc
/tmp/log97b822e4_97b801e4_0001_0000000038_1_38_751844965.arc