Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1424099
  • 博文数量: 314
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 3473
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(314)

文章存档

2021年(46)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

分类: Oracle

2012-02-10 10:12:02

 
  1. -- Create table

  2. -- drop table WATCH3;

  3. create table WATCH3
  4. (
  5.   ID NUMBER not null,
  6.   CAR_NO VARCHAR2(20) not null,
  7.   CAR_SPEED NUMBER(8,2) not null,
  8.   WATCH_TIME DATE not null,
  9.   DEVICE_NO VARCHAR2(20),
  10.   CLBZ VARCHAR2(1) default '0'
  11. )
  12. partition by range (WATCH_TIME)
  13. (
  14.   partition PART_BEFORE_2012_Q1 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  15.     tablespace USERS
  16.     pctfree 10
  17.     initrans 1
  18.     maxtrans 255
  19.     storage
  20.     (
  21.       initial 64K
  22.       minextents 1
  23.       maxextents unlimited
  24.     ),
  25.   partition PART_2012_Q1 values less than (TO_DATE(' 2012-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  26.     tablespace USERS
  27.     pctfree 10
  28.     initrans 1
  29.     maxtrans 255
  30.     storage
  31.     (
  32.       initial 64K
  33.       minextents 1
  34.       maxextents unlimited
  35.     ),
  36.   partition PART_2012_Q2 values less than (TO_DATE(' 2012-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  37.     tablespace USERS
  38.     pctfree 10
  39.     initrans 1
  40.     maxtrans 255
  41.     storage
  42.     (
  43.       initial 64K
  44.       minextents 1
  45.       maxextents unlimited
  46.     ),
  47.   partition PART_2012_Q3 values less than (TO_DATE(' 2012-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  48.     tablespace USERS
  49.     pctfree 10
  50.     initrans 1
  51.     maxtrans 255
  52.     storage
  53.     (
  54.       initial 64K
  55.       minextents 1
  56.       maxextents unlimited
  57.     ),
  58.   partition PART_2012_Q4 values less than (TO_DATE(' 2012-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  59.     tablespace USERS
  60.     pctfree 10
  61.     initrans 1
  62.     maxtrans 255
  63.     storage
  64.     (
  65.       initial 64K
  66.       minextents 1
  67.       maxextents unlimited
  68.     ),
  69.   partition PART_2013_Q1 values less than (TO_DATE(' 2013-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  70.     tablespace USERS
  71.     pctfree 10
  72.     initrans 1
  73.     maxtrans 255
  74.     storage
  75.     (
  76.       initial 64K
  77.       minextents 1
  78.       maxextents unlimited
  79.     ),
  80.   partition PART_2013_Q2 values less than (TO_DATE(' 2013-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  81.     tablespace USERS
  82.     pctfree 10
  83.     initrans 1
  84.     maxtrans 255
  85.     storage
  86.     (
  87.       initial 64K
  88.       minextents 1
  89.       maxextents unlimited
  90.     ),
  91.   partition PART_2013_Q3 values less than (TO_DATE(' 2013-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  92.     tablespace USERS
  93.     pctfree 10
  94.     initrans 1
  95.     maxtrans 255
  96.     storage
  97.     (
  98.       initial 64K
  99.       minextents 1
  100.       maxextents unlimited
  101.     ),
  102.   partition PART_2013_Q4 values less than (TO_DATE(' 2013-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  103.     tablespace USERS
  104.     pctfree 10
  105.     initrans 1
  106.     maxtrans 255
  107.     storage
  108.     (
  109.       initial 64K
  110.       minextents 1
  111.       maxextents unlimited
  112.     ),
  113.   partition PART_2014_Q1 values less than (TO_DATE(' 2014-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  114.     tablespace USERS
  115.     pctfree 10
  116.     initrans 1
  117.     maxtrans 255
  118.     storage
  119.     (
  120.       initial 64K
  121.       minextents 1
  122.       maxextents unlimited
  123.     ),
  124.   partition PART_2014_Q2 values less than (TO_DATE(' 2014-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  125.     tablespace USERS
  126.     pctfree 10
  127.     initrans 1
  128.     maxtrans 255
  129.     storage
  130.     (
  131.       initial 64K
  132.       minextents 1
  133.       maxextents unlimited
  134.     ),
  135.   partition PART_2014_Q3 values less than (TO_DATE(' 2014-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  136.     tablespace USERS
  137.     pctfree 10
  138.     initrans 1
  139.     maxtrans 255
  140.     storage
  141.     (
  142.       initial 64K
  143.       minextents 1
  144.       maxextents unlimited
  145.     ),
  146.   partition PART_2014_Q4 values less than (TO_DATE(' 2014-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  147.     tablespace USERS
  148.     pctfree 10
  149.     initrans 1
  150.     maxtrans 255
  151.     storage
  152.     (
  153.       initial 64K
  154.       minextents 1
  155.       maxextents unlimited
  156.     ),
  157.   partition PART_2015_Q1 values less than (TO_DATE(' 2015-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  158.     tablespace USERS
  159.     pctfree 10
  160.     initrans 1
  161.     maxtrans 255
  162.     storage
  163.     (
  164.       initial 64K
  165.       minextents 1
  166.       maxextents unlimited
  167.     ),
  168.   partition PART_2015_Q2 values less than (TO_DATE(' 2015-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  169.     tablespace USERS
  170.     pctfree 10
  171.     initrans 1
  172.     maxtrans 255
  173.     storage
  174.     (
  175.       initial 64K
  176.       minextents 1
  177.       maxextents unlimited
  178.     ),
  179.   partition PART_2015_Q3 values less than (TO_DATE(' 2015-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  180.     tablespace USERS
  181.     pctfree 10
  182.     initrans 1
  183.     maxtrans 255
  184.     storage
  185.     (
  186.       initial 64K
  187.       minextents 1
  188.       maxextents unlimited
  189.     ),
  190.   partition PART_2015_Q4 values less than (TO_DATE(' 2015-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  191.     tablespace USERS
  192.     pctfree 10
  193.     initrans 1
  194.     maxtrans 255
  195.     storage
  196.     (
  197.       initial 64K
  198.       minextents 1
  199.       maxextents unlimited
  200.     ),
  201.   partition PART_2016_Q1 values less than (TO_DATE(' 2016-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  202.     tablespace USERS
  203.     pctfree 10
  204.     initrans 1
  205.     maxtrans 255
  206.     storage
  207.     (
  208.       initial 64K
  209.       minextents 1
  210.       maxextents unlimited
  211.     ),
  212.   partition PART_2016_Q2 values less than (TO_DATE(' 2016-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  213.     tablespace USERS
  214.     pctfree 10
  215.     initrans 1
  216.     maxtrans 255
  217.     storage
  218.     (
  219.       initial 64K
  220.       minextents 1
  221.       maxextents unlimited
  222.     ),
  223.   partition PART_2016_Q3 values less than (TO_DATE(' 2016-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  224.     tablespace USERS
  225.     pctfree 10
  226.     initrans 1
  227.     maxtrans 255
  228.     storage
  229.     (
  230.       initial 64K
  231.       minextents 1
  232.       maxextents unlimited
  233.     ),
  234.   partition PART_2016_Q4 values less than (TO_DATE(' 2016-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  235.     tablespace USERS
  236.     pctfree 10
  237.     initrans 1
  238.     maxtrans 255
  239.     storage
  240.     (
  241.       initial 64K
  242.       minextents 1
  243.       maxextents unlimited
  244.     ),
  245.   partition PART_2017_Q1 values less than (TO_DATE(' 2017-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  246.     tablespace USERS
  247.     pctfree 10
  248.     initrans 1
  249.     maxtrans 255
  250.     storage
  251.     (
  252.       initial 64K
  253.       minextents 1
  254.       maxextents unlimited
  255.     ),
  256.   partition PART_2017_Q2 values less than (TO_DATE(' 2017-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  257.     tablespace USERS
  258.     pctfree 10
  259.     initrans 1
  260.     maxtrans 255
  261.     storage
  262.     (
  263.       initial 64K
  264.       minextents 1
  265.       maxextents unlimited
  266.     ),
  267.   partition PART_2017_Q3 values less than (TO_DATE(' 2017-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  268.     tablespace USERS
  269.     pctfree 10
  270.     initrans 1
  271.     maxtrans 255
  272.     storage
  273.     (
  274.       initial 64K
  275.       minextents 1
  276.       maxextents unlimited
  277.     ),
  278.   partition PART_2017_Q4 values less than (TO_DATE(' 2017-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  279.     tablespace USERS
  280.     pctfree 10
  281.     initrans 1
  282.     maxtrans 255
  283.     storage
  284.     (
  285.       initial 64K
  286.       minextents 1
  287.       maxextents unlimited
  288.     ),
  289.   partition PART_2018_Q1 values less than (TO_DATE(' 2018-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  290.     tablespace USERS
  291.     pctfree 10
  292.     initrans 1
  293.     maxtrans 255
  294.     storage
  295.     (
  296.       initial 64K
  297.       minextents 1
  298.       maxextents unlimited
  299.     ),
  300.   partition PART_2018_Q2 values less than (TO_DATE(' 2018-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  301.     tablespace USERS
  302.     pctfree 10
  303.     initrans 1
  304.     maxtrans 255
  305.     storage
  306.     (
  307.       initial 64K
  308.       minextents 1
  309.       maxextents unlimited
  310.     ),
  311.   partition PART_2018_Q3 values less than (TO_DATE(' 2018-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  312.     tablespace USERS
  313.     pctfree 10
  314.     initrans 1
  315.     maxtrans 255
  316.     storage
  317.     (
  318.       initial 64K
  319.       minextents 1
  320.       maxextents unlimited
  321.     ),
  322.   partition PART_2018_Q4 values less than (TO_DATE(' 2018-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  323.     tablespace USERS
  324.     pctfree 10
  325.     initrans 1
  326.     maxtrans 255
  327.     storage
  328.     (
  329.       initial 64K
  330.       minextents 1
  331.       maxextents unlimited
  332.     ),
  333.   partition PART_2019_Q1 values less than (TO_DATE(' 2019-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  334.     tablespace USERS
  335.     pctfree 10
  336.     initrans 1
  337.     maxtrans 255
  338.     storage
  339.     (
  340.       initial 64K
  341.       minextents 1
  342.       maxextents unlimited
  343.     ),
  344.   partition PART_2019_Q2 values less than (TO_DATE(' 2019-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  345.     tablespace USERS
  346.     pctfree 10
  347.     initrans 1
  348.     maxtrans 255
  349.     storage
  350.     (
  351.       initial 64K
  352.       minextents 1
  353.       maxextents unlimited
  354.     ),
  355.   partition PART_2019_Q3 values less than (TO_DATE(' 2019-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  356.     tablespace USERS
  357.     pctfree 10
  358.     initrans 1
  359.     maxtrans 255
  360.     storage
  361.     (
  362.       initial 64K
  363.       minextents 1
  364.       maxextents unlimited
  365.     ),
  366.   partition PART_2019_Q4 values less than (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  367.     tablespace USERS
  368.     pctfree 10
  369.     initrans 1
  370.     maxtrans 255
  371.     storage
  372.     (
  373.       initial 64K
  374.       minextents 1
  375.       maxextents unlimited
  376.     ),
  377.   partition PART_2020_Q1 values less than (TO_DATE(' 2020-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  378.     tablespace USERS
  379.     pctfree 10
  380.     initrans 1
  381.     maxtrans 255
  382.     storage
  383.     (
  384.       initial 64K
  385.       minextents 1
  386.       maxextents unlimited
  387.     ),
  388.   partition PART_2020_Q2 values less than (TO_DATE(' 2020-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  389.     tablespace USERS
  390.     pctfree 10
  391.     initrans 1
  392.     maxtrans 255
  393.     storage
  394.     (
  395.       initial 64K
  396.       minextents 1
  397.       maxextents unlimited
  398.     ),
  399.   partition PART_2020_Q3 values less than (TO_DATE(' 2020-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  400.     tablespace USERS
  401.     pctfree 10
  402.     initrans 1
  403.     maxtrans 255
  404.     storage
  405.     (
  406.       initial 64K
  407.       minextents 1
  408.       maxextents unlimited
  409.     ),
  410.   partition PART_2020_Q4 values less than (TO_DATE(' 2020-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  411.     tablespace USERS
  412.     pctfree 10
  413.     initrans 1
  414.     maxtrans 255
  415.     storage
  416.     (
  417.       initial 64K
  418.       minextents 1
  419.       maxextents unlimited
  420.     ),
  421.   partition PART_2021_Q1 values less than (TO_DATE(' 2021-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  422.     tablespace USERS
  423.     pctfree 10
  424.     initrans 1
  425.     maxtrans 255
  426.     storage
  427.     (
  428.       initial 64K
  429.       minextents 1
  430.       maxextents unlimited
  431.     ),
  432.   partition PART_2021_Q2 values less than (TO_DATE(' 2021-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  433.     tablespace USERS
  434.     pctfree 10
  435.     initrans 1
  436.     maxtrans 255
  437.     storage
  438.     (
  439.       initial 64K
  440.       minextents 1
  441.       maxextents unlimited
  442.     ),
  443.   partition PART_2021_Q3 values less than (TO_DATE(' 2021-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  444.     tablespace USERS
  445.     pctfree 10
  446.     initrans 1
  447.     maxtrans 255
  448.     storage
  449.     (
  450.       initial 64K
  451.       minextents 1
  452.       maxextents unlimited
  453.     ),
  454.   partition PART_2021_Q4 values less than (TO_DATE(' 2021-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  455.     tablespace USERS
  456.     pctfree 10
  457.     initrans 1
  458.     maxtrans 255
  459.     storage
  460.     (
  461.       initial 64K
  462.       minextents 1
  463.       maxextents unlimited
  464.     ),
  465.   partition PART_OTHERS values less than (MAXVALUE)
  466.     tablespace USERS
  467.     pctfree 10
  468.     initrans 1
  469.     maxtrans 255
  470.     storage
  471.     (
  472.       initial 64K
  473.       minextents 1
  474.       maxextents unlimited
  475.     )
  476. );

检查分区设置情况:

select p.* from user_tab_partitions p order by 1,p.partition_position;

 

阅读(1313) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~