建立oracle10g 11g分割槽表

1.1 9i、10g、11gR1、11gR2支援分割槽情況

     區間     列表    雜湊

區間 11gR1   9iR2    9iR2

列表 11gR1   11gR1   11gR1

雜湊 11gR2   11gR2   11gR2

1.2. 單分割槽建立舉例

1.2.1.建立範圍分割槽表和全域性索引

SQL> create table aning_range

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date)

7 (

8 partition aning_p1_2010 values less than

9 (to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace aningtbs1,

10 partition aning_p2_2011 values less than

11 (to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace aningtbs2,

12 partition aning_max values less than (maxvalue)

13 );

Table created.

建立enable row movement的範圍分割槽表

SQL> create table aning_range_en_rowmove

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 storage (initial 100k next 50k) logging

7 partition by range (aning_date)

8 (partition aning_p1_2010 values less than

9 (to_date('2011-01-01','yyyy-mm-dd')) tablespace aningtbs1 storage (initial 20k next 10k),

10 partition aning_p2_2011 values less than

11 (to_date('2012-01-01','yyyy-mm-dd')) tablespace aningtbs2,

12 partition aning_max values less than (maxvalue)

13 )

14 enable row movement;

Table created.

建立範圍分割槽表全域性索引

SQL> create index idx_aning_range on aning_range(aning_date)global;

Index created.

SQL> create index idx_aning_range_1 on aning_range(aning_id) global;

Index created.

建立索引時，後面加global也不是全域性分割槽索引

建立全域性分割槽索引

SQL> create index idx_aning_range_g on aning_range(aning_date,aning_id)

2 global partition by range(aning_date)

3 (partition aning_p1_2010 values less than

4 (to_date('2011-01-01','yyyy-mm-dd')) tablespace aningtbs1,

5 partition aning_p2_2011 values less than

6 (to_date('2012-01-01','yyyy-mm-dd')) tablespace aningtbs2,

7 partition aning_max values less than (maxvalue)

8 );

Index created.

測試Oracle不支援非字首全域性分割槽索引

SQL> create index idx_aning_range_g on aning_range(aning_id)

2 global partition by range(aning_date)

3 (partition aning_p1_2010 values less than

4 (to_date('2011-01-01','yyyy-mm-dd')) tablespace aningtbs1,

5 partition aning_p2_2011 values less than

6 (to_date('2011-01-01','yyyy-mm-dd')) tablespace aningtbs2,

7 partition aning_max values less than (maxvalue)

8 );

global partition by range(aning_date)

*

ERROR at line 2:

ORA-14038: GLOBAL partitioned index must be prefixed

這裡的字首是指建立分割槽索引時的索引分割槽鍵

測試全域性分割槽索引的分割槽方式不一定必須和分割槽表分割槽一致

SQL> create index idx_aning_range_g1 on aning_range(aning_date,aning_id)

2 global partition by range(aning_date)

3 (

4 partition aning_p2_2011 values less than

5 (to_date('2012-01-01','yyyy-mm-dd')) tablespace aningtbs2,

6 partition aning_max values less than (maxvalue)

7 );

Index created.

測試使用非分割槽鍵建立全域性分割槽索引

SQL> create index idx_aning_range_g2on aning_range(aning_id)

2 global partition by range(aning_id)

3 (

4 partition aning_p2_2011 values less than

5 (100) tablespace aningtbs2,

6 partition aning_max values less than (maxvalue)

7 );

Index created.

1.2.2.建立間隔分割槽表

1.2.2.1.按月間隔

SQL> create table aning_interval

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date)

7 interval(numtoyminterval(1,'MONTH'))

8 (partition aning_201009 values less than (to_date('2010-10-01','yyyy-mm-dd'))

9 );

Table created.

1.2.2.2.按日間隔

SQL> create table aning_interval_day

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date)

7 interval(numtodsinterval(1,'day'))

8 (partition aning_20100930 values less than (to_date('2010-10-01','yyyy-mm-dd'))

9 );

Table created.

1.2.2.3.按秒間隔

SQL> create table aning_interval_second

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date)

7 interval(numtodsinterval(1,'second'))

8 (partition aning_20100930111111 values less than (to_date('2010-10-01 11:11:12','yyyy-mm-dd hh24:mi:ss'))

9 );

Table created.

1.2.3.建立雜湊分割槽和全域性索引

方法一、

SQL> create table aning_hash

