Sunday, June 14, 2009

partititon ve subpartitionlu tablo yaratma örnek kodu

CREATE TABLE deneme_part
(
OWNER VARCHAR2(30 BYTE) NOT NULL,
OBJECT_NAME VARCHAR2(30 BYTE) NOT NULL,
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID NUMBER NOT NULL,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19 BYTE),
CREATED DATE NOT NULL,
LAST_DDL_TIME DATE NOT NULL,
TIMESTAMP VARCHAR2(19 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE),
GENERATED VARCHAR2(1 BYTE),
SECONDARY VARCHAR2(1 BYTE)
)
TABLESPACE users
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
)
NOLOGGING
PARTITION BY RANGE (object_id)
SUBPARTITION BY HASH (object_name)
(
PARTITION PART_001 VALUES LESS THAN (10000)
NOLOGGING
NOCOMPRESS
TABLESPACE users
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
( SUBPARTITION SPART_001_001 TABLESPACE users,
SUBPARTITION SPART_001_002 TABLESPACE users,
SUBPARTITION SPART_001_003 TABLESPACE users,
SUBPARTITION SPART_001_004 TABLESPACE users,
SUBPARTITION SPART_001_005 TABLESPACE users,
SUBPARTITION SPART_001_006 TABLESPACE users,
SUBPARTITION SPART_001_007 TABLESPACE users,
SUBPARTITION SPART_001_008 TABLESPACE users,
SUBPARTITION SPART_001_009 TABLESPACE users,
SUBPARTITION SPART_001_010 TABLESPACE users,
SUBPARTITION SPART_001_011 TABLESPACE users,
SUBPARTITION SPART_001_012 TABLESPACE users,
SUBPARTITION SPART_001_013 TABLESPACE users,
SUBPARTITION SPART_001_014 TABLESPACE users,
SUBPARTITION SPART_001_015 TABLESPACE users,
SUBPARTITION SPART_001_016 TABLESPACE users ),
PARTITION PART_002 VALUES LESS THAN (20000)
NOLOGGING
NOCOMPRESS
TABLESPACE users
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
( SUBPARTITION SPART_002_001 TABLESPACE users,
SUBPARTITION SPART_002_002 TABLESPACE users,
SUBPARTITION SPART_002_003 TABLESPACE users,
SUBPARTITION SPART_002_004 TABLESPACE users,
SUBPARTITION SPART_002_005 TABLESPACE users,
SUBPARTITION SPART_002_006 TABLESPACE users,
SUBPARTITION SPART_002_007 TABLESPACE users,
SUBPARTITION SPART_002_008 TABLESPACE users,
SUBPARTITION SPART_002_009 TABLESPACE users,
SUBPARTITION SPART_002_010 TABLESPACE users,
SUBPARTITION SPART_002_011 TABLESPACE users,
SUBPARTITION SPART_002_012 TABLESPACE users,
SUBPARTITION SPART_002_013 TABLESPACE users,
SUBPARTITION SPART_002_014 TABLESPACE users,
SUBPARTITION SPART_002_015 TABLESPACE users,
SUBPARTITION SPART_002_016 TABLESPACE users ),
PARTITION PART_003 VALUES LESS THAN (30000)
NOLOGGING
NOCOMPRESS
TABLESPACE users
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
( SUBPARTITION SPART_003_001 TABLESPACE users,
SUBPARTITION SPART_003_002 TABLESPACE users,
SUBPARTITION SPART_003_003 TABLESPACE users,
SUBPARTITION SPART_003_004 TABLESPACE users,
SUBPARTITION SPART_003_005 TABLESPACE users,
SUBPARTITION SPART_003_006 TABLESPACE users,
SUBPARTITION SPART_003_007 TABLESPACE users,
SUBPARTITION SPART_003_008 TABLESPACE users,
SUBPARTITION SPART_003_009 TABLESPACE users,
SUBPARTITION SPART_003_010 TABLESPACE users,
SUBPARTITION SPART_003_011 TABLESPACE users,
SUBPARTITION SPART_003_012 TABLESPACE users,
SUBPARTITION SPART_003_013 TABLESPACE users,
SUBPARTITION SPART_003_014 TABLESPACE users,
SUBPARTITION SPART_003_015 TABLESPACE users,
SUBPARTITION SPART_003_016 TABLESPACE users ),
PARTITION PART_004 VALUES LESS THAN (40000)
NOLOGGING
NOCOMPRESS
TABLESPACE users
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
( SUBPARTITION SPART_004_001 TABLESPACE users,
SUBPARTITION SPART_004_002 TABLESPACE users,
SUBPARTITION SPART_004_003 TABLESPACE users,
SUBPARTITION SPART_004_004 TABLESPACE users,
SUBPARTITION SPART_004_005 TABLESPACE users,
SUBPARTITION SPART_004_006 TABLESPACE users,
SUBPARTITION SPART_004_007 TABLESPACE users,
SUBPARTITION SPART_004_008 TABLESPACE users,
SUBPARTITION SPART_004_009 TABLESPACE users,
SUBPARTITION SPART_004_010 TABLESPACE users,
SUBPARTITION SPART_004_011 TABLESPACE users,
SUBPARTITION SPART_004_012 TABLESPACE users,
SUBPARTITION SPART_004_013 TABLESPACE users,
SUBPARTITION SPART_004_014 TABLESPACE users,
SUBPARTITION SPART_004_015 TABLESPACE users,
SUBPARTITION SPART_004_016 TABLESPACE users ),
PARTITION PART_005 VALUES LESS THAN (50000)
NOLOGGING
NOCOMPRESS
TABLESPACE users
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
( SUBPARTITION SPART_006_001 TABLESPACE users,
SUBPARTITION SPART_006_002 TABLESPACE users,
SUBPARTITION SPART_006_003 TABLESPACE users,
SUBPARTITION SPART_006_004 TABLESPACE users,
SUBPARTITION SPART_006_005 TABLESPACE users,
SUBPARTITION SPART_006_006 TABLESPACE users,
SUBPARTITION SPART_006_007 TABLESPACE users,
SUBPARTITION SPART_006_008 TABLESPACE users,
SUBPARTITION SPART_006_009 TABLESPACE users,
SUBPARTITION SPART_006_010 TABLESPACE users,
SUBPARTITION SPART_006_011 TABLESPACE users,
SUBPARTITION SPART_006_012 TABLESPACE users,
SUBPARTITION SPART_006_013 TABLESPACE users,
SUBPARTITION SPART_006_014 TABLESPACE users,
SUBPARTITION SPART_006_015 TABLESPACE users,
SUBPARTITION SPART_006_016 TABLESPACE users ),
PARTITION PART_006 VALUES LESS THAN (60000)
NOLOGGING
NOCOMPRESS
TABLESPACE users
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
( SUBPARTITION SPART_005_001 TABLESPACE users,
SUBPARTITION SPART_005_002 TABLESPACE users,
SUBPARTITION SPART_005_003 TABLESPACE users,
SUBPARTITION SPART_005_004 TABLESPACE users,
SUBPARTITION SPART_005_005 TABLESPACE users,
SUBPARTITION SPART_005_006 TABLESPACE users,
SUBPARTITION SPART_005_007 TABLESPACE users,
SUBPARTITION SPART_005_008 TABLESPACE users,
SUBPARTITION SPART_005_009 TABLESPACE users,
SUBPARTITION SPART_005_010 TABLESPACE users,
SUBPARTITION SPART_005_011 TABLESPACE users,
SUBPARTITION SPART_005_012 TABLESPACE users,
SUBPARTITION SPART_005_013 TABLESPACE users,
SUBPARTITION SPART_005_014 TABLESPACE users,
SUBPARTITION SPART_005_015 TABLESPACE users,
SUBPARTITION SPART_005_016 TABLESPACE users )
)
NOCOMPRESS
NOCACHE
PARALLEL ( DEGREE 16 INSTANCES 1 )
MONITORING;

insert into deneme_part select * from all_objects
drop table deneme_part

No comments: