Thứ Ba, 1 tháng 12, 2020

Tạo view nhiều bảng trong Oracle Database

DROP VIEW user1.V_table1;

/* Formatted on 17/06/2013 09:02:28 (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FORCE VIEW user1.V_table1

Script rebuild index theo ngày, tháng, năm trong Oracle Database

---index theo nam
DECLARE
   v_nam          varchar2(4) := '2012';
   v_tablespace   varchar2(50):='INDX';
   cursor c1 is  
--     select table_owner,table_name,max(partition_name)
--     from dba_tab_partitions group by table_owner,table_name having max(partition_name) like '%'||v_nam||'%' and length(max(partition_name))<9;
     select table_owner,table_name,max(partition_name)
     from dba_tab_partitions where table_name='table1' group by table_owner,table_name;

Quản lý Index Partition trong Oracle Database

--1.CHECK
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 not like 'SYS%' and b.partition_name like '%20131231';

-- Script rebuild index partititon unusable
select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX nologging parallel 8 online;' from dba_ind_partitions where status='UNUSABLE';


-- Script rebuild các partition index 
--+ Bước 1: Rebuild
select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX' ||substr(partition_name,5,4)||' nologging parallel 8 online;' from dba_ind_partitions where 
index_owner='APP_OWNER'
and index_name in (
'index_name_1',
'index_name_2'
)
order by partition_name desc
;
--+ Bước 2: Nologging noparallel
alter index app_owner.index_name_1 noparallel nologging;
alter index app_owner.index_name_2 noparallel nologging;

Thử nghiệm index chạy chậm hơn quét FULL trong Oracle Database

Mục đích: Demo cho các bạn full chậm hơn index, nên dùng index phải rất chuẩn (3-5% giá trị trả về thôi nhé).

Tạo virtual index trong Oracle Database

CREATE TABLE objects_tab AS SELECT * FROM all_objects;

ALTER TABLE objects_tab ADD (
  CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)
);

Chuyển bảng non-partition sang partition theo tháng trong Oracle Database

-- Thu tuc chuyen bang user1.table1 partition theo bdate sang partition theo thang
--1.CHECK, lay bang partition
select * from dba_tab_partitions where length(partition_name)=10;

--Lay cau truc bang sau ra lam mau
user1    table1

Chuyển bảng non-partition sang partition theo ngày trong Oracle Database

Mục đích: Khi bảng non-partiton lớn (> 2GB hoặc > 50 triệu row) thì chúng ta cân nhắc chuyển bảng sang partition, nếu quét theo ngày thì tạo partition theo ngày theo thủ tục sau:

select min(month) from user1.table1_NOPART;
     
--select min(load_date) from user1.table1  ;
--1.Rename table
alter table user1.table1 rename to table1_NOPART;

Tạo bảng partition theo ngày và add thêm partition thiếu của 1 bảng theo ngày trong Oracle Database

1. TẠO BẢNG

CREATE TABLE test_owner.tab1
    (ID                     VARCHAR2(15) NOT NULL,
    start_datetime                  DATE NOT NULL,
    col1 DATE NOT NULL,
    col2 VARCHAR2(1) NOT NULL,
    col3 NUMBER(10,0),
)
TABLESPACE DATA
PARTITION BY RANGE (start_datetime)
(
  PARTITION DATA20210303 VALUES LESS THAN (TO_DATE(' 2021-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA202103
)

2. THÊM PARTITION CÁC NGÀY KHÁC CHO BẢNG

DECLARE
   v_nam          NUMBER (4) := 2021;
   v_owner        varchar2 (50) := 'test_owner';
   v_tablename    VARCHAR2 (50) := 'tab1';

GIẢI PHÁP: NÉN DỮ LIỆU LÀM GIẢM DUNG LƯỢNG VÀ TĂNG TỐC ĐỘ TRUY XUẤT DỮ LIỆU trong Oracle Database

1. Kiểm tra dung lượng của từng partition trước khi compress:
 
select segment_name,partition_name,bytes/1024/1024 "MB"
from dba_Segments where segment_type = 'TABLE PARTITION' 
and owner like 'user1'
--and lower(SEGMENT_name) like 'segment_name1%'
and bytes/1024/1024 >2
order by MB desc

Tạo bảng Hash Partition trong Oracle Database

--Tạo bảng sales hash partition 

CREATE TABLE sales_hash
  (s_productid  NUMBER,
   s_saledate   DATE,
   s_custid     NUMBER,
   s_totalprice NUMBER)
PARTITION BY HASH(s_productid)
( PARTITION p1 TABLESPACE tbs1
, PARTITION p2 TABLESPACE tbs2
, PARTITION p3 TABLESPACE tbs3
, PARTITION p4 TABLESPACE tbs4
);


--Tạo  composite range-hash partitioned table dùng mệnh đề STORE IN 

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)

  SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

 );

--Tạo bảng với composite range-hash partitioning

CREATE TABLE page_history

( id                NUMBER NOT NULL

, url               VARCHAR2(300) NOT NULL

, view_date         DATE NOT NULL

, client_ip         VARCHAR2(23) NOT NULL

, from_url          VARCHAR2(300)

, to_url            VARCHAR2(300)

, timing_in_seconds NUMBER

) PARTITION BY RANGE(view_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))

SUBPARTITION BY HASH(client_ip)

SUBPARTITIONS 32

(PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2006','dd-MON-yyyy')))

PARALLEL 32 COMPRESS;


CREATE  TABLE ABC(

id VARCHAR2(100) primary key,

datecreated DATE)

PARTITION BY RANGE (datecreated) INTERVAL (NUMTODSINTERVAL(1,'DAY'))

  SUBPARTITION BY HASH (ID) SUBPARTITIONS 4

 (PARTITION lessthan2018  VALUES LESS THAN (TIMESTAMP' 2018-01-01 00:00:00') );

Tạo bảng partition theo năm trong Oracle Database

CREATE TABLE binhtv.table1
(
  ACTION_AUDIT_ID  NUMBER(20),
  SHOP_CODE        VARCHAR2(20 BYTE),

Thủ tục tạo bảng partition theo quý trong Oracle Database

DROP TABLE REPDB.SALES CASCADE CONSTRAINTS;

CREATE TABLE REPDB.SALES
(
  PROD_ID        NUMBER(6),
  CUST_ID        NUMBER,
  TIME_ID        DATE,
  CHANNEL_ID     CHAR(1 BYTE),
  PROMO_ID       NUMBER(6),
  QUANTITY_SOLD  NUMBER(3),
  AMOUNT_SOLD    NUMBER(10,2),
  SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)

Thủ tục tạo bảng partition theo tháng trong Oracle Database

--DROP TABLE user1.table1 CASCADE CONSTRAINTS;

CREATE TABLE user1.table1
(
  SUB_ID    NUMBER(20),
  ISDN      VARCHAR2(15 BYTE),
  REG_DATE  DATE,
  SYS_DATE  DATE,
  COMMAND   VARCHAR2(50 BYTE)
)

Thủ tục tạo bảng partition theo ngày trong Oracle Database

--Thủ tục tạo partition theo ngày trong Oracle Database
-- 1.Tao bang
CREATE TABLE cus.table1
    (imsi                           VARCHAR2(15) NOT NULL,
    bill_datetime                  DATE NOT NULL,    
    acc_profile                    VARCHAR2(20),
    credit_charged                 NUMBER(10,2)
)
TABLESPACE DATA
PARTITION BY RANGE (bill_datetime)
(
  PARTITION DATA20210101 VALUES LESS THAN (TO_DATE('2021-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2021
)

--2.Add them partition
DECLARE
   v_nam          NUMBER (4) := 2021; --2014
   v_tablename    VARCHAR2 (50) := 'table1';
   v_date_from   date    := to_date('02/01/2021','dd/mm/yyyy');
   v_date_to     date    := to_date('31/12/2021','dd/mm/yyyy');
   v_numday     number(5);
   v_tablespace varchar2(50):='DATA2021';
BEGIN
   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 '||v_tablespace||';');
   END LOOP;
END;

-- Vao muc DBMS_Ouput cua TOAD:

--3.Tao index
create index  cus.table1_I1 on cus.table1(imsi) local parallel 8 nologging online

create index  cus.table1_I2 on cus.table1(acc_profile)  parallel 8 nologging online

alter table  cus.table1 truncate partition data20210101

alter table  cus.table1 drop partition data20210101

select * from dba_indexes where owner='CUS'
and table_name='TABLE1';

alter table  cus.table1 truncate partition data20210102

select * from dba_ind_partitions where index_owner='CUS'
and index_name='TABLE1_I1'
and status!='USABLE';


alter index  cus.table1_I1 nologging noparallel;

--3.Rebuild index ve tablespace INDX
DECLARE
   v_date_from   date    := to_date('01/01/2021','dd/mm/yyyy');
   v_date_to     date    := to_date('31/12/2021','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 b.table_owner='CUS' and b.table_name='TABLE1'  and a.index_name not like '%$%' and b.partition_name like '%20211231'  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;

-- Set nologging noparallel
DECLARE
   cursor c1 is
        select distinct a.index_owner, a.index_name from DBA_ind_partitions a where  a.index_owner='CUS' and a.index_name not like '%$%' and a.partition_name like '%20211231'  
        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 || ' nologging noparallel;');
   END LOOP;
END;

Quản lý bảng partition trong Oracle Database từ A-Z

1.CHECK

--Check các partition đã tạo cho DB
select table_name,partition_name,TABLESPACE_NAME,last_analyzed,high_value from dba_tab_partitions where partition_name LIKE '%2012%' and (table_name, partition_name) in (
SELECT table_name, MAX(partition_name)--, tablespace_name
  FROM dba_tab_partitions
WHERE  partition_name LIKE '%2012%'
GROUP BY TABLE_NAME);

Câu lệnh MERGE trong Oracle Database

CREATE TABLE source_tab AS
SELECT object_id, owner, object_name, object_type
FROM   all_objects;

Tạo bảng, index, sequence trong Oracle Database

-- Table rpt_offline
CREATE TABLE user1.rpt_offline
    (rpt_id                         NUMBER(10,0) NOT NULL,
    rpt_code                       VARCHAR2(100 BYTE),
    rpt_name                       VARCHAR2(500 BYTE),
    user_name                      VARCHAR2(50 BYTE) NOT NULL,

Các câu lệnh DML với bảng trong Oracle Database

--1.SELECT
 -- Size Cho table : non-partition
select segment_name, tablespace_name, sum(bytes)/1024/1024 "MB" 
from dba_Segments where segment_type = 'TABLE' 
and owner like 'USER1'
group by segment_name, tablespace_name
order by MB desc;

Quản lý cấu trúc DLL của bảng trong Oracle Database

--1.CHECK
• Check bảng
SQL> SELECT table_name, tablespace_name, num_rows FROM DBA_TABLES WHERE tablespace_name in ('USERS', 'MY_SPACE');

[VIP5] Quản lý dữ liệu LOB trong Oracle Database từ A-Z

--1.CHECK    

dba_lobs, dba_lob_partitions,dba_lob_subpartitions,dba_segments
    select * from dba_lobs;
    select * from dba_lob_partitions;
    select * from dba_lob_subpartitions;
    select * from dba_segments where segment_type like '%LOB%';

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master