2 (aning_id number,

3 aning_name varchar2(100)

4 )

5 partition by hash(aning_id)

6 partitions 4

7 store in(aningtbs1,aningtbs2,aningtbs3,aningtbs4);

Table created.

方法二、

SQL> create table aning_hash2

2 (aning_id number,

3 aning_name varchar2(100)

4 )

5 storage(initial 10k)

6 partition by hash(aning_id)

7 (partition aning_p1 tablespace aningtbs1,

8 partition aning_p2 tablespace aningtbs2,

9 partition aning_p3 tablespace aningtbs3,

10 partition aning_p4 tablespace aningtbs4

11 );

Table created.

建立本地索引

SQL> create index idx_aning_hash on aning_hash(aning_id) local;

Index created.

建立雜湊全域性索引，10g新特性

方法一、

SQL> create index idx_aning_hash_global on

2 aning_hash(aning_name,aning_id) global

3 partition by hash(aning_name)

4 (

5 partition aning_p1 tablespace aningtbs1,

6 partition aning_p2 tablespace aningtbs2,

7 partition aning_p3 tablespace aningtbs3,

8 partition aning_p4 tablespace aningtbs4

9 );

Index created.

方法二、

SQL> create index idx_aning_hash_global on

2 aning_hash(aning_name) global

3 partition by hash(aning_name)

4 partitions 8;

Index created.

1.2.4.建立列表分割槽表

SQL> create table aning_list

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_address varchar2(10)

5 )

6 partition by list (aning_address)

7 (partition aning_list_p1 values('BJ','PG'),

8 partition aning_list_p2 values('CN','SH')

9 );

Table created.

建立帶預設分割槽的列表分割槽

SQL> create table aning_list_default

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_address varchar2(10)

5 )

6 partition by list (aning_address)

7 (partition aning_list_p1 values('BJ','PG'),

8 partition aning_list_p2 values('CN','SH'),

9 partition aning_list_default values(default)

10 );

Table created.

1.2.5.建立引用分割槽表

SQL> create table aning_reference

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date,

5 constraint aning_ref_pk primary key(aning_id)

6 )

7 partition by range (aning_date)

8 (

9 partition aning_ref_p2010 values less than

10 (to_date('2011-01-01','yyyy-mm-dd')),

11 partition aning_ref_p2011 values less than

12 (to_date('2012-01-01','yyyy-mm-dd'))

13 );

Table created.

SQL>

SQL>

SQL> create table aning_ref

2 (aning_id number not null,

3 aning_ref_id number not null,

4 aning_address varchar2(100),

5 constraint aning_ref_fk

6 foreign key(aning_id) references aning_reference(aning_id)

7 )

8 partition by reference(aning_ref_fk);

Table created.

驗證引用分割槽表

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a30

SQL> select partition_name, high_value

2 from user_tab_partitions

3 where table_name = 'ANING_REF';

PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------

ANING_REF_P2010

ANING_REF_P2011

SQL>

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select partition_name, high_value

2 from user_tab_partitions

3 where table_name = 'ANING_REFERENCE';

PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

ANING_REF_P2010 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

LS_CALENDAR=GREGORIA

ANING_REF_P2011 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

LS_CALENDAR=GREGORIA

SQL>

1.2. 建立複合分割槽表

1.3.1.建立範圍-雜湊複合分割槽表

SQL> create table aning_range_hash

2 (aning_id number,

3 aning_name number,

4 aning_date date

5 )

6 partition by range(aning_date)

7 subpartition by hash(aning_id)

8 subpartitions 4 store in(aningtbs1,aningtbs2,aningtbs3,aningtbs4)

9 (partition aning_range_hash2010 values less than (to_date('2011-01-01','yyyy-mm-dd')),

10 partition aning_range_hash2011 values less than (to_date('2012-01-01','yyyy-mm-dd')),

11 partition aning_range_hash2012 values less than (to_date('2013-01-01','yyyy-mm-dd')),

12 partition aning_range_hashmax values less than(maxvalue)

13 );

Table created.

range指定不同子分割槽

SQL> create table aning_range_hash1

2 (aning_id number,

3 aning_name number,

4 aning_date date

5 )

6 partition by range(aning_date)

7 subpartition by hash(aning_id)

8 subpartitions 4 store in(aningtbs1,aningtbs2,aningtbs3,aningtbs4)

