创建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> commi