干掉蝴蝶效应中的蝴蝶。。。
分类： Oracle
2013-08-06 23:25:51
CREATE OR REPLACE DIRECTORY dumpdir AS '/oracle/dumpdir';
CREATE TABLESPACE ADDATA DATAFILE
'+DATADG/andy11g/datafile/addata001' SIZE 8000M AUTOEXTEND OFF,
'+DATADG/andy11g/datafile/addata002' SIZE 8000M AUTOEXTEND OFF;
CREATE TABLESPACE ADINDX DATAFILE
'+DATADG/andy11g/datafile/adindx001' SIZE 8000M AUTOEXTEND OFF,
'+DATADG/andy11g/datafile/adindx002' SIZE 8000M AUTOEXTEND OFF;
CREATE USER ADUSER
IDENTIFIED BY andy00
DEFAULT TABLESPACE ADDATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for ADUSER
GRANT CONNECT TO ADUSER;
ALTER USER ADUSER DEFAULT ROLE ALL;
-- 2 Tablespace Quotas for ADUSER
ALTER USER ADUSER QUOTA UNLIMITED ON ADINDX;
ALTER USER ADUSER QUOTA UNLIMITED ON ADDATA;
CREATE USER DATAGUARD
IDENTIFIED BY andy00
DEFAULT TABLESPACE ADDATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for DATAGUARD
GRANT CONNECT TO DATAGUARD;
ALTER USER DATAGUARD DEFAULT ROLE ALL;
-- 2 Tablespace Quotas for DATAGUARD
ALTER USER DATAGUARD QUOTA UNLIMITED ON ADINDX;
ALTER USER DATAGUARD QUOTA UNLIMITED ON ADDATA;
nohup impdp \"\/ as sysdba\" directory=dumpdir dumpfile=AD_RESULT1.dmp logfile=imp_AD_RESULT1.log job_name=TE_RESULT1_imp tables=ADUSER.AD_RESULT1 remap_schema=ADUSER:DATAGUARD &
--在dataguard和aduser下建好分区表，表名都叫ad_result1，dataguard.ad_result1有12年的数据。aduser.ad_result1是空表。
SQL> select name,user# from user$ where name in ('ADUSER','DATAGUARD');
NAME USER#
------------------------------ ----------
DATAGUARD 85
ADUSER 84
SQL> show parameter def
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL>
SQL> select owner,table_name,partitioned from dba_tables where owner in ('ADUSER','DATAGUARD');
OWNER TABLE_NAME PAR
------------------------------ ------------------------------ ---
ADUSER AD_RESULT1_2010_MID NO
DATAGUARD AD_RESULT1 YES
ADUSER AD_RESULT1 YES
SQL>
--这时因为aduser.ad_result1是空表，由于deferred_segment_creation=true，只能在dba_segments里看到dataguard的表
SQL> select owner,header_file,header_block from dba_segments where segment_name='AD_RESULT1' and partition_name='AD_RESULT1_201201_SAD_R1_1';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
DATAGUARD 7 182417
SQL>
SQL> select user# from seg$ where file#=7 and block#=182417;
USER#
----------
85
--把dataguard.ad_result1里12年所有子分区，通过mid普通表exchange到aduser.ad_result1以后，发现只能查到aduser.ad_result1了，因为dataguard.ad_result1是空表了。
--这时seg$里的user#却没变成84
SQL> select owner,header_file,header_block from dba_segments where segment_name='AD_RESULT1' and partition_name='AD_RESULT1_201201_SAD_R1_1';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
ADUSER 7 182417
SQL> select user# from seg$ where file#=7 and block#=182417;
USER#
----------
85
SQL>
--重现问题
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201201;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201202;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201203;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201204;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201205;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201206;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201207;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201208;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201209;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201210;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201211;
alter table ADUSER.AD_RESULT1 truncate partition AD_RESULT1_201212;
SQL> select owner,header_file,header_block from dba_segments where segment_name='AD_RESULT1' and partition_name='AD_RESULT1_201201_SAD_R1_1';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
ADUSER 7 182417
SQL> select user# from seg$ where file#=7 and block#=182417;
USER#
----------
85
SQL> select count(1) from aduser.AD_RESULT1;
COUNT(1)
----------
0
SQL> select count(1) from dataguard.AD_RESULT1;
COUNT(1)
----------
1204
SQL> select distinct substr(partition_name,1,15) from dba_segments t where owner='ADUSER';
SUBSTR(PARTITION_NAME,1,15)
---------------------------------------------
AD_RESULT1_2012
SQL> select distinct substr(partition_name,1,15) from dba_segments t where owner='DATAGUARD';
SUBSTR(PARTITION_NAME,1,15)
---------------------------------------------
AD_RESULT1_MAX_
SQL>
SQL> drop user dataguard cascade;
User dropped.
SQL> CREATE USER DATAGUARD
2 IDENTIFIED BY andy00
3 DEFAULT TABLESPACE ADDATA
4 TEMPORARY TABLESPACE TEMP
5 PROFILE DEFAULT
6 ACCOUNT UNLOCK;
User created.
SQL> GRANT CONNECT TO DATAGUARD;
ALTER USER DATAGUARD DEFAULT ROLE ALL;
Grant succeeded.
SQL>
User altered.
SQL> ALTER USER DATAGUARD QUOTA UNLIMITED ON ADINDX;
ALTER USER DATAGUARD QUOTA UNLIMITED ON ADDATA;
User altered.
SQL>
User altered.
SQL>
SQL> select name,user# from user$ where name in ('ADUSER','DATAGUARD');
NAME USER#
------------------------------ ----------
DATAGUARD 86
ADUSER 84
SQL> select owner,header_file,header_block from dba_segments where segment_name='AD_RESULT1' and partition_name='AD_RESULT1_201201_SAD_R1_1';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
ADUSER 7 182417
SQL> select user# from seg$ where file#=7 and block#=182417;
USER#
----------
85
SQL>
重新导入dataguard
SQL> select owner,header_file,header_block from dba_segments where segment_name='AD_RESULT1' and partition_name='AD_RESULT1_201201_SAD_R1_1';
OWNER HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
ADUSER 7 182417
DATAGUARD 6 182417
SQL> select name,user# from user$ where name in ('ADUSER','DATAGUARD');
NAME USER#
------------------------------ ----------
DATAGUARD 86
ADUSER 84
SQL> select user# from seg$ where file#=7 and block#=182417;
USER#
----------
85
SQL> select user# from seg$ where file#=6 and block#=182417;
USER#
----------
86
SQL>
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /oracle/orabase/diag/rdbms/ad1
1g/andy11g1/trace
SQL> show parameter max_dump_file_size trace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string unlimited
alter session set tracefile_identifier='adtest';
alter session set events '10046 trace name context forever, level 12';
alter table dataguard.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION;
alter table ADUSER.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION;
alter session set events '10046 trace name context off';
SQL> alter session set tracefile_identifier='adtest';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter table dataguard.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION;
Table altered.
SQL> alter table ADUSER.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION;
alter table ADUSER.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'ADDATA'
SQL> alter session set events '10046 trace name context off';
Session altered.
vi andy11g1_ora_1101_adtest.trc
PARSING IN CURSOR #47887786011128 len=159 dep=0 uid=0 oct=15 lid=0 tim=1333956457878482 hv=2252224127 ad='647e34d10' sqlid='8s5g0da33wdmz'
alter table ADUSER.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION
END OF STMT
PARSE #47887786011128:c=6999,e=6915,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1333956457878481
PARSE #47887786058712:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1333956457878707
PARSE #47887786459656:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=1388734953,tim=1333956457878804
EXEC #47887786459656:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=1388734953,tim=1333956457878837
FETCH #47887786459656:c=0,e=143,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=1388734953,tim=1333956457878996
CLOSE #47887786459656:c=0,e=2,dep=2,type=3,tim=1333956457879047
PARSE #47887786444232:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=1388734953,tim=1333956457879083
EXEC #47887786444232:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=1388734953,tim=1333956457879109
FETCH #47887786444232:c=0,e=99,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=1388734953,tim=1333956457879222
CLOSE #47887786444232:c=0,e=2,dep=2,type=3,tim=1333956457879266
WAIT #47887786011128: nam='enq: TT - contention' ela= 135 name|mode=1414791172 tablespace ID=6 operation=16 obj#=-1 tim=1333956457897169
=====================
/47887786011128
STAT #47887786103704 id=2 cnt=0 pid=1 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=6 us cost=0 size=0 card=1)'
CLOSE #47887786103704:c=0,e=4,dep=1,type=0,tim=1333956457900880
=====================
PARSING IN CURSOR #47887786103704 len=190 dep=1 uid=0 oct=3 lid=0 tim=1333956457901164 hv=293780133 ad='6a71b2d48' sqlid='4kt3cun8s5fp5'
select name,password,datats#,tempts#,type#,defrole,resource$, ptime, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2 from user$ where user#=:1
END OF STMT
PARSE #47887786103704:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2709293936,tim=1333956457901163
BINDS #47887786103704:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b8dbe7c9d48 bln=22 avl=02 flg=05
value=85
EXEC #47887786103704:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2709293936,tim=1333956457901304
FETCH #47887786103704:c=0,e=6,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=2709293936,tim=1333956457901327
STAT #47887786103704 id=1 cnt=0 pid=0 pos=1 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=1 pr=0 pw=0 time=6 us cost=1 size=102 card=1)'
STAT #47887786103704 id=2 cnt=0 pid=1 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=5 us cost=0 size=0 card=1)'
CLOSE #47887786103704:c=0,e=3,dep=1,type=0,tim=1333956457901379
EXEC #47887786011128:c=20997,e=23043,p=0,cr=279,cu=1,mis=0,r=0,dep=0,og=1,plh=0,tim=1333956457901568
ERROR #47887786011128:err=1950 tim=1333956457901597
WAIT #47887786011128: nam='SQL*Net break/reset to client' ela= 23 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1333956457902352
WAIT #47887786011128: nam='SQL*Net break/reset to client' ela= 69 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1333956457902446
WAIT #47887786011128: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1333956457902460
*** 2012-04-09 15:27:51.649
WAIT #47887786011128: nam='SQL*Net message from client' ela= 13747270 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1333956471649774
CLOSE #47887786011128:c=0,e=10,dep=0,type=0,tim=1333956471649882
=====================
PARSING IN CURSOR #47887786011128 len=55 dep=0 uid=0 oct=42 lid=0 tim=1333956471649990 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #47887786011128:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1333956471649990
EXEC #47887786011128:c=999,e=282,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1333956471650345
tkprof andy11g1_ora_1101_adtest.trc andy11g1_ora_1101_adtest.txt
SQL> select distinct substr(partition_name,1,15) from dba_segments t where owner='ADUSER';
SUBSTR(PARTITION_NAME,1,15)
---------------------------------------------
AD_RESULT1_2012
SQL> select distinct substr(partition_name,1,15) from dba_segments t where owner='DATAGUARD';
SUBSTR(PARTITION_NAME,1,15)
---------------------------------------------
AD_RESULT1_MAX_
AD_RESULT1_2012
SQL>
SQL> select count(1) from ADUSER.AD_RESULT1;
COUNT(1)
----------
0
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201201;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201202;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201203;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201204;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201205;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201206;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201207;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201208;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201209;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201210;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201211;
alter table ADUSER.AD_RESULT1 drop partition AD_RESULT1_201212;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201201, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-02-28 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201202, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-03-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201203, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201204, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201205, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-06-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201206, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-07-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201207, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-08-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201208, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-09-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201209, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-10-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201210, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-11-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201211, partition AD_RESULT1_MAX) update indexes;
alter table ADUSER.AD_RESULT1 split partition AD_RESULT1_MAX at (TO_DATE('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
into (partition AD_RESULT1_201212, partition AD_RESULT1_MAX) update indexes;
alter table dataguard.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION;
alter table ADUSER.AD_RESULT1 exchange subpartition AD_RESULT1_201201_SAD_R1_1 with table ADUSER.AD_RESULT1_2010_MID including indexes WITHOUT VALIDATION;
SQL> select count(1) from dataguard.AD_RESULT1;
COUNT(1)
----------
1204
SQL>
成功end
