创建ASSM/MSSM管理表空间及创建表、分区表、大对象段等示例
1.创建ASSM和MSSM管理的表空间SQL> create tablespace users2 datafile 'f:\userdata\users2.dbf' size 10m autoextend on next 1m segment space management manual;
Tablespace created
SQL> create tablespace users3 datafile 'f:\userdata\users3.dbf' size 10m autoextend on next 1m segment space management auto;
Tablespace created
SQL> select tablespace_name,status,segment_space_management,bigfile from dba_tablespaces where tablespace_name like 'USERS%';
TABLESPACE_NAME STATUS SEGMENT_SPACE_MANAGEMENT BIGFILE
------------------------------ --------- ------------------------ -------
USERS ONLINE AUTO NO
USERS2 ONLINE MANUAL NO
USERS3 ONLINE AUTO NO
2.创建表和分区表对象：
SQL> create table test (a varchar2(100));
Table created
SQL> select segment_name,segment_type,tablespace_name,extents from dba_segments where segment_name like 'TEST%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS
-------------------------------------------------------------------------------- ------------------ ------------------------------ ----------
这里创建完表后未分配段空间，是11GR2新特性---延迟段创建 ，即默认创建的表不会立及分配segment，不会占用磁盘空间，当第一条数据insert时才会分配空间。
SQL> insert into test select rownum from dual connect by rownum<=100;
100 rows inserted
SQL> col segment_name for a20
SQL> select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-------------------- ------------------ ------------------------------ ---------- ----------
TEST TABLE USERS 1 64
SQL> commit;
Commit complete
SQL> select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-------------------- ------------------ ------------------------------ ---------- ----------
TEST TABLE USERS 1 64
分区表对象：SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST1_UNDO
USERS2
USERS3
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> create table test1(ab number) partition by range(ab) (partition p1 values less than (5) tablespace users,partition p2 values less than (10) tablespace users2,partition p3 values less than (maxvalue) tablespace users3);
Table created
SQL> select * from test1;
AB
----------
SQL> select * from test1 partition(p1);
AB
----------
SQL> select * from test1 partition(p2);
AB
----------
SQL> select * from test1 partition(p3);
AB
----------
SQL> insert into test1 select rownum from dual connect by rownum<13;
12 rows inserted
SQL> select * from test1;
AB
----------
1
2
3
4
5
6
7
8
9
10
11
12
SQL> select * from test1 partition(p1);
AB
----------
1
2
3
4
SQL> select * from test1 partition(p2);
AB
----------
5
6
7
8
9
SQL> select * from test1 partition(p3);
AB
----------
10
11
12
SQL> col segment_name for a20
SQL> select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST1%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-------------------- ------------------ ------------------------------ ---------- ----------
TEST1 TABLE PARTITION USERS 1 64
TEST1 TABLE PARTITION USERS2 1 64
TEST1 TABLE PARTITION USERS3 1 64
大对象段：SQL> create table test3(ac clob);
Table created
SQL> select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-------------------- ------------------ ------------------------------ ---------- ---------
TEST1 TABLE PARTITION USERS 1 64
TEST1 TABLE PARTITION USERS2 1 64
TEST1 TABLE PARTITION USERS3 1 64
SQL> insert into test3 values('本报讯中纪委要求纪检监察干部要在6月20日前自行清退所收受各种名目的会员');
1 row inserted
SQL> select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST3%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-------------------- ------------------ ------------------------------ ---------- ----------
TEST3 TABLE USERS 1 64
SQL> commit;
Commit complete
SQL> select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like 'TEST3%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-------------------- ------------------ ------------------------------ ---------- ----------
TEST3 TABLE USERS 1 64
