-
Hi friends,
I created a tablespace using the following sql:
create tablespace abcd_20010102_ts datafile
'/tools/oracle/database/abc/abc_1_1_1.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_2.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_3.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_4.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_5.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_6.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_7.dbf' SIZE 1796M REUSE,
'/tools/oracle/database/abc/abc_1_1_8.dbf' SIZE 1796M REUSE AUTOEXTEND ON
MINIMUM EXTENT 1772M default storage (INITIAL 1772M NEXT 1772M MINEXTENTS 8
PCTINCREASE 0)
The datafiles are on raw devices, the intent of the SQL is to create 1 extent for each datafile that uses the entire raw device and to create all of the extents at once. When I tried to create a table using this tablespace using the following SQL:
CREATE TABLE table1 (
2> dialed_number varchar2(25),
3> outpulse_digits varchar2(25),
4> originating_switch smallint,
5> originating_trunk smallint,
6> call_conn_datetime number,
7> call_ans_datetime number(14),
8> call_disc_datetime number,
9> calling_number varchar2(25),
10> call_duration integer,
11> ring_duration integer,
12> origination_npa smallint,
13> call_disposition smallint,
14> call_dto_count smallint,
15> intended_term_type smallint,
16> intended_term_addr varchar2(26),
17> actual_term_type smallint,
18> actual_term_addr varchar2(26),
19> corporate_id int,
20> insert_timestamp number(14),
21> suppcode_digits varchar2(2),
22> country_code smallint,
23> call_orig_datetime number(14),
24> cdid integer,
25> originating_port integer,
26> cdr_type smallint,
27> switched_data_flag smallint,
28> product_start_time number(14),
29> product_end_time number(14),
30> product_duration integer,
31> product_entry_code smallint,
32> app_counters varchar(12),
33> ncr_reason smallint,
34> ncr_fault smallint,
35> dnis_digits varchar2(10),
36> orig_bill_switch varchar2(4),
37> origination_class smallint,
38> action_code smallint,
39> calling_num_type smallint,
40> service_location varchar2(10),
41> evs_indicator smallint ) PARALLEL
42>
43> partition by range (call_orig_datetime)
44> subpartition by hash (dialed_number) subpartitions 256
45> (partition abcd_20010102_pt
46> values less than ( 20010103000000 )
47> store in ( abcd_20010102_ts )) nologging;
I got the following error:
CREATE TABLE table1 (
*
ORA-01237: cannot extend datafile 15
ORA-01110: data file 15: '/tools/oracle/database/abc/abc_1_1_8.dbf'
ORA-19502: write error on file "/tools/oracle/database/abc/abc_1_1_8.dbf", block
no 666306 (blocksize=8192)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 246784
Additional information: 524288
If I try to run the same sql for reduced extent sizes for example:MINIMUM EXTENT 2M default storage (INITIAL 2M NEXT 2M
PCTINCREASE 0) and then try to create the table it works fine.
Any idea what the problem is?
My database is 8.1.6 and is on Tru64 5.0 compaq ES-40
Thanks.
manjunath
[Edited by manjunathk on 01-04-2001 at 08:12 PM]
-
What's the block size ?
Gert
-
thanks gert,
block size is 8 K
manjunath
-
Hi,
Try removing 'minimum extent 1772M' from the command as you are creating all your extents when you create the tablespace and the size of these are identified by the 'initial' and 'next' parameters.
If this tablespace is to use the entire raw device on creation I would switch autoextend off - as there should be little or no space to extend into...
Let me know if this helps or if you manage to solve your problem another way.
Kind Regards
Moff
-
thanks Moff,
but I had tried that already and it does not help, though.
Considering my datafile layout and their sizes, do you think there is any error in specifying 256 numbers of subpartitions? I mean if each subpartion will be allocated initially one extent of 1772M then obviously there is not so much space present(available is 1796 M X 8 files).So, if this is the error then i should be able to create a table with 256 subpartitions but having initial extent of less than 1796 X 8/256 say around 56 M only instead of 1772M as I am trying to do?
Manjunath
