TRẦN VĂN BÌNH MASTER: Quy trình tạo partion hết năm 2023 cho CẢ cơ sở dữ liệu Oracle (theo năm, theo tháng, theo ngày)?

Năm 2023 sắp đến rồi, anh/em DBA cũng cần chuẩn bị partition cho năm mới, tất nhiên bạn có thể tạo partition cho 1 tháng, 3 tháng trước nhưng tiện nhất bạn cứ tạo partition cho cả 1 năm để đỡ phải "lăn tăn" do job tạo partition lỗi làm gián đoạn nghiệp vụ mà có những thời điểm cao tải các bạn cũng khó có thể tạo partition được. Thường chúng ta tạo partition vào cuối tháng 10 hoặc chậm nhất là cuối tháng 11 năm trước vì một số nghiệp vụ vẫn cần có partition của năm tiếp theo (như có 1 gói cước 4G đăng ký từ 07/12/2022 đến 07/12/2023, partition theo trường end_date,...)

Cách sử dụng script này:

1. Mở TOAd hoặc SQL Navigator

2. Copy toàn bộ và thực hiện từng bước một cho đến khi kết thúc

/***** 1. Tao tablespace *****/

--select * from dba_tablespaces where tablespace_name like '%2022%' order by tablespace_name;

--select * from dba_data_files where tablespace_name in ('DATA2022','INDX2022');

-- Sau nay read only tablespace này

create tablespace DATA2023 datafile '+DATA' size 1g autoextend on next 100m;

create tablespace INDX2023 datafile '+DATA' size 1g autoextend on next 100m;

-- Sau này read write tablespace 3, 5, 10 năm

create tablespace DATA2023_RW datafile '+DATA' size 1g autoextend on next 100m;

create tablespace INDX2023_RW datafile '+DATA' size 1g autoextend on next 100m;

/**** 2. GEN PARTITION *****/

/* 2.1.GEN PARTITION THEO NĂM: */

select 'alter table '||table_owner||'.'||table_name||' add PARTITION DATA2021 VALUES LESS THAN (TO_DATE(''2024-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE DATA2023;'

from dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2022%'

and table_name not like '%$%' and table_name not like 'XXX%' --and table_name not like 'TMP%'

and table_owner not in ('Test')

GROUP BY table_owner, table_name

having length(max(partition_name))<9

order by table_owner,table_name;

--2.1.1.Rebuild index theo năm:

DECLARE

v_nam varchar2(4) := '2023';

v_tablespace varchar2(50):='INDX';

cursor c1 is

select table_owner,table_name,max(partition_name)

from dba_tab_partitions where table_name not like '%$%' and table_name not like 'XXX%' --and table_name not like 'TMP%'

and table_name not in ('Test')

group by table_owner,table_name having max(partition_name) like '%'||2023||'%' and length(max(partition_name))<9 order by table_owner,table_name;

cursor c2 (p_tablename varchar2,p_owner varchar2) is

select * from DBA_PART_INDEXES where table_name = p_tablename and owner=p_owner;

BEGIN

for r1 in c1

LOOP

FOR r2 in c2(r1.table_name,r1.table_owner)

LOOP

DBMS_OUTPUT.put_line ('alter index '|| r1.table_owner||'.'||r2.index_name || ' REBUILD PARTITION DATA'||v_nam||' TABLESPACE '||v_tablespace||v_nam||' nologging noparallel online;');

END LOOP;

end loop;

END;

/* 2.2.GEN PARTITION THEO THÁNG */

DECLARE

v_nam NUMBER (4) := 2023;

v_thang_from NUMBER (2) := 1;

v_thang_to NUMBER (2) := 12;

v_tablespace varchar2(50):='DATA';

CURSOR c1

select table_owner, table_name,max(partition_name)

from dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2022%'

and table_name not like '%$%' and table_name not like '%TEST%' and table_name not like 'XXX%' and table_name not like '%BAK' and table_name not like 'BK%'

GROUP BY table_owner, table_name

having length(max(partition_name))=10 and max(partition_name) like '%202212'

order by table_owner,table_name;

BEGIN

for r1 in c1 loop

FOR i IN v_thang_from .. (v_thang_to-1)

LOOP