9 (partition aning_range_hash2010 values less than (to_date('2011-01-01','yyyy-mm-dd')),

10 partition aning_range_hash2011 values less than (to_date('2012-01-01','yyyy-mm-dd'))

11 store in(aningtbs5,aningtbs6),

12 partition aning_range_hash2012 values less than (to_date('2013-01-01','yyyy-mm-dd')),

13 partition aning_range_hashmax values less than(maxvalue)

14 (subpartition aning_range_hashmaxp1 tablespace aningtbs7,

15 subpartition aning_range_hashmaxp2 tablespace aningtbs8

16 )

17 );

Table created.

建立本地索引

SQL> CREATE INDEX idx_aning_range_hash ON aning_range_hash(aning_date)

2 LOCAL STORE IN (aningtbs1, aningtbs3, aningtbs5);

Index created.

1.3.2.建立範圍-列表複合分割槽表

SQL> create table aning_range_list

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 tablespace aningtbs1

7 partition by range(aning_date)

8 subpartition by list(aning_name)

9 (partition aning_range_list2010 values less than (to_date('2011-01-01','yyyy-mm-dd'))

10 (subpartition range_list2010_sub1 values('BJ','PG'),

11 subpartition range_list2010_sub2 values('CN','SH')

12 ),

13 partition aning_range_list2011 values less than (to_date('2012-01-01','yyyy-mm-dd'))

14 (subpartition range_list2011_sub1 values('BJ','PG'),

15 subpartition range_list2011_sub2 values('CN','SH')

16 )

17 );

Table created.

1.3.3.建立範圍-範圍複合分割槽表

SQL> create table aning_range_range

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date,

5 aning_time timestamp

6 )

7 partition by range(aning_date)

8 subpartition by range(aning_time)

9 (partition aning_range_range2010 values less than

10 (to_date('2011-01-01','yyyy-mm-dd'))

11 (subpartition range_range_sub201009 values less than

12 (to_date('2010-10-01','yyyy-mm-dd')),

13 subpartition range_range_sub201010 values less than

14 (to_date('2010-11-01','yyyy-mm-dd')),

15 subpartition range_range_sub2010max values less than (maxvalue)

16 ),

17 partition aning_range_range2011 values less than

18 (to_date('2012-01-01','yyyy-mm-dd'))

19 (subpartition range_range_sub201109 values less than

20 (to_date('2011-10-01','yyyy-mm-dd')),

21 subpartition range_range_sub201110 values less than

22 (to_date('2011-11-01','yyyy-mm-dd')),

23 subpartition range_range_sub2011max values less than (maxvalue)

24 )

25 );

Table created.

1.3.4.建立列表-雜湊複合分割槽表

SQL> create table aning_list_hash

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_address varchar2(10)

5 )

6 partition by list(aning_address)

7 subpartition by hash(aning_id) subpartitions 4

8 (partition list_hash_sub1 values('BJ','PG'),

9 partition list_hash_sbu2 values('CN','SH')

10 );

Table created.

驗證子分割槽

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select partition_name, high_value

2 from user_tab_partitions

3 where table_name = 'ANING_LIST_HASH';

PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

LIST_HASH_SBU2 'CN', 'SH'

LIST_HASH_SUB1 'BJ', 'PG'

SQL>

SQL>

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select subpartition_name, high_value

2 from user_tab_subpartitions

3 where table_name = 'ANING_LIST_HASH';

SUBPARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

SYS_SUBP168

SYS_SUBP167

SYS_SUBP166

SYS_SUBP165

SYS_SUBP172

SYS_SUBP171

SYS_SUBP170

SYS_SUBP169

8 rows selected.

1.3.5.建立列表-列表複合分割槽表

SQL> create table aning_list_list

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_address varchar2(10),

5 aning_region varchar2(10)

6 )

7 partition by list (aning_region)

8 subpartition by list (aning_address)

9 (partition list_list_p1 values('CN','US')

10 (subpartition list_listsub1 values('BJ','SH'),

11 subpartition list_listsub2 values('GZ','PG')

12 ),

13 partition list_list_p2 values('HK','TW')

14 (subpartition list_listsub3 values('TLW','TB'),

15 subpartition list_listsub4 values('JL','TM')

16 )

17 );

Table created.

驗證分割槽情況

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select partition_name, high_value

2 from user_tab_partitions

3 where table_name = 'ANING_LIST_LIST';

PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

LIST_LIST_P1 'CN', 'US'

LIST_LIST_P2 'HK', 'TW'

