Oracle中建立和管理普通表

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

查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/