DBMS_OUTPUT.put_line ('alter table '||r1.table_owner ||'.'|| r1.table_name || ' add PARTITION DATA'||v_nam||LPAD (i, 2, '0')||' VALUES LESS THAN (TO_DATE('''|| v_nam ||'-'||LPAD (i+1, 2, '0')||'-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||v_nam||';');

END LOOP;

DBMS_OUTPUT.put_line ('alter table '|| r1.table_owner ||'.'|| r1.table_name || ' add PARTITION DATA'||v_nam||LPAD (to_char(v_thang_to), 2, '0')||' VALUES LESS THAN (TO_DATE('''|| (v_nam +1) ||'-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||v_nam||';');

end loop;

END;

-- 2.2.1.Rebuild INDEX theo thang:

DECLARE

v_nam varchar2(4) := '2023';

v_thang_from NUMBER (2) := 01;

v_thang_to NUMBER (2) := 12;

v_tablespace varchar2(50):='INDX';

cursor c1 is

select table_owner,table_name,max(partition_name)

from dba_tab_partitions where table_name not like '%$%' and table_name not like 'XXX%'

group by table_owner,table_name having max(partition_name) like '%'||2023||'%' and length(max(partition_name))=10 order by table_owner,table_name;

cursor c2 (p_tablename varchar2,p_owner varchar2) is

select * from DBA_PART_INDEXES where table_name = p_tablename and owner=p_owner;

BEGIN

for r1 in c1

LOOP

FOR r2 in c2(r1.table_name,r1.table_owner)

LOOP

FOR i IN v_thang_from .. (v_thang_to)

LOOP

DBMS_OUTPUT.put_line ('alter index '|| r1.table_owner||'.'||r2.index_name || ' REBUILD PARTITION DATA'||v_nam||LPAD (to_char(i), 2, '0')||' TABLESPACE '||v_tablespace||v_nam||' nologging parallel 8 online;');

END LOOP; END LOOP;

end loop;

END;

/* 2.3.GEN PARTITION THEO NGAY */

--2.3.1.Gen data partition theo ngay_MAIN_OWNER

-- Chay vao ban dem > 22h, lock ~200, active session ~ 1000

-- Có thể gặp Event: library cache lock, cursor: pin S wait on X

DECLARE

v_tablename VARCHAR2 (50);

v_date_from date;

v_date_to date := to_date('31/12/2023','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='DATA';

cursor c1 is

select table_owner,table_name,MAX(PARTITION_NAME) par_name from dba_tab_partitions where (table_owner, table_name, partition_name) in (

select table_owner,table_name,MAX(PARTITION_NAME) par_name

from dba_tab_partitions

where LENGTH(PARTITION_NAME)>=11

and table_name not like '%$%' and table_name not like 'XXX%'

and table_owner= 'MAIN_OWNER'

group by table_owner, table_name

)

and partition_name like 'DATA20221231%'

GROUP BY table_owner,table_name order by table_owner,table_name;

BEGIN

for r1 in c1 loop

v_tablename:=r1.table_owner||'.'||r1.table_name;

v_date_from:=to_date(substr(r1.par_name,5,8),'YYYYMMDD')+1;

v_numday:=v_date_to-v_date_from;

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE DATA2020;');

END LOOP;

end loop;

END;

-- 2.3.1.1.Rebuild Index theo ngay MAIN_OWNER

DECLARE

v_date_from date := to_date('01/01/2023','dd/mm/yyyy');

v_date_to date := to_date('31/12/2023','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='INDX';

cursor c1 is

select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME

and a.owner='CUS_OWNER' and a.index_name not like '%$%' and b.partition_name like '%20191231' order by a.owner,a.index_name;

BEGIN

v_numday:=v_date_to-v_date_from;

FOR i1 in c1

LOOP

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYY')||' nologging parallel 8 online;');

END LOOP;

END;

--2.3.2.Gen data partition theo ngay_User khac

DECLARE

v_tablename VARCHAR2 (50);

v_date_from date;

v_date_to date := to_date('31/12/2023','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='DUMP_DATA';

cursor c1 is

select table_owner,table_name,MAX(PARTITION_NAME) par_name

from dba_tab_partitions

WHERE (LENGTH(PARTITION_NAME)>=12 AND PARTITION_NAME LIKE '%20221231'

and table_name not like '%$%' and table_name not like 'XXX%' and table_name not like '%TEST%' and table_name not like '%DAILY_20%'

and table_owner not in ('MAIN_OWNER')

GROUP BY table_owner,table_name order by table_owner,table_name;

BEGIN

for r1 in c1 loop

v_tablename:=r1.table_owner||'.'||r1.table_name;

v_date_from:=to_date(substr(r1.par_name,5,8),'YYYYMMDD')+1;

v_numday:=v_date_to-v_date_from;

if r1.table_name in ('REQUEST_LOG','RESPOND_LOG') then

FOR i IN 0 .. v_numday

LOOP

END LOOP;

elsif r1.table_name in ('AUDIT_LOG') then

FOR i IN 0 .. v_numday

LOOP

END LOOP;

else

LOOP

END LOOP;

end if;

end loop;

END;

-- 2.3.2.1.Rebuild Index theo ngày user khac

-- Lưu tại DUMP_INDX

DECLARE

v_date_from date := to_date('01/01/2023','dd/mm/yyyy');

v_date_to date := to_date('31/12/2023','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='DUMP_INDX';

cursor c1 is

select a.*,b.partition_name from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME

and a.owner!='USER_OWNER' and a.index_name not like '%$%' and b.partition_name like '%20221231'

order by a.owner,a.index_name;

BEGIN

v_numday:=v_date_to-v_date_from;

FOR i1 in c1

LOOP

if i1.owner='TEST_GATEWAY' then

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE LOB nologging parallel 8 online;');

END LOOP;

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' noparallel;');

else

FOR j IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+j,'YYYYMMDD')||' TABLESPACE INDX' || substr(i1.partition_name,5,4) ||' nologging parallel 8 online;');

END LOOP;

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' noparallel;');

end if;

END LOOP;

END;

-- 2.3.4.Set noparallel index

DECLARE

cursor c1 is

select distinct a.index_owner, a.index_name from DBA_ind_partitions a where a.index_owner='USER_OWNER' and a.index_name not like '%$%' and a.partition_name like '%20221231'

order by a.index_owner,a.index_name;

BEGIN

FOR i1 in c1

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.index_owner||'.'||i1.index_name || ' noparallel;');

END LOOP;

END;

-- Một số câu lệnh khác hỗ trợ trong quá trình thực hiện

--Optional: Rebuild INDX PARTITION theo ngay, 1 bang

DECLARE

v_tablename VARCHAR2 (50) := 'REQUEST';

v_owner VARCHAR2 (50) := 'USER_OWNER';

v_date_from date := '01/01/2023';

v_date_to date := '31/12/2023';

v_numday number(2);

v_tablespace varchar2(50):='INDX';

cursor c1 is

select * from DBA_PART_INDEXES where table_name = v_tablename and owner=v_owner;

BEGIN

v_numday:=v_date_to-v_date_from;

FOR i1 in c1

LOOP

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||v_owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYYMM')||';');

END LOOP;

END;

-- MONITORING: Neu active session > 600, lock > 50 thi kill tien trinh

-- Trong quá trình thêm partition cần MONITOR chặt chẽ tránh tình trạng lock, cao tải gây timeout ứng dụng, nếu gặp lock, cao tải cần dừng lại và chọn thời điểm THẤP TẢI khác để chạy (ví dụ 1h,2h,...)

-- Active session

SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,

s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3

FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA

WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'

--and s.username not in ('SYS','SYSMAN')

--AND username LIKE 'SYS'

--and s.sid=1234

--and s.machine like '%app-2%'

--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'

--and s.sql_id ='cb66zngs8xz5j'

--and lower(ss.sql_text) like lower('%test_customer%')

ORDER BY i#,username,sql_id,machine,S.SID;

Note: Nếu bảng nào gây tải cao thì chọn giờ thấp điểm hẳn, 1-2h sáng hoặc yêu cầu ứng dụng tắt nghiệp vụ đi đẻ thực hiện vì cố làm sẽ gây treo DB

-- Kill gấp nếu gây cao tải

SELECT /*5.SID*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND

FROM gv$session a, gv$process b

WHERE b.addr = a.paddr

AND a.inst_id=b.inst_id

--and b.inst_id=2

AND a.sid in (

select sid from (SELECT /*1.ActiveSession*/ distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,

s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3

FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA

WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'

--and s.username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')

AND username like 'SYS%'

--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'

and lower(ss.sql_text) like lower('%alter table%')

and lower(ss.sql_text) not like lower('%ACTIVE, LOCK%'))

--and s.sid=4588

--and s.machine like '%BINHTV%'

--and s.sql_id ='ccwg0nqr1zbu7'

)