SQL>

SQL>

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select subpartition_name, high_value

2 from user_tab_subpartitions

3 where table_name = 'ANING_LIST_LIST';

SUBPARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

LIST_LISTSUB1 'BJ', 'SH'

LIST_LISTSUB2 'GZ', 'PG'

LIST_LISTSUB3 'TLW', 'TB'

LIST_LISTSUB4 'JL', 'TM'

1.3.6.建立列表-範圍複合分割槽表

SQL> create table aning_list_range

2 (aning_id number,

3 aning_name varchar2(100),

4 balance number

5 )

6 partition by list (aning_name)

7 subpartition by range (balance)

8 (partition list_range_p1 values('CN','PG')

9 (subpartition list_range_sub_p1_1 values less than (1000),

10 subpartition list_range_sub_p1_2 values less than (10000),

11 subpartition list_range_sub_p1max values less than (maxvalue)

12 ),

13 partition list_range_p2 values('BJ','SH')

14 (subpartition list_range_sub_p2_1 values less than (1000),

15 subpartition list_range_sub_p2_2 values less than (10000),

16 subpartition list_range_sub_p2max values less than (maxvalue)

17 )

18 );

Table created.

驗證建立分割槽

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select partition_name, high_value

2 from user_tab_partitions

3 where table_name = 'ANING_LIST_RANGE';

PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

LIST_RANGE_P1 'CN', 'PG'

LIST_RANGE_P2 'BJ', 'SH'

SQL>

SQL>

SQL>

SQL> set line 100

SQL> col PARTITION_NAME for a30

SQL> col HIGH_VALUE for a60

SQL> select subpartition_name, high_value

2 from user_tab_subpartitions

3 where table_name = 'ANING_LIST_RANGE';

SUBPARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------------------------------------

LIST_RANGE_SUB_P1MAX MAXVALUE

LIST_RANGE_SUB_P1_2 10000

LIST_RANGE_SUB_P1_1 1000

LIST_RANGE_SUB_P2MAX MAXVALUE

LIST_RANGE_SUB_P2_2 10000

LIST_RANGE_SUB_P2_1 1000

6 rows selected.

1.3.7.建立間隔-雜湊複合分割槽表

SQL> create table aning_interval_hash

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date) interval (numtoyminterval(1,'month'))

7 subpartition by hash (aning_id) subpartitions 4

8 (partition interval_hash201009 values less than (to_date('2010-10-01','yyyy-mm-dd')));

Table created.

1.3.8.建立間隔-列表複合分割槽表

SQL> create table aning_interval_list

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date) interval (numtodsinterval(1,'day'))

7 subpartition by list (aning_name)

8 subpartition template

9 (subpartition list_sub_p1 values('CN'),

10 subpartition list_sub_p2 values('BJ')

11 )

12 (partition inter_list100930 values less than (to_date('2010-10-01','yyyy-mm-dd')));

Table created.

1.3.9.建立間隔-範圍複合分割槽表

SQL> create table aning_interval_range

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date,

5 banlance number

6 )

7 partition by range (aning_date) interval (numtodsinterval(1,'day'))

8 subpartition by range (banlance)

9 subpartition template

10 (subpartition range_sub_p1 values less than (1000),

11 subpartition range_sub_p2 values less than (10000),

12 subpartition range_sub_max values less than (maxvalue)

13 )

14 (partition inter_list100930 values less than (to_date('2010-10-01','yyyy-mm-dd')));

Table created.

1.3. 建立其他型別分割槽表

1.3.1. 用多列分割槽鍵建立範圍分割槽表

SQL> create table aning_mutilcol_range

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_year number,

5 aning_month number,

6 aning_day number,

7 aning_amount number

8 )

9 partition by range (aning_year,aning_month)

10 (partition mutil_range2011 values less than (2012,1),

11 partition mutil_range_1203 values less than (2012,4),

12 partition mutil_range_1205 values less than (2012,6),

13 partition mutil_range_1302 values less than (2013,3),

14 partition mutil_range_max values less than (maxvalue,0)

15 );

Table created.

插入測試資料

INSERT INTO aning_mutilcol_range VALUES(1,'aning1',2011,12,12, 1000);

INSERT INTO aning_mutilcol_range VALUES(2,'aning2',2012,3,17, 2000);

INSERT INTO aning_mutilcol_range VALUES(3,'aning3',2012,5,5, 5000);

