分区表管理

分区表管理

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1577319/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1577319/