#!/bin/bash

username=’test_caolh’
paswd=’1qaz!QAZ’
tnsname=’PROD’

sql=’sqlplus ‘$username‘/‘$paswd‘@’$tnsname’’

echo “Check oracle test table oracle_tab[1~9) is exist?”

$sql << EOF

drop table oracle_tab1 purge;
drop table oracle_tab2 purge;
drop table oracle_tab3 purge;
drop table oracle_tab4 purge;
drop table oracle_tab6 purge;
drop table oracle_tab5 purge;
drop table oracle_tab7 purge;
drop table oracle_tab8 purge;
drop table oracle_tab9 purge;
drop table oracle_tab10 purge;

EOF

echo “start create test table like oracle_tab[1~8]…”

$sql << EOF

    create table oracle_tab1 (test_1 number(38) primary key,test_2 number(38,29),test_3 integer,test_4 float(126),test_5 binary_float,test_6 binary_double);
    create table oracle_tab2 (test_1 number(38) primary key,test_2 char(255),test_3 varchar2(4000),test_4 nvarchar2(2000),test_5 varchar(30));
    create table oracle_tab3 (test_1 number(38) primary key,test_2 date,test_3 timestamp,test_4 TIMESTAMP WITH LOCAL TIME ZONE,test_5 TIMESTAMP WITH TIME ZONE);
    create table oracle_tab4 (test_1 number(38) primary key,test_2 blob,test_3 clob,test_4 long,test_5 raw(2000));
    create table oracle_tab5 (test_1 number(38,27),test_2 char(255),test_3 date,test_4 varchar2(4000),constraint pk_test12 primary key (test_1,test_2));
    create table oracle_tab6 (test_1 number(38,27),test_2 char(255),test_3 integer,test_4 float(126),test_5 timestamp,constraint pk_test1 primary key (test_1,test_2),constraint fk_test foreign key(test_1,test_2) references oracle_tab5(test_1,test_2));
    create table oracle_tab7 (test_1 nchar(200),test_2 real primary key);
    create table "oracle_tab8" (test_1 nclob,test_2 long raw,test_3 number primary key,"number" number(12));
    create table "ORACLE_TAB9" (test_1 number primary key);
    create table "create" ("number" number);

EOF

read -p “Please check oracle_tab[0~8] added to ZCBUS software queue! [Y/N]” var

if [ $var = ‘y’ -o $var = ‘y’ ];then

$sql << EOF

         ALTER TABLE ORACLE_TAB7 ADD LONG_RAW LONG RAW;
   alter table oracle_tab7 add tab7_var varchar2(4000);
   alter table oracle_tab7 add tab7_lob blob;
   alter table oracle_tab7 add tab7_null number(38) null;
   alter table oracle_tab7 add tab7_not_null number(38) not null;
   alter table oracle_tab7 add tab7_defa number(38) default 10;
   alter table oracle_tab7 add tab7_defa1 number(38) default 10  not null;
   alter table oracle_tab7 add (tab7_var2 varchar2(300),tab7_num2 number(38));

   alter table oracle_tab7 modify TAB7_NUM2 number;
   alter table oracle_tab7 modify tab7_var2 varchar(30);
   alter table oracle_tab7 modify tab7_num2 number default 10;
   alter table ORACLE_TAB7 modify tab7_num2 number default 10 not null;

   alter table oracle_tab7  rename column tab7_defa to tab7_defa2;

   ALTER TABLE "oracle_tab8" add "timestamp" timestamp;
         ALTER TABLE "oracle_tab8" ADD TEST_4 NUMBER(12);
         alter table "oracle_tab8" add test_5 varchar(10) null;
         ALTER TABLE "oracle_tab8" ADD Test_6 number default 10 not null;
         create index index_tab8_test_4 on "oracle_tab8"(test_4); 
         alter table "oracle_tab8" drop COLUMN TEST_5;      

   ALTER TABLE "ORACLE_TAB9" ADD TEST_2 NUMBER(18);
   alter table     "ORACLE_TAB9" add test_3 varchar2(30) default 'abc';
   create index INDEX_ORACLE_TAB9_TEST_5 ON "ORACLE_TAB9"(TEST_3);
   ALTER TABLE "ORACLE_TAB9" DROP COLUMN TEST_3; 



   create unique index index_oracle_tab7 on oracle_tab7(tab7_defa2);
   alter table oracle_tab7 add constraint uni_cons_tab7_defa1 unique(tab7_defa1);      
   create index index_tab1_test_2  on oracle_tab1(test_2);
   drop index index_oracle_tab7;
   alter table oracle_tab7 drop constraint uni_cons_tab7_defa1;
   comment on table oracle_tab7 is 'this is a test table';
   comment on column oracle_tab7.test_2  is  'this is a test filed';

   alter table oracle_tab7 drop column tab7_null;



   alter table "create" add "VARCHAR2" number(38) primary key;
   alter table "create" drop primary key;
   alter table "create" add constraint pk_t_create primary key ("number");
   create unique index ind_create_varchar2 on "create"("VARCHAR2");
   alter table "create" add "integer" integer default 112 not null; 