INSERT INTO aning_mutilcol_range VALUES(4,'aning4',2013,2,2, 4000);

INSERT INTO aning_mutilcol_range VALUES(5,'aning5',2013,5,2, 6000);

commit;

查詢分割槽表資料分佈

SQL> set line 100

SQL> col ANING_NAME for a10

SQL> SELECT * FROM aning_mutilcol_range PARTITION(mutil_range2011);

ANING_ID ANING_NAME ANING_YEAR ANING_MONTH ANING_DAY ANING_AMOUNT

---------- ---------- ---------- ----------- ---------- ------------

1 aning1 2011 12 12 1000

SQL>

SQL> SELECT * FROM aning_mutilcol_range PARTITION(mutil_range_1203);

ANING_ID ANING_NAME ANING_YEAR ANING_MONTH ANING_DAY ANING_AMOUNT

---------- ---------- ---------- ----------- ---------- ------------

2 aning2 2012 3 17 2000

SQL>

SQL> SELECT * FROM aning_mutilcol_range PARTITION(mutil_range_1205);

ANING_ID ANING_NAME ANING_YEAR ANING_MONTH ANING_DAY ANING_AMOUNT

---------- ---------- ---------- ----------- ---------- ------------

3 aning3 2012 5 5 5000

SQL>

SQL> SELECT * FROM aning_mutilcol_range PARTITION(mutil_range_1205);

ANING_ID ANING_NAME ANING_YEAR ANING_MONTH ANING_DAY ANING_AMOUNT

---------- ---------- ---------- ----------- ---------- ------------

3 aning3 2012 5 5 5000

SQL>

SQL> SELECT * FROM aning_mutilcol_range PARTITION(mutil_range_max);

ANING_ID ANING_NAME ANING_YEAR ANING_MONTH ANING_DAY ANING_AMOUNT

---------- ---------- ---------- ----------- ---------- ------------

5 aning5 2013 5 2 6000

SQL>

1.3.2. 基於虛擬列建立分割槽表

使用虛擬列做為子分割槽鍵值

SQL> create table aning_virtual_col

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date,

5 aning_quantity number,

6 aning_amount number,

7 aning_total as (aning_quantity * aning_amount)

8 )

9 partition by range(aning_date) interval (numtoyminterval(1,'month'))

10 subpartition by range (aning_total)

11 subpartition template

12 (subpartition vircol_small values less than (1000),

13 subpartition vircol_medium values less than (5000),

14 subpartition vircol_large values less than (10000),

15 subpartition vircol_max values less than (maxvalue)

16 )

17 (partition vircol2010 values less than (to_date('2011-01-01','yyyy-mm-dd'))

18 )

19 enable row movement

20 parallel nologging;

Table created.

1.3.3. 建立壓縮分割槽表

SQL> create table aning_par_compress

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date)

7 (partition par_compress2010

8 values less than (to_date('2011-01-01','yyyy-mm-dd')) compress,

9 partition par_compress2011

10 values less than (to_date('2012-01-01','yyyy-mm-dd')),

11 partition par_compressmax

12 values less than (maxvalue)

13 );

Table created.

1.3.4. 建立壓縮分割槽索引

1.4.4.1.使用分割槽鍵建立本地分割槽索引

SQL> create index idx_aning_par_compress on aning_par_compress(aning_date) compress local

2 (partition par_compress2010,

3 partition par_compress2011,

4 partition par_compressmax nocompress

5 );

Index created.

1.4.4.2.使用非分割槽鍵建立本地分割槽索引

SQL> create index pk_aning_par_compress on aning_par_compress(aning_id) compress local

2 (partition pk_par_compress2010,

3 partition pk_par_compress2011,

4 partition pk_par_compressmax nocompress

5 );

Index created.

1.5.建立分割槽表需要表空間

create tablespace aningtbs1 datafile '/home/oracle/oradata/lottery/aningtbs1.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs2 datafile '/home/oracle/oradata/lottery/aningtbs2.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs3 datafile '/home/oracle/oradata/lottery/aningtbs3.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs4 datafile '/home/oracle/oradata/lottery/aningtbs4.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs5 datafile '/home/oracle/oradata/lottery/aningtbs5.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs6 datafile '/home/oracle/oradata/lottery/aningtbs6.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs7 datafile '/home/oracle/oradata/lottery/aningtbs7.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs8 datafile '/home/oracle/oradata/lottery/aningtbs8.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;