segment

1.创建表，分区表，大对象字段，分别查询出它们是否为段对象，给出SQL演示的整个过程。

SQL> create table tt1 as select * from dba_objects where object_id<1000;

 

Table created.

 

SQL> create table tt2(id number(10), object_type varchar2(40)) partition by list(object_type) (partition p_table values('TABLE'), partition p_others values (default));

 

Table created.

 

SQL> insert into tt2 value select object_id,object_type from dba_objects where object_id<1000;

 

942 rows created.

 

SQL>  create table tt3(id number(10), object_type varchar2(40), description clob);

 

Table created.

 

SQL> insert into tt3 value select object_id,object_type,object_name from dba_objects where object_id<1000;

 

942 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> set linesize 200
SQL> select segment_name,partition_name,tablespace_name,extents,blocks from user_segments where SEGMENT_NAME IN ('TT1','TT2');

 

SEGMENT_NAME                                      PARTITION_NAME         TABLESPACE_NAME           EXTENTS     BLOCKS

--------------------------------------------------------------------------------- ------------------------------ ------------------------------ ---------- ----------

TT1                                                         SYSTEM                  2       16

TT2                                          P_OTHERS             SYSTEM                  1        8

TT2                                          P_TABLE             SYSTEM                  1        8

 

SQL> select s.segment_name,s.partition_name,s.tablespace_name,s.extents,s.blocks from user_segments s, user_lobs l where s.segment_name=l.SEGMENT_NAME and l.TABLE_NAME='TT3';

 

SEGMENT_NAME                                      PARTITION_NAME         TABLESPACE_NAME           EXTENTS     BLOCKS

--------------------------------------------------------------------------------- ------------------------------ ------------------------------ ---------- ----------

SYS_LOB0000075214C00003$$                                             SYSTEM                  1        8

2.分别创建一个ASSM，MSSM管理的表空间。

SQL>  show parameter db_create_file_dest

 

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest             string

SQL> alter system set db_create_file_dest='/u01/oradata/orcl';

 

System altered.

 

SQL> show parameter db_create_file_dest

 

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest             string     /u01/oradata/orcl

 

SQL> create tablespace mssm segment space management manual;

 

Tablespace created.

 

SQL> create tablespace assm segment space management auto;

 

Tablespace created.

 

3.验证段存储属性和所在表空间存储属性的关系，给出SQL演示，并得出结论。

SQL> SELECT TABLESPACE_NAME ,SEGMENT_SPACE_MANAGEMENT ,EXTENT_MANAGEMENT ALLOCATION_TYPE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('USERS02','USER03');

 

TABLESPACE_NAME            SEGMEN ALLOCATION

------------------------------ ------ ----------

USER03                   MANUAL LOCAL

USERS02                AUTO   LOCAL

 

SQL> CREATE TABLE t1 TABLESPACE USERS02 AS SELECT * FROM DBA_OBJECTS;

 

Table created.

 

SQL> CREATE TABLE t2 TABLESPACE USER03 AS SELECT * FROM DBA_OBJECTS;

 

Table created.

 

SQL> SELECT TABLE_NAME ,TABLESPACE_NAME,PCT_FREE, PCT_USED,FREELISTS ,STATUS FROM USER_TABLES WHERE TABLE_NAME IN ('USERS02','USER03');

T!                                USERS02        10                  VALIDMU_TABLE

T2                                USER03         10     40         1 VALID

 

4.SQL演示临时表的机制，并说明它的适用场合。

SQL> create global temporary table tt1 on commit preserve rows as select * from dba_objects;

 

Table created.

 

SQL> select count(*) from tt1;

 

  COUNT(*)

----------

     72595

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracle ~]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 14 07:41:27 2013

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> conn / as sysdba;

Connected.

SQL> select count(*) from tt1;

 

  COUNT(*)

----------

     0

 

SQL> create global temporary table tt2 on commit delete rows as select * from dba_objects;

 

Table created.

 

SQL> select count(*) from tt2;

 

  COUNT(*)

----------

     0

5.示例演示数据压缩的效果。

 

SQL> create table tt1 as select * from t1;

 

Table created.

 

Elapsed: 00:00:12.61

 

SQL> create table tt2 compress as select * from t1;

 

Table created.

 

Elapsed: 00:00:07.32

 

SQL> create table tt3 as select * from t1 where 1=2;

 

Table created.

 

Elapsed: 00:00:00.05

SQL> create table tt4 as select * from t1 where 1=2;

 

Table created.

 

Elapsed: 00:00:00.04

SQL> insert into /*+ append */ tt3 select * from tt1;

 

1000000 rows created.

 

Elapsed: 00:00:18.48

SQL> insert into /*+ append */ tt4 select * from tt2;

 

1000000 rows created.

 

Elapsed: 00:00:37.04

SQL> exec show_space('TT3');
Free Blocks............................. 5
Total Blocks............................ 13,312
Total Bytes............................. 109,051,904
Total MBytes............................ 104
Unused Blocks........................... 259
Unused Bytes............................ 2,121,728
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 138,496
Last Used Block......................... 765

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.04

SQL> exec show_space('TT4');
Free Blocks............................. 5
Total Blocks............................ 13,312
Total Bytes............................. 109,051,904
Total MBytes............................ 104
Unused Blocks........................... 259
Unused Bytes............................ 2,121,728
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 151,808
Last Used Block......................... 765

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.03

SQL> create table tt5 compress as select * from t1 where 1=2;

 

Table created.

 

Elapsed: 00:00:00.06

SQL> create table tt6 compress as select * from t1 where 1=2;

 

Table created.

 

Elapsed: 00:00:00.04

SQL> insert into /*+ append */ tt5 select * from tt1;

 

1000000 rows created.

 

Elapsed: 00:00:23.30

SQL> insert into /*+ append */ tt6 select * from tt2;

 

1000000 rows created.

 

Elapsed: 00:00:27.71

SQL> exec show_space('TT5');
Free Blocks............................. 5
Total Blocks............................ 12,288
Total Bytes............................. 100,663,296
Total MBytes............................ 96
Unused Blocks........................... 544
Unused Bytes............................ 4,456,448
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 164,096
Last Used Block......................... 480

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.02

SQL> exec show_space('TT6');
Free Blocks............................. 5
Total Blocks............................ 12,288
Total Bytes............................. 100,663,296
Total MBytes............................ 96
Unused Blocks........................... 544
Unused Bytes............................ 4,456,448
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 176,384
Last Used Block......................... 480