默認(rèn)情況下，oracle的分區(qū)表對(duì)于分區(qū)字段是不允許進(jìn)行update操作的，如果有對(duì)分區(qū)字段行進(jìn)update，就會(huì)報(bào)錯(cuò)——ORA-14402: 更新分區(qū)關(guān)鍵字列將導(dǎo)致分區(qū)的更改。但是可以通過打開表的row movement屬性來允許對(duì)分區(qū)字段的update操作。
我們提供的服務(wù)有：成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、前郭ssl等。為上千余家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的前郭網(wǎng)站制作公司
例：創(chuàng)建分區(qū)表test_part進(jìn)行實(shí)驗(yàn)
create table TEST_PART
(
A1 NUMBERnot null,
A2 DATE not null,
A3 VARCHAR2(6) not null,
A4 DATE not null,
A5 NUMBER not null,
)
partition by range (A1)
(
partition P1 values less than (1000),
partition P2 values less than (2000),
partition P3 values less than (3000),
partition P4 values less than (4000),
partition P5 values less than (5000),
partition P6 values less than (MAXVALUE)
);
插入如下的數(shù)據(jù)
SQL select * from test_part;
A1 A2 A3 A4 A5
---------- ----------- ------ ----------- ----------
123 2006-06-30 123456 2006-06-30 123
456 2006-06-30 asdfgh 2006-06-30 456
1 2006-06-30 234123 2006-06-30 1
2 2006-06-30 234234 2006-06-30 2
1234 2006-06-30 456789 2006-06-30 1234
1111 2006-06-30 ewrqwe 2006-06-30 1111
2222 2006-06-30 fdafda 2006-06-30 2222
3333 2006-06-30 342342 2006-06-30 3333
5678 2006-06-30 qwerty 2006-06-30 5678
9 rows selected
分區(qū)P1、P2的數(shù)據(jù)分別為：
SQL select rowid,t.* from test_part partition(p1) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLoAAGAAAtsEAAB 456 2006-06-30 asdfgh 2006-06-30 456
AAAGLoAAGAAAtsEAAC 1 2006-06-30 234123 2006-06-30 1
AAAGLoAAGAAAtsEAAD 2 2006-06-30 234234 2006-06-30 2
AAAGLoAAGAAAtsEAAE 123 2006-06-30 123456 2006-06-30 123
SQL select rowid,t.* from test_part partition(p2) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
直接update提示錯(cuò)誤
SQL update test_part set a1=1123 where a1=123;
update test_part set a1=1123 where a1=123
ORA-14402: 更新分區(qū)關(guān)鍵字列將導(dǎo)致分區(qū)的更改
打開row movement屬性
SQL alter table test_part enable row movement;
Table altered
再次執(zhí)行update操作
SQL update test_part set a1=1123 where a1=123;
1 row updated
執(zhí)行是成功的并遷移到分區(qū)P2上了，且這時(shí)候rowid也發(fā)生了變化
SQL select rowid,t.* from test_part partition(p2) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
AAAGLwAAGAAA+8PAAB 1123 2006-06-30 123456 2006-06-30 123
SQL
enable row movement可以允許數(shù)據(jù)段的壓縮、update分區(qū)字段的數(shù)據(jù)（跨分區(qū)的）
通過Update語句將A表的某列值修改為B表中的指定列的值時(shí)，所用的sql語句在Oracle和SQL Server中是不一樣的，
代碼如下：
Oracle中的語句：
UPDATE A
SET A.COL1=(SELECT B.COL1 from B where A.KEY=B.KEY [and 其它限制(可選)] )
where 其它條件
SQL Server中更新語句：
update A
SET A.COL1=B.COL1
FROM A,B where A.KEY=B.KEY where 其它條件限制
若關(guān)聯(lián)條件為多個(gè)，where后面用and 連接指定條件即可
oracle定時(shí)更新(JOBS)
rm.pro_hrm_emp_info_job過程
需要每天都更新，所以先創(chuàng)建個(gè)procedure，再創(chuàng)建個(gè)job就可以實(shí)現(xiàn)定時(shí)更新
create or replace procedure rm.pro_hrm_emp_info_job is
zm
begin
update rm.hrm_emp_info
set ages = to_number(TRUNC(nvl((sysdate brith_date) / 365, 0))),
leng_serv = to_number(TRUNC(nvl((sysdate work_date) / 365, 0))),
ins_len_ser = to_number(TRUNC(nvl((sysdate ente_appe_date) / 365,
0)));
commit;
end pro_hrm_emp_info_job;
相應(yīng)的`job
declare
jobno number;
begin
dbms_job.submit(jobno,’rm.pro_hrm_emp_info_job;’,trunc(sysdate),’trunc(sysdate)+1’);每天零點(diǎn)更新
commit;
end;
/
分解下job
dbms_job.submit(jobno, job號(hào)，由sys.jobseq生成
’rm.pro_hrm_emp_info_job;’, 要執(zhí)行的procedure
trunc(sysdate), 下次要執(zhí)行的時(shí)間
’trunc(sysdate)+1’); 每次間隔的時(shí)間，此處的1標(biāo)識(shí)一天 也可1/1440每一分鐘執(zhí)行一次
查看
select * from sys.user_jobs;
其中創(chuàng)建的jobno(任務(wù)號(hào))由sys.jobseq序列生成
主由packages中的sys.dbms_job執(zhí)行
細(xì)節(jié)可以查看sys.dbms_job包內(nèi)容。 ;
CentOS 7 64 位 Linux 發(fā)行版。我假設(shè)你已經(jīng)在你的系統(tǒng)上安裝了 Oracle 11g。
這里我會(huì)展示一下安裝 Oracle 11g 時(shí)我的操作步驟。
我在 Oracle 11g 上選擇 “Create and configure a database”，如下圖所示。
然后我選擇安裝 Oracle 11g “Decktop Class”。如果是生產(chǎn)環(huán)境，你必須選擇 “Server Class”。
然后你輸入安裝 Oracle 11g 的各種路徑以及密碼。下面是我自己的 Oracle 11g 安裝配置。確保你正確輸入了 Oracle 的密碼。
下一步，我按照如下設(shè)置 Inventory Directory。
到這里，我已經(jīng)向你展示了我安裝 Oracle 11g 所做的工作，因?yàn)槲覀冮_始想升級(jí)到 12c。
讓我們將 Oracle 11g 升級(jí)到 Oracle 12c 吧。
你需要從該鏈接上下載兩個(gè) zip 文件。下載并解壓兩個(gè)文件到相同目錄。文件名為 linuxamd6412cdatabase_1of2.zip linuxamd6412cdatabase_2of2.zip。提取或解壓完后，它會(huì)創(chuàng)建一個(gè)名為 database 的文件夾。
注意：升級(jí)到 12c 之前，請(qǐng)確保在你的 CentOS 上已經(jīng)安裝了所有必須的軟件包，并且所有的路徑變量也已經(jīng)正確配置，還有其它前提條件也已經(jīng)滿足。
下面是必須使用正確版本安裝的一些軟件包
binutils
compat-libstdc++
gcc
glibc
libaio
libgcc
libstdc++
make
sysstat
unixodbc
在因特網(wǎng)上搜索正確的 rpm 版本。
你也可以用一個(gè)查詢處理多個(gè)軟件包，然后在輸出中查找正確版本。例如，在終端中輸入下面的命令：
rpm -q binutils compat-libstdc++ gcc glibc libaio libgcc libstdc++ make sysstat unixodbc
你的系統(tǒng)中必須安裝了以下軟件包（版本可能或新或舊）
binutils-2.23.52.0.1-12.el7.x86_64
compat-libcap1-1.10-3.el7.x86_64
gcc-4.8.2-3.el7.x86_64
gcc-c++-4.8.2-3.el7.x86_64
glibc-2.17-36.el7.i686
glibc-2.17-36.el7.x86_64
glibc-devel-2.17-36.el7.i686
glibc-devel-2.17-36.el7.x86_64
ksh
libaio-0.3.109-9.el7.i686
libaio-0.3.109-9.el7.x86_64
libaio-devel-0.3.109-9.el7.i686
libaio-devel-0.3.109-9.el7.x86_64
libgcc-4.8.2-3.el7.i686
libgcc-4.8.2-3.el7.x86_64
libstdc++-4.8.2-3.el7.i686
libstdc++-4.8.2-3.el7.x86_64
libstdc++-devel-4.8.2-3.el7.i686
libstdc++-devel-4.8.2-3.el7.x86_64
libXi-1.7.2-1.el7.i686
libXi-1.7.2-1.el7.x86_64
libXtst-1.2.2-1.el7.i686
libXtst-1.2.2-1.el7.x86_64
make-3.82-19.el7.x86_64
sysstat-10.1.5-1.el7.x86_64
你也需要 unixODBC-2.3.1 或更新版本的驅(qū)動(dòng)。
我希望你安裝 Oracle 11g 的時(shí)候已經(jīng)在你的 CentOS 7 上創(chuàng)建了名為 oracle 的用戶。讓我們以用戶 oracle 登錄 CentOS。以用戶 oracle 登錄到 CentOS 之后，在你的 CentOS上打開一個(gè)終端。
使用終端更改工作目錄并導(dǎo)航到你解壓兩個(gè) zip 文件的目錄。在終端中輸入以下命令開始安裝 12c。
./runInstaller
如果一切順利，你會(huì)看到類似下面的截圖，已經(jīng)開始安裝 12c。
然后你可以選擇跳過更新或者下載最近更新。如果是生產(chǎn)服務(wù)器，建議你必須更新。我這里選擇跳過。
現(xiàn)在，選擇升級(jí)現(xiàn)有數(shù)據(jù)庫。
對(duì)于語言，這里已經(jīng)有 English。點(diǎn)擊下一步繼續(xù)，或者你可以根據(jù)你的需要添加語言。
現(xiàn)在，選擇企業(yè)版。你可以根據(jù)你的需求選擇。
然后選擇軟件位置路徑，這些都是不言自明的。
對(duì)于第七步，像下面這樣使用默認(rèn)的選擇繼續(xù)下一步。
在第九步中，你會(huì)看到一個(gè)類似下面這樣的總結(jié)報(bào)告。
如果一切正常，你可以點(diǎn)擊第九步中的 install 開始安裝，進(jìn)入第十步。
其中你可能會(huì)遇到一些錯(cuò)誤，你需要通過谷歌找到這些錯(cuò)誤的解決方法。你可能遇到的問題會(huì)有很多，因此我沒有在這里詳細(xì)介紹。
要有耐心，一步一步走下來最后它會(huì)告訴你成功了。否則，在谷歌上搜索做必要的操作解決問題。再一次說明，由于你可能會(huì)遇到的錯(cuò)誤有很多，我無法在這里提供所有詳細(xì)介紹。
現(xiàn)在，只需要按照下面屏幕指令配置監(jiān)聽器。
數(shù)據(jù)庫升級(jí)助手
配置完監(jiān)聽器之后，它會(huì)啟動(dòng)數(shù)據(jù)庫升級(jí)助手Database Upgrade Assistant。選擇 Upgrade Oracle Database。
在第二步，你會(huì)發(fā)現(xiàn)它顯示了 11g 的位置路徑以及 12c 的位置路徑。同時(shí)你也會(huì)發(fā)現(xiàn)它指示說從原來的 Oracle Home Release 11 安裝 Oracle Home Release 12.點(diǎn)擊下一步進(jìn)入步驟三。
按照屏幕上的說明完成安裝。
在最后一步，你會(huì)看到一個(gè)成功窗口，其中你會(huì)看到成功升級(jí)了 oracle 數(shù)據(jù)庫。
一個(gè)忠告：對(duì)于你的生產(chǎn)服務(wù)器，在升級(jí)到 12c 之前，請(qǐng)確保你已經(jīng)在其它平臺(tái)上測(cè)試過，以便你能修復(fù)升級(jí)過程中遇到的所有錯(cuò)誤。永遠(yuǎn)不要嘗試一無所知的時(shí)候就升級(jí)生產(chǎn)服務(wù)器。
用scott用戶打開兩個(gè)窗口
1、外鍵無索引時(shí)，子表更新外鍵未提交，主表更新非子表引用的主鍵時(shí)被阻塞
會(huì)話1：
create table t1 (x int primary key);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
commit;
create table t2（y int references t1）；
insert into t2 values（1）；
commit;
update t2 set y=2 where y=1;
會(huì)話2：
update t1 set x=4 where x=3; //會(huì)話被阻塞
2、外鍵有索引時(shí)，子表更新外鍵未提交，主表更新非子表引用的主鍵時(shí)不會(huì)被阻塞
會(huì)話1：
create index t2_index on t2(y) ; //創(chuàng)建外鍵索引
update t2 set y=2 where y=1;
會(huì)話2：
update t1 set x=4 where x=3;
已更新 1 行；//可以正常更新
3、外鍵有無索引，對(duì)于子表更新外鍵未提交，主表更新相對(duì)應(yīng)的主鍵無影響，更新主鍵的session都會(huì)被阻塞
會(huì)話1：
update t2 set y=2 where y=1;
會(huì)話2：
update t1 set x=4 where x=1; //更新子表已引用的
會(huì)話被阻塞。
會(huì)話1：
update t2 set y=2 where y=1;
會(huì)話2：
update t1 set x=4 where x=2 ; //更新子表將要引用的
會(huì)話被阻塞。――很好理解，主表要判斷是否違反約束
二、更新子表非外鍵列未提交
1、外鍵無索引，更新主表已被外鍵引用的主鍵時(shí)，更新主鍵的session被阻塞
會(huì)話1：
create table t1 (x int primary key,x1 int);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
commit ;
create table t2(y int references t1,y1 int);
insert into t2 values(1,1);
commit ;
update t2 set y1=2 where y1=1;
會(huì)話2：
update t1 set x=4 where x=1; //更新外鍵引用的主鍵
會(huì)話被阻塞。
2、外鍵有索引，更新主表已被外鍵引用的主鍵時(shí)，更新主鍵的session不會(huì)被阻塞而報(bào)約束錯(cuò)誤
會(huì)話1：
create index t2_index on t2(y);
update t2 set y1=2 where y1=1;
會(huì)話2：
update t1 set x=4 where x=1
*
ERROR 位于第 1 行:
ORA-02292: 違反完整約束條件 (SCOTT.SYS_C001607) - 已找到子記錄日志
3、外鍵無索引，更新主表未被外鍵引用的主鍵時(shí)，更新主鍵的session被阻塞
會(huì)話1：
drop index t2_index；
update t2 set y1=2 where y1=1
會(huì)話2:
update t1 set x=4 where x=2;
會(huì)話被阻塞。
4、外鍵有索引，更新主表未被外鍵引用的主鍵時(shí)，更新主鍵的session不會(huì)被阻塞
會(huì)話1：
create index t2_index on t2(y);
update t2 set y1=2 where y1=1;
會(huì)話2：
update t1 set x=4 where x=2;
已更新 1 行。
另外在一個(gè)主表有on delete cascade，子表沒有外鍵索引時(shí)，對(duì)主表操作會(huì)級(jí)聯(lián)到子表，子表將進(jìn)行全表掃描。
總結(jié)：在需要更新主鍵的情況下，最好是創(chuàng)建子表的外鍵索引。
1、update emp A,emp2 B set B.comm=A.comm WHERE B.empno=A.empno;
2、update emp2 B set B.comm=(select A.comm emp A where A.empno=B.empno group by A.comm);
如果不增加group by,存在重復(fù)值會(huì)報(bào)錯(cuò)。
3、將所有id存為一個(gè)文件或者指定id
spoll id.txt
select empno from emp;
spool off
寫個(gè)shell
#!/bin/sh
cid=$1
if [ -z "$1" ];then
echo "Usage : $0 id.txt"
exit 1;
fi
for id in `cat id.txt`;do
echo -e "update emp2 B set B.comm=(select A.comm from emp A where A.empno='$id')
where B.id='$id';"
done | sqlplus -s 'user/123456'
重慶分公司
重慶分公司
