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

    IS

        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 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

          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  LOB noparallel;');

       END LOOP;

       elsif r1.table_name in ('AUDIT_LOG') then

       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  ' || substr(r1.par_name,1,8)|| ' noparallel;');

       END LOOP;      

       else

       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  ' || substr(r1.par_name,1,8) ||' noparallel;');

       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 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;

    

    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