Nice Explanation
VJ, June 01, 2001 - 11:52 am UTC
This was nice explanation and made the understanding very clear.
Julie, June 06, 2001 - 9:30 am UTC
Explaining tablespaces
A.Senthil Kumar, June 27, 2001 - 5:35 am UTC
It was really informative , I have also gone thru the
previous articles and find them very useful.
Dictionary and locally managed tablespace
A reader, June 27, 2001 - 8:04 am UTC
clearest explanation read on this point
Dictionary and locally managed tablespace
Umesh S. Kasturi, June 27, 2001 - 8:05 am UTC
clearest explanation read on this point
subbarao, January 25, 2002 - 7:32 am UTC
This is the perfect explanition i ever read. A small clarification in this. I will have 2 gb of data in a table, if i use this locally managed tablespace for this, I want to give 1m as uniform extent. In this case the table may have 2048 extents. Is it ok. In earlier versions they say if number of extents increase there is performance drain, is there in current versions. Please let us know.
Thanks in advance
v6163
January 25, 2002 - 8:41 am UTC
Who is "they" -- "they" would be incorrect, inaccurate at best.
Anyway, 2048 extents in a single object is getting high. 2048 extents in a single tablespace with many objects is just fine.
Very nice
Brian Tkatch, January 25, 2002 - 12:11 pm UTC
1) Is there a reason to *not* use a dictionary managed tablespace in 8i?
2) If one follows the directions set forth in </code>
http://technet.oracle.com/deploy/availability/pdf/defrag.pdf <code>
then there should be no fragmentation in a dictionary managed tablespace either. Would there still be a significant difference in between the two?
January 25, 2002 - 1:53 pm UTC
1) not really... i use them exclusively
2) with LMTs, you its enforced, its not a choice, not something to follow -- its just a fact of life.
LMTs are faster on some operations, like allocating space, truncating tables, dropping objects...
best explaination of LMT and DMT
Partha, January 25, 2002 - 12:12 pm UTC
Perfect explaination
Very Nice
Guillermo Portugal, January 25, 2002 - 1:40 pm UTC
This is the perfect explanition i ever read
RE: Brian's Feedback
Mark A. Williams, January 25, 2002 - 4:44 pm UTC
Tom:
Not sure which one of you (Brian or yourself) is "backwards"...
Brian's question:
1) Is there a reason to *not* use a dictionary managed tablespace in 8i?
Followup:
1) not really... i use them exclusively
I'm quite sure you meant to say you use LMT's exclusively - unless you've had a radical change of mind :) and/or maybe Brian also meant to say "locally managed" instead of "dictionary"...
Just want to avoid any confusion for the casual readers...
- Mark
January 26, 2002 - 6:47 pm UTC
Correct -- yes, I got that totally 100% backwards, guess I read it too fast.
I use LMT's 100% (except for system which cannot be).
Dictionary managed tablespaces have drawbacks (they can get fragmented, they allocate and deallocate space slower for example).
Use LMT's. In 9i, its the DEFAULT type of tablespace.
Subbarao, January 26, 2002 - 2:42 am UTC
Hi tom,
As i asked u about a clarification on giving extent size as 1Mb and tabledata as 2GB getting 2048 extents. So in this case how much size i need to give for my extent.
U told that 2048 extents in a single object is getting high. So in any case how many max extents i can plan for a object. And also i have a tables of size 60GB and 30GB that will grow in a year, what is the best way to give extents for this. Please let us know.
January 26, 2002 - 6:54 pm UTC
Try to target 1000 or so as the max number of extents. That is a reasonable number, one that should accomidate virtually any sized table.
The size of bitmap in LMT
A reader, January 26, 2002 - 7:21 pm UTC
How do we determine the size of the bitmap (in header) in LMT. By default is it 64K? What happens if we get more than 64K extents in that tablespace, does the bitmap grow?
January 27, 2002 - 10:23 am UTC
it is 64k/datafile.
There are a lot of BITS in 64k.
allocating extents
David, April 12, 2004 - 10:48 pm UTC
Is it true that oracle allocates extents in a round robin fashion if I had multiple data files in a tablespace? Thanks.
April 13, 2004 - 7:13 am UTC
yes, with a caveat for LMTs using system allocated extent sizes... In that case, the first couple of extents (which are all small) will be allocated in the same datafile and then it'll go round robin.
System tablespace in LMT
nick, June 09, 2004 - 12:03 pm UTC
Hi Tom,
You said it does't matter either LMT or DMT for system tablespace. But oracle recomanded to use system tablesapce as LMT.
can you please explain.
Thanks
June 09, 2004 - 12:10 pm UTC
in 8i, system could not be LMT
in 9i, it can be (and if it is, all tablespaces are LMT)
In versions that support it, use it.
I'm really considering the idea of ....
smith, June 09, 2004 - 1:26 pm UTC
paying someone to monitor your site so that I get to know when it opens for new questions. ;)
Best regards!
Not Uniform and Hence Fragmentation?
Sea, July 02, 2004 - 3:01 am UTC
You said "it is IMPOSSIBLE to have free space fragmentation in a locally managed tablespace with uniform extents."
Most of our tablespaces have the following settings in the dba_tablespaces table:
EXTENT_MANAGEMENT: LOCAL
ALLOCATION_TYPE: SYSTEM
SEGMENT_SPACE_MANAGEMENT: AUTO
Does it mean the extent sizes are not uniform and there will be fragmentation in the tablespace?
July 02, 2004 - 9:18 am UTC
the extents will be of very very few different sizes and nice powers of 2 meaning small ones fit very nicely inside of larger ones and it'll try to clump all of the 64k extents together if possible (so if you drop a table, it'll leave a bunch of 64k extents next to eachother typically).
I would say no, free space fragmentation is not going to happen here.
Indexes & Data Tablespaces
Mariano, August 04, 2004 - 9:28 pm UTC
Tom, hi.
Is it a good idea to have two (at least) tablespaces: one to hold the data (tables) and other just for the indexes?
I saw that kind of design a lot and I just wonder if it's a good idea.
If you don't mind, another related question: I'm planning the build of a tablespace. I've got a sample of the data I want to store, estimate its growing through time and the tables' datatypes: how can I size the space needed for indexes?
As always thanks in advance.
August 05, 2004 - 12:33 pm UTC
it does nothing performance wise.
It is totally "your preference". me, I give a modest sized application "a tablespace". for me, that is the easiest way on the planet to admin it. i can back it up independently, i can restore it independently (even PITR it to a different point in time).
for sizing, the only tried and true method I know is "load X% of representative data, analyze, get the size, multiply".
That'll give you the low end rough cut estimate.
A reader, August 25, 2004 - 10:07 am UTC
How to find the total space and the used space for a tablespace..i looked at dba_segments and dba_tablespaces..not much luck
thanks
August 25, 2004 - 10:55 am UTC
did you think to sum(bytes) in dba-segments grouping by tablespace?
but search for
"free.sql"
i've a script that reports allocated, used and so on by tablespace for you.
index tablespace
A reader, August 31, 2004 - 9:42 am UTC
Tom,
We have two DMT tablespaces, data and index. The index tablespace always stays at warning level as follows:
%FREE FREEKB TOTALKB USEDKB
WARN TAB_DATA 20.48 2097104 10240000 8142896
WARN TAB_INDEX 14.97 1920168 12823600 10903432
However index tablespace has around 1.9 gb freespace left.
Other details
NAME = TAB_INDEX
FREE_SPACE = 1920168
NUM_FREE_Extents = 97
LARG_CONTINUOUS_FREE_SPACE = 923152
LARGEST_EXTENT = 100552 MIN_USABLE_EXTENTS = 16
Does it indicate a real warning just because it always stays in warn mode?
August 31, 2004 - 1:14 pm UTC
who is "warning you" and what are they warning you about exactly?
A reader, August 31, 2004 - 2:10 pm UTC
Tom,
That is a tablespace calculation script that warns when fre percentage is less than 20%. How is data tablespace (which has application tables) related to index tablespace technically and when (on what grounds/basis) should the space be increased? On 05/20/2004, TAB_INDEX was 16.18% free. Now it is 14.97 free.
Thanks in advance
Datafile status:
TSPACE FILE_ID TOT_TS_SIZE (MB)FREE_TS_SIZE (MB)USED_TS_SIZE (MB)
TAB_DATA 17 2000 1508.5078125 491.4921875
TAB_DATA 9 8000 539.4453125 7460.5546875
TSPACE FILE_ID TOT_TS_SIZE (MB)FREE_TS_SIZE (MB)USED_TS_SIZE (MB)
TAB_INDEX 16 1000 904.09375 95.90625
TAB_INDEX 15 1000 770.953125 229.046875
TAB_INDEX 10 8523.046875 190.859375 8332.1875
TAB_INDEX 14 1000 7.6484375 992.3515625
TAB_INDEX 13 1000 1.609375 998.390625
August 31, 2004 - 2:52 pm UTC
only you, having the experience of monitoring your systems and knowing their growth patterns, can say if this is a cause for "alarm"
Me, i use autoextend datafiles most of the time -- i have no "warning" scripts like this (keep a nice pad in the OS)
so, is 14.97% free sufficient growth for you, does it keep you going (two data points is very much insufficient to get a pattern. So, on May 20 of this year, it was 16.18% free -- it might have been that way for the last year -- meaning you have more than 7 years of growth in this 14.97%)
Only you can say for sure, only you have sufficient data points to make this call.
A reader, August 31, 2004 - 4:24 pm UTC
Thank you. That helped
Nishant, December 15, 2004 - 10:02 am UTC
Sir,
i want to change my system tablespace from locally managed to dictionary managed . i am using 9.2.0.1 and 9.2.0.4 .. is it possible to do this ..plz let me know .it would be an great help
thanx in advance...
December 15, 2004 - 2:18 pm UTC
why?????
I'm very very curious as to the sound technical reason for doing this.
But you won't like the method -- which involves "a rebuild"
if you have SYSTEM as an LMT, all tablespaces in that database are LMTs -- all of them.
Nishant, December 16, 2004 - 5:30 am UTC
Sir,
i do agree you.. not to go with DMT but i think it is required ... the way i want my RMAN catalog to work.. i have discussed the reason on RMAN topic . the below error made me to thing in this way.
Database mounted.
ORA-00402: database changes by release 9.2.0.0.0 cannot be used by release
8.1.0.0.0
ORA-00405: compatibility type "Locally Managed SYSTEM tablespace"
so there's no other way except REBUILD.. it is possible to make it re-creating the controlfile and re-running the catalog and cataproc script..
plz suggest
thanx in advance
December 16, 2004 - 8:29 am UTC
stop, go back, you need to use the 8174 rman binary.
Explaining tablespace
Suresh Shinde, January 06, 2005 - 7:52 am UTC
Nicely explained the topic for LMT and DMT tablespaces...
January 06, 2005 - 10:59 am UTC
Your opinion...
Gee, January 10, 2005 - 11:30 am UTC
Tom I have read from you previously that you feel strong about having ONE tablespace per application for "medium" sized
applications.
Do you still keep the USERS, RBS, and TEMP tablespaces that are made by default or do you use the tablespace that you create?
I know that you at least have to have the SYSTEM tablespace and you would need at least one tablespace for rollback segs.
Im curious because I know that Oracle stores the definition of a temp table in the SYSTEM tablespace then creates the table in another tablespace when it is needed.
I want to get rid of the default tablespaces and use ONE tablespace for my application.
Having six like we have gives us no advantage as far as management or performance.
January 10, 2005 - 11:42 am UTC
RBS is not my application data, it stays
TEMP is not my application data, it stays
USERS is not my application and isn't really necessary so it can stay or go, I don't care.
you have SYSTEM, SYSAUX (sysaux is going to be there in 10g).
You do NOT want to your your tablespace as "temp" which is what you seem to be saying. a global temporary table consumes no storage (not in system, not anywhere) until you insert into it, and then it consumes TEMP storage which rightly belongs in TEMP and not in your application's tablespace.
what I was saying...
gee, January 10, 2005 - 2:34 pm UTC
......was that the DEFINITION of a temp table is stored in the SYSTEM tablespace.
I say/know this because of this
EXECUTE dbms_tts.transport_set_check('SAMS_INTERFACE,SAMS_AUDIT,SAMS_LOB,SAMS_INDX,SAMS_TABS,USERS,RBS', TRUE);
Index SAMS.SM_TRANSFER_DATA_TEMP_PK in tablespace SAMS_TABS enforces primary co
striants of table SAMS.SM_TRANSFER_DATA_TEMP in t
ablespace SYSTEM
Domain/Functional IndexSAMS.PERSONNEL_SSN_LAST_FOUR_NDX in tablespace SAMS_INDX
not allowed in transportable set
Domain/Functional IndexSAMS.PERSONNEL_SSN_LAST_ONE_NDX in tablespace SAMS_INDX
ot allowed in transportable set
Domain/Functional IndexSAMS.PERSONNEL_SSN_LAST_TWO_NDX in tablespace SAMS_INDX
ot allowed in transportable set
Rollback segment RBS01 in tablespace RBS not allowed in transportable set
Rollback segment RBS02 in tablespace RBS not allowed in transportable set
Rollback segment RBS03 in tablespace RBS not allowed in transportable set
Rollback segment RBS04 in tablespace RBS not allowed in transportable set
Index SAMS.PERS_READINESS_TEMP_PK in tablespace SAMS_TABS enforces primary cons
riants of table SAMS.PERS_READINESS_TEMP in table
space SYSTEM
which shows me that constraints and indexes for temp tables are stored in user created tablespaces but the ddl for it is kept in the data dictionary.
Ive been having to drop the temp tables BEFORE transporting my tablespace then recreating it after I import that tablespace set.
This is why Im asking the question because you were helping me out on another thread about an error importing the set.
I posted today that the error was resolved.
But its not. I can import the tablespace set one time successfully.
Then it gives me an error as if the path given for the datafiles are incorrect.
I would like to clear all of this up and use ONE tablespace for the application and I dont have to worry about where six different files may be.
Still have yet to figure out the reason why import is failing. I did as you said and checked the path and files.
January 10, 2005 - 4:13 pm UTC
I cannot reproduce that at all.
is that a true TEMP table (create global TEMPORARY table) or just a table you call "temp"
segment
CG, January 10, 2005 - 10:06 pm UTC
Its a true global temporary table.
When I get to work tomorrow I will post the script.
should I also query dba_segments?
this is really the root of all my trouble.
I logged a TAR with Metalink today about the error I posted before where import cannot find the datafile.
There is a bug that says that when u try to import a tablespace that was exported ( in a plugged in state )
that you get the ORA-19721 ERROR.
But my tablespace is not exported as plugged in it is a non plugged in tablespace.
I will run the self contained check again tomorrow and post.
January 11, 2005 - 8:50 am UTC
script and VERSION.
reader
A reader, January 11, 2005 - 2:03 pm UTC
i was reading this and unable to understand one line ...will u be able to explain the following " If you have
more then 1 file, you can have more then 1 space request being processed --
hence it removes contention."..here you are talking about which files and what exactly it means.
thanks
January 11, 2005 - 2:50 pm UTC
talking about datafiles and the ability for each datafile to manage itself, instead of the single dictionary table that used to do it.
READER
A reader, January 11, 2005 - 3:01 pm UTC
thanks a lot :-))
Segment - Script and version u requested
CG, January 12, 2005 - 12:11 pm UTC
sys@SAMSDBA> EXECUTE dbms_tts.transport_set_check('SAMS_INTERFACE,SAMS_AUDIT,SAMS_LOB,SAMS_INDX,SAMS_TABS,USERS', TRUE);
PL/SQL procedure successfully completed.
sys@SAMSDBA> select * from transport_set_violations;
VIOLATIONS
----------------------------------------------------------------------------------------------------------------------------------
Index SAMS.SM_TRANSFER_DATA_TEMP_PK in tablespace SAMS_TABS enforces primary constriants of table SAMS.SM_TRANSFER_DATA_TEMP in t
ablespace SYSTEM
Index SAMS.PERS_READINESS_TEMP_PK in tablespace SAMS_TABS enforces primary constriants of table SAMS.PERS_READINESS_TEMP in table
space SYSTEM
Domain/Functional IndexSAMS.PERSONNEL_SSN_LAST_TWO_NDX in tablespace SAMS_INDX not allowed in transportable set
Domain/Functional IndexSAMS.PERSONNEL_SSN_LAST_FOUR_NDX in tablespace SAMS_INDX not allowed in transportable set
Domain/Functional IndexSAMS.PERSONNEL_SSN_LAST_ONE_NDX in tablespace SAMS_INDX not allowed in transportable set
sys@SAMSDBA>
The following are the scripts for the temp tables;
CREATE GLOBAL TEMPORARY TABLE PERS_READINESS_TEMP
(
GENDER VARCHAR2(1) NOT NULL,
PERS_SEQ NUMBER NOT NULL
)
ON COMMIT DELETE ROWS;
CREATE UNIQUE INDEX PERS_READINESS_TEMP_PK ON PERS_READINESS_TEMP
(PERS_SEQ);
ALTER TABLE PERS_READINESS_TEMP ADD (
CONSTRAINT PERS_READINESS_TEMP_PK PRIMARY KEY (PERS_SEQ));
GRANT DELETE, INSERT, SELECT, UPDATE ON PERS_READINESS_TEMP TO PROGRAMMERS;
GRANT DELETE, INSERT, SELECT, UPDATE ON PERS_READINESS_TEMP TO SAMSHS_VISITOR;
CREATE GLOBAL TEMPORARY TABLE SM_TRANSFER_DATA_TEMP
(
TABLE_NAME VARCHAR2(30) NOT NULL,
ROW_NUM NUMBER NOT NULL,
COLUMN_NUM NUMBER NOT NULL,
COLUMN_NAME VARCHAR2(30) NOT NULL,
DATA_TYPE VARCHAR2(1) NOT NULL,
IN_OUT_IND VARCHAR2(1) NOT NULL,
GEN_KEY_TYPE VARCHAR2(1),
DATA VARCHAR2(4000),
NEW_KEY NUMBER
)
ON COMMIT DELETE ROWS;
CREATE UNIQUE INDEX SM_TRANSFER_DATA_TEMP_PK ON SM_TRANSFER_DATA_TEMP
(TABLE_NAME, ROW_NUM, COLUMN_NUM);
ALTER TABLE SM_TRANSFER_DATA_TEMP ADD (
CONSTRAINT SM_TRANSFER_DATA_TEMP_PK PRIMARY KEY (TABLE_NAME, ROW_NUM, COLUMN_NUM));
GRANT DELETE, INSERT, SELECT, UPDATE ON SM_TRANSFER_DATA_TEMP TO PROGRAMMERS;
GRANT DELETE, INSERT, UPDATE ON SM_TRANSFER_DATA_TEMP TO SAMS_ADMIN;
GRANT SELECT ON SM_TRANSFER_DATA_TEMP TO SAMS_USER;
The version is 8.1.7.4.11
January 12, 2005 - 1:32 pm UTC
can you get this to reproduce, something standalone (yet 100% complete). I cannot:
ops$tkyte@ORA817DEV> drop tablespace testing including contents;
Tablespace dropped.
ops$tkyte@ORA817DEV> create tablespace testing datafile '/tmp/testing.dbf' size 5m reuse;
Tablespace created.
ops$tkyte@ORA817DEV> alter user ops$tkyte default tablespace testing;
User altered.
ops$tkyte@ORA817DEV> create table t ( x int );
Table created.
ops$tkyte@ORA817DEV> create global temporary table gtt ( x int primary key, y int, z int );
Table created.
ops$tkyte@ORA817DEV> create index gtt_idx on gtt(x,y);
Index created.
ops$tkyte@ORA817DEV> exec sys.dbms_tts.transport_set_check('TESTING',true);
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> select table_name, tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GTT
T TESTING
2 rows selected.
ops$tkyte@ORA817DEV> select index_name, tablespace_name from user_indexes;
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GTT_IDX
SYS_C0014560
Same results
CG, January 12, 2005 - 5:01 pm UTC
I got the same results as you:
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 12 16:33:39 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production
sams@SAMSDBA> CREATE TABLESPACE test_t
2 DATAFILE 'c:\oracle\oradata\samsdb\test_t.dbf'
3 SIZE 5M REUSE
4 AUTOEXTEND ON NEXT 5M;
Tablespace created.
sams@SAMSDBA> CREATE TABLE ROUTE1
2 (
3 ROUTE_NAME VARCHAR2(20) NOT NULL,
4 ROUTE_SEQ NUMBER NOT NULL
5 )
6 TABLESPACE TEST_T
7 PCTUSED 40
8 PCTFREE 20
9 INITRANS 1
10 MAXTRANS 255
11 STORAGE (
12 INITIAL 64K
13 NEXT 64K
14 MINEXTENTS 1
15 MAXEXTENTS 1000
16 PCTINCREASE 0
17 FREELISTS 1
18 FREELIST GROUPS 1
19 BUFFER_POOL DEFAULT
20 )
21 LOGGING
22 NOCACHE
23 NOPARALLEL;
Table created.
sams@SAMSDBA> CREATE GLOBAL TEMPORARY TABLE SM_TRANSFER_DATA_TEMP2
2 (
3 TABLE_NAME VARCHAR2(30) NOT NULL,
4 ROW_NUM NUMBER NOT NULL,
5 COLUMN_NUM NUMBER NOT NULL,
6 COLUMN_NAME VARCHAR2(30) NOT NULL,
7 DATA_TYPE VARCHAR2(1) NOT NULL,
8 IN_OUT_IND VARCHAR2(1) NOT NULL,
9 GEN_KEY_TYPE VARCHAR2(1),
10 DATA VARCHAR2(4000),
11 NEW_KEY NUMBER
12 )
13 ON COMMIT DELETE ROWS;
Table created.
sams@SAMSDBA> drop table route1;
Table dropped.
sams@SAMSDBA> CREATE UNIQUE INDEX SM_TRANSFER_DATA_TEMP_PK2 ON SM_TRANSFER_DATA_TEMP2
2 (TABLE_NAME, ROW_NUM, COLUMN_NUM);
Index created.
sams@SAMSDBA> ALTER TABLE SM_TRANSFER_DATA_TEMP2 ADD (
2 CONSTRAINT SM_TRANSFER_DATA_TEMP_PK2 PRIMARY KEY (TABLE_NAME, ROW_NUM, COLUMN_NUM));
Table altered.
sams@SAMSDBA> exec sys.dbms_tts.transport_set_check('TEST_T',TRUE);
PL/SQL procedure successfully completed.
sams@SAMSDBA> select * from sys.transport_set_violations;
no rows selected
and when I select from the user_tables and user_indexes table the column for the temp table is null.
sams@SAMSDBA> select table_name, tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
MM_EXCESS_TEMP
PERS_READINESS_TEMP
RH_PERS_NAVSEA08_GROUP_TEMP
RH_PERS_NAVSEA08_REJ_TEMP
RH_PERS_NAVSEA08_TEMP
ROUTE1 TEST_T
SM_EIC_CLOB_TEMP
SM_TRANSFER_DATA_TEMP
SM_TRANSFER_DATA_TEMP2
9 rows selected.
sams@SAMSDBA> show user
USER is "SAMS"
sams@SAMSDBA> select table_name, tablespace_name from user_indexes;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PERS_READINESS_TEMP
SM_TRANSFER_DATA_TEMP
SM_TRANSFER_DATA_TEMP2
sams@SAMSDBA>
It is also null in the problem tablespace set for all the temp tables in the schema.
Ill have to do more digging.
error while insert
A reader, February 21, 2005 - 2:24 pm UTC
Hi,
I am trying to insert into a table tab1 created in a user defined tablespace and I am getting the error shown below:
ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexInsert
ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index CTXSYS.SYS_IOT_TOP_42181 by 1024 in tablespace SYSAUX
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 629
Shud I manually extend SYSAUX tablespace? I need to know if it is managed by Oracle.
Pls help.
February 21, 2005 - 3:51 pm UTC
it is out of space, either add a datafile to it or resize an existing datafile that belongs to it to be larger.
Explanation needed
James, April 06, 2005 - 11:51 am UTC
Dear Tom,
Do you support creating a tablespace with different block
sizes? Where they are useful??
April 06, 2005 - 2:06 pm UTC
no, it would not be the first approach and probably not the last approach I would take. It complicates things.
Yes, I'll be having a paper on this, it'll be on the home page/files tab sometime soon.
Error while inserting in another language
Venki, December 07, 2005 - 7:31 am UTC
I am getting this error while inserting the record in to a 10G database. What could be the possible reason ?
ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexInsert
ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column for primary key DRG-13201: KOREAN_LEXER
is desupported
ORA-30576: ConText Option dictionary loading error
ORA-06512: at "CTXSYS.DRUE", line 160 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 633
December 08, 2005 - 12:56 am UTC
could it be that you are in fact using a desupported lexer?
LMT and DMT
A reader, April 17, 2006 - 7:24 am UTC
In terms of fragmentation:
If I don't define storage characteristics at "segment level"
and keep initial=next extent and keep pctincrease=0 in DMT then:
Is there any fragmentation in tablespace?
April 17, 2006 - 8:08 am UTC
well, fragmentation would only happen if you drop/truncate segments so....
It would be highly unlikely to have "fragementation" of a tablespace with initial=next and pctincrease=0
DMT initial=next pctincrease=0
A reader, April 17, 2006 - 8:26 am UTC
"well, fragmentation would only happen if you drop/truncate segments so...."
But drop/truncate would bring HWM down. Still is there any fragmentation in DMT ?
April 17, 2006 - 8:35 am UTC
what does a HWM (high water mark) which applies to segments have to do with a tablespace?
Define fragmentation for us - to me, it would be having a tablespace where you have a bunch of extents - pretty much ALL of different sizes - and every other extent is "free" (so you have allocated space/unallocated/allocated/unallocated.....).
In other words, you have swiss cheese - lots of different sized holes - that may or may not be reusable in your tablespace (eg: you could have gigabytes of free space - but yet not be able to allocate a NEXT extent for something).
Now, unless you drop or truncate segements, you'll not get these "holes"
Nice
Santhanamuthu, May 20, 2008 - 11:10 pm UTC
hi,
its nice, its makes me perfect in understanding the diff
thanx
santhanam
Please clarify
Pavan Reddy, May 05, 2009 - 12:11 am UTC
Hi Tom,
Can there be a table with out being assigned to a tablespace? when I queried dba_tables in our database, I found some tables have no value (value being null) for tablespace_name column.
Please clarify on this
May 09, 2009 - 10:46 am UTC
global temporary tables would be one
tables that are not a segment would be another.
ops$tkyte%ORA11GR1> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 -- subpartition by hash(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
12 )
13 ;
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select tablespace_name from user_tables where table_name = 'T';
TABLESPACE_NAME
------------------------------
ops$tkyte%ORA11GR1>
the table isn't the segment in this case, the partitions are!
also ...
Sokrates, May 10, 2009 - 6:25 am UTC
external tables also don't need a tablespace
May 11, 2009 - 5:02 pm UTC
IOT's won't either... Just some examples.
ORA-1683
Ankit, August 20, 2009 - 1:33 am UTC
Hi Tom,
I got this error in the alert log of Dev. Database(running on 10.2.0.3)
ORA-1683: unable to extend index SYS.WRH$_ACTIVE_SESSION_HISTORY_PK partition WRH$_ACTIVE_194773700_16232 by 128 in tablespace SYSAUX
I increased the size of SYSAUX datafile for this, i was wondering if there is any other approach for addressing this.
Also can we add another datafile to Tablespaces like SYSTEm,SYSAUX.....I have never seen these tablespace having more than one datafile so just wanted confirm
August 24, 2009 - 5:06 pm UTC
you could make the datafiles autoextend if you wish.
If you have not ever seen a tablespace with more than one datafile - do not worry, they exists by the billions.
:)
Ankit, August 26, 2009 - 1:53 am UTC
That is obvious :)
But what i meant if there is anything else i could have done beyond increasing space to counter this ?
Like if we could purge any data so that freed space could be allocated back to the segment.
August 26, 2009 - 7:11 pm UTC
Reg the size value in ORA-01654
Lal, March 06, 2014 - 11:13 am UTC
Tom,
One doubt. We get the following error when the tablespace is full.
"ORA-01654: unable to extend index <index name> by <space> in tablespace <tablespacename>"
Have seen erros with space as 64/128/etc. what factors determine this space value? Means what is the space value in this error, is it the extent size?