管理表（二）--管理普通表

 

1、建立普通表

02:25:59 SQL> create table deparment(
02:26:52   2   deptno number(4),dname varchar2(20),loc varchar2(12))
02:27:22   3  pctfree 20 pctused 50 tablespace users01
02:27:45   4   storage(minextents 3);

Table created.

02:28:19 SQL> alter table deparment rename to department;

Table altered.

02:28:52 SQL> desc department;
 Name         Null?    Type
 ---------- -------- -----------------
DEPTNO                 NUMBER(4)
DNAME                  VARCHAR2(20)
LOC                    VARCHAR2(12)

02:30:13 SQL> select tablespace_name,extents,bytes from user_segments
02:30:31   2    where segment_name='DEPARTMENT';

TABLESPACE_NAME                   EXTENTS      BYTES
------------------------------ ---------- ----------
USERS01                                 3     393216

02:30:47 SQL>

查看EMP1表段信息：

22:59:29 SQL> COL SEGMENT_NAME FOR A20
22:59:40 SQL> R
  1  select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT ,TABLESPACE_NAME from user_segments
  2*   where segment_name='EMP1'

SEGMENT_NAME         SEGMENT_TYPE           BLOCKS    EXTENTS INITIAL_EXTENT TABLESPACE_NAME
-------------------- ------------------ ---------- ---------- -------------- ------------------------------
EMP1                 TABLE                       8          1          65536 USERS

23:00:31 SQL> SELECT TABLE_NAME,BLOCKS,NUM_ROWS,EMPTY_BLOCKS FROM USER_TABLES
23:01:31   2    WHERE TABLE_NAME='EMP1';

TABLE_NAME                         BLOCKS   NUM_ROWS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1                                    4         13            0

23:01:40 SQL> ANALYZE TABLE EMP1 COMPUTE STATISTICS;

Table analyzed.

23:02:36 SQL> SELECT TABLE_NAME,BLOCKS,NUM_ROWS,EMPTY_BLOCKS FROM USER_TABLES
23:02:39   2    WHERE TABLE_NAME='EMP1';

TABLE_NAME                         BLOCKS   NUM_ROWS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1                                    4         13            4

23:02:42 SQL> DELETE FROM EMP1;

13 rows deleted.

23:03:30 SQL> ANALYZE TABLE EMP1 COMPUTE STATISTICS;

Table analyzed.

23:03:35 SQL> SELECT TABLE_NAME,BLOCKS,NUM_ROWS,EMPTY_BLOCKS FROM USER_TABLES
23:03:39   2    WHERE TABLE_NAME='EMP1';

TABLE_NAME                         BLOCKS   NUM_ROWS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1                                    4          0            4

23:03:43 SQL> TRUNCATE TABLE EMP1;

Table truncated.

23:04:37 SQL> ANALYZE TABLE EMP1 COMPUTE STATISTICS;

Table analyzed.

23:04:44 SQL> SELECT TABLE_NAME,BLOCKS,NUM_ROWS,EMPTY_BLOCKS FROM USER_TABLES
23:04:47   2    WHERE TABLE_NAME='EMP1';

TABLE_NAME                         BLOCKS   NUM_ROWS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1                                    0          0            8

23:05:29 SQL> SELECT SEGMENT_NAME ,EXTENT_ID,BLOCKS FROM USER_EXTENTS
23:06:01   2   WHERE SEGMENT_NAME='EMP1';

SEGMENT_NAME          EXTENT_ID     BLOCKS
-------------------- ---------- ----------
EMP1                          0          8

2、修改表的物理结构
02:52:49 SQL> alter table department
02:52:56   2  pctfree 40 pctused 35 initrans 3;

Table altered.

00:10:38 SQL> create table emp3(id int,name varchar2(10))
00:10:50   2     PCTFREE 20
00:11:07   3    PCTUSED 40
00:11:23   4     STORAGE(
00:11:27   5           NEXT 200K
00:11:30   6           MINEXTENTS 2
00:11:37   7           MAXEXTENTS 10);

Table created.

Emp3 默认表空间为users  ，其表空间管理方式local，没有定义initial 参数，其初始化extent大小为64k

00:11:40 SQL> select segment_name,blocks,extents,initial_extent,next_extent,MIN_EXTENTS,MAX_EXTENTS from user_segments
00:11:50   2  where segment_name='EMP3';

SEGMENT_NAME             BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------------- ---------- ---------- -------------- ----------- ----------- -----------
EMP3                         40          5         270336                       1  2147483645

00:12:02 SQL> select segment_name,blocks,extents,initial_extent/1024,next_extent,MIN_EXTENTS,MAX_EXTENTS from user_segments
00:13:23   2  where segment_name='EMP3';

SEGMENT_NAME             BLOCKS    EXTENTS INITIAL_EXTENT/1024 NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------------- ---------- ---------- ------------------- ----------- ----------- -----------
EMP3                         40          5                 264                       1  2147483645

INITIAL_EXTENT=64 + 200 k ，初始化extent 为64，next extent 200，最少分配两个区（minextents=2）；

3、重新组织表
02:55:06 SQL> alter table department move tablespace users01;

Table altered
重新组织表后，原来的索引会无效，必须重建索引。
4、手工分配和释放表空间
02:59:18 SQL> COL FILE_NAME FOR A30
02:59:29 SQL> R
  1* select * from dba_data_files where tablespace_name like 'USERS01'

FILE_NAME                         FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------------------------ ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- --------/disk1/oradata/orcl/user01.dbf          6 USERS01                          10485760       1280 AVAILABLE            6 NO       0           0            0   10354688        1264 ONLINE

03:02:12 SQL> alter table department allocate extent
03:02:31   2   (size 500k datafile '/disk1/oradata/orcl/user01.dbf');

Table altered.

03:03:00 SQL> alter table department deallocate unused keep 0;

Table altered.
默认情况下释放剩余空间后表段的区个数不会低于MINEXTENTS，而如果要释放minextents下的剩余空间，需要带有keept 0 的选项。
 
5、修改列定义
03:37:52 SQL> desc department;
 Name                                                                             Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------- DEPTNO                                                                            NUMBER(2)
 DNAME                                                                             VARCHAR2(10)

03:38:02 SQL> alter table department add (loc varchar2(10));

Table altered.

03:38:33 SQL> alter table department modify loc varchar2(15);

Table altered.
需要注意：当缩减char类型列的长度时，如果该表中已经包含数据，就必须将初始化参数blank_trimming 设置为true
03:01:37 SQL> show parameter blank

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
blank_trimming                       boolean     FALSE
03:41:00 SQL>
6、修改列名
03:40:49 SQL> alter table department rename column loc to loca;

Table altered.

03:42:59 SQL> desc department;
 Name                                                                             Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------- DEPTNO                                                                            NUMBER(2)
 DNAME                                                                             VARCHAR2(10)
 LOCA                                                                              VARCHAR2(15)
修改列名会使视图、过程等相关对象转变为无效状态。