Building and managing common tables in Oracle

1, the establishment of ordinary tables

02:25:59 sql> CREATE TABLE Deparment (

02:26:52 2 Deptno Number (4), Dname varchar2 (M), loc varchar2 (12))

02:27:22 3 pctfree pctused 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>

View EMP1 Table Segment information:

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;

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/