and type='USER'

order by inst_id;

/******************************************** LOCK SESSION, WAITING *************************************************/

select status, count(*) from gv$session group by status order by status;

Select /*blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME

From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' order by inst_id --order by blocking_session;

SELECT /*blocking_session*/ inst_id, sid, DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess, id1,id2,lmode, request,TYPE

FROM GV$LOCK WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM GV$LOCK WHERE request > 0) ORDER BY id1, request;

/*********************************************** SESSIONS ***********************************************/

select count(*) from gv$session ;

select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

select USERNAME,count(*) from gv$session group by USERNAME order by count(*) desc;

select machine,count(*) from v$session group by machine order by count(*) desc;

select inst_id, count(*) from gv$session group by inst_id;

select /*Thong ke theo status*/ username,status,count(*) from gv$session where username like 'TEST_OWNER%' group by username,status order by count(*) desc;

select /*Thong ke theo inst_id*/ inst_id,count(*), username from gv$session where username like 'TEST_OWNER%' group by inst_id, username order by username;

select /* user theo machine */ machine,count(*), username from gv$session where username like 'TEST_OWNER%' group by machine, username order by username;

/********************************************** KILLER *********************************************/

SELECT /*username*/ 'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT

FROM gv$session a, gv$process b

WHERE b.ADDR = a.paddr

