查询锁具体信息 select
A.ADDR,A.TRX_ID,A.LMODE,A.TABLE_ID,B.OBJECT_NAME,C.SQL_TEXT,C.SESS_ID,C.CLNT_HOST,C.CLNT_TYPE
from SYS."V
L
O
C
K
"
A
L
E
F
T
J
O
I
N
D
B
A
O
B
J
E
C
T
S
B
O
N
A
.
T
A
B
L
E
I
D
=
B
.
O
B
J
E
C
T
I
D
A
N
D
B
.
O
B
J
E
C
T
T
Y
P
E
=
′
T
A
B
L
E
′
L
E
F
T
J
O
I
N
V
LOCK" A LEFT JOIN DBA_OBJECTS B ON A.TABLE_ID=B.OBJECT_ID AND B.OBJECT_TYPE='TABLE' LEFT JOIN V
LOCK"ALEFTJOINDBAO?BJECTSBONA.TABLEI?D=B.OBJECTI?DANDB.OBJECTT?YPE=′TABLE′LEFTJOINVSESSIONS C ON A.TRX_ID=C.TRX_ID 根据实际情况来判定是否杀该进程 CALL
SP_CLOSE_SESSION(SESS_ID);
创建统计sql执行时间大于2秒存储到表便于分析 1、创建存放表 CREATE TABLE
“SYSDBA”.“PERFORMANCE_HISTORY” ( “SQL_TXT” VARCHAR(1024),
“EXEC_TIME” BIGINT, “USER_NAME” VARCHAR(128), “CREATE_TIME”
DATETIME(6)) STORAGE(ON “MAIN”, CLUSTERBTR) ;
执行语句 insert into performance_history (select
a.SQL_TXT,a.EXEC_TIME,b.USER_NAME,b.CREATE_TIME from
SYS.“V
S
Q
L
S
T
A
T
H
I
S
T
O
R
Y
"
a
l
e
f
t
j
o
i
n
s
y
s
.
"
V
SQL_STAT_HISTORY" a left join sys."V
SQLS?TATH?ISTORY"aleftjoinsys."VSESSION_HISTORY” b on
a.SESSID=B.SESS_ID and a.SESS_SEQ=B.SESS_SEQ where a.EXEC_TIME >2000); commit;
创建job,每日间隔5分钟执行 call
SP_CREATE_JOB(‘performance_history’,1,0,’’,0,0,’’,0,’’); call
SP_JOB_CONFIG_START(‘performance_history’); call
SP_ADD_JOB_STEP(‘performance_history’, ‘performance_history1’, 0,
‘insert into performance_history (select
a.SQL_TXT,a.EXEC_TIME,b.USER_NAME,b.CREATE_TIME from
SYS.“V
S
Q
L
S
T
A
T
H
I
S
T
O
R
Y
"
a
l
e
f
t
j
o
i
n
s
y
s
.
"
V
SQL_STAT_HISTORY" a left join sys."V
SQLS?TATH?ISTORY"aleftjoinsys."VSESSION_HISTORY” b on
a.SESSID=B.SESS_ID and a.SESS_SEQ=B.SESS_SEQ where a.EXEC_TIME>2000); commit;’, 1, 2, 0, 0, NULL, 0); call SP_ADD_JOB_SCHEDULE(‘performance_history’, ‘performance_history’, 1,
1, 1, 0, 5, ‘00:00:00’, ‘23:59:59’, ‘2021-07-28 18:16:14’, NULL,
‘’); call SP_JOB_CONFIG_COMMIT(‘performance_history’);
----迁移数据流程----- 1、收集源统计信息 begin dbms_stats.gather_schema_stats( ownname=>‘HM’, estimate_percent=>dbms_stats.AUTO_SAMPLE_SIZE,
options=>‘gather’, degree=>dbms_stats.AUTO_DEGREE,
method_opt=>‘for all columns size repeat’, cascade=>true); end;
2、统计源对象分类统计 select object_type,t.owner,count() from all_objects t
where t.owner=‘HM’ group by t.owner,object_type order by t.owner
3、统计源对象状态 select object_type,t.owner,count(),(t.status) from
all_objects t where t.owner in(‘HM’) group by
t.owner,object_type,status order by t.owner,status 4、select * from
dba_objects where status=‘INVALID’ and owner=‘HM’ --统计失效对象明细
5、统计源表数据量 select
e.TABLE_NAME,e.TABLESPACE_NAME,e.STATUS,e.NUM_ROWS,e.LAST_ANALYZED
from user_tables e order by NUM_ROWS desc 6、统计目标数据库所有用到的数据类型 select
distinct s.DATA_TYPE from dba_tab_columns s where
owner=‘SL_SZY_CQ_EXTRA’
----按季度分区 CREATE TABLE “TELEMETRY”.“DC_DATA_ARRIVE1” ( “ARRIVE_ID” VARCHAR2(20) NOT NULL, “STCD” CHAR(8) NOT NULL, “DATA_TYPE”
VARCHAR2(50) NOT NULL, “ARRIVED” CHAR(1), “STATUS” VARCHAR2(10),
“ADDIVE_TIME” DATETIME(6), “CREATE_TIME” DATETIME(6), “UPDATE_TIME”
DATETIME(6), “REMARK” VARCHAR2(200), “MSG_ID” VARCHAR2(50))
PARTITION BY RANGE(“CREATE_TIME”) INTERVAL(NUMTOYMINTERVAL(3,
‘MONTH’)) ( PARTITION “DC_DATA_ARRIVE_P1” VALUES LESS
THAN(DATETIME’2021-07-01 00:00:00’) STORAGE(INITIAL 100, NEXT 100,
ON “TELEMETRY”, CLUSTERBTR) ) STORAGE(INITIAL 100, NEXT 100, ON
“TELEMETRY”, CLUSTERBTR) ;