alter table oracle_tab7 add check(tab7_defa2 not like ‘%2%’);

EOF

else

    echo "Please add table oracle_tab[1~8] to ZCBUS software queue!"

fi

分区表测试

一、 ##　RANGE分区
create table oracle_range_tab (id number primary key,name varchar2(18),date1 timestamp)
partition by range(date1)
interval (numtoyminterval(1,’year’))
(
partition part1 values less than (to_date(‘2019-12-31’,’yyyy-mm-dd’)),
partition part2 values less than (to_date(‘2020-12-31’,’yyyy-mm-dd’))
);

create table oracle_range_tab1 (id number primary key,name varchar2(18),date1 timestamp)
partition by range(date1)
(
partition part1 values less than (to_date(‘2019-12-31’,’yyyy-mm-dd’)),
partition part2 values less than (to_date(‘2020-12-31’,’yyyy-mm-dd’)),
partition part3 values less than (maxvalue)
);

添加range分区(如果主分区是介于最小以及最大分区之间，那么添加不了（有max的需要删掉在添加），只能添加值大于最后一个分区的主分区以及相对应的子分区)

split分区
alter table oracle_range_tab1 split partition part3 at (to_date(‘2021-12-31’,’yyyy-mm-dd’)) into (partition part3_split,partition part3);
alter table oracle_range_list_tab split subpartition ……

删除range分区

alter table oracle_range_tab1 drop partition part1;

清空range分区

alter table oracle_range_tab1 truncate subpartition (part2);

交换分区表

create table oracle_ex_range_tab (id number primary key,name varchar2(18),date1 timestamp);(创建一张空表做交换表)
alter table oracle_range_tab exchange partition with table oracle_ex_range_tab including indexes update global indexes;

二、 ## HASH分区
create table oracle_hash_tab (id number primary key,name varchar2(18),date1 date)
compress
partition by hash(date1)
(
partition part1,
partition part2
);

添加HASH分区

alter table oracle_hash_tab add partition part3;
alter table oracle_hash_tab add partition part4;

删除HASH分区

alter table oracle_hash_tab drop partition part3; （执行报异常）
alter table oracle_hash_tab drop partition part4; （执行报异常）

清空HASH分区

alter table oracle_hash_tab truncate partition part1;

三、 ## LIST分区
create table oracle_list_tab (id number primary key,name varchar2(18),sex varchar2(4),date1 date)
partition by list(name)
(
partition part1 values (‘x’,’X’),
partition part2 values (‘y’,’Y’)
);

添加LIST分区

alter table oracle_list_tab add partition part3 values (‘z’,’Z’);

删除list分区

alter table oracle_list_tab drop partition part3;

清空list分区

alter table oracle_list_tabe truncate partition part1;

###