AND a.inst_id=b.inst_id

--AND B.inst_id = 1

--and a.status='INACTIVE'

--and A.USERNAME LIKE 'TEST%'

--AND A.USERNAME not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE')

--and a.username not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE','APP1','APP2','APP3')

AND a.program not LIKE '%Toad.exe%'

--AND sql_id in ('44t0dk94q3xqg','6uac7nnud2gfa')

and machine like '%TCTK-BINHTV%'

--and a.event in ('library cache lock','brary cache load lock','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','db file parallel read')

and type='USER'

order by a.inst_id;

SELECT /*SID*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND

FROM gv$session a, gv$process b

WHERE b.addr = a.paddr

AND a.inst_id=b.inst_id

--and b.inst_id=2

AND a.sid in (

5812

)

and type='USER'

order by inst_id;

/***** 3.CHECK PARTITION *****/

--Check partition thieu

SELECT table_owner,

table_name AS TABLE_NAME,

partition_name,

tablespace_name,

last_analyzed,

high_value

FROM dba_tab_partitions

WHERE (table_owner, table_name, partition_position) IN

( SELECT table_owner, table_name, MAX (partition_position)

FROM dba_tab_partitions

WHERE

partition_name like '%202%'

-- partition_name LIKE '%202___' --thang

-- partition_name like '%202_____' --ngay

-- partition_name like '%202_' --nam

GROUP BY table_owner, table_name)

-- AND table_owner NOT LIKE 'SYS%'

AND table_name NOT LIKE '%$%'

ORDER BY 3;

-- Check partition theo NAM, max partition 2023 la OK

select table_owner,table_name,partition_name from dba_tab_partitions

where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

and length(partition_name)<9

order by 1,2,3;

---- Check partition theo THANG, max partiton 202312 la OK

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

and length(partition_name)>=9

--and partition_name like 'DATA202312'

and length(partition_name)<11

order by table_owner, table_name, partition_name;

---- Check partition theo NGAY, max partiton 20231231 la OK

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

and length(partition_name)>=11

and partition_name like 'DATA20231231'

order by table_owner, table_name, partition_name;

-- Check ALL (nam, thang, ngay)

-- Dam bao nam 2023, thang 202312, ngay 20231231

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

order by table_owner, table_name, partition_name;

--Test lai sau khi gen partitioin, null la OK

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2023%' and table_name not like '%$%'

GROUP BY table_owner, table_name)

MINUS

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2022%' and table_name not like '%$%'

GROUP BY table_owner, table_name);

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2023%' and table_name not like '%$%'

GROUP BY table_owner, table_name)

MINUS

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2022%' and table_name not like '%$%'

GROUP BY table_owner, table_name);

--Fix khi add nhầm tablespace

-- Với bảng cần online luôn: Chuyen partition va rebuild index luon

select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' tablespace tbs_name nologging parallel 8;', partition_name, tablespace_name from dba_tab_partitions

where table_owner='user1' and table_name = 'table1' and partition_name like 'DATA201401%'

union

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name

|| ' tablespace tbs_name nologging parallel 8 online;', partition_name, tablespace_name from dba_ind_partitions

where index_name in

(select index_name from dba_indexes where

table_owner='user1' and table_name = 'table1')

and partition_name like 'DATA201401%'

union

select 'alter index '||owner||'.'||index_name||' noparallel;', null, null from dba_indexes where

table_owner='user1' and table_name = 'table1'

order by 2, 1 desc, 3;

-- Move từng partition xong rebuild index partition luôn

set serverout on size 1000000

declare

cursor c_tab_partitions is

select * from dba_tab_partitions where tablespace_name='DUMP' and table_owner='BINHTV';

cursor c_ind_partitions is

select * from dba_ind_partitions where status='UNUSABLE';

cursor c_tables is

select * from dba_tables where tablespace_name='DUMP' and partitioned='NO';

cursor c_indexes is

select * from dba_indexes where status='UNUSABLE';

sql_move_tab_partition varchar2(1000);

