建立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