【Oracle】第四次实验报告

【Oracle】第四次实验报告

1)日期型

——获取当前日期

SQL> select sysdate from dual;

SYSDATE
-----------
2021/11/5 1

SQL> select to_char(sysdate,‘DY’) from dual;

TO_CHAR(SYSDATE,'DY')
---------------------
星期五

SQL> select to_date(‘2021/11/05’,‘yyyy/mm/dd’)from dual;

TO_DATE('2021/11/05','YYYY/MM/
------------------------------
2021/11/5

2)list 分区

——分区表的建立(list)

SQL> create table part_book1
2 ( bid number(4),
3 bookname VARCHAR2(20),
4 bookpress VARCHAR2(30),
5 booktime date)
6 partition by list(bookpress)
7 (partition part1 values(‘清华大学出版社’) tablespace system,
8 partition part2 values(‘教育出版社’) tablespace users);

Table created

SQL> insert into part_book1 values(1,‘oralce’,‘清华大学出版社’,to_date(‘20110102’,‘yyyymmdd’));

1 row inserted

SQL> insert into part_book1 values(2,‘音乐基础鉴赏’,‘教育出版社’,to_date(‘20120102’,‘yyyymmdd’));

1 row inserted

SQL> select * from part_book1;

  BID BOOKNAME             BOOKPRESS                  BOOKTIME
----- -------------------- ------------------------------ -----------
    1 oralce               清华大学出版社               2011/1/2
    2 音乐基础鉴赏           教育出版社                  2012/1/2

SQL> select * from part_book1 partition(part1);

  BID BOOKNAME             BOOKPRESS                  BOOKTIME
----- -------------------- ------------------------------ -----------
   1 oralce               清华大学出版社                2011/1/2

List分区表如何增加分区：
SQL> alter table part_book1 add partition part3 values(default) tablespace system;

Table altered

与分区表相关的数据字典：
SQL> select * from dba_tab_partitions where table_name=‘PART_BOOK1’;

TABLE_OWNER                    TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ ------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
SYSTEM                         PART_BOOK1                     NO        PART1                                           0 '清华大学出版社'                                                                                16                  1 SYSTEM                                 10         40          1        255          65536                      1 2147483645                       1               1 YES     DISABLED                                                                                                   DEFAULT     NO           NO
SYSTEM                         PART_BOOK1                     NO        PART2                                           0 '教育出版社'                                                                                    12                  2 USERS                                  10                     1        255          65536                      1 2147483645                                         YES     DISABLED                                                                                                   DEFAULT     NO           NO
SYSTEM                         PART_BOOK1                     NO        PART3                                           0 default                                                                                          7                  3 SYSTEM                                 10         40          1        255          65536                      1 2147483645                       1               1 YES     DISABLED                                                                                                   DEFAULT     NO           NO

3)range 分区

分区表的建立（range）：

SQL> create table part_book
2 ( bid number(4),
3 bookname VARCHAR2(20),
4 bookpress VARCHAR2(30),
5 booktime date)
6 partition by range(booktime)
7 (partition part1 values less than(to_date(‘20100101’,‘yyyymmdd’)) tablespace system,
8 partition part2 values less than (to_date(‘20120101’,‘yyyymmdd’)) tablespace users,
9 partition part3 values less than (MAXVALUE) tablespace users
10 );

Table created

SQL> insert into part_book values(1,‘oracle’,‘清华大学出版社’,to_date(‘20110102’,‘yyyymmdd’));

1 row inserted

SQL> insert into part_book values(2,‘oracle’,‘清华大学出版社’,to_date(‘20090101’,‘yyyymmdd’));

1 row inserted

SQL> select * from part_book;

  BID BOOKNAME             BOOKPRESS                  BOOKTIME
----- -------------------- ------------------------------ -----------
    2 oracle              清华大学出版社                2009/1/1
    1 oracle              清华大学出版社                2011/1/2

SQL> select * from part_book partition(part1);

  BID BOOKNAME             BOOKPRESS                  BOOKTIME
----- -------------------- ------------------------------ -----------
    2 oracle              清华大学出版社                2009/1/1

分区表的切割：

SQL> alter table part_book
2 split partition part3 at (to_date(‘20140101’,‘yyyymmdd’))
3 Into (partition part3,partition part4)

Table altered