| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Question on tablespace/file allocation
I am trying to create a set of tablespaces to divide my tables up into. For
example, a tablespace for blobs, a tablespace for infrequently modified
lookup tables, a tablespace for history records, and a tablespace for
everything else. Plus, each of these tablespaces also has a corresponding
separate tablespace for indexes. (I also have RBS's and System separated).
I have also read that it is good practice to set up two files for each
tablespace so that the files within each tablespace can be multiplexed
across drives to minimize loss of data (please let me know if this is not a
good practice).
I have gone through the excercise of sizing each table and allocating the appropriate amount of space for it when it is created, and I have also added up the total amount of space I need for each tablespace and created the tablespace with that amount of space in two files (with the amount divided by 2). I have also done this for my indexes.
Here is my question: When I actually run the SQL scripts to create my tables, Oracle is putting almost all of the tables in only one of the two files I allocate, causing it to expand with as many as 5 extra extents, and in some cases it never uses the second file at all, or maybe only one or two tables will end up in the second file. Also, interestingly enough, I don't seem to have this same problem with my indexes, they seem more evenly distributed. Why would this be, and what (if anything) can I do to even out the distribution?
I am running 8.1.6 on an NT box.
Here is a sample of two of my tablespaces for lookup tables and their indexes:
REM ********** TABLESPACE FOR INFREQENTLY MODIFIED LOOKUP TABLES **********
REM *** sized 5/17/01 ****************
REM ********** TABLESPACE FOR INDEXES ON LKUP TABLES ********** REM *** sized 5/17/01 **************** REM *** size updated 7/16/01 REM *** size updated 8/20/01
TABLESPACE_NAME OWNER OBJECT Id BLOCKSKBYTES
SC_LIDX_TS free space 26 25
100
SC_LIDX_TS SCOWNER IX_MM_OPER_MM_C 380
1520
ONT_KEYW_KEYW
SC_LIDX_TS SCOWNER IX_SC_KEYW_DISC 44
176
R_VALUE_DESC
SC_LIDX_TS SCOWNER IX_SC_TASK_LIST 2
8
_NAME
SC_LIDX_TS SCOWNER PK_SC_SYSCONFIG 2
8
SC_LIDX_TS SCOWNER PK_SC_TASK_CODE 12
48
SC_LIDX_TS SCOWNER PK_SC_TASK_CODE 12
48
_COMD
SC_LIDX_TS SCOWNER PK_SC_TASK_CODE 24
96
_DESC
SC_LIDX_TS SCOWNER PK_SC_TASK_LIST 2
8
SC_LIDX_TS SCOWNER PK_SC_TASK_LIST 4
16
_DESC
SC_LIDX_TS SCOWNER PK_SC_TASK_LIST 2
8
_NAME
SC_LIDX_TS SCOWNER PK_SC_VERSION 2
8
SC_LIDX_TS SCOWNER IX_LU_ACCESS 27 2 8
SC_LIDX_TS SCOWNER IX_LU_PROP_NAME 8
32
SC_LIDX_TS SCOWNER IX_MM_ACQ_ACTIO 2
8
N
SC_LIDX_TS SCOWNER IX_MM_FUNC_NAME 12
48
_PLUG_IN
SC_LIDX_TS SCOWNER IX_SC_KEYW 56
224
SC_LIDX_TS SCOWNER PK_LU_ACCESS 2
8
SC_LIDX_TS SCOWNER PK_LU_ACQ_CONT 2
8
SC_LIDX_TS SCOWNER PK_LU_ACTION 2
8
SC_LIDX_TS SCOWNER PK_LU_DATATYPE 2
8
SC_LIDX_TS SCOWNER PK_LU_FUNC_NAME 2
8
SC_LIDX_TS SCOWNER PK_LU_HIST_TYPE 2
8
SC_LIDX_TS SCOWNER PK_LU_IMG_TYPE 2
8
SC_LIDX_TS SCOWNER PK_LU_KEYW_DISC 26
104
R_VALUE
SC_LIDX_TS SCOWNER PK_LU_KEYW_OPTI 2
8
ONS
SC_LIDX_TS SCOWNER PK_LU_KEYW_PREP 2
8
OST
SC_LIDX_TS SCOWNER PK_LU_LANG 2
8
SC_LIDX_TS SCOWNER PK_LU_LDAP_TYPE 2
8
SC_LIDX_TS SCOWNER PK_LU_LOB_PARAM 2
8
File
TABLESPACE_NAME OWNER OBJECT Id BLOCKS
KBYTES
_TYPE
SC_LIDX_TS SCOWNER PK_LU_OPER 27 2
8
SC_LIDX_TS SCOWNER PK_LU_PATH_TYPE 2 8 SC_LIDX_TS SCOWNER PK_LU_PLUG_IN 2 8 SC_LIDX_TS SCOWNER PK_LU_PROP_DESC 30 120 SC_LIDX_TS SCOWNER PK_LU_PROP_NAME 2 8 SC_LIDX_TS SCOWNER PK_LU_TASK_CODE 2 8KBYTES
_TYPE
SC_LIDX_TS SCOWNER PK_LU_TASK_LIST 2 8
_LDAP_TYPE
SC_LIDX_TS SCOWNER PK_LU_TASK_LIST 2 8
_TYPE
SC_LIDX_TS SCOWNER PK_LU_TRANS_LEV 2 8 EL SC_LIDX_TS SCOWNER PK_MM_ACQ_ACTIO 2 8 N SC_LIDX_TS SCOWNER PK_MM_ACQ_ACTIO 2 8 N_SUPPORT SC_LIDX_TS SCOWNER PK_MM_ACQ_MM_CO 6 24 NT SC_LIDX_TS SCOWNER PK_MM_ACQ_MM_PR 2 8 OP SC_LIDX_TS SCOWNER PK_MM_ACTION_PR 2 8 EPOST_KEYW SC_LIDX_TS SCOWNER PK_MM_FUNC_NAME 12 48
_PLUG_IN
SC_LIDX_TS SCOWNER PK_MM_IMG_TYPE 12 48 SC_LIDX_TS SCOWNER PK_MM_KEYW_DISC 80 320 R_VALUE SC_LIDX_TS SCOWNER PK_MM_KEYW_KEYW 30 120
_TYPE
SC_LIDX_TS SCOWNER PK_MM_KEYW_LOB_ 2 8 DEF_SQL SC_LIDX_TS SCOWNER PK_MM_KEYW_LOB_ 2 8 DEF_SQL_MM_CONT SC_LIDX_TS SCOWNER PK_MM_KEYW_MM_C 20 80 ONT SC_LIDX_TS SCOWNER PK_MM_OPER_MM_C 30 120 ONT_KEYW_KEYW File TABLESPACE_NAME OWNER OBJECT Id BLOCKS
_TASK_CODE_TYPE
SC_LIDX_TS SCOWNER PK_MM_TASK_LIST 2 85120
_HIST
SC_LIDX_TS SCOWNER PK_MM_TASK_LIST 6 24
_TASK_CODE
SC_LIDX_TS SCOWNER PK_MM_TASK_LIST 8 32
_TASK_CODE_PRPT
SC_LIDX_TS SCOWNER PK_MM_TASK_LIST 2 8
_TASK_LIST_TYPE
SC_LIDX_TS SCOWNER PK_SC_ACQ 2 8 SC_LIDX_TS SCOWNER PK_SC_DOC_PATH 2 8 SC_LIDX_TS SCOWNER PK_SC_KEYW 16 64 SC_LIDX_TS SCOWNER PK_SC_KEYW_DESC 32 128 SC_LIDX_TS SCOWNER PK_SC_KEYW_DISC 22 88 R_VALUE_DESC SC_LIDX_TS SCOWNER PK_SC_KEYW_MASK 2 8 SC_LIDX_TS SCOWNER PK_SC_LOB_DEF 2 8 SC_LIDX_TS SCOWNER PK_SC_LOB_PARAM 6 24 SC_LIDX_TS SCOWNER PK_SC_MODULE 2 8 SC_LIDX_TS SCOWNER PK_SC_NON_BUS_D 5 20 ATE SC_LKUP_TS free space 24 511 2044 SC_LKUP_TS SCOWNER SC_KEYW_DESC 1280
SC_LKUP_TS free space 25 195
780
SC_LKUP_TS SCOWNER LU_ACCESS 2
8
SC_LKUP_TS SCOWNER LU_ACQ_CONT 2
8
SC_LKUP_TS SCOWNER LU_DATATYPE 2
8
SC_LKUP_TS SCOWNER LU_FUNC_NAME 2
8
SC_LKUP_TS SCOWNER LU_HIST_TYPE 2
8
SC_LKUP_TS SCOWNER LU_IMG_TYPE 8
32
SC_LKUP_TS SCOWNER LU_KEYW_ACTION 2
8
SC_LKUP_TS SCOWNER LU_KEYW_DISCR_V 26
104
ALUE
SC_LKUP_TS SCOWNER LU_KEYW_PREPOST 2
8
SC_LKUP_TS SCOWNER LU_KEYW_TYPE 2
8
SC_LKUP_TS SCOWNER LU_LANG 2
8
SC_LKUP_TS SCOWNER LU_LDAP_TYPE 2
8
SC_LKUP_TS SCOWNER LU_LOB_PARAM_TY 2
8
PE
SC_LKUP_TS SCOWNER LU_OPER 2
8
SC_LKUP_TS SCOWNER LU_PATH_TYPE 2
8
SC_LKUP_TS SCOWNER LU_PLUG_IN 2
8
SC_LKUP_TS SCOWNER LU_PROP_DESC 90
360
SC_LKUP_TS SCOWNER LU_PROP_NAME 20
80
File
TABLESPACE_NAME OWNER OBJECT Id BLOCKS
KBYTES
PE
SC_LKUP_TS SCOWNER LU_TASK_LIST_LD 2
8
AP_TYPE
SC_LKUP_TS SCOWNER LU_TASK_LIST_TY 2
8
PE
SC_LKUP_TS SCOWNER LU_TRANS_LEVEL 2
8
SC_LKUP_TS SCOWNER MM_ACQ_ACTION 8
32
SC_LKUP_TS SCOWNER MM_ACQ_ACTION_S 2
8
UPPORT
SC_LKUP_TS SCOWNER MM_ACQ_MM_CONT 6
24
SC_LKUP_TS SCOWNER MM_ACQ_MM_PROP 2
8
SC_LKUP_TS SCOWNER MM_ACTION_PREPO 6
24
ST_KEYW
SC_LKUP_TS SCOWNER MM_IMG_TYPE 12
48
SC_LKUP_TS SCOWNER MM_KEYW_DISCR_V 80
320
ALUE
SC_LKUP_TS SCOWNER MM_KEYW_KEYW_TY 30
120
PE
SC_LKUP_TS SCOWNER MM_KEYW_LOB_DEF 2
8
_SQL
SC_LKUP_TS SCOWNER MM_KEYW_LOB_DEF 2
8
_SQL_MM_CONT
SC_LKUP_TS SCOWNER MM_KEYW_MM_CONT 28
112
SC_LKUP_TS SCOWNER MM_LOB_DEF_SQL_ 2
8
MM_CONT
SC_LKUP_TS SCOWNER MM_OPER_MM_CONT 410
1640
_KEYW_KEYW
SC_LKUP_TS SCOWNER MM_PLUG_IN_FUNC 24
96
_NAME
SC_LKUP_TS SCOWNER MM_TASK_CODE_TA 24
96
SK_CODE_TYPE
SC_LKUP_TS SCOWNER MM_TASK_LIST_HI 2
8
ST
SC_LKUP_TS SCOWNER MM_TASK_LIST_TA 10
40
SK_CODE
SC_LKUP_TS SCOWNER MM_TASK_LIST_TA 16
64
SK_CODE_PROMPT
SC_LKUP_TS SCOWNER MM_TASK_LIST_TA 2
8
SK_LIST_TYPE
File
TABLESPACE_NAME OWNER OBJECT Id BLOCKS
KBYTES
SC_LKUP_TS SCOWNER SC_ACQ 25 2
8
SC_LKUP_TS SCOWNER SC_DOC_PATH 2
8
SC_LKUP_TS SCOWNER SC_KEYW 72
288
SC_LKUP_TS SCOWNER SC_KEYW_DISCR_V 126
504
ALUE_DESC
SC_LKUP_TS SCOWNER SC_KEYW_MASK 2
8
SC_LKUP_TS SCOWNER SC_LOB_DEF 12
48
SC_LKUP_TS SCOWNER SC_LOB_PARAM 6
24
SC_LKUP_TS SCOWNER SC_MODULE 2
8
SC_LKUP_TS SCOWNER SC_NON_BUS_DATE 2
8
SC_LKUP_TS SCOWNER SC_SYSCONFIG 2
8
SC_LKUP_TS SCOWNER SC_TASK_CODE 20
80
SC_LKUP_TS SCOWNER SC_TASK_CODE_CO 1024
4096
MD
SC_LKUP_TS SCOWNER SC_TASK_CODE_DE 74
296
SC
SC_LKUP_TS SCOWNER SC_TASK_LIST 2
8
SC_LKUP_TS SCOWNER SC_TASK_LIST_DE 10
40
SC
SC_LKUP_TS SCOWNER SC_TASK_LIST_NA 2
8
ME
SC_LKUP_TS SCOWNER SC_VERSION 2
8
Received on Tue Aug 21 2001 - 11:14:47 CDT
 |
 |