Re: REP: Locally Managed Tablespaces
Alex, X$ tables are simply memory structures. In this case it is populated by data from the file headers. This stuff does not exist in the database. Jared On Wednesday 25 April 2001 07:49, Hillman, Alex wrote: And what is x$ktfbue and how it is maintained if there is no info in data dictionary? Alex Hillman -Original Message- Sent: Tuesday, April 24, 2001 9:20 PM To: Multiple recipients of list ORACLE-L On Tuesday 24 April 2001 14:24, Henry Poras wrote: Isn't it also in sys.uet$? Oops, that's for dictionary managed, not locally managed. You're right. But isn't there a way to see locally managed extents? Yes, they show up in dba_extents, but it doesn't come from sys.uet$. Here's the view as of 8.1.7. Extents in LMT's are retrieved by the second SELECT statement in the SQL. select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, ds.tablespace_name, e.ext#, f.file#, e.block#, e.length * ds.blocksize, e.length, e.file# from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f where e.segfile# = ds.relative_fno and e.segblock# = ds.header_block and e.ts# = ds.tablespace_id and e.ts# = f.ts# and e.file# = f.relfile# and bitand(NVL(ds.segment_flags,0), 1) = 0 union all -- get the LMT extents select /*+ ordered use_nl(e) use_nl(f) */ ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, ds.tablespace_name, e.ktfbueextno, f.file#, e.ktfbuebno, e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f where e.ktfbuesegfno = ds.relative_fno and e.ktfbuesegbno = ds.header_block and e.ktfbuesegtsn = ds.tablespace_id and e.ktfbuesegtsn = f.ts# and e.ktfbuefno = f.relfile# and bitand(NVL(ds.segment_flags, 0), 1) = 1 / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: REP: Locally Managed Tablespaces
Thanks Mark. -Original Message- Sent: Wednesday, April 25, 2001 12:56 PM To: Multiple recipients of list ORACLE-L I haven't got this back on the list yet - and haven't recieved a message from the list since about 9 this morning - so I thought I would resend this just in case.. -- Right, I have done a little diggin in to this and the upshot is that if you create a LMT with UNIFORM extent sizes, then the tablespace will adere to those extent sizes no matter what you specifiy in the INITIAL storage parameter. I have a LMT already created with a UNIFORM extent size of 128k: SQL select tablespace_name, 2 initial_extent, 3 next_extent, 4 extent_management 5 from DBA_TABLESPACES 6 where tablespace_name = 'LOCAL'; TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT EXTENT_MAN -- -- --- -- LOCAL 131072 131072 LOCAL I created a table in LOCAL as follows: SQL select table_name, 2 tablespace_name, 3 initial_extent, 4 next_extent 5 from DBA_TABLES 6 where table_name = 'EXTENT_TEST'; TABLE_NAMETABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT - -- -- --- EXTENT_TEST LOCAL 1048576131072 You notice that DBA_TABLES *DOES* report that this table does in fact show extent sizes of 1MB. Now, I have a tool that can show a tablespace map of both dictionary and locally managed tablespaces, this reported that the table EXTENT_TEST has a total of 8 extents each at a size of 128K - indicating that Oracle seems to take the initial extent size given by the create table statement, and makes up that initial extent with a number of the uniform extents. I also took Jared's statement, and if I am correct this will give you a row for each extent allocated (correct me if I'm wrong Jared), so based on this I did the following; 1 select count(*) from (select /*+ ordered use_nl(e) use_nl(f) */ 2 ds.owner, ds.segment_name, ds.partition_name, 3 ds.segment_type, ds.tablespace_name, 4 e.ktfbueextno, f.file#, e.ktfbuebno, 5 e.ktfbueblks * ds.blocksize, 6 e.ktfbueblks, e.ktfbuefno 7 from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f 8 where e.ktfbuesegfno = ds.relative_fno 9 and e.ktfbuesegbno = ds.header_block 10 and e.ktfbuesegtsn = ds.tablespace_id 11 and e.ktfbuesegtsn = f.ts# 12 and e.ktfbuefno = f.relfile# 13 and bitand(NVL(ds.segment_flags, 0), 1) = 1 14 and ds.segment_name = 'EXTENT_TEST' 15* ) SQL / COUNT(*) -- 8 The tool that I use does not show any other extent sizes other than than 128K. This pretty much seems to that LMT's with UNIFORM extent sizes actually DO stick that uniform size. Sorry for the long reply.. I had a bit of spare time :) HTH Mark -Original Message- Sent: Tuesday, April 24, 2001 10:46 To: Multiple recipients of list ORACLE-L I haven't tried it, but I recall hearing that all extents really are 1M, it is just the data dictionary displays that are confusing. If you create a table with initial=next=1.5M, I believe dba_tables will show the create parameters, while dba_extents will show the actual extent sizes. I don't have a good test database right now to try this. Maybe I have the dictionary tables wrong (I guess I could check their sources). Henry -Original Message- Sent: Tuesday, April 24, 2001 4:47 PM To: Multiple recipients of list ORACLE-L I am doing some research on locally managed tablespaces prior to using them. Looking for problems, etc. This is the first negative response I have heard about them. Perhaps, storage parameters were specified on the create table? Patricia -Original Message- Sent: Tuesday, April 24, 2001 12:52 PM To: Multiple recipients of list ORACLE-L A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
RE: REP: Locally Managed Tablespaces
And what is x$ktfbue and how it is maintained if there is no info in data dictionary? Alex Hillman -Original Message- Sent: Tuesday, April 24, 2001 9:20 PM To: Multiple recipients of list ORACLE-L On Tuesday 24 April 2001 14:24, Henry Poras wrote: Isn't it also in sys.uet$? Oops, that's for dictionary managed, not locally managed. You're right. But isn't there a way to see locally managed extents? Yes, they show up in dba_extents, but it doesn't come from sys.uet$. Here's the view as of 8.1.7. Extents in LMT's are retrieved by the second SELECT statement in the SQL. select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, ds.tablespace_name, e.ext#, f.file#, e.block#, e.length * ds.blocksize, e.length, e.file# from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f where e.segfile# = ds.relative_fno and e.segblock# = ds.header_block and e.ts# = ds.tablespace_id and e.ts# = f.ts# and e.file# = f.relfile# and bitand(NVL(ds.segment_flags,0), 1) = 0 union all -- get the LMT extents select /*+ ordered use_nl(e) use_nl(f) */ ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, ds.tablespace_name, e.ktfbueextno, f.file#, e.ktfbuebno, e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f where e.ktfbuesegfno = ds.relative_fno and e.ktfbuesegbno = ds.header_block and e.ktfbuesegtsn = ds.tablespace_id and e.ktfbuesegtsn = f.ts# and e.ktfbuefno = f.relfile# and bitand(NVL(ds.segment_flags, 0), 1) = 1 / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: REP: Locally Managed Tablespaces
Right, I have done a little diggin in to this and the upshot is that if you create a LMT with UNIFORM extent sizes, then the tablespace will adere to those extent sizes no matter what you specifiy in the INITIAL storage parameter. I have a LMT already created with a UNIFORM extent size of 128k: SQL select tablespace_name, 2 initial_extent, 3 next_extent, 4 extent_management 5 from DBA_TABLESPACES 6 where tablespace_name = 'LOCAL'; TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT EXTENT_MAN -- -- --- -- LOCAL 131072 131072 LOCAL I created a table in LOCAL as follows: SQL select table_name, 2 tablespace_name, 3 initial_extent, 4 next_extent 5 from DBA_TABLES 6 where table_name = 'EXTENT_TEST'; TABLE_NAMETABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT - -- -- --- EXTENT_TEST LOCAL 1048576131072 You notice that DBA_TABLES *DOES* report that this table does in fact show extent sizes of 1MB. Now, I have a tool that can show a tablespace map of both dictionary and locally managed tablespaces, this reported that the table EXTENT_TEST has a total of 8 extents each at a size of 128K - indicating that Oracle seems to take the initial extent size given by the create table statement, and makes up that initial extent with a number of the uniform extents. I also took Jared's statement, and if I am correct this will give you a row for each extent allocated (correct me if I'm wrong Jared), so based on this I did the following; 1 select count(*) from (select /*+ ordered use_nl(e) use_nl(f) */ 2 ds.owner, ds.segment_name, ds.partition_name, 3 ds.segment_type, ds.tablespace_name, 4 e.ktfbueextno, f.file#, e.ktfbuebno, 5 e.ktfbueblks * ds.blocksize, 6 e.ktfbueblks, e.ktfbuefno 7 from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f 8 where e.ktfbuesegfno = ds.relative_fno 9 and e.ktfbuesegbno = ds.header_block 10 and e.ktfbuesegtsn = ds.tablespace_id 11 and e.ktfbuesegtsn = f.ts# 12 and e.ktfbuefno = f.relfile# 13 and bitand(NVL(ds.segment_flags, 0), 1) = 1 14 and ds.segment_name = 'EXTENT_TEST' 15* ) SQL / COUNT(*) -- 8 The tool that I use does not show any other extent sizes other than than 128K. This pretty much seems to that LMT's with UNIFORM extent sizes actually DO stick that uniform size. Sorry for the long reply.. I had a bit of spare time :) HTH Mark -Original Message- Sent: Tuesday, April 24, 2001 10:46 To: Multiple recipients of list ORACLE-L I haven't tried it, but I recall hearing that all extents really are 1M, it is just the data dictionary displays that are confusing. If you create a table with initial=next=1.5M, I believe dba_tables will show the create parameters, while dba_extents will show the actual extent sizes. I don't have a good test database right now to try this. Maybe I have the dictionary tables wrong (I guess I could check their sources). Henry -Original Message- Sent: Tuesday, April 24, 2001 4:47 PM To: Multiple recipients of list ORACLE-L I am doing some research on locally managed tablespaces prior to using them. Looking for problems, etc. This is the first negative response I have heard about them. Perhaps, storage parameters were specified on the create table? Patricia -Original Message- Sent: Tuesday, April 24, 2001 12:52 PM To: Multiple recipients of list ORACLE-L A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ --
Re: REP: Locally Managed Tablespaces
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: REP: Locally Managed Tablespaces
A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: The Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: REP: Locally Managed Tablespaces
Or tables were imported with compress=y? Or an alter index rebuild (into one extent for an index)? Anyone? Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Patricia Ashe [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, April 24, 2001 3:47 PM To: Multiple recipients of list ORACLE-L Subject: RE: REP: Locally Managed Tablespaces I am doing some research on locally managed tablespaces prior to using them. Looking for problems, etc. This is the first negative response I have heard about them. Perhaps, storage parameters were specified on the create table? Patricia -Original Message- Sent: Tuesday, April 24, 2001 12:52 PM To: Multiple recipients of list ORACLE-L A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: REP: Locally Managed Tablespaces
I haven't tried it, but I recall hearing that all extents really are 1M, it is just the data dictionary displays that are confusing. If you create a table with initial=next=1.5M, I believe dba_tables will show the create parameters, while dba_extents will show the actual extent sizes. I don't have a good test database right now to try this. Maybe I have the dictionary tables wrong (I guess I could check their sources). Henry -Original Message- Sent: Tuesday, April 24, 2001 4:47 PM To: Multiple recipients of list ORACLE-L I am doing some research on locally managed tablespaces prior to using them. Looking for problems, etc. This is the first negative response I have heard about them. Perhaps, storage parameters were specified on the create table? Patricia -Original Message- Sent: Tuesday, April 24, 2001 12:52 PM To: Multiple recipients of list ORACLE-L A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: The Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: REP: Locally Managed Tablespaces
Isn't it also in sys.uet$? Oops, that's for dictionary managed, not locally managed. You're right. But isn't there a way to see locally managed extents? Henry -Original Message- Sent: Tuesday, April 24, 2001 5:19 PM To: [EMAIL PROTECTED]; Henry Poras Henry, The extent info does not come from the DD, it is in the datafile header. Jared On Tuesday 24 April 2001 14:45, Henry Poras wrote: I haven't tried it, but I recall hearing that all extents really are 1M, it is just the data dictionary displays that are confusing. If you create a table with initial=next=1.5M, I believe dba_tables will show the create parameters, while dba_extents will show the actual extent sizes. I don't have a good test database right now to try this. Maybe I have the dictionary tables wrong (I guess I could check their sources). Henry -Original Message- Sent: Tuesday, April 24, 2001 4:47 PM To: Multiple recipients of list ORACLE-L I am doing some research on locally managed tablespaces prior to using them. Looking for problems, etc. This is the first negative response I have heard about them. Perhaps, storage parameters were specified on the create table? Patricia -Original Message- Sent: Tuesday, April 24, 2001 12:52 PM To: Multiple recipients of list ORACLE-L A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: REP: Locally Managed Tablespaces
Henry, The extent info does not come from the DD, it is in the datafile header. Jared On Tuesday 24 April 2001 14:45, Henry Poras wrote: I haven't tried it, but I recall hearing that all extents really are 1M, it is just the data dictionary displays that are confusing. If you create a table with initial=next=1.5M, I believe dba_tables will show the create parameters, while dba_extents will show the actual extent sizes. I don't have a good test database right now to try this. Maybe I have the dictionary tables wrong (I guess I could check their sources). Henry -Original Message- Sent: Tuesday, April 24, 2001 4:47 PM To: Multiple recipients of list ORACLE-L I am doing some research on locally managed tablespaces prior to using them. Looking for problems, etc. This is the first negative response I have heard about them. Perhaps, storage parameters were specified on the create table? Patricia -Original Message- Sent: Tuesday, April 24, 2001 12:52 PM To: Multiple recipients of list ORACLE-L A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: REP: Locally Managed Tablespaces
Sorry, I've been using LMT since they came out. I'll have to see hard evidence and a reproducible test case. Jared On Tuesday 24 April 2001 12:52, The Oracle DBA wrote: A couple of threads here and in other lists have mentioned this. A person puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they notice that not all extents are 1M. Thanks, Earl --- TheOracleDBA [EMAIL PROTECTED] On Mon, 23 Apr 2001 11:01:37 Jared Still wrote: On Monday 23 April 2001 11:40, The Oracle DBA wrote: Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Do you have a source for this information? A number of us have been using LMT's with uniform extents, and have not seen that. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: REP: Locally Managed Tablespaces
On Tuesday 24 April 2001 14:24, Henry Poras wrote: Isn't it also in sys.uet$? Oops, that's for dictionary managed, not locally managed. You're right. But isn't there a way to see locally managed extents? Yes, they show up in dba_extents, but it doesn't come from sys.uet$. Here's the view as of 8.1.7. Extents in LMT's are retrieved by the second SELECT statement in the SQL. select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, ds.tablespace_name, e.ext#, f.file#, e.block#, e.length * ds.blocksize, e.length, e.file# from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f where e.segfile# = ds.relative_fno and e.segblock# = ds.header_block and e.ts# = ds.tablespace_id and e.ts# = f.ts# and e.file# = f.relfile# and bitand(NVL(ds.segment_flags,0), 1) = 0 union all -- get the LMT extents select /*+ ordered use_nl(e) use_nl(f) */ ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, ds.tablespace_name, e.ktfbueextno, f.file#, e.ktfbuebno, e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f where e.ktfbuesegfno = ds.relative_fno and e.ktfbuesegbno = ds.header_block and e.ktfbuesegtsn = ds.tablespace_id and e.ktfbuesegtsn = f.ts# and e.ktfbuefno = f.relfile# and bitand(NVL(ds.segment_flags, 0), 1) = 1 / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: REP: Locally Managed Tablespaces
And just who is it that told you that? [EMAIL PROTECTED] 04/23/01 02:40PM Are you using uniform extents? We are told they are not really = and actually are not the same at all over time. Thanks, --- TheOracleDBA [EMAIL PROTECTED] On Thu, 19 Apr 2001 15:25:31 Peter Barnett wrote: We are using LMTs with several of our databases. No problems. With these databases we are also running large file systems. Although our OS imposes a theoretical limit of 31.G, we have kept our datafiles to 20G or less. Again, no problems. Pete Barnett Oracle Database Administrator Regence BlueCross BlueShield [EMAIL PROTECTED] On Tue, 17 Apr 2001, Patricia Ashe wrote: I am interested in some statistics on Oracle locally managed tablespaces. I have been looking for any bugs or negative info about them. Are they in use at alot of sites? Seems like all the information I have come across is positive. Which is great! But maybe they aren't being used at alot of sites. Can I hear about experiences from others on this list? how many sites are actually using them? I have several databases that I am getting ready to go production soon and would like to create the tablespaces as locally managed, but need more statistics. PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: The Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
