demo06.sql

Expert Oracle Database Architecture 9i and 10g sql源码

create table t1
(  x int primary key,
   y varchar2(25),
   z date
)
organization index;
create table t2
(  x int primary key,
   y varchar2(25),
   z date
)
organization index
OVERFLOW;
create table t3
(  x int primary key,
   y varchar2(25),
   z date
)
organization index
overflow INCLUDING y;
select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;


create table iot
( owner, object_type, object_name,
  constraint iot_pk primary key(owner,object_type,object_name)
)
organization index
NOCOMPRESS
as
select distinct owner, object_type, object_name
  from all_objects
/
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
       opt_cmpr_count, opt_cmpr_pctsave
  from index_stats;
alter table iot move compress 1;
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
       opt_cmpr_count, opt_cmpr_pctsave
  from index_stats;
alter table iot move compress 2;
analyze index iot_pk validate structure;
select lf_blks, br_blks, used_space,
       opt_cmpr_count, opt_cmpr_pctsave
  from index_stats;
begin
  dbms_metadata.set_transform_param
  ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
end;
 /
select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
select dbms_metadata.get_ddl( 'TABLE', 'T3' ) from dual;