demo04.sql

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

create or replace directory my_dir as '/tmp/'
/

CREATE TABLE BIG_TABLE_ET
(
  "ID" NUMBER,
  "OWNER" VARCHAR2(30),
  "OBJECT_NAME" VARCHAR2(30),
  "SUBOBJECT_NAME" VARCHAR2(30),
  "OBJECT_ID" NUMBER,
  "DATA_OBJECT_ID" NUMBER,
  "OBJECT_TYPE" VARCHAR2(19),
  "CREATED" DATE,
  "LAST_DDL_TIME" DATE,
  "TIMESTAMP" VARCHAR2(19),
  "STATUS" VARCHAR2(7),
  "TEMPORARY" VARCHAR2(1),
  "GENERATED" VARCHAR2(1),
  "SECONDARY" VARCHAR2(1)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY MY_DIR
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
    READSIZE 1048576
    FIELDS TERMINATED BY "|" LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "ID" CHAR(255)
        TERMINATED BY "|",
      "OWNER" CHAR(255)
        TERMINATED BY "|",
      "OBJECT_NAME" CHAR(255)
        TERMINATED BY "|",
      "SUBOBJECT_NAME" CHAR(255)
        TERMINATED BY "|",
      "OBJECT_ID" CHAR(255)
        TERMINATED BY "|",
      "DATA_OBJECT_ID" CHAR(255)
        TERMINATED BY "|",
      "OBJECT_TYPE" CHAR(255)
        TERMINATED BY "|",
      "CREATED" CHAR(255)
        TERMINATED BY "|",
      "LAST_DDL_TIME" CHAR(255)
        TERMINATED BY "|",
      "TIMESTAMP" CHAR(255)
        TERMINATED BY "|",
      "STATUS" CHAR(255)
        TERMINATED BY "|",
      "TEMPORARY" CHAR(255)
        TERMINATED BY "|",
      "GENERATED" CHAR(255)
        TERMINATED BY "|",
      "SECONDARY" CHAR(255)
        TERMINATED BY "|"
    )
  )
  location 
  (
    'big_table.dat'
  )
)REJECT LIMIT UNLIMITED
/


alter table big_table_et PARALLEL;


create tablespace lmt_uniform
datafile '/u03/ora10gr1/lmt_uniform.dbf' size 1048640K reuse
autoextend on next 100m
extent management local
uniform size 100m;
create tablespace lmt_auto
datafile '/u03/ora10gr1/lmt_auto.dbf' size 1048640K reuse
autoextend on next 100m
extent management local
autoallocate;
create table uniform_test
parallel
tablespace lmt_uniform
as
select * from big_table_et;
create table autoallocate_test
parallel
tablespace lmt_auto
as
select * from big_table_et;
select sid, serial#, qcsid, qcserial#, degree
from v$px_session;
select segment_name, blocks, extents
from user_segments 
where segment_name in ( 'UNIFORM_TEST', 'AUTOALLOCATE_TEST' );
exec show_space('UNIFORM_TEST' );
exec show_space('AUTOALLOCATE_TEST' );
select segment_name, extent_id, blocks
from user_extents where segment_name = 'UNIFORM_TEST';
select segment_name, blocks, count(*)
from user_extents
where segment_name = 'AUTOALLOCATE_TEST'
group by segment_name, blocks
/
alter session enable parallel dml;
insert /*+ append */ into UNIFORM_TEST 
select * from big_table_et;
insert /*+ append */ into AUTOALLOCATE_TEST 
select * from big_table_et;
commit;
exec show_space( 'UNIFORM_TEST' );
exec show_space( 'AUTOALLOCATE_TEST' );