sql_rebuild_ind_partition varchar2(1000);

begin

-- Chuyen bang partition va rebuild index partition UNUSABLE ngay sau tung partition

for r1 in c_tab_partitions

loop

sql_move_tab_partition := 'alter table ' || r1.table_owner || '.' || r1.table_name || ' move partition ' || r1.partition_name ||' tablespace USERS parallel 8 nologging ';

dbms_output.put_line(sql_move_tab_partition);

execute immediate sql_move_tab_partition;

for r2 in (select * from dba_ind_partitions where status='UNUSABLE')

loop

dbms_output.put_line('Before sql_rebuild_ind_partition');

sql_rebuild_ind_partition := 'alter index ' || r2.index_owner || '.' || r2.index_name || ' rebuild partition ' || r2.partition_name || ' nologging parallel 8 online';

dbms_output.put_line(sql_rebuild_ind_partition);

dbms_output.put_line('Before execute sql_rebuild_ind_partition');

execute immediate sql_rebuild_ind_partition;

end loop;

send_sms_binhtv('Finish to move BINHTV from DUMP to users');

end;

-- Chuyen bang non-partition va rebuild index non-partition UNUSABLE ngay sau 1 partition

/* for

for c1 in (select owner,segment_name, segment_type,decode(segment_type,'TABLE','MOVE','REBUILD') operation,

case when owner='BAOCAO' and segment_type='TABLE' then 'usr_d_01'

when owner='BAOCAO' and segment_type='INDEX' then 'usr_x_01'

when owner='THAMSOHOA' and segment_type='TABLE' then 'usr_d_02'

when owner='THAMSOHOA' and segment_type='INDEX' then 'usr_x_02' end tablespace_name

from dba_segments

where owner in ('BAOCAO','THAMSOHOA')

and segment_type in ('TABLE','INDEX') ) loop

begin

dbms_output.put_line('alter '||c1.segment_type||'.'||c1.owner||'.'||

c1.segment_name||' '||c1.operation||' tablespace '||c1.tablespace_name);

/* Once you tested and ok with the output of this procedure as per your database requirements

remove the comments on the below execute immediate statement and run the procedure to perform the task */

/* execute immediate 'alter '||c1.segment_type||' '||c1.owner||'.'||

c1.segment_name||' '||c1.operation||' tablespace '||c1.tablespace_name; */

-- exception

-- when others then

-- dbms_output.put_line(c1.owner||' '||c1.segment_name||' '||c1.segment_type||' '||sqlerrm);

-- end;

-- end loop;*/

-- Chuyển partition depot2 thuộc bảng (ko quan tâm đang ở tablespace nào) sang tablespace ts094

ALTER TABLE parts MOVE PARTITION DATA201301 TABLESPACE DATA2013 nologging parallel 8;

ALTER TABLE parts MOVE PARTITION DATA201301 TABLESPACE DATA2013;

procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) is

cursor pCur(vTname varchar2, vTspName varchar2) is

select table_name, partition_name

from user_tab_partitions

where table_name = vTname

and tablespace_name not like vTspName

order by partition_position desc;

begin

for pRow in pCur(a_tname, a_destTS) loop

sqlStmnt := 'alter table '||pRow.table_name||

' move partition '||pRow.partition_name||

' tablespace '||a_destTS;

execute immediate sqlStmnt;

end loop;

end mvTabPart;

=============================

* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.

* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888

* Chi tiết tham khảo:

https://bit.ly/oaz_w

=============================

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:

📧 Mail: binhoracle@gmail.com

☎️ Mobile/Zalo: 0902912888

👨 Facebook: https://www.facebook.com/BinhOracleMaster

👨 Inbox Messenger: https://m.me/101036604657441 (profile)

👨 Fanpage: https://www.facebook.com/tranvanbinh.vn

👨 Inbox Fanpage: https://m.me/tranvanbinh.vn

👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam

👨 Website: https://www.tranvanbinh.vn

👨 Blogger: https://tranvanbinhmaster.blogspot.com

🎬 Youtube: http://bit.ly/ytb_binhoraclemaster

👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi

👨 Linkin: https://www.linkedin.com/in/binhoracle

👨 Twitter: https://twitter.com/binhoracle

👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================

Quy trình tạo partion hết năm 2023 cho CẢ cơ sở dữ liệu Oracle (theo năm, theo tháng, theo ngày), oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty

Thứ Bảy, 7 tháng 12, 2019

Quy trình tạo partion hết năm 2023 cho CẢ cơ sở dữ liệu Oracle (theo năm, theo tháng, theo ngày)?

ĐỌC NHIỀU