segment

最新推荐文章于 2021-11-22 11:55:08 发布

ailvjiu6879

最新推荐文章于 2021-11-22 11:55:08 发布

阅读量94

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/jiaoweixue/archive/2013/06/14/3136753.html

版权

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

SQL> conn / as sysdba;

Connected.

 
    SQL 
    > 
    select 
    count( 
    *) 
    from tt1; 
   

COUNT(*)

----------

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

Table created.

 
     SQL 
     > 
     select 
     count( 
     *) 
     from tt2; 
    

COUNT(*)

----------

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 
   