四、 ## RANG-LIST组合分区
create table oracle_range_list_tab (id number primary key,name varchar2(18),sex varchar2(4),date1 date)
partition by range(date1)
subpartition by list(sex)
(
partition part1 values less than (to_date(‘2019-12-31’,’yyyy-mm-dd’))
(
subpartition part1_list1 values (‘X’),
subpartition part1_list2 values (‘Y’)
),
partition part2 values less than (to_date(‘2020-12-31’,’yyyy-mm-dd’))
(
subpartition part2_list1 values (‘X’),
subpartition part2_list2 values (‘Y’)
)
);

添加主分区时并指定子分区 (在添加分区时，若不指定子分区，则主分区不能再添加子分区)

alter table oracle_range_list_tab add partition part3 values less than (to_date(‘2021-12-31’,’yyyy-mm-dd’)) (subpartition part3_list1 values (‘X’),subpartition part3_list2 values (‘Y’));

添加子分区

alter table oracle_range_list_tab modify partition part1 add subpartition part1_list3 values (‘z’,’Z’);

删除分区及子分区

alter table oracle_range_list_tab drop partition part3;
alter table oracle_range_list_tab drop subpartition part1_list3;

清空分区及子分区

alter table oracle_range_list_tab truncate suppartition part1_list2;
alter table oracle_range_list_tab truncate partition part1;

交换子分区

create table oracle_ex_range_list_tab (id number primary key,name varchar2(18),sex varchar2(4),date1 date)；（创建一张空交换表）
alter table oracle_range_list_tab exchange subpartition part2_list2 with table oracle_ex_range_list_tab including indexes update global indexes;

在分区表和普通变进行分区交换的时候，不带上including indexes update global indexes参数，交换后，分区表交换的分区索引变成不可用（不管是本地还是全局索引），同时普通表索引也会变成不可用。需要重建全局索引，或者按照分区重建分区索引。
local本地索引，除了exchange交换分区的不带上including indexes update global indexes参数时候，本地索引会变成不可用,需要重建对应的交换分区索引alter index index_name rebuild partition P1;其它情况均可用;
Global全局索引，除了add, exchange交换分区的带上including indexes update global indexes参数时候有效，其它操作均会失效（drop,truncate,split,exchange不带including indexes update global indexes）

分区表有两种索引：全局索引和本地索引（global索引和local 索引），有关全局索引和本地索引具体怎么用，以及那种更好的结合场景，上面是实验操作。

注意：生产中或者面试的时候，如果有提到全局索引，为了让全局索引不失效，建议加上update indexes或者update global index，

即 alter table drop/truncate/spit/merge partition_name/subpartition_name update (global) indexes;如果不加，生产上24*7业务有可能全局立马失效，然后你就可能下岗了……

包括一些create index online/nologging 操作，最大程度的不影响DML性能；update indexes 是直接作用基表，再进行分区操作的时候 不会让索引失效，并且DDL操作不受影响！

五、 ## IOT表
create table oracle_iot_tab (id number primary key,name varchar(12),phone varchar(11)) organization index;

begin
for i in 1 .. 1000 loop
if mod(i,2)=0 then
insert into oracle_range_tab values (i,’aaa’||to_char(i,’0000’),sysdate-i);
insert into oracle_hash_tab values (i,’maria’||to_char(i,’0000’),sysdate-i);
else
insert into oracle_range_tab values (i,’bbb’||to_char(i,’0000’),sysdate-i);
insert into oracle_hash_tab values (i,’luoli’||to_char(i,’0000’),sysdate-i);
end if;
end loop;
commit;
end;

create table test.test_poc (id number,name varchar2(500),op_time timestamp);

delete from test.test_poc;
begin
for i in 1 .. 20 loop
insert into test.test_poc values (i,’abc’||to_char(i,’0000000’),sysdate-i);
end loop;
commit;
end;

begin
for i in 1 .. 18 loop
update test.test_poc set name=’ZFFFF’||to_char(i,’0000000’),op_time=sysdate-i where id=i;
end loop;
commit;
end;

begin
for i in 4 .. 15 loop
delete from test.test_poc where id=i;
end loop;
commit;
end;