分区表管理
1 分区表创建
SQL> create tablespace t1 datafile '/home/oracle/t1.dbf' size 5M;
SQL> create tablespace t2 datafile '/home/oracle/t2.dbf' size 5M;
SQL> create tablespace t3 datafile '/home/oracle/t3.dbf' size 5M;
SQL> create table txn(id number,cc varchar2(30)) partition by range(id)
(
partition part1 values less than(10) tablespace t1,
partition part2 values less than(20) tablespace t2,
partition part3 values less than(30) tablespace t3
);
2 创建索引
SQL> create index txn_id on txn(id);
SQL> select index_name,index_type,table_name from user_indexes where table_name='TXN';
INDEX_NAME INDEX_TYPE TABLE_NAME
---------- --------------------------- ----------
TXN_ID NORMAL TXN
3 插入数据
SQL> declare
2 begin
3 for i in 1..29 loop
4 insert into txn(id) values(i);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> commit;
4 导入导出分区表
[oracle@chen ~]$ exp card/card file=txn.dmp log=txn.log tables=txn
[oracle@chen ~]$ exp card/card file=txn01.dmp log=txn01.log tables=txn:part1
[oracle@chen ~]$ exp card/card file=txn02.dmp log=txn02.log tables=txn:part2
[oracle@chen ~]$ exp card/card file=txn03.dmp log=txn03.log tables=txn:part3
[oracle@chen ~]$ imp card01/card01 file=txn01.dmp fromuser=card touser=card01
SQL> conn card01/card01
SQL> select * from txn;
[oracle@chen ~]$ imp card01/card01 file=txn02.dmp fromuser=card touser=card01 ignore=y
SQL> select * from card01.txn;
5 分区表查看
怎样查询出oracle数据库中所有的的分区表
SQL> select * from user_tables a where a.partitioned='YES';
SQL> select table_name,partition_name from user_tab_partitions where table_name=’TXN’;
TABLE_NAME PARTITION_NAME
---------- ------------------------------
TXN PART1
TXN PART2
TXN PART3
SQL> select * from txn partition(part1);
SQL> select * from txn partition(part2);
SQL> select * from txn partition(part3);
6
分区表增加分区
SQL> alter table txn add partition part4
values less than(40) tablespace t1;
SQL> select table_name,partition_name from user_tab_partitions where table_name='TXN';
TABLE_NAME PARTITION_NAME
---------- ------------------------------
TXN PART1
TXN PART2
TXN PART3
TXN PART4
7 分区表truncate
SQL> alter table txn truncate partition(part1);
SQL> select * from txn partition(part1);
SQL> select * from txn partition(part2);
ID CC
---------- ------------------------------
10
11
12
13
14
15
16
17
18
19
10 rows selected.
SQL> select * from txn partition(part3);
ID CC
---------- ------------------------------
20
21
22
23
24
25
26
27
28
29
10 rows selected.
SQL> select table_name,partition_name from user_tab_partitions where table_name='TXN';
TABLE_NAME PARTITION_NAME
---------- ------------------------------
TXN PART1
TXN PART2
TXN PART3
TXN PART4
8 分区表删除分区
SQL> alter table txn drop partition part1;
SQL> select table_name,partition_name from user_tab_partitions where table_name='TXN';
TABLE_NAME PARTITION_NAME
---------- ------------------------------
TXN PART2
TXN PART3
TXN PART4
9 删除表空间
SQL> drop tablespace t1 including contents and datafiles;
drop tablespace t1 including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
SQL> drop index txn_id;
Index dropped.
SQL> drop tablespace t1 including contents and datafiles;
drop tablespace t1 including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
如何之前没有创建索引，那么可以删除表空间
SQL> drop tablespace t1 including contents and datafiles;
SQL> drop tablespace t2 including contents and datafiles;
SQL> drop tablespace t3 including contents and datafiles;
SQL> select * from txn;
select * from txn
*
ERROR at line 1:
ORA-00942: table or view does not exist
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29785807/viewspace-1577319/ ，如需转载，请注明出处，否则将追究